Saturday, August 21, 2021

Cannot open server 'xxx' requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the server

Error/Issue:


While I was trying to create a Linked Service for Azure based SQL database, upon clicking on Test Connection, got below error

Client with IP address is not allowed to access the server


                              

   

Cannot connect to SQL Database: 'demosqlserveradf.database.windows.net', Database: 'ADFDemo', User: 'adfdemouser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. Cannot open server 'demosqlserveradf' requested by the login. Client with IP address '52.140.104.134' 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: e6201b40-a98f-4d76-98b0-8fc724c0eb00.






Why it happened


As the error says the connection is not established and three  probable reasons as to why it happened, let's see which is the actual reason

1.SQL Database firewall allows the integration runtime to access.  -- The firewall settings has Deny public network access set to No, thus access over internet is possible
2.Cannot open server 'demosqlserveradf' requested by the login. -- Credentials to open Server is correct (using SSMS it works) 
3.Client with IP address '52.140.104.134' is not allowed to access the server -- Here it says client is not allowed and this is the actual reason and it is because (highlighted in below image)

                       Allow Azure services and resources to access this server is set to No 


Allow Azure services and resources to access this server





What to do


Just moving the radio button from No to Yes against setting - 

Allow Azure services and resources to access this server .



Learn More about some more Azure Data Factory errors



No comments:

Post a Comment

If you have any suggestions or questions or want to share something then please drop a comment