Loosing connection with Microsoft Azure SQL Database

I'm having really frequent and occasional connection problems between my local instance of Microsoft SQL Server 2014 Management Studio (MSSQLSMS) and my cloud Microsoft Azure SQL Database.

Sporadically, my MSSQLSMS reports Disconnected along with the following error message when asking queries:
Msg 10054, Level 20, State 0, Line 0
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.)

Unfortunately, Googling this problem doesn't give me any clue on how to solve it.

Facts:

  • I don't have any bad or lousy internet connection. It is fast and stable!
  • I don't have any problems connecting to other remote SQL Server Databases.
  • My cloud Microsoft Azure SQL Database is just for learning purpose - there is not heavy load on it since I'm the only user.

Does anyone know what's the problem here?

July 28th, 2015 5:06pm

Hello,

You always have 3 replica of your Azure Database: The currently running one, a backup in the same datacenter and a second backup in a different datacenter. Sometime MS has to patch the server for security and for this the "current" shut down and the backup comes up; so you get disconnected. But this should be an issue at all if you well program database connectivity.

Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 3:12am

Hi, 

Azure SQL database has firewall settings where you need to update with your IP address, otherwise it will not be connected. If your IP address changes, it needs to be updated.

thanks,

Mostafa

July 29th, 2015 3:19am

Thanks for your quick reply!

Hmmm... I'm nut sure that I get it correctly. Does it (what you wrote) mean that is impossible to have a stable connection between my local instance of SSMS and the cloud based Azure database?

When I connect to a new Database Engine from SSMS I usually do it like this Object Explorer > Connect > Database Engine > "Connect to Server"-window > [Provide the "Server name" with corresponding credentials]

Am I doing something odd here? Should I do it in another way to "well program database connectivity" to get a stable and persistent connection?

Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 4:47am

See MSDN:
Guidelines for Connecting to Azure SQL Database Programmatically
How to: Reliably connect to Azure SQL Database

So for connecting to SQL Azure we have the speciallized ReliableSqlConnection Class, it automatically reconnect on a server switch / disconnect.

July 29th, 2015 5:15am

Hi Olaf,

Thanks again for your reply, and thanks for your links with C# examples. Unfortunately I still don't get it. How can I use the ReliableSqlConnection class when I connect to my Azure database through SQL Server Management Studio?

OBS! SQL Server Management Studio, not a custom C# .NET-application.


Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 6:44am

In SSMS just reconnect manually.
July 29th, 2015 6:57am

Hi HaunsTM1,

The error seems to be related to the Azure SQL Database connection mechanism. If a network problem causes connection-loss or Connections to SQL Database that are idle for 30 minutes or longer ,then the connection will be terminated.

See more reasons for the connection loss by clicking Windows Azure SQL Database Connection Management

Btw, since your question is regarding Azure SQL Database, I would suggest you post your question in a dedicated forum. It is more appropriate and you would get more prompt and precise response there.
Azure SQL Database Forum

If you have any question, feel free to let me

Free Windows Admin Tool Kit Click here and download it now
July 30th, 2015 12:32am

Hi Eric,

Sorry for posting this in wrong forum but thanks for your answer.

My problem with "Disconnected mode" occurs more often than every 30 min (whether the database has been idle or not).  I will check the other alternatives in your link and see if it solves my problem before I mark this as an answer.

July 31st, 2015 3:57pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics