Windows Azure SQL Database Connectivity Troubleshooting Guide

Windows Azure SQL Database Connectivity Troubleshooting Guide

The information provided in this article are intended to help troubleshoot some of the common connectivity error messages that you would see while connecting to Windows Azure SQL Database.

Important Note:  To troubleshoot common connectivity issues with SQL Azure database, check the recently published Troubleshoot connection problems to Azure SQL Database guide walk through at  http://support.microsoft.com/kb/2980233/en-us


Note: For more information on handling connection-losses while your application runs, see Connection Management in Windows Azure SQL Database.

 Note
If you wish to contribute to this page, use the Edit tab at the top (sign-in required). If you wish to provide feedback for this documentation please either send e-mail to azuredocs@microsoft.com or use the Comment field at the bottom of this page (sign-in required).

 

Common Connectivity Errors

  • A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
  • System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.
  • An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections
  • Error: Microsoft SQL Native Client: Unable to complete login process due to delay in opening server connection.
  • 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.

Return to Top


Verify SQL Server Management Studio Version

If you can not connect to SQL Database from SQL Server Management Studio (SSMS), it could be the SSMS version. The SQL Server Management Studio from SQL Server 2008 R2 with SP1 and SQL Server 2008 R2 Express with SP1 can be used to access, configure, manage and administer SQL Database. Previous versions of SQL Server Management Studio are not supported.

The links to get the latest updates available in SQL Server 2008 R2 SP1 are:

Return to Top


Verify Azure Firewall Settings and Service Availability

When a computer attempts to connect to your SQL Database server from the
Internet, the SQL Database firewall checks the originating IP address of the
request against the full set of firewall settings. If the IP address of the
request is not within one of the ranges specified, the connection attempt is
blocked and does not reach the SQL Database server. Being blocked by the SQL
Azure firewall is usually self evident because the service returns a
specific error message similar to the one below:

C:\>SQLCMD –U<user>@<server> -P<password> -S<server>.database.windows.net

Cannot open server 'ljvt54v9zo' requested by the login. Client with IP address '131.107.0.111' is not allowed to access the server. To enable access, use the SQL Database Portal or run
sp_set_firewall_rule on the master database to create a firewall rule
for this IP address or address range. It may take up to five minutes for
this change to take effect.

Note: SQL Azure Portal has been replaced by Windows Azure Platform Management Portal.

To ensure that the SQL Database firewall is correctly configured, perform
the following steps.

  1. Login to Windows Azure Platform Management Portal, select your SQL Database server and then click Firewall Rules. Verify that the SQL Database firewall is
        configured to allow remote connections from the IP address(es) that you
        will be connecting from:
       

    Reference: Windows Azure SQL Database Firewall - http://msdn.microsoft.com/en-us/library/ee621782.aspx

  2. On the Databases tab of your portal, select the database that you are trying to access and click the Test Connectivity button. This will prompt for your username and password to use when connecting.
    1. If the connection succeeds ,the SQL Database services are available
              and your server and database is online. Proceed to the steps in Verify that You Can Reach the Server IP.
    2. If the connection fails, note the Location listed for your server and check the SQL Database service status for this location
              in the Azure Services Dashboard. In the event of a known outage you will be able to find more information there. If the dashboard does not reflect an outage you may call Microsoft at 1-800- 642-7676 to report the issue and obtain assistance.

    Note: If the Microsoft Services firewall isn’t enabled you must enable it and wait up to 5 minutes for the firewall setting to take effect. If you attempt to test connectivity without waiting for 5 minutes, you will continue to receive the error until the firewall setting takes effect.

    Reference: How and when to contact Microsoft Customer Service and Support - http://support.microsoft.com/kb/295539

Return to Top


