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    Install SSMS 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

User · Accepted Answer

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:

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

User · Answer

I know this is almost 1 5 years old  but I hope I can help someone with what I found   I had built both a console app and a UWP app and my console connnected 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  amp  Server    It s under Package appxmanifest and the Capabilities tab Screenshot

User · Answer

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

User · Answer

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

User · Answer

Launch SQL Server Configuration Manager on your VPS  Take a look at the SQL Server Network Configuration  Make sure that TCP IP is enabled  Next look at SQL Server Services  Make sure that SQL Server Browser is running  Restart the service for your instance of SQL Server

User · Answer

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      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       Now SQL Server is ready to access over LAN        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

User · Answer

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

User · Answer

Open mysql 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 tool bar  then click on this play button  wait till its state turn into  running   Now your good  Switch back to the sql server management studio  switch the  server type  to  database engine  and  authentification  to  sql server authentification   the default login is  sa   and the password is your password that you ve choose on creating the server  Now your good to work

User · Answer

Disable the firewall and try to connect  If that works  then enable the firewall and Windows Defender Firewall - gt  Advanced Settings - gt  Inbound Rules Right Click  - gt  New Rules - gt  Port - gt  Allow Port 1433  Public and Private  - gt  Add Do the same for Outbound Rules  Then Try again

User · Answer

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

[sql-server] Unable to connect to SQL Server instance remotely

Examples related to sql-server

Examples related to database

Examples related to remote-access

Examples related to sql-server-2008r2-express