Connect from Azure to an SQL Server Named Instance

In some situations you can't or don't want to move all your data completely to the cloud. Be it to connect to your existing infrastructure, a company policy, to remain multi-tenant or simply when migrating slowly step by step. Common to these cases is often the requirement to synchronize with or connect from Azure to some local or offsite SQL Server database. For synchronization you may want to try the Microsoft Sync Framework. This post is about the other option: connecting to an external named SQL Server instance.

Connecting to Named SQL Server Instances

In addition to its own storage options like SQL Azure and Azure Table Storage, Azure also allows you to connect to external SQL Servers over TCP/IP. However, there's a pitfall right now when using named SQL Server instances:

System.Data.SqlClient.SqlException:
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)
source

Provided your connection string is correct, this is likely to be an issue with how SQL server finds your named instance.

Instance Resolution using SQL Server Browser

Since SQL Server 2005, the SQL Server Browser service is responsible for enumerating available instances on a machine, and to resolve instance names to the actual named pipe or TCP port (for SQL Server 2000 it was the SQL Server Resolution Protocol).

In order to resolve the TCP port of a named instance, the client sends an UDP datagram to port 1434, to which the server browser replies with another datagram listing the instance endpoint to which the client then connects to. Thanks to this mechanism it is no longer required to have the server listen on the standard SQL server TCP port 1433, so it can fully support multiple (named) instances. In fact, the default for named instances is to use a dynamic random TCP port.

Azure vs. SQL Server Browser

When connecting from Azure this resolution mechanism fails, simply because the UDP datagrams never reach their target (this may change in the future). So there's no way the client can find the actual probably random TCP port to connect to, and will throw the SqlException cited above.

Solution

To work around this issue, you can configure your named instance to listen on a static TCP port instead of randomly selecting a new dynamic one on every restart (related kb). You can then specify this static port directly in the connection string in your Azure worker role:

1: 
2: 

Data Source={domain/ip},{port};Network Library=DBMSSOCN;
Initial Catalog={dbname};User ID={user};Password={pw}

Note that in this case there's no need to specify the name of the instance in the connection string. The network library parameter tells the client to use TCP/IP instead of e.g. Named Pipes.