Verify That You Can Reach the Server IP

  1. Open a command prompt window and use the PING command to confirm that name resolution successfully translates your logical server name to an IP address. The “Request timed out” message shown below is expected because SQL Database will never respond to ping requests.
       

    C:\>ping <myserver>.database.windows.net

    Pinging data.sn1-1.database.windows.net [65.55.74.144] with 32 bytes of data:

    Request timed out.

    Request timed out.

    Request timed out.

    Request timed out.

    Ping statistics for 65.55.74.144:

    Packets: Sent = 4, Received = 0, Lost = 4 (100% loss)

    1. If the server name resolves to an IP address, note the value and proceed with step 2.
    2. If this step fails, contact your network administrator or Internet service provider for assistance with fixing your name resolution problem.
  2. From your command prompt window, try to telnet to port 1433 using the IP address returned in the prior step. This will test whether there are any firewalls/routers blocking traffic to port 1433.

    C:\>telnet 65.55.74.144 1433

    Connecting To 65.55.74.144...

    1. If Telnet is successful, the window will change to a completely blank screen. Exit and proceed to the next step.
    2. If it is not successful, capture the output and move to step 4.

    Reference: Telnet Troubleshooting Guide - http://technet.microsoft.com/en-us/library/cc753360(WS.10).aspx

  3. From your command prompt window, repeat the test by trying to telnet to the DNS name instead of the IP address.

    C:\>telnet <myserver>.database.windows.net 1433

    Note: If Telnet is not part of your default windows installation, you may enable it from Add/Remove Windows Components.

  4. Use the tracert utility and capture the output showing the intermediate steps taken while trying to reach the server. This information will be useful if a network engineer is needed to troubleshoot the issue.

    C:\>tracert 65.55.74.144

    Tracing route to 65.55.74.144 over a maximum of 30 hops

    1 2 ms 1 ms 1 ms 192.168.0.1

    2 6 ms 5 ms 5 ms 10.33.34.50

    3 5 ms 4 ms 5 ms 4-1-753.DLLSTX-LCR-07.verizon-gni.net [130.81.107.26]

    4 8 ms 5 ms 5 ms so-6-2-0-0.DFW01-BB-RTR1.verizon-gni.net [130.81.28.208]

    5 44 ms 43 ms 44 ms so-11-0-0-0.LCC1-RES-BB-RTR1-RE1.verizon-gni.net [130.81.17.40]

    6 49 ms 44 ms 44 ms so-6-0-0-0.ASH-PEER-RTR1-re1.verizon-gni.net [130.81.10.90]

    Reference: How to Use TRACERT to Troubleshoot TCP/IP Problems in Windows - http://support.microsoft.com/kb/314868

Return to Top


Isolating Network Problems

If you encountered difficulties when Verifying that You Can Reach the Server IP, then you are not reaching the VIP and are instead being blocked between your client and the VIP. The below steps may be helpful, but at this point an investigation by your ISP or network engineer is required.

