Cannot connect to SQL Database.

Question

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '[redacted].database.windows.net', Database: '[redacted]', User: '[redacted]'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Cannot open server '[redacted]' requested by the login. Client with IP address '[redacted]' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect., SqlErrorNumber=40615,Class=14,State=1,
Activity ID: 731d40a7-1bbf-46b0-8a9a-c4f29cf88ff8.

Answer 1

Hi @Dzidefoalamenu-8541,

Thank you for the ask and posting the question on MS Q&A Portal.

As the error says, we need to let the IP address access the database by creating a firewall rule.

There are lot of ways we can do this.

1. In Azure portal, at Azure SQL Server level
2. In Azure portal, at Azure SQL database level
3. Using SQL procedure.

Request you to check this page for the detailed steps.

Hope it helps. Let us know how it goes.

Answer 2

Below could be the possible root case and resolution for the issue.

Error code=40615

• Possible root cause:
  ADF Azure IR unable to connect to Azure SQL server with firewall enabled. IP address mentioned in the error message has not been whitelisted.
• Possible resolution/workaround:
  a). As the Azure IR IP Ranges gets updated weekly basis, new Azure IP range of the region should be whitelisted if this error is noticed. For more info about Azure IR IP range please refer to this doc : Azure Integration Runtime IP addresses: Specific regions
  b). Another option is to Turned OFF Allow all Azure Services in SQL Server firewall Settings. In case if your corporate policy don’t allow you to turn off Allow Azure Services in SQL Server Settings, then you will have to create Azure IR within managed Virtual Network and establish a Private Endpoint connection from ADF to Azure SQL Server and then used Managed VNET Integration Runtime to run the pipeline.

Additional information:
Here are couple of helpful resources related to connectivity issues and ADF Managed Virtual Network with Private Endpoint.

• Troubleshooting connectivity issues and other errors with Azure SQL Database and Azure SQL Managed Instance
• Azure SQL Database and Azure Synapse IP firewall rules
• Managed Virtual Networks and Private Endpoints in Azure Synapse and Azure Data Factory

Hope this helps.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.