CODEWITHSUNDEEP
sql-serverentity-frameworkazureazure-sql-database
Tom Halladay
Tom Halladay

Reputation: 5761

SQL Azure - Failed Connections - But no problems?

We recently migrated a client to the SQL Azure platform during the course of a production deployment. By all outward signs, it was successful: the web apps connected to it are all working.

However, when we look at the Azure monitor, we see many failed connections. We haven't been able to find much documentation explaining what constitutes a failed connection. And none of our users have been reporting problems. Does anyone know how this could be?

enter image description here

Using the sample query from this article (View Connection Issues on an SQL Azure Instance)

SELECT      
            [Date From] = EL.[start_time],
            [Date To] = EL.[end_time],
            [Database Name] = EL.[database_name],
            [Event Type] = EL.[event_type],
            [Event Sub Type] = EL.[event_subtype_desc],
            [Description] = EL.[description],
            [Additional Data] = EL.additional_data
FROM sys.event_log EL
WHERE EL.event_type != 'connection_successful'
AND EL.event_subtype_desc != 'idle_connection_timeout'
ORDER BY [Date From] DESC

We see results like this:

2013-04-19 16:40:00.0000000 2013-04-19 16:45:00.0000000 [DATABASE]  connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:40:00.0000000 2013-04-19 16:45:00.0000000             connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:35:00.0000000 2013-04-19 16:40:00.0000000 [DATABASE]  connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:35:00.0000000 2013-04-19 16:40:00.0000000             connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:30:00.0000000 2013-04-19 16:35:00.0000000 [DATABASE]  connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:30:00.0000000 2013-04-19 16:35:00.0000000             connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:25:00.0000000 2013-04-19 16:30:00.0000000 [DATABASE]  connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:25:00.0000000 2013-04-19 16:30:00.0000000             connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:20:00.0000000 2013-04-19 16:25:00.0000000 [DATABASE]  connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:20:00.0000000 2013-04-19 16:25:00.0000000             connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL
2013-04-19 16:15:00.0000000 2013-04-19 16:20:00.0000000 [DATABASE]  connection_failed   blocked_by_firewall Client IP address is not allowed to access the server.  NULL

And an example of our connection string, as requested:

  <add name="[MyContext]"
     providerName="System.Data.SqlClient"
     connectionString="
      Server=tcp:[machine].database.windows.net,1433;
          Database=[database];
          User ID=[user]@[machine];
          Password=[password];
          Trusted_Connection=False;
          Encrypt=True;
          Connection Timeout=30;"/>

Upvotes: 9

Views: 6604

Answers (2)

pcdev
pcdev

Reputation: 3062

I had this same problem and it perplexed me until I realised that I had a SQL Server Management Studio instance open and connected on my home office machine, and my ISP had changed my IP address on me! As soon as I closed SSMS the failed connections stopped. Thanks OP for the link to the SQL query, that was useful to know as well!

Upvotes: 1

Tom Halladay
Tom Halladay

Reputation: 5761

So ever since the first day of launch, we have not seen anymore failed connections. My current thinking is that there may be a back end process on the Azure side that was not functioning properly with the new instance, but has since either stopped, or been fixed, as we have not changed anything. I'll let it sit a few more days and if we don't see any more problems, chalk it up to environment setup glitches.

enter image description here

Here's the status a week or so later. Not a heavily used site, but no sign of anymore connection errors.

enter image description here

Upvotes: 1

Related Questions