Many firewall, router, and proxy products allow individual users and domain administrators to block or restrict outbound access on a specific port and or destination IP address. Therefore it is necessary to check the outbound rules that are defined on the local machine as well as any firewalls, routers or proxies that are between you and SQL Database. Rules must
allow outbound access on port 1433 to destination IP address range
65.55.*.*. Network products that might block access include:

  1. Windows Firewall on the local machine

    Frequently Asked Questions - http://msdn.microsoft.com/en-us/library/bb736261(VS.85).aspx

    Windows Firewall with Advanced Security Getting Started Guide - http://technet.microsoft.com/en-us/library/cc748991(WS.10).aspx

    Firewall rules can also be configured as part of domain policies and hence please work with your domain policies to check if there are any such rules that can block the connection.

  2. Other Firewall products

    For information on working with other firewall products, please consult the specific firewall product documentation.

  3. Proxy and Routers and other components in your network

    If you are going through a proxy or router to connect to the internet, confirm the external IP address that is exposed on the internet. This is usually the IP address that the SQL Database Firewall
        must allow in order for you to connect to the server. You can use http://www.whatismyip.com/ to find the external IP address that is used by the proxy or router to communicate with services on the Internet. The AddFirewallRule dialog box under SQL Database Firewall Setting Tab in the portal will
        also list your external IP address.

  4. If possible try connecting to SQL Database from a machine outside your
        personal or company firewall that is directly connected to your ISP.
       

    Please repeat all the steps provided in Verify Azure Firewall Settings and Service Availability. This test would eliminate firewall and other policies that could potentially be blocking access from inside your network, and will indicate whether the problem is inside your network or with your ISP's network.

  5. Generate a Network Trace using Microsoft Network Monitor tool

    Reference: How to capture network traffic with Network Monitor - http://support.microsoft.com/kb/148942

    Review the trace by adding a filter TCP.port==1433 which would show conversations on TCP port 1433. If you do not see an ACK back from the server, it is highly likely that the connection is blocked on your network or over the internet.

    A network snippet from a connection that was not successful:

    11:12:27.007      25   {TCP:7, IPv4:6}  111.11.11.111    data.sn1-1.database.windows.net   TCP        TCP:Flags=......S., SrcPort=49411, DstPort=1433, PayloadLen=0, Seq=2375502765, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192
    11:12:30.007 26 {TCP:7, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #25]Flags=......S., SrcPort=49411, DstPort=1433, PayloadLen=0, Seq=2375502765, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 11:12:36.007 29 {TCP:7, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #25]Flags=......S., SrcPort=49411, DstPort=1433, PayloadLen=0, Seq=2375502765, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 819211:12:48.014      40   {TCP:12, IPv4:6} 111.11.11.111    data.sn1-1.database.windows.net   TCP        TCP:Flags=......S., SrcPort=49412, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=3206081271, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192
    11:12:49.015      41   {TCP:13, IPv4:6} 111.11.11.111    data.sn1-1.database.windows.net   TCP        TCP:Flags=......S., SrcPort=49413, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=1361088072, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192
    11:12:49.019      42   {IPv4:6}         111.11.11.111    data.sn1-1.database.windows.net   ICMP       ICMP:Echo Request Message, From 111.11.11.111 To 65.55.74.144
    11:12:50.769      45   {IPv4:6}         111.11.11.111    data.sn1-1.database.windows.net   ICMP       ICMP:Echo Request Message, From 111.11.11.111 To 65.55.74.144
    11:12:51.014 46 {TCP:12, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #40]Flags=......S., SrcPort=49412, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=3206081271, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 11:12:52.015 47 {TCP:13, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #41]Flags=......S., SrcPort=49413, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=1361088072, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 819211:12:52.771      48   {TCP:14, IPv4:6} 111.11.11.111    data.sn1-1.database.windows.net   TCP        TCP:Flags=......S., SrcPort=49414, DstPort=NETBIOS Session Service(139), PayloadLen=0, Seq=265110652, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192
    11:12:55.771 49 {TCP:14, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #48]Flags=......S., SrcPort=49414, DstPort=NETBIOS Session Service(139), PayloadLen=0, Seq=265110652, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 11:12:57.021 52 {TCP:12, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #40]Flags=......S., SrcPort=49412, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=3206081271, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 11:12:58.016 53 {TCP:13, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #41]Flags=......S., SrcPort=49413, DstPort=Microsoft-DS(445), PayloadLen=0, Seq=1361088072, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 11:13:01.771 56 {TCP:14, IPv4:6} 111.11.11.111 data.sn1-1.database.windows.net TCP TCP:[SynReTransmit #48]Flags=......S., SrcPort=49414, DstPort=NETBIOS Session Service(139), PayloadLen=0, Seq=265110652, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192

    Here we can see that the client is trying to establish a connection to SQL Database and keeps retransmitting three times and
        fails. This trace indicates that the connection did not even reach
        our VIP and hence the connection is likely blocked in the client’s
        network or over the Internet.

    Snippet of trace from a successful connection:

    14:50:01.512      847  SQLCMD.EXE  {TCP:428, IPv4:427}  99.99.999.99      157.54.118.18    TCP        TCP:Flags=......S., SrcPort=13415, DstPort=1433, PayloadLen=0, Seq=4009972475, Ack=0, Win=8192 ( Negotiating scale factor 0x2 ) = 8192
    14:50:01.515      848  SQLCMD.EXE  {TCP:428, IPv4:427}  157.54.118.18     99.99.999.99     TCP        TCP:Flags=...A..S., SrcPort=1433, DstPort=13415, PayloadLen=0, Seq=2117383693, Ack=4009972476, Win=8192 ( Scale factor not supported ) = 8192
    14:50:01.516      849  SQLCMD.EXE  {TCP:428, IPv4:427}  99.99.999.99      157.54.118.18    TCP        TCP:Flags=...A...., SrcPort=13415, DstPort=1433, PayloadLen=0, Seq=4009972476, Ack=2117383694, Win=17040 (scale factor 0x0) = 17040

    The above snippet represents a successful three-way handshake.

  6. SQL Database Denial of Service (DOS) Guard
       

    SQL Database utilizes techniques to prevent denial of service
        attacks. If your connection is getting reset by our service due to a
        potential DOS attack you would be able to see a three way handshake
        established and then a RESET in your network trace.

Return to Top


See Also

The information presented in this article was originally contained in the SQL Database Connectivity Troubleshooting Guide published on the SQL Database Team's blog; it has been relocated here so that the troubleshooting guide is easier to update and can be contributed to by the community. The author of the original information contained in this article is Abi Iyer.

Sort by: Published Date | Most Recent | Most Useful
Comments
  • Thanks for the great link

  • Thanx. Really helpful article

  • Excellent article overall, and very well-formatted

  • Good one

Page 1 of 1 (4 items)