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

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     In the Start Menu  open Programs   Microsoft SQL Server 2008   Configuration Tools   SQL Server Surface Area Configuration  In the Surface Area Configuration utility  click the link  SQL Server Configuration Manager   Expand  SQL Server Network Configuration  and select Protocols   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   Add an exception for sqlserver exe when you use the  Dynamic Port  system   Otherwise you can put  exceptions for the SQL Server ports  default port 1433  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

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

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

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

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

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

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

[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