38

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leah
  • 2,547
  • 5
  • 23
  • 28
  • possible duplicate of [Can't connect to SQL Server 2005](http://stackoverflow.com/questions/873924/cant-connect-to-sql-server-2005) – ig0774 Aug 08 '11 at 20:23
  • 2
    This is more of a server configuration question than a programming one, you might have better luck asking on serverfault.com. – JYelton Aug 08 '11 at 20:23
  • Related: [Configuring Windows Authentication on SQLEXPRESS](http://serverfault.com/q/238837/29508) – JYelton Aug 08 '11 at 20:28
  • Try this: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-access-server-configuration-option?view=sql-server-2017 – Taras Melnyk May 11 '18 at 10:29

12 Answers12

58

  • 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 tcp/1433)
  3. Also add an exception for the SQL Server Browser. (port udp/1434)

More information:

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

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
  • @Filip De Vos I've followed instructions in your first link to open the two ports for SQL Server instance and SQL Server Browser service and I'm now getting a different error when I try to connect: "Cannot connect to [server].Login failed for user [user]" – Leah Aug 10 '11 at 13:38
  • After all restart SQL Server service – Shahdat Apr 12 '17 at 16:35
  • 1
    Funny thing is that I walked through this answer 5 or 6 times, then again browsed all around the internet, came again and this UDP 1434 just worked. I have no idea how because I even turned off firewall but that also didn't helped. – Abdul Saleem Jun 12 '23 at 20:42
21

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

Ashkan S
  • 10,464
  • 6
  • 51
  • 80
Keith Hays
  • 211
  • 2
  • 2
10

  1. Launch SQL Server Configuration Manager on your VPS.

  2. Take a look at the SQL Server Network Configuration. Make sure that TCP/IP is enabled.

  3. Next look at SQL Server Services. Make sure that SQL Server Browser is running.

  4. Restart the service for your instance of SQL Server.

JYelton
  • 35,664
  • 27
  • 132
  • 191
  • Thanks for your help. However, I've already tried all those steps as detailed in the link in my question. I've got that far and the error message is still appearing. – Leah Aug 08 '11 at 20:10
  • Ah, sorry I didn't realize the link had the same basic steps. You don't need SSMS necessarily because it is essentially a client application that connects to the server to allow you to execute queries and manage database tables, etc. If the server instance is set to not allow windows authentication (covered by steps 10-12) you will need to know the sql user/pw combo to connect. I'm not sure you can change the login method without SSMS. – JYelton Aug 08 '11 at 20:22
  • Leah just FYI SQL Server always allows windows authentication, but sql user authentication can be enabled/disabled. (Per [How can I disable the windows authentication mode of SQL Server](http://serverfault.com/q/141698/29508)). Good luck. – JYelton Aug 08 '11 at 20:29
  • So I can't connect with my normal remote desktop details then? I haven't set up any details to connect to the SQL Server instance because the SSMS installation fails as well! Is SSMS Express not compatible with SQL Server 2008 Express R2 or is there no reason you are aware of why the installation should fail? Thanks for bearing with me! – Leah Aug 08 '11 at 20:32
  • You should be able to connect using SSMS on your local workstation, using Windows credentials. My recommendation to make life easier is to post a question on serverfaul or superuser with the exact error messages you get when trying to install SSMS and try to get that to work. Configuring the SQL Server will be much easier then. To answer your question, SSMS Express should work just fine with SQL Server Express. – JYelton Aug 08 '11 at 20:37
  • Thanks. I didn't get much of an error message when trying to install SSMS but I'll try again and post the error message as you've suggested. – Leah Aug 08 '11 at 20:45
  • I have managed to install SSMS on my VPS now - I still can't seem to connect to the VPS instance from my local machine though. Do you have any ideas? – Leah Aug 10 '11 at 11:55
2

  1. Open the SQL Server Configuration Manager.... 2.Check wheather TCP and UDP are running or not.... 3.If not running , Please enable them and also check the SQL Server Browser is running or not.If not running turn it on.....

  2. Next you have to check which ports TCP and UDP is using. You have to open those ports from your windows firewall.....

5.Click here to see the steps to open a specific port in windows firewall....

  1. Now SQL Server is ready to access over LAN.......

  2. If you wan to access it remotely (over internet) , you have to do another job that is 'Port Forwarding'. You have open the ports TCP and UDP is using in SQL Server on your router. Now the configuration of routers are different. If you give me the details of your router (i. e name of the company and version ) , I can show you the steps how to forward a specific port.

Paul S
  • 81
  • 2
  • 13
2

I had the same issue where my firewall was configured properly, TCP/IP was enabled in SQL Server Configuration Manager but I still could not access my SQL database from outside the computer hosting it. I found the solution was SQL Server Browser was disabled by default in Services (and no option was available to enable it in SQL Server Configuration Manager).

I enabled it by Control Panel > Administrative Tools > Services then double click on SQL Server Browser. In the General tab set the startup type to Automatic using the drop down list. Then go back into SQL Server Configuration Manager and check that the SQL Server Browser is enabled. Hope this helps. enter image description here

Anthony Phan
  • 353
  • 3
  • 13
2

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.

Shemeemsha R A
  • 1,416
  • 16
  • 22
  • Outbound rule is not required. When setting firewall rules on the server, "outbound" means connecting to some other machine from the server. – mrmashal Jul 25 '22 at 20:01
0

I recently upgraded from SQL 2008 R2 to SQL 2012 and had a similar issue. The problem was the firewall, but more specifically the firewall rule for SQL SERVER. The custom rule was pointed to the prior version of SQL Server. Try this, open Windows Firewall>Advanced setting. Find the SQL Server Rule (it may have a custom name). Right-Click and go to properties, then Programs and Services Tab. If Programs-This program is selected, you should browse for the proper version of sqlserver.exe.

Jim
  • 1
0

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.

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Armand Mamitiana Rakotoarisoa
  • 57
  • 9
0

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

radrow
  • 6,419
  • 4
  • 26
  • 53
Andy
  • 1
0

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...

Dharman
  • 30,962
  • 25
  • 85
  • 135
Shahzaib Hassan
  • 57
  • 4
0

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.

radrow
  • 6,419
  • 4
  • 26
  • 53
0

Before download the last version and update your sql server to fix errors of TLS 1.2 on Sql Server 2012. For more information, check here.

lemon
  • 14,875
  • 6
  • 18
  • 38
Leandro Amaral Alves
  • 1