Connecting to remote SQL Server 2008 from Windows

Question

I am trying to connect to a SQL Server 2008 instance from a Windows Azure worker role (it's not SQL Azure, but a remotely hosted SQL Server 2008 Standard Edition), but I get the following error message

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) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at ...

The code snippet is the following:

using (var connection = new SqlConnection("MY CONNECTION STRING")
{
  connection.Open();
  var command = new SqlCommand("Select 1", connection);

  try
  {
     var res = command.ExecuteScalar();
  }
  catch (Exception e) {
     Log.Log("error", e.ToString());
  }
}

When I run the snippet from my local command-line (directly accessing the remote SQL Server), it works OK, yet when run from Windows Azure, I end-up with the error message outline here above. In order to make sure there were no problem with the connection string sure, I re-try by hardcoding the string in the source code pushed toward Azure (just to be 100%, but I am still hitting the very same problem).

I have no firewall setup on the SQL Server 2008 instance, and I running out of ideas.

Can someone spot what I am doing wrong here?

Answer 1

Can you control your Azure instance through Remote Desktop?

If you can, log on, and try:

telnet yoursqlserverhostname 1433

If this blocks, you know that the network is not configured correctly. Usually a firewall issue, on either host or the network in between.

Answer 2

I've noticed some issues with the SQL Server Resolution Protocol (or the SQL Server Browser) from Azure worker roles when connecting to external non-Azure SQL Server instances running on ports other than 1433. In these cases it can help to explicitly specify the TCP port of the instance in the connection string:

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

See also Connect from Azure to an SQL Server Named Instance