question

GS-4786 avatar image
0 Votes"
GS-4786 asked KranthiPakala-MSFT edited

Azure Data Factory - SQL connectivity error: Cannot connect to SQL database: `Error Code:40615` , Error Message: Cannot open server - requested by the login. Client with IP address ;xx.xxx.33.145; is not allowed to access the server

While trying to establish connection from Azure Data factory to Azure SQL, getting below error message. Please assist...

Error code=40615

Error Message: {'StatusCode':'DFExecutorUserError','Message':'Job failed due to reason: at Source 'source1': java.lang.RuntimeException: Cannot connect to SQL database: 'jdbc:sqlserver://sql-xxxserver.database.windows.net;database={xxxManage}', 'User: xxxtransition'.[SQL Exception] Error Code:40615 , Error Message: Cannot open server 'sql-xxxerver' requested by the login. Client with IP address 'xx.xxx.33.145' 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.

azure-data-factoryazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KranthiPakala-MSFT avatar image
2 Votes"
KranthiPakala-MSFT answered

Hi @GarrySingh-4786,

Welcome to Microsoft Q&A forum and thanks for reaching out. In addition to @NandanHegde-7720's response, please see below related information:

By looking at the error message and the error code, 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.

Hope this information helps. Do let us know if you have further query.



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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered

Hey, there can be multiple reasons for that.some if them are as below:
1) check whether the allow azure service property is enabled in Azure SQL server.
If not then you would have to white list the IP range of the region in which the ADF is created
2) in case if your source or sink is leveraging an integration runtime,then the azure database must also be accessible from the IR

Can you please provide the source and sine details of your adf

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.