One time, at a company I heard about, there was a development team having issues connecting to one of the SQL Server database servers. The DBAs were called to investigate, they reviewed the instance and found nothing different than any of the hundreds of SQL Server instances they managed. The developers were certain the DBAs were incompetent fools that didn’t know how to configure a server correctly, so they decided to help. They found a blog post about SQL Server connectivity and sent the DBA team a link to the post with the following instructions:
“Make certain you have configured the server in the exact way as outlined in this blog post.”
[Because, you know, that’s how database servers should be administered, by following the directions of some random blog post and not by the team of DBAs that managed to have hundreds of instances running for years without any trouble. By the way, if this describes how you administer your database servers then I want you to stop working in IT immediately. Wait. Don’t quit until AFTER you’ve contacted me for help. Thanks. But I digress…]
After a bit of back and forth about the issue, the developers finally located the root cause: they were opening more than 32,767 connections to the server, causing SQL to refuse connections at that point.
Through the years I have found that many issues with SQL Server are common things to diagnose and fix. One such area is with connections. Microsoft has published a page for solving general SQL Server connectivity issues. This page is perfect for you or anyone you know that has suffered from one of the following error messages:
A network-related or instance-specific error occurred while establishing a connection to SQL Server
No connection could be made because the target machine actively refused it
SQL Server does not exist or access denied
PivotTable Operation Failed: We cannot locate a server to load the workbook Data Model
Cannot generate SSPI context
Login failed for user
Timeout Expired
The timeout period elapsed prior to obtaining a connection from the pool
The page will also help guide you through the steps for connecting to SQL server using a UDL file.
The wizard is good, but not perfect. For example, it wouldn’t have told me that the developer was doing something silly by opening more than 30k connections at once. But it will help you to determine if you have configured your SQL Server properly for connections. And that’s a start.
Together with the page for troubleshooting AlwaysOn issues and the page for troubleshooting connectivity issues with Microsoft Azure SQL Database, these three pages should be bookmarked for quick reference. In fact, that’s what I’ve done here: https://thomaslarock.com/sql-server-connectivity/
So now you can find all the pages in one place. I will add links to new pages as they become available. If you know of a page that should be included just leave a comment for me to review.
Very helpful – Thank you for compiling them in one place !!
Welcome!
I’m conflicted. Should I follow the advice of this random blog post? 🙂
I thought the same thing, so I bookmarked it and sorted my bookmarks so now it’s not random.
This might be the bacon talking, but I love you both for leaving these comments.
I have mirrored configuration. Intermittently I get an error from the mirrored (secondary) server that the user cannot explicitly open database, this is because the mirror db has not failed over. My question is, why is the app cannot connect to the principal database? How can I find out what causing the connection error to the principal? Is there a way to find from principal if the connection has been refused? What is the best way to troubleshoot this issue?
Sounds like a network issues between the app and the database. If the app tries to connect to the principal, but can’t, it could try to connect to the secondary. Are you using a witness server? Using a witness may help with this issue. For troubleshooting, I would look to monitor the network between the app and database servers, and I can recommend some great network tools for just that purpose.
I have a witness. My networking team said there is nothing wrong with our network and the app and sql servers are right next to each other and plugged in into one switch. I have been getting this intermittent error very once in a while.