Troubleshooting connectivity issues with Microsoft Azure SQL Database

You receive error messages when the connection to Azure SQL Database fails. The connection problems can be caused by SQL Azure database reconfiguration, firewall settings, connection time-out, or incorrect login information. Additionally, if the maximum limit on some Azure SQL Database resources is reached, you cannot connect to Azure SQL Database.

What does this guide do?

  • Resolves the error codes or error messages that you get when you connect to Microsoft Azure SQL Database.
  • Provides the best practices to prevent request denials or connection terminations when you connect to Azure SQL Database.

Who is it for?

Users who have problem connecting to Microsoft Azure SQL Database.

How does it work?

We’ll begin by asking you the error that you are facing. Then we’ll take you through a series of steps that are specific to your situation.

Estimated time of completion:

15-30 minutes.

Welcome to the troubleshooter

Select the issue you encounter when you connect to the Azure SQL Database or select the last option to go to next page to select the exact error message you get.

Welcome to the troubleshooter

Select the issue you encounter when you connect to the Azure SQL Database or select the last option to go to next page to select the exact error message you get.

Cannot connect to <servername> due to firewall issues
What error message did you receive?

ADDITIONAL INFORMATION:

Cannot open server ‘servername’ requested by the login. Client with IP address 'ip address' is not allowed to access the server.  To enable access, use the Windows Azure Management 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. (Microsoft SQL Server, Error: 40615)

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

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 - Access is denied.) (Microsoft SQL Server, Error: 5)

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

Access is denied

Select the detailed error message you encountered
Solve the "Error 40615: Cannot connect to <servername>" error

To resolve this issue, configure firewall settings on SQL Database using the Azure portal.

Did this solve your problem?

The server was not found or was not accessible (Errors 26, 40, 10053)

What error message did you receive?

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)

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)

10053: A transport-level error has occurred when receiving results from the server. (Provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine)

Solve the "A network-related or instance-specific error occurred while establishing a connection to SQL Server" issue

The issue occurs because the application is not able to connect to the server.

To resolve this issue, try the following steps (in that order):

  1. Make sure that TCP IP is enabled as a client protocol on the application server. For more information on how to do this, see Configure client protocols. On application servers where you do not have SQL Server tools installed, you can check this by running cliconfg.exe (SQL Server Client Network Utility).
  2. Check the application’s connection string to make sure that it is correctly configured. For example, make sure that the connection string specifies the correct port (1433) and the fully qualified server name.
    See Obtain the connection string from the Azure portal.
  3. Try increasing the connection timeout. Microsoft recommends using a connection timeout of at least 30 seconds.

  4. Test the connectivity between the application server and the Azure SQL database by using a UDL file, ping, and telnet. For more information about how to do this, see Troubleshooting SQL Server connectivity issues and Diagnostics for connectivity issues.
    Note As a troubleshooting step, you can also try to test the connectivity on a different client computer.

  5. As a best practice ensure retry logic is in place. For more information about the retry logic, see Troubleshoot transient faults and connection errors to SQL Database.

If the previous steps do not resolve your problem, try the following step to collect more data and contact support:

Did this solve your problem?
Solve the issue

Note  These error messages occur because of some system-specific issue such as an outage or some component failure. Therefore, you may eventually have to open a support case.

To find more information about these errors, try these methods:

  1. Check the  Microsoft Azure Service Dashboard  for any known outages.
  2. Test the connectivity by using ping and telnet. For more information about how to do this, see Troubleshooting SQL Server Connectivity Issues and Diagnostics for Connectivity issues.

    Note As a troubleshooting step, try to test the connectivity on a different client computer.Check whether you can log on by using SQL Server Management Studio and also the Azure portal:

  3. If you can log on, you can query the following DMVs in master database in order to understand the reason for the failure (Such as reconfiguration, idle connection threshold and so on).

        SELECT * FROM sys.resource_stats         WHERE database_name='<Database name>'         SELECT * FROM sys.event_log         WHERE database_name='<Database name>'      

If you cannot log on, click the "Unable to login to the server" and "Connections failing from SQL Server Management Studio (SSMS)" scenarios below.

Unable to login to the server (Errors 18456, 40531)

What error message did you receive?

Login failed for user '<User name>'.This session has been assigned a tracing ID of '<Tracing ID>'. Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 18456)

Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match. (Microsoft SQL Server, Error: 40531)

Solve the "Server name cannot be determined" issue

To resolve this issue, make sure that user name is specified as username@servername. For example, if the user name is  user1  and the fully qualified server name is  abc123.database.windows.net, you should specify user name as  user1@abc123.


Did this solve your problem?

Solve the "Login failed for user" issue

To resolve this issue, contact your service administrator to provide you the valid SQL user name and password. For more information, refer to Managing Databases and Logins in Azure SQL Database.

Typically, the service administrator can use the following steps to add the login:

  1. Login to the server using SQL Server Management Studio (SSMS).
  2. Check whether the login name is disabled by using the following SQL query:
    SELECT name, is_disabled FROM sys.sql_logins
  3. If the corresponding name is disabled, enable it by using the following statement:
    Alter login <User name> enable
  4. If the SQL login user name does not exist, create it by using SSMS. To do this, follow these steps:
    1. Double-click Security to expand it.
    2. Right-click Logins, and then select New login.
    3. In the generated script with placeholders, you can edit and run the following SQL query:
      CREATE LOGIN <SQL_login_name, sysname, login_name>
      WITH PASSWORD = ‘<password, sysname, Change_Password>’
      GO
  5. Double-click Database.
  6. Select the database to which you want to grant user the permission.
  7. Double-click Security.
  8. Right-click Users, and then select New User.
  9. In the generated script with placeholders, you can edit and run the following SQL query:
    CREATE USER <user_name, sysname, user_name>          FOR LOGIN <login_name, sysname, login_name>WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>GO

    -- Add user to the database owner role
    EXEC sp_addrolemember N’db_owner’, N’<user_name, sysname, user_name>’GO
  10. Note that you can also use sp_addrolemember to map specific users to specific database roles.


Did this solve your problem?

Connections failing from SQL Server Management Studio (SSMS)

What error message did you receive?

Solve the "Microsoft SQL Server Management Studio Express version" issue

This issue occurs because of the earlier versions of SQL Server Management Studio (SSMS) are not supported.

To resolve this issue, download and install one of the following supported version of SSMS:

Note: You can also use SQL Server 2008 R2 SSMS. For more information, check this link.


Did this solve your problem?

Solve the “Cannot open database "master" requested by the login. ” issue

This issue occurs because the account does not have access permission to the master database. However, by default, SQL Server Management Studio (SSMS) tries to connect to the master database.

To resolve this issue, follow these steps:

  1. On the login screen of SSMS, click Options, and then click Connection Properties.
  2. In Connect to database, type the user’s default database name as the default login database, and then click Connect.
    3844_image10
     
Did this solve your problem?
Solve the “Client may have timed out when establishing connection” issue

 To resolve this issue, increase the connection timeout. The connection timeout should be set to at least 30 seconds.

 

  Did this solve your problem?

Connection Terminated due to hitting some system-defined limit

What error message did you receive?

10928 : Resource ID: %d. The %s limit for the database is %d and has been reached. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance. The Resource ID value in error message indicates the resource for which limit has been reached. For sessions, Resource ID = 2.

10929: Resource ID: 1. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d. However, the server is currently too busy to support requests greater than %d for this database. See http://go.microsoft.com/fwlink/?LinkId=267637 for assistance. Otherwise, please try again later.

40501: The service is currently busy. Retry the request after 10 seconds. Incident ID: %ls. Code: %d.

40544 : The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Incident ID: <ID>. Code: <code>.

40549 : Session is terminated because you have a long-running transaction. Try shortening your transaction.

40551 : The session has been terminated because of excessive TEMPDB usage. Try modifying your query to reduce the temporary table space usage.

40552 : The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

40553 : The session has been terminated because of excessive memory usage. Try modifying your query to process fewer rows.

Solve the "10928" reach the limits issue

To work around this issue, try the following methods:

  • Verify whether there are long-running queries. To do this, follow these steps:

    Note This is a minimalist approach that may not necessarily resolve the issue.

    1. Check the sys.dm_exec_requests view to see any blocking requests. To do this, execute the following SQL query:
      SELECT * FROM dm_exec_requests
    2. Determine the inputbuffer for the head blocker.
    3. Tune the head blocker query.

      For an in-depth troubleshooting procedure refer to the following post on MSDN: 
      Is my query running fine in the cloud?.

  • If the database consistently reaches its limit despite addressing blocking and long-running queries, consider upgrading to one of the new Preview editions (such as Standard or Premium editions).
  • For more information about dynamic management views, see the dynamic management views and functions (Transact-SQL).
  • For more information about this error message, see Azure SQL Database resource limits.

Did this solve your problem?

Solve the “10929” reach the limits issue

For more information about this error, see the following MSDN webpage: 
Error messages for SQL Database client programs


Did this solve your problem?

Solve the “40544 : The database has reached its size quota” issue

This error happens when the database size exceeds the currently configured maximum size.

The following steps can help you with either working around the problem or provide you with additional options that you can consider to

  1. Check the current size of the database by using the dashboard in Azure management portal.
    Note:To identify which tables are consuming the most space and potential candidates for cleanup, you can use the following SQL query:
    SELECT o.name, a.SUM(p.row_count) AS 'Row Count', b.SUM(p.reserved_page_count) * 8.0 / 1024 AS 'Table Size (MB)'
    FROM sys.objects o
    JOIN sys.dm_db_partition_stats p on p.object_id = o.object_id
    GROUP BY o.name
    ORDER BY [Table Size (MB)] DESC
  2. If the current size does not exceed the maximum size supported for your edition , you can use ALTER DATABASE to increase the MAXSIZE setting.

  3. If the database size is already past the maximum supported size for your edition, you can take one of the following steps:
    1. Perform normal database cleanup activities (cleaning up the unwanted data by using truncate/delete etc. or move data out using SSIS, bcp etc.)
    2. Consider upgrading your edition to another edition that supports higher database sizes. For more information, check the following two links:

      Basic, Standard, and Premium Preview for Azure SQL Database.

      SQL Database Pricing Details.

Did this solve your problem?

Solve the “40552 : The session has been terminated because of excessive transaction log space usage” issue

To resolve this issue, follow these methods: 

  • The issue occurs because of insert, update, or delete operations.

    Try to reduce the number of rows that are operated on immediately by implementing batching or splitting into multiple smaller transactions.

  • The issue occurs because of the index rebuild operations. Make sure that you adhere to the following formula:

    number of rows that are affected in table * (average size of field that is updated in bytes + 80) < 2 GB

    Note For index rebuild, the average size of the field that is updated should be substituted by average index size.

Did this solve your problem?
Solve the “40551 : The session has been terminated because of excessive TEMPDB usage.” issue

To work around this issue, follow these steps:

  1. Change the queries to reduce the temporary table space usage.
  2. Drop temporary objects after they are no longer needed.
  3. Truncate tables, or remove unused tables.
Did this solve your problem?
Solve the “40553 : The session has been terminated because of excessive memory usage.” issue

To work around this issue, try to optimize the query.

For an in-depth troubleshooting procedure, refer to the following MSDN webpage: 
Is my query running fine in the cloud?.


Did this solve your problem?

Solve the “40549 : Session is terminated because you have a long-running transaction.” issue

If you repeatedly encounter this error message, try the following steps to resolve this issue:

  1. Check the sys.dm_exec_requests view to see any open sessions that have a high value for the total_elapsed_time column. To do this, execute the following SQL Script:
    SELECT * FROM dm_exec_requests
  2. Determine the inputbuffer for the query that is long running.
  3. Tune the query.

For an in-depth troubleshooting procedure, refer to the following MSDN webpage: 
Is my query running fine in the cloud?


Did this resolve your issue?

Solve the "40501 : The service is currently busy. " issue

This is an engine throttling error, for more info about throttling error, see Azure SQL Database resource limits and "SQL Database Throttling" section from Windows Azure SQL Database Performance and Elasticity Guide.


Did this solve your problem?

Transient errors (Errors 40197, 40545)

What error message did you receive?

