database - Unable to connect to SQL Server instance remotely

Question

I’m trying to access the SQL Server instance on my VPS from SQL Server Management Studio on my local machine. It’s not working (the error I’m getting is:

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.

I think this is because I need to configure the database engine to allow remote connections (correct me if I’m wrong!). So I’ve found this step-by-step guide to help me do that: http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/ I’ve got to point 10 in the guide and I am now stuck! I don’t have SQL Server Management Studio installed on my VPS. Anyway, this has left me with two options:

1. Install SSMS
2. Find another way to do point 10 onwards in the guide without having SSMS installed

I tried installing SSMS on my VPS using the Web Platform Installer but it keeps failing. I don’t know why it’s failing because it doesn’t seem to give a reason why. Does anyone know how I could allow remote connections a different way?

The version of SQL Server installed on my VPS is SQL Server 2008 R2 Express.

Update:

I have tried to disable the firewall on both my laptop and VPS to see if it is a firewall issue. This made no difference to the error message.

Another Update:

Having now been able to install SSMS (I installed directly from the website rather than using the WPI), I have been able to check that the server is configured to allow remote connections (I went to SSMS, connected to the SQL Server instance, right-clicked on the connection, clicked Properties, went to the Connections tab. "Allow remote connections to this server" is already ticked).

SOLUTION

Thanks to everyone for helping me get to this solution! I've finally managed to get it to work! I followed Filip De Vos's advice and opened the ports in the Firewall on my VPS and then I received a different error message. This led me to investigate further and I found that I was using the wrong credentials to login! So I've set a password for the sa user and I've managed to login using that! Thanks again!

Answer 1

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

  HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerLoginMode
  

  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.