How to fix errors connecting to MSSQL Server

With the addition of the database query monitoring feature of Overseer Network Monitor, I’ve had a couple users come to me with errors connecting to their MSSQL server using Overseer. This is primarily because the default configuration of Microsoft SQL Server disables TCP/IP communication for security reasons. When Overseer is unable to access the MSSQL server, you may get an error like this:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding

To properly configure MSSQL server to listen on TCP/IP, you’ll have to follow these steps:

1. Find and run the “SQL Server Configuration Manager” on the start menu. This should be under “Microsoft SQL Server 2008 R2->Configuration Tools”.

2. Once you open the SQL Server Configuration Manager, navigate to SQL Server Network Configuration->Protocols for MSSQLSERVER. Find ‘TCP/IP’ on the right side, and double-click it:

3. On the TCP/IP Properties page, make sure ‘Enabled’ is set to Yes, and ‘Listen All’ is set to yes, per this screenshot:

4. Also, make sure the port is specified on the ‘IP Addresses’ tab, under the “IPAll” section. This should typically be 1433:

Once this is done, you’ll have to restart the MSSQL Server service, and then attempt to connect to it with Overseer again. If things still don’t work, you may have a firewall blocking port 1433, either on the MSSQL Server machine, the Overseer machine, or potentially a hardware firewall in between– make sure port 1433 is allowed/forwarded as needed.