Unable to connect to SQL Server instance remotely

• To enable mixed authentication you can change the following registry key:

  HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\LoginMode
  

  Update the value to 2 and restart the Sql Server service to allow mixed authentication. Note that MSSQL.1 might need to be updated to reflect the number of the SQL Server Instance you are attempting to change.

• A reason for connection errors can be a virus scanner installed on the server which blocks sqlserver.exe.

• Another reason can be that the SQL Server Browser service is not running. When this service is not running you cannot connect on named instances (when they are using dynamic ports).

• It is also possible that Sql Server is not setup to listen to TCP connections and only allows named pipes.

  1. In the Start Menu, open Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Surface Area Configuration
  2. In the Surface Area Configuration utility, click the link "SQL Server Configuration Manager"
  3. Expand "SQL Server Network Configuration" and select Protocols.
  4. Enable TCP/IP. If you need Named Pipes, then you can enable them here as well.

• Last but not least, the Windows firewall needs to allow connections to SQL Server

  1. Add an exception for sqlserver.exe when you use the "Dynamic Port" system.
  2. Otherwise you can put exceptions for the SQL Server ports (default port 1433)
  3. Also add an exception for the SQL Server Browser. (udp port 1434)

More information:

• How to: Configure a Windows Firewall for Database Engine Access
• Server Connectivity How-to Topics (Database Engine)

As a last note, SqlLocalDB only supports named pipes, so you can not connect to it over the network.

In addition to configuring the SQL Server Browser service in Services.msc to Automatic, and starting the service, I had to enable TCP/IP in: SQL Server Configuration Manager | SQL Server Network Configuration | Protocols for [INSTANCE NAME] | TCP/IP