Error/Issue:
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 .
If you have questions or suggestions, feel free to do in comments section below !!!
Do share if you find this helpful ....... Knowledge Sharing is Caring !!!!!!
Knowledge Sharing is Caring !!!!!!
Learn More about some more Azure Data Factory errors
- Cannot find the object "xxx" because it does not exist or you do not have permissions.
- The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'.
- ErrorCode=UserErrorInvalidColumnMappingColumnCountMismatch,Message=The column count in column mappings is more than the count in source/sink table.
- ErrorCode=UserErrorInvalidColumnMappingColumnNotFound,Column 'xxx' specified in column mapping cannot be found in source data.
- The path 'xxx' specified in the add operation already exists. Please specify a new path
- Cannot create Sql Source. Error: The value of the property 'Value' is invalid for the stored procedure parameter 'XXX'
- Cannot connect to SQL Database:'XXX' . Verify that the instance name is correct
- Operation on target Copy data failed: Failure happened on 'Source' side
Tags:
Azure Data Factory Error