{"id":38221,"date":"2020-01-14T06:00:11","date_gmt":"2020-01-14T13:00:11","guid":{"rendered":"http:\/\/devblogs.microsoft.com\/premier-developer\/?p=38221"},"modified":"2020-01-07T20:06:13","modified_gmt":"2020-01-08T03:06:13","slug":"secure-access-to-azure-sql-servers-for-power-bi","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/premier-developer\/secure-access-to-azure-sql-servers-for-power-bi\/","title":{"rendered":"Secure Access to Azure SQL Servers for Power BI"},"content":{"rendered":"

Premier Developer Consultant Jean Hayes<\/a> outlines a strategy for controlling access to Azure SQL Servers used by Power BI.<\/p>\n

\n

When provisioning an Azure SQL Server for Azure SQL DB or Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse), organizations can allow all or no access from other Azure resources. This configuration setting is available on the Firewall and virtual networks<\/strong> settings on the Azure SQL server:<\/p>\n

<\/p>\n

When using Azure SQL DB or Azure Synapse as a Power BI data source, configuring this setting to \u201cON\u201d allows the Power BI service to refresh data from the server\u2019s databases without configuring a gateway. However, it allows traffic from all Azure resources and services to your database(s), not just resources in your Azure subscription. Database credentials restrict access to your databases, but the inability to restrict traffic to only your Azure resources may not be acceptable to some organizations.<\/p>\n

So how do you refresh Power BI datasets in the Power BI service from Azure SQL server resources while restricting traffic to specific resources? By using the On-Premises Data Gateway and SQL endpoints. This allows you to lock down traffic to Azure SQL resources by setting \u201cAllow Azure services and resources to access this server\u201d to OFF, and use the gateway to provide secure access between Power BI and Azure SQL database. This is similar to the way you would provide Power BI access to on-premises SQL Server instances.<\/p>\n

This requires the ability to create and configure a VM in Azure and to configure data gateways in the Power BI service.<\/p>\n

\"A<\/p>\n

The steps involved are:<\/p>\n

    \n
  1. Provisioning the VM in a VNet. See recommendations and requirements for the gateway server.<\/a><\/li>\n
  2. Adding a Service endpoint<\/a> on your VNet for SQL Server<\/li>\n<\/ol>\n

    \"A<\/p>\n

      \n
    1. Adding an Outbound Rule on the Network Security Group to allow outbound traffic to the Power BI Service. The allowed destination port ranges are 443, 5671, 5672, and 9350-9354.<\/li>\n<\/ol>\n

      \"A<\/p>\n

      \"A<\/p>\n

      \"A<\/p>\n

        \n
      1. Installing the On-Premises Gateway on the VM<\/a><\/li>\n
      2. Configuring the gateway in the Power BI Service<\/a> as you would for an on-premises SQL Server instance.<\/li>\n<\/ol>\n

        And that\u2019s it! You can now access your Azure SQL server resources to Power BI while limiting traffic to other Azure resources.<\/p>\n","protected":false},"excerpt":{"rendered":"

        How do you refresh Power BI datasets in the Power BI service from Azure SQL server resources while restricting traffic to specific resources? Learn how using the On-Premises Data Gateway and SQL endpoints can help you tighten security.<\/p>\n","protected":false},"author":582,"featured_media":37933,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[25,8,1],"tags":[24,3],"class_list":["post-38221","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure","category-data","category-permierdev","tag-azure","tag-team"],"acf":[],"blog_post_summary":"

        How do you refresh Power BI datasets in the Power BI service from Azure SQL server resources while restricting traffic to specific resources? Learn how using the On-Premises Data Gateway and SQL endpoints can help you tighten security.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/38221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/users\/582"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/comments?post=38221"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/posts\/38221\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media\/37933"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/media?parent=38221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/categories?post=38221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/premier-developer\/wp-json\/wp\/v2\/tags?post=38221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}