Sunday, August 22, 2021

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 .


Incorrect SQL Server Instance Name

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.


No comments:

Post a Comment

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