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.