I have opened up port 1433 on my firewall, but every time I try and connect to my remote SQL Database with SQL Server Management Console I receive (Microsoft SQL Server, Error: 53)
asked May 8, 2013 at 16:05
10
What you need to do is go into SQL Server Configuration Manager where SQL Server is installed.
In the Start Menu, under the Microsoft SQL Server folder, open the Configuration Tools folder and select SQL Server Configuration Manager.
In the Config Manager, click the arrow next to SQL Server Network Configuration, then click on «Protocols for {Instance}». {Instance} will be your install, if it’s the default instance it will be MSSQLSERVER.
On the right side, make sure that TCP/IP is «Enabled». Double click TCP/IP, in the window that opens, change the drop down to «Yes».
answered May 8, 2013 at 16:48
DanDan
5373 silver badges10 bronze badges
10
Here is the how to fix it and gain your connectivity back:
Click WindowsKey + R and enter services.msc
Once Services opens scroll down and locate service SQL Server
Highlight the service and click START
Wait for the service to start and retry to connect to your MSSQL instance
It should now work again and you will be able to connect to your MSSQL instance and run queries.
answered Dec 3, 2018 at 6:38
I have solved this problem. Please refer to this thread: http://social.technet.microsoft.com/Forums/forefront/en-US/d2624655-e6ff-4947-b1a8-a2edcffd8a21/denied-connection-netbios-session-protocol#68321990-4ac4-46fa-b7f8-9e0ded3234bb
In detail: I was receiving the following error from ISA when trying to connect from my SBS 2003 Server to my offsite MSSQL Database Provider via SQL Server Management Studio:
Denied Connection
Log type: Firewall service
Status: A packet generated on the local host was rejected because its source IP address is assigned to one network adapter and its destination IP address is reachable through another network adapter.
Rule:
Source: Local Host ( 192.168.1.1:29859)
Destination: External (...:139)
Protocol: NetBios Session
I have figured this out. I had to add «Local Host» as one of the sources for the ISA Rule that I had initially created to allow SQL Server Management Console connections in the first place. That rule was allowing «Internal» sources but not the Local Host (127.0.01), which is needed for this scenario.
answered Jun 21, 2013 at 0:40
e-zeroe-zero
3453 gold badges9 silver badges17 bronze badges
1
in SSMS while opening give the server name as your_Hostnameyour_sqlservername (give both the names as applicable instead of local or just only servername) it works fine.
answered Nov 23, 2014 at 11:11
I could ping my Virtual Machine SQL server but couldn’t connect to it.
I disabled my wired and wireless NICs and then it connected !
answered Feb 26, 2016 at 4:56
Today, I will explain how to fix SQL Server error 53 (The network path was not found). The details about SQL error 53 are given as “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. (Provider: Named Pipes Provider, error: 40 – Could not open a connection on SQL Server) (Microsoft SQL Server, Error : 53)”
You can also get below error similar to error 53 under below details.
An error has occurred while establishing a connection to the server. When connecting to SQL Server, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
Root Cause
Microsoft SQL Server Error 53 looks very much like the Error 40: A network-related or instance-specific error occurred while establishing a connection to SQL Server at first look. Like SQL Server error 40, here also SQL Server client cannot connect to the SQL Server Instance. This error could occur because either the client cannot resolve the name of the server or the name of the server is incorrect.
You might get this issue due to other reasons as well although main reason is the wrong server name or port name. I have given all those possible reasons behind the Microsoft SQL Server error 53 in below points.
- Wrong SQL Server Instance name while establishing database connection.
- Entering wrong port no can be one of the reason for SQL Server error 53.
- SQL Server Instance is not accessible due to firewall or any reason.
- Telnet port 1433 or port no on which SQL Server is running. These ports might be blocked.
- TCP/IP or Named Pipes protocol is disabled in SQL Server Configuration Manager.
- Remote Connection is disabled for this SQL Server Instance.
- SQL Server Browser service is stopped.
Solution
We need to check and validate multiple factors to rectify Microsoft SQL Server error 53. Check all factors given in below step by step points to fix all possibilities for this error.
- Sometimes, we enter wrong server details by mistake. Make sure to enter correct SQL Server instance name while making database connection otherwise you will face sql error 53.
- Try to connect using ip address and port number instead of putting server name in connection string.
- Make sure SQL Server services are running fine and its accessible.
- Check firewall details. Firewall should open between your client machine and database server. Port 1433 and 1434 should be enabled. Telnet both ports to make sure ports are open.
- Make sure to enable TCP/IP and Named Pipes. Launch SQL Server Configuration Manager. Expand SQL Server Network Configuration from left side pane and click on Protocols for MSSQLSERVER. MSSQLSERVER is SQL Server Instance name. Now you will see all protocols in to right side pane. Right click on given protocols and select Enable button to enable both protocols.
- Check TCP/IP name resolution, you can use the ping command in the Windows operating system. You can also right-click on TCP/IP and select Properties in SQL Server Configuration Manager. Verify that, under IP2, the IP Address is set to the computer’s IP address on the local subnet. Scroll down to IPAll. Make sure that TCP Dynamic Ports is blank. Make sure that TCP Port is set to 1433 or your identified port no.
- Remote connection must be enabled to connect to databases remotely. Connect to SQL Server Instance in SSMS. Right click on SQL Server instance name in SSMS and choose Properties. You will get Server properties window. Click on Connections from left side pane and tick the check box on Allow remote connections to this server option from right side pane.
- SQL browser service should not be stopped if your security policy allows it. If you have disabled this service to apply hardening then make sure to pass port no along with SQL Server instance name/IP while making database connection.
You can fix SQL error 53 by applying above solutions on your SQL Server instance. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
Related Articles:
- Fix Error 28 – Server doesn’t support requested protocol
- Error 40: A network-related or instance-specific error occurred while establishing a connection to SQL Server
- Error 4064: Cannot open user default database. Login failed.
- Fix Login Drop issue Error 15138 and Error 3729
- Author
- Recent Posts
I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Summary
Article Name
Fix Error 53: Could not open a connection on SQL Server
Description
Read this article if you are getting error 53 while connecting to SQL Server. Error details are «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. (Provider: Named Pipes Provider, error: 40 — Could not open a connection on SQL Server) (Microsoft SQL Server, Error : 53)»
Author
Manvendra Deo Singh
Publisher Name
www.techyaz.com
Hi N P,
Error 53 is an OS error which maps to ERROR_BAD_NETPATH (The network path was not found). Depending on how you setup your linked server, a connection from the current SQL Server process to the remote one will be attempted. Due to the nature of the error, it seems to be attempted with Named Pipes protocol and some of the OS calls (like CreatePipe for instance) returns the OS error 53. In most of the cases this is related to the network name resolution of the remote server or it might be permissions-related, e.g. if your current SQL Server instance is running under Local System account (which doesn’t have network access privileges).
Please see the detailed description on how to setup a linked server in the SQL Server Books Online, but to start you could try the following:
(a) Examine your current SQL Server service account (the local instance — the one where you are trying to setup the linked server to the remote machine). Depending on the authentication configured for the linked server, you might need to use an account which has network access. You could examine the service account using Management Studio or Configuration Manager
(b) Check the network name of the remote server. To eliminate network resolution issues, try to connect to it from the local SQL Server machine via sqlcmd or try to setup the linked server with the IP address of the remote machine instead of the name.
(c) Force using TCP/IP protocol instead of named pipes by either prefixing the server name with «tcp:» or by setting the protocols order (Configuration Manager) or by creating an explicit alias for it.
If the issue persists, please post the exact error again and your linked server configuration (security, etc.).
HTH,
Jivko Dobrev — MSFT
———————————————————————————
This posting is provided «AS IS» with no warranties, and confers no rights.
Hello there,
I am trying to establish a connection to another SQL Server using a linked server but I receive the below error message.
==================================
The test connection to the linked server failed.
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider «SQLNCLI10» for linked server «151.211.5.XXX» returned message «Login timeout expired».
OLE DB provider «SQLNCLI10» for linked server «151.211.5.XXX» returned message «A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check
if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.». (.Net SqlClient Data Provider)
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
——————————
Server Name: XXXXXXXXXX.ac.uk
Error Number: 53
Severity: 16
State: 1
——————————
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
By the way, trying to connect directly from Management studio to this external SQL server is not a problem.
It is establishing a linked server what is failing.
Just in case this might be of help
The server I am trying to connect to is version 10.0.1600.22
And the server I am connecting from is version 10.50.2500.0
Hello there,
I am trying to establish a connection to another SQL Server using a linked server but I receive the below error message.
==================================
The test connection to the linked server failed.
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(String cmd, Boolean retry)
at Microsoft.SqlServer.Management.Smo.LinkedServer.TestConnection()
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.LinkedServerConnectionTest.Invoke()
===================================
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider «SQLNCLI10» for linked server «151.211.5.XXX» returned message «Login timeout expired».
OLE DB provider «SQLNCLI10» for linked server «151.211.5.XXX» returned message «A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check
if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.». (.Net SqlClient Data Provider)
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.2500&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476
——————————
Server Name: XXXXXXXXXX.ac.uk
Error Number: 53
Severity: 16
State: 1
——————————
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
By the way, trying to connect directly from Management studio to this external SQL server is not a problem.
It is establishing a linked server what is failing.
Just in case this might be of help
The server I am trying to connect to is version 10.0.1600.22
And the server I am connecting from is version 10.50.2500.0