Unable to connect to SQL Server instance remotely

user_447722 0
Viewed 225281

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!

sql-server-2008r2-express remote-access sql-server database
11 Answers

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

enter image description here

user_70386670

Disable the firewall and try to connect.

If that works, then enable the firewall and

Windows Defender Firewall -> Advanced Settings -> Inbound Rules(Right Click) -> New Rules -> Port -> Allow Port 1433 (Public and Private) -> Add

Do the same for Outbound Rules.

Then Try again.

user_34424590

Open SQL Server Configuration Manager.

Click SQL Server Services, on the right side choose the server you've created during installation (by default its state is stopped), click once on it and a play button should appear on the toolbar. Click on this play button, wait til its state turns to "Running". Now you're good.

Open SQL Server Management Studio; switch the "Server Type" to "Database Engine" and "Authentication" to "SQL Server Authentication". The default login is "sa", and the password is the password that you chose on creating the server. Now you're good to work.

user_104037150

I had built both a console app and a UWP app and my console connected fine, but not my UWP. After hours of banging my head against the desk - if it's a intranet server hosting the SQL database you must enable "Private Networks (Client & Server)". It's under Package.appxmanifest and the Capabilities tab.Screenshot

user_104432400

If you have more than one Instances... Then make sure the PORT Numbers of all Instances are Unique and no one's PORT Number is 1433 except Default One...

user_107028560

In my case the problem was caused by the inconsistency between computer names. In system settings my computer was named with some long name, but apparently the name used for some certain communications was trimmed.

I changed the name in the settings to a shorter one and it worked.

user_44000600
Related