Lessons Learned #551: Azure SQL Connection Timeouts: Three Things to Check
An application starts reporting intermittent timeouts when connecting to Azure SQL Database. Some requests succeed, others fail, and a test from a developer’s laptop works perfectly. The database appears online, no recent deployment seems related, and the natural reaction is to ask: Is Azure SQL unavailable? Is the firewall blocking the connection? Should we increase the connection timeout? Should we change the driver or scale the database? Those are reasonable questions, but they may lead the investigation in the wrong direction. The most important lesson is simple: A timeout tells us how long the application waited. It does not tell us what the application was waiting for. Not every “SQL timeout” happens inside Azure SQL From the application’s point of view, opening a database connection may involve several operations: Resolving the server name. Reaching the SQL endpoint. Obtaining a Microsoft Entra access token. Waiting for an available pooled connection. Completing the SQL login. Executing the first command. When all these operations are reported through the same application method or log entry, it can look as though Azure SQL took thirty seconds to accept the connection. In reality, only part of that time may have been spent connecting to the database. In one anonymized support scenario, the application experienced problems mainly on its first connection. Network tests were successful and no corresponding SQL connection failure was identified. The investigation eventually showed that access-token acquisition was consuming a significant part of the available time. Increasing the SQL timeout or changing the firewall would not have addressed the real delay. Check 1: Capture the complete error and the exact time A screenshot containing only “Connection Timeout Expired” is rarely enough. Capture: The complete exception and inner exception. The operation being performed. The driver and version. The authentication method. The exact timestamp in UTC. Whether the issue affects every connection or only some of them. The wording around the timeout matters. For example, a timeout while obtaining a connection from the pool points toward the application’s pooling and concurrency behavior. A pre-login or TLS error belongs to a different investigation. A command timeout after the connection was established is usually a query-performance problem rather than a connection problem. Check 2: Measure the application timeline The application should record important operations separately. A simple timeline can completely change the investigation: 10:14:20.100 Token acquisition started 10:14:28.400 Token acquired 10:14:28.405 SQL connection started 10:14:29.050 SQL connection established The complete operation took almost nine seconds, but Azure SQL connection establishment took less than one second. Useful measurements include: Token-acquisition duration. Time waiting for a pooled connection. SQL connection-open duration. SQL command duration. Number of retry attempts. Applications using Microsoft Entra authentication must obtain an access token before authenticating to Azure SQL. Measuring that operation separately helps distinguish an identity delay from a database connectivity problem. This is particularly useful when the issue appears: On the first connection after startup. After a token expires. Only with Managed Identity or Workload Identity. Intermittently, while SQL authentication connections remain unaffected. Check 3: Test from the application environment A successful connection from a laptop does not validate the path used by an application running in: Azure App Service. Azure Functions. Azure Kubernetes Service. A virtual machine. An on-premises application server. A container or integration runtime. The laptop and the application may use different DNS servers, routes, firewalls, proxies and identities. Connectivity and DNS tests should therefore be performed from the environment that is actually failing. This becomes especially important when Private Endpoint is used. The application should continue connecting with: <server>.database.windows.net It should not use the Private Endpoint IP address or the privatelink.database.windows.net hostname directly. Direct login attempts using the private IP or the private-link FQDN fail; the normal logical-server FQDN must remain in the connection string. From the affected environment, confirm that: The expected DNS server answers the request. The server FQDN resolves to the expected private IP. The Private Endpoint connection is approved. The Private DNS zone is linked correctly. The resolved address is reachable through the intended route. A test from an unrelated machine is still useful for comparison, but it does not prove that the application path is healthy. Observed symptom Likely investigation area Timeout while obtaining a connection from the pool Application connection pooling Server name cannot be resolved DNS TCP connection to the endpoint cannot be established Network path, firewall or routing Error during the pre-login handshake TLS, driver, network interruption or pre-login processing Authentication or access-token error Microsoft Entra authentication, identity or token acquisition Timeout during the post-login phase Login completion, session initialization or server-side processing Execution or command timeout after connecting Query execution and database performance Avoid changing several things at once During a production incident, it is tempting to: Increase the timeout. Add firewall rules. Change the connection policy. Upgrade the driver. Restart the application. Clear connection pools. Applying several changes together makes it difficult to determine which one helped, and some may only hide the symptom. A better approach is to define one hypothesis: We believe DNS in the application environment is resolving the public endpoint instead of the Private Endpoint. Then define: The evidence supporting the hypothesis. One controlled change. The expected result. How the result will be measured. How the change will be reverted. Azure SQL supports Proxy and Redirect connection policies, which determine how traffic flows after reaching the Azure SQL gateway. The policy is configured for the logical server, so it should be verified before making firewall assumptions or changes. What should we collect before opening a support request? A small but precise evidence package can avoid several rounds of questions: Complete error and inner exception. Exact UTC timestamps. Application platform and location. Public or Private Endpoint. Server FQDN used by the application. Driver and version. Authentication method. Token, pool, connection and command durations. DNS result from the affected environment. Whether the issue is constant, intermittent or limited to the first connection. Recent application, network, identity or configuration changes.
Problem with the startswith function in NET 9
Hi everyone, I have this request with Odata: http://localhost:5194/NationalStates?$filter=startswith(state, 'NE') This works correctly up to version 8.0.14 for the following packages: MySql.EntityFrameworkCore Microsoft.Extensions.DependencyInjection.Abstractions Microsoft.EntityFrameworkCore among others... When I update these packages to version 9.0.0 or later, they stop working and give an internal mapping error. Any clues about this?
Cannot connect to LocalDB (provider: Named Pipes Provider, error: 0 - No process is at the other end
I'm trying to manually connect to LocalDB (both, SQL Server 2019 and SQL Server 2022 services) creating a new application database using ADO.NET (Microsoft.Data.SqlClient). Everytime I try to connect, I get a generic error telling me that the database file could not be attached. Error.log: 2024-09-17 19:30:18.40 Logon Error: 15350, Severity: 16, State: 1. 2024-09-17 19:30:18.40 Logon An attempt to attach an auto-named database for file C:\Temp\CvTest.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. 2024-09-17 19:31:02.66 Logon Error: 15350, Severity: 16, State: 1. 2024-09-17 19:31:02.66 Logon An attempt to attach an auto-named database for file C:\Temp\CvTest.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Debugging into the Microsoft.Data.SqlClient source code, the following error turned out to be the real reason behind the issue: A connection to the server could be established, but an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is at the other end of the pipe). What is causing this error? This is my simple test connection setup. It's a cheap connection string that every Microsoft sample is quoting: using SqlConnection con = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;Integrated Security=SSPI;MultipleActiveResultSets=True;AttachDBFilename=C:\Temp\CvTest.mdf"); con.Open(); So, it should work off the cuff. But it doesn't. Using SSMS, I can easily connect to the LocalDB instance and create/delete databases promptly, even with the provided path. I already updated from MsLocalDb 15 to MsLocalDb 16, but to no avail.
"Thread was interrupted from a waiting state" from DbConnectionPool.CleanupCallback
Hi, I'm getting this error, and I'm not able to fix it, or even understand from where it comes. I'm also sure that all my SqlConnection are instanciated with Using (VB.NET), so the Dispose should be called for every one. Does anyone have an idea about the cause? This error started after upgraded the app to the .NET Framework 4.8.1, from 4.6.2. Maybe it's a coincidence... Source: mscorlib; Message: Thread was interrupted from a waiting state.; Data:; StackTrace: at System.Threading.WaitHandle.WaitOneNative(SafeHandle waitableSafeHandle, UInt32 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext) at System.Threading.WaitHandle.InternalWaitOne(SafeHandle waitableSafeHandle, Int64 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext) at System.Threading.WaitHandle.WaitOne(Int32 millisecondsTimeout, Boolean exitContext) at System.Data.ProviderBase.DbConnectionPool.CleanupCallback(Object state) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) at System.Threading.TimerQueueTimer.CallCallback() at System.Threading.TimerQueueTimer.Fire() at System.Threading.TimerQueue.FireNextTimers(); Regards, PedroSQL Server Authentication
I am developing a new windows console app using .Net Core 6 and I have added a reference to System.Data.SQLServer (version 4.8.3) when I go to connect to my Azure SQL database I supply the same connection string that I would when connecting with .NET Framework, however I get an error saying the the authentication parameter is not recognized. Why would this be different? Is there a different process? The parameter is Authentication='Active Directory Integrated'
SQL Sever and Apache Log4 vulnerabilities
We use SQL Server 2014 Management Studio, and SQL Server 2015 Reporting Services (SSRS?) on a server that is not owned by us. The operating system is Windows Sever 2012 R2. I did not set up our SQL server. An MS article I found says: SQL Server (on Windows) – all editions Note: If a customer installs Java support and deploys Java Archives (JARs) that depend on the Log4j 2 library, they are advised to upgrade to the latest version or remove the Java Archives (JARs) that require the dependency. Source: https://msrc-blog.microsoft.com/2021/12/11/microsofts-response-to-cve-2021-44228-apache-log4j2/ How do I check if we have installed Java Support? How do I check if we use Java Archives (JARs)? Appreciate any leads.Does the new Cosmos DB patch API gives us enough reasons to rethink about our designs?
One of the most emphasized rules I have learned from my experience when working with Cosmos DB is that, in your documents, you have to separate the parts which change very often from the parts which are rarely updated. Like for instance, likes count in a post or comment. Whilst the posts content, title, author and tags are less likely to change after being set, it ok to put them together in a single document. But to include the post's likes count was not a good practice as every time the post get liked, shared or commented on, you had to update the whole document, which might not be ideal. Edit: I just selected the SqlClient label because there's no Cosmos DB label and I wasn't able to post without a label. 🤷♂️
