Cannot connect to SQL Database:'XXX' . Verify that the instance name is correct

Error/Issue:

While creating linked service to connect to Azure SQL Database, after configuring clicked on Test connection and got below error

Cannot connect to SQL Database: 'demosqlserveradf', Database: 'ADFDemo', User: 'adfdemouser'. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server), SqlErrorNumber=53,Class=20,State=0, The network path was not found Activity ID: 0ebaa64f-d5b7-48bb-a9f9-21e1fdac28a3.

                             

Why it happened

It was silly mistake, and as stated in error message(highlighted in red above) the SQL Server Instance name was incorrect.

As can be seen in below image, instance name is without domain, thus the resolution of it doesn't happen and as no specific address was resolved(ip address) thus linked service couldn't connect .

What to do

Just need to provide full name of SQL Server i.e. SQL Server Instance name with domain (azure provided or custom domain - if you have one)

            demosqlserveradf.database.windows.net

Doing this fixes the issue, connection was established  - as now when linked service looks up for above it gets the resolved address.

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 !!!!!!

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 open server 'xxx' requested by the login. Client with IP address 'x.x.x.x' is not allowed to access the server
• Operation on target Copy data failed: Failure happened on 'Source' side