Azure Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server

Question

I have SQL server & web app hosted over Microsoft Azure platform. I am constantly getting above error when ever i run web application (and try to connect azure sql server) from my local network, however i never face issue on azure web app service (i.e. After hosting my web app on azure)

I know that this error is very familiar. But in this case it seems weird. I have already checked below things,

1. SQL connection is managed properly. (I am using ADO.Net)
2. All gateway IPs are white listed against azure sql.
3. No firewall is blocking my private network.

Any help will be appreciated.

Biplane

this is because of remote connection is not enable in sql server, I am posting answer with details – Scabious

is the sql server in a azure virtual machine ? – Scabious

@Scabious forgot to add it in list but it is already enabled. It's not in virtual machine. – Biplane

what tier of sql azure are you using ? if it is free it may sometimes be throttled due to more traffic – Delsiedelsman

@Aravind: Standard: S0, which is good i guess. – Biplane

okay. this generally shows up when there is a firewall issue. are you able to login to the db using SQL management studio ? – Delsiedelsman

@Aravind: I am able to login through SQLMS. In both case, this error fires frequently. – Biplane

Is your connection string using SSL? – Originative

You need to add "tcp: " in front of server name. I.e. tcp:<servername>.database.windows.net – Photocopier

@Photocopier - it works, thanks. Any specific reason for this? – Biplane

Answer 1

From Mihaela's comment: You need to add "tcp: " in front of server name. I.e. tcp:<servername>.database.windows.net

---

EDIT:

This is specified in the [documentation] (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx)

Data Source must use the TCP format or the Named Pipes format.

TCP format is as follows:

•tcp:\

•tcp:,

The TCP format must start with the prefix "tcp:" and is followed by the database instance, as specified by a host name and an instance name.

The host name MUST be specified in one of the following ways:

•NetBIOSName

•IPv4Address

•IPv6Address

The instance name is used to resolve to a particular TCP/IP port number on which a database instance is hosted. Alternatively, specifying a TCP/IP port number directly is also allowed. If both instance name and port number are not present, the default database instance is used.

Adz

Any specific reason for this? – Biplane

It may be attempting to default to a named pipes format, which would not resolve correctly with Azure. Thus, it's best to follow the documentation which says '"must start with the prefix 'tcp:' " :) – Adz

Might be a old one. But in the documentation it says TCP format is applicable when connecting to Azure SQL databases. It says - "The TCP format must start with the prefix "tcp:" and is followed by the database instance, as specified by a host name and an instance name. This format is not applicable when connecting to Azure SQL Database. TCP is automatically selected for connections to Azure SQL Database when no protocol is specified." – Rebarebah

Answer 2

you need to white your IP address for same, SQL azure will need to know the your Application IP

Kesley

Can you please put how to white list it? – Calci

@NickGallimore you can white list goto db-server on azure and goto set firewall rules option you can see add ip options there . – Avidin

Answer 3

In my case this error happened in an Azure Web App, I only restart the Azure Web App and working

Beleaguer