While I was trying to create a Linked Service for Azure based SQL database, upon clicking on Test Connection, got below error
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
What to do
Just moving the radio button from No to Yes against setting -
Allow Azure services and resources to access this server .