Solve the "40197: The service has encountered an error processing your request" issue

This issue occurs because of a transient error encountered during a reconfiguration/failover on the backend.

To resolve this issue, wait a short period and retry. No support case is required unless the issue remains persistent.


Did this solve your problem?

Solve the issue
  1. Check the Microsoft Azure Service Dashboard for any known outages.
  2. If there are no known outages go to the Microsoft Azure Support website to open a support case.

Did this solve your problem?

Connection timeout expired errors

What error message did you receive?

System.Data.SqlClient.SqlException (0x80131904): Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=3; handshake=29995;

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. -> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. -> System.ComponentModel.Win32Exception: The wait operation timed out

Cannot connect to <server name>.ADDITIONAL INFORMATION:Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=231; handshake=983; [Login] initialization=0; authentication=0; [Post-Login] complete=13000; (Microsoft SQL Server, Error: -2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=-2&LinkId=20476 The wait operation timed out

Solve the connection timeout issue

The exception 0x80131904 can occur either because of connection or query issues. To confirm this error is because of connectivity issues, review the stack trace for frames that show calls to open a connection like the following ones (Note the reference to the SqlConnection class):

  System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)   at System.Data.SqlClient.SqlConnection.Open()
at AzureConnectionTest.Program.Main(String[] args)
ClientConnectionId:<Client connection ID>

When the exception happens due to query issues, you will notice a call stack that is similar to the following ones (Note the reference to the Sqlcommand class).In these scenarios, you have to work on tuning your queries.

  at System.Data.SqlClient.SqlCommand.ExecuteReader()
at AzureConnectionTest.Program.Main(String[] args)
ClientConnectionId:<Client ID>

To resolve the issue you can try the following steps (in that order):

  1. Check the application’s connection string to make sure that it is correctly configured. For example, make sure that the connection string specifies the correct port (1433) and the fully qualified server name.

    See Obtain the connection string from the Azure portal.

  2. Make sure that TCP IP is enabled as a client protocol on the application server. For more information on how to do this, see Configure client protocols. On application servers where you do not have SQL Server tools installed, you can check this by running cliconfg.exe (SQL Server Client Network Utility).
  3. Test the connectivity between the application server and the Azure SQL database by using a UDL file, ping, and telnet. For more information about how to do this, see Troubleshooting SQL Server connectivity issues and Diagnostics for Connectivity issues.

  4. Try increasing the connection timeout. Microsoft recommends using a connection timeout of at least 30 seconds.
  5. As a best practice ensure retry logic is in place. For more information about the retry logic, see Troubleshoot transient faults and connection errors to SQL Database.

If the previous steps do not resolve your problem, follow these steps to collect more data and contact support:

  1. If your application is a cloud service, enable the logging. This step returns a UTC time stamp of the failure. Additionally, SQL Azure returns the tracing ID. Microsoft Customer Support Services can use this information.
  2. For more information about how to enable the logging, see how to enable diagnostic logging for Azure Web sites and Developing SQL Database Applications section in Azure SQL Database Development Considerations.
  3. Check out the list of best practices for Connecting to Windows Azure SQL Database.
Did this solve your problem?
Success

Congratulations, your Azure SQL Database connectivity issue is resolved.

For more information about how to avoid Azure SQL Database connectivity issues, go and refer the following Web pages:

The troubleshooter cannot be of help

We are sorry you weren’t able to connect to the Azure SQL Database.

For more information about troubleshooting Azure SQL Database connectivity issues, go and refer the following Web pages:

Solve the "Error 5: Cannot connect to <servername>" error

To solve this issue, make sure that port 1433 is open for outbound connections on all firewalls between the client and the Internet. Refer to Configure the Windows Firewall to Allow SQL Server Access for additional pointers.

Did this solve your problem?

Solve the "Database <x> on server <y> is not currently available" issue

The full error message you get is as below:

40613: Database <DBname> on server <server name> is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of '<Tracing ID>'.

To solve this issue, see Troubleshoot "Database on server is not currently available" error.

Did this solve your problem?

Proprietà

ID articolo: 10085 - Ultima revisione: 11 mag 2016 - Revisione: 52

Feedback