locked
What means 'Allow Azure services and resources to access this server' RRS feed

  • Question

  • Hi all,

    When I create a DB/DW I get the option 'Allow Azure services and resources to access this server'.

    I have some questions about this:

    1. It seems to be necessary to enable this to give ADF and AAS and Power BI the ability to connect this database. But what does this mean? Am I enabling all IP's from whole Azure to connect the DB (off course I can understand they can't connect because of lacking the correct credentials)? Or is this only meant for services like ADF or also for all the VM's that are created in Azure by anyone?
    2. Can I achieve ADF, AAS, Power BI and other services to connect to my DB without enabling this option? I couldn't do it untill now because of the continous changing IP's
    3. Is there a way to enable everything in your own subscription but not outside? And if so; what to do with Power BI?

    I hope somebody can help me understand this a little more.

    Friday, May 22, 2020 12:02 PM

All replies

  • These two things that I have noted below apply in your case, These would solve all your questions. What you face is a firewall issue.

    Server-level IP firewall rules
    These rules enable clients to access your entire Azure SQL server, that is, all the databases within the same SQL Database server. The rules are stored in the master database. You can have a maximum of 128 server-level IP firewall rules for an Azure SQL Server. If you have the Allow Azure Services and resources to access this server setting enabled, this counts as a single firewall rule for Azure SQL Server.

    You can configure server-level IP firewall rules by using the Azure portal, PowerShell, or Transact-SQL statements.

    To use the portal or PowerShell, you must be the subscription owner or a subscription contributor.
    To use Transact-SQL, you must connect to the SQL Database instance as the server-level principal login or as the Azure Active Directory administrator. (A server-level IP firewall rule must first be created by a user who has Azure-level permissions.)
    Database-level IP firewall rules
    These rules enable clients to access certain (secure) databases within the same SQL Database server. You create the rules for each database (including the master database), and they're stored in the individual database.

    You can only create and manage database-level IP firewall rules for master and user databases by using Transact-SQL statements and only after you configure the first server-level firewall.

    If you specify an IP address range in the database-level IP firewall rule that's outside the range in the server-level IP firewall rule, only those clients that have IP addresses in the database-level range can access the database.

    You can have a maximum of 128 database-level IP firewall rules for a database.

    Friday, May 22, 2020 12:19 PM
  • Hi @MSTGuy,

    Thank you for your reply. To be honoust you answer doesn't solve my question.

    I understand from you that you can set firewall settings on server or db level. That's clear, no questions about that. Both can have 128 IP rules and at server level you can enable Azure services/resources as 1 of these 128. So far clear.

    But what does this answer for my questions? My questions are not solved I guess;

    1. It's still not clear to me what enabling the Azure services means. Am I enabling all IP's from whole Azure to connect the DB (off course I can understand they can't connect because of lacking the correct credentials)? Or is this only meant for services like ADF or also for all the VM's that are created in Azure by anyone?
    2. Can I achieve ADF, AAS, Power BI and other services to connect to my DB or server without enabling this option?
    3. Is there a way to enable everything in your own subscription but not outside your subscription? And if so; what to do with Power BI?

    Friday, May 22, 2020 1:34 PM
  • If you enable “Allow access to Azure services”, it would allow any traffic from resources/services hosted in Azure (not just your Azure subscription) to access the database.

    For more details, please refer https://docs.microsoft.com/en-us/archive/blogs/azureedu/what-should-i-know-when-setting-up-my-azure-sql-database-paas


    - Vaibhav Gujral

    Friday, May 22, 2020 1:59 PM
  • Yes, that's what I've been reading. But when I want to do things with Azure services like ADF I have to enable this option. Right?

    But when I enable this do I also enable this for all VM's in Azure? And can I take care of not enabling whole Azure but my own ADF and AAS?

    Friday, May 22, 2020 2:25 PM
  • Unfortunately, if you are going to use this option, it will enable for all the azure services including Azure VMs. I suggest looking into the possibility of integrating your databases with a VNet.

    - Vaibhav Gujral

    Friday, May 22, 2020 9:22 PM
  • There is an option to add IP ranges for specific services for a given region if you want to narrow the scope down to a limited range. In some cases you can add the service tags (Azure Firewall for example) and when there is an IP change, there is no need to update the IP address itself. If you use explicit IP addresses, you will need to update these values in the IP Firewall for your database instance. Azure SQL (logical) server does not support service tags. 

    Download Azure IP Ranges and Service Tags – Public Cloud

    Please let us know if you have additional questions.

    Regards,

    Mike

    Tuesday, May 26, 2020 2:49 AM
  • There is an option to add IP ranges for specific services for a given region if you want to narrow the scope down to a limited range. In some cases you can add the service tags (Azure Firewall for example) and when there is an IP change, there is no need to update the IP address itself. If you use explicit IP addresses, you will need to update these values in the IP Firewall for your database instance. Azure SQL (logical) server does not support service tags. 

    Download Azure IP Ranges and Service Tags – Public Cloud

    Please let us know if you have additional questions.

    Regards,

    Mike

    So you have to enable and maintain IP-ranges. And still you are not rocksolid I think? Everyone using that services from that region (lets say every ADF user in West-Europe) can connect to our DB.

    Unfortunately, if you are going to use this option, it will enable for all the azure services including Azure VMs. I suggest looking into the possibility of integrating your databases with a VNet.

    - Vaibhav Gujral

    I've done this and also tried to understand the private endpoints. But it sounds not really great to me. You need VM to self host the Integration Runtime for Data Factory and gateways for Analysis Services and Power BI if I understood this correct.

    How are you guys all doing this? Are we to strict here you think? Or is there a good solution that I forgot??


    Tuesday, May 26, 2020 10:43 AM