Unable to connect to SQL Azure from Management Studio 2008 R2

Question

Hi

I have created a database on SQL Azure and configured firewall as mentioned at the URL: http://msdn.microsoft.com/en-us/library/ee621783.aspx. However, when I try connecting to my database from management studio (I am using Windows7), I get the following error:

TITLE: Connect to Server
------------------------------

Cannot connect to xxxxx.database.windows.net.

------------------------------
ADDITIONAL INFORMATION:

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) (Microsoft SQL Server, Error: 53)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

------------------------------
Error Number: 53
Severity: 20
State: 0


------------------------------
Program Location:

  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
  at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
  at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
  at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
  at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
  at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
  at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
  at System.Data.SqlClient.SqlConnection.Open()
  at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
  at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

Please note that I have also done the following things:

1. Opened TCP port in firewall (Inbound Rule) as mentioned at URL: http://msdn.microsoft.com/en-us/library/ms175043.aspx
2. When I do telnet command : telnet xxxx.database.windows.net 1433 I get the following message:

   Connecting To xxxx.database.windows.net...Could not open connection to the host, on port 1433: Connect failed

Please advise what I should do?

Thanks

Answer 1

I'd double check your SQL Azure firewall setup (maybe temporarially give everything access) as well as see if there's another firewall between you and the server (maybe on your company network).

Answer 2

I am behind my company's firewall and I enabled access to ports 1433 and 1434. Is there anything more that I need to do? Also for information I am using SQL Server 2008 R2. I hope this is ok for connecting to SQL Azure.

Answer 3

the Server 2008 R2 management studio will connect to SQL Azure just fine. From the error you're getting, there still appears to be something blocking the connection. if you can, have someone you trust that's not within your network try and connect to it. Be sure to configure the SQL Azure firewall to allow them to connect.

Answer 4

It appears you are attempting to connect using Named Pipes (Named Pipes Provider, error: 40). SQL Azure only supports TCP connections. Make sure you either disable Named Pipes as a communication link, or add tcp: in front of your server name. That should do the trick. 

---

Herve Roggero - SQL Server Firewall and Auditing Consultant

Answer 5

Do you

1) establish direct connection to internet or

2) connect it to some proxy server / TGM / ISA server?

If it's option 2, you will need to ask your IT admin to open up the SQL Azure IP and port 1433 for you.

Answer 6

It appears you are attempting to connect using Named Pipes (Named Pipes Provider, error: 40). SQL Azure only supports TCP connections. Make sure you either disable Named Pipes as a communication link, or add tcp: in front of your server name. That should do the trick. 

---

Herve Roggero - SQL Server Firewall and Auditing Consultant

I disabled NamedPipes and also added tcp: in front of the server name. This time I got the below error:

===================================

Cannot connect to tcp:xxxxx.database.windows.net.

===================================

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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

------------------------------
Error Number: 10060
Severity: 20
State: 0


------------------------------
Program Location:

  at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
  at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
  at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
  at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
  at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
  at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
  at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
  at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
  at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
  at System.Data.SqlClient.SqlConnection.Open()
  at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

I am behind my company's firewall. So I disabled it : Start > Run > Firewall.cpl > Turn Windows Firewall on or off . I turned it off in all the domains (domain network, home, public)

But still when I give the telnet command: telnet xxxxx.database.windows.net 1433 I get the error message:

Could not open connection to the host, on port 1433: Connect failed

I even added port 1433 in inbound and outbound proxy (when we give the wf.msc command) but no luck :(

Answer 7

If it's option 2, you will need to ask your IT admin to open up the SQL Azure IP and port 1433 for you.

The problem is solved. The IT folks opened up the port in COmpany's firewall and now I am able to connect. thanks for the information.

Answer 8

 

I would check the following. The name pipe is not really a valid choice. It was shown in the error because by default, the Management Studio tries Namepipe after TCP fails (unless you specify tcp only).

1) SQL Azure Firewall is open to your address

2) Your firewall is not blocked (corporate especially)

 

---

Thanks, Scott Ma Schlumberger IT & Software Community Leader. MS SME for Entity Framework and Windows Azure Platform.

Answer 9

Install "Forefront TMG Client" to resolve SQL Azure DB connection issue

Answer 10

A couple other notes to add on to the knowledge of this error message. 

I ran into this problem this morning and the root cause was that I didn't shutdown a VPN connection I had going.  

After I stopped the VPN connection I still was not able to connect because the Azure IP firewall number which I had updated was not really what SQL Server reported in the error message.   I updated the Azure FW with that IP number and was able to connect.  

Tony

 

 

Answer 11

The windows firewall on the server is not allowing TCP connection to port 1433..  Go to firewall and creat new rule that allow port to be open for Private and Public.  This will solve your connection issue.

Answer 12

Recheck the incoming rule in the firewall settings.

Do you have any proxy clients like (TMG/ISA) installed on the machine?
If not, try installing Proxy client based on the recommendations from your company's network administrator.

If you have already installed it and still getting issue, then either internet connectivity or firewall rules on sql azure server can be a possible issue. It might be possible that the ip range specified do not accommodate the IP for your server. 

Also, to isolate this issue, try connecting from your local/home pc  and allow temporarily all IPs in the firewall rules of your azure DB.

---

Arindam Basu