SQL Server Connection Problems – a Checklist

This Checklist came about after installing additional SQL Server Instances on existing Servers and finding that a bit of additional work was required before Users could access. It’s equally valid for default installations of SQL Server.

Allow remote connections to this server

This is the suggestion that most “helpful” users provide when they report that they cannot connect. In my experience, it’s the least likely fix but worth checking just in case.

Check that the SQL Server is set to Allow remote connections to this Server. Right-click the SQL Server name in SSMS, choose Properties and then select the “Connections” page.

The default setting is for this option to be ticked, but it is worth checking.

SQL Server Browser Service

Is the SQL Browser Service running? This is required for SQL Server to direct connections to additional SQL Server Instances.

Open SQL Server Configuration Manager and check that “SQL Server Browser” is running and set to Automatic.

Are the correct Protocols Enabled?

Confirm that the relevant connection Protocols are Enabled for the SQL Server Instance. TCP/IP should be Enabled for network connections and Named Pipes needs to be Enabled for some Applications.

Firewall

In larger, more complex environments, it’s worth checking to see if your Connection is being blocked by a Firewall. Your friendly Network team should be able to check this and make the required Changes.

·         TCP Port 1433 is the default Port used for the default SQL Server Instance

·         UDP Port 1434 is required if additional Instances are in use

Additional Instances will have a TCP Port assigned and this will also need to be let through

Used fixed Port instead of Dynamic Ports

By default, additional SQL Server Instances will use Dynamic TCP Ports. This adds security but makes it more difficult to set up pass through for a Firewall. By setting the TCP/IP Properties for the Instance, a fixed Port can be used.

Open the Protocols for the Instance, right-click TCP/IP, choose Properties then the IP Addresses tab. Set TCP Dynamic Ports to be blank and set the TCP Port as required.

User Permissions

Nearly missed this one, but you do need to set up a Login for the User (or put them into an AD Group that has access). Check under Security | Logins to confirm that they are set up. Open Properties for the Login and check the Status to be sure that it’s set to Grant access and that the Login is Enabled as follows:

Hopefully, this will help others at some point in the future, including me. Please let me know if I’ve missed anything.

Nigel.