Connection Problems When Deploying SQL DB To Azure DB

Question

I have created a new Azure account to publish my web app + DB.
I have created the app service, service plan, SQL Server and SQL DB,
I have also configured the Azure sql server firewall to accept request from my client IP.

WHAT'S WORKING
When I'm using the connection string for the Azure DB in my local server explorer (In Visual Studio and SQL Server Management Studio) I can see the server and the DB and all is OK. TEST CONNECTION also works fine.
Also - I can publish my web app to Azure and it is successful.

WHAT' NOT WORKING

Only when I am trying to publish/deploy my local DB to Azure I keep getting the same connection error:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I Get this message either when trying to connect from the Visual Studio DB Publish Window, And also when trying to export the data directly from SSMS using BACPAC...

Is there any way to fix this?

Answer 1

Hello,

The error (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) appear mostly while trying to connect to a SQL Server named instance and not when you use default instance. The client stack could not receive SSRP response UDP packet from SQL Browser.

Some fast steps to troubleshoot this error:

Recheck the server name for any error and fix it
Check the instance name for any error
Check if SQL Server browser is running
Control if this instance exist on server
Ping the server and check if DNS can be resolved correctly

Also you can follow the following article here to troubleshoot the error step by step.