Error Has Occurred While Establishing A Connection To SQL Server 2005 Which Does Not Allow Local and Remote Connections
When connecting to an instance of Microsoft SQL Server 2005 database, including SQL Server 2005 Express, Developer and Enterprise editions, for authentication or accessing and retrieving and updating the data, the the following error message may appear. The problem happens no matter the connection is originated from remote computer or on SQL Server local computer itself, and can be caused by any program, such as SQLCMD utility, used to connect to SQL Server.
Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
Or,
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, 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) (Microsoft SQL Server, Error: 2)
This problem may occur when SQL Server 2005 is not configured to accept incoming local and remote connections, which is disabled by default in SQL Server 2005 Express Edition, SQL Server 2005 Developer Edition and also SQL Server 2005 Enterprise Edition. To solve the connection forbidden problem, SQL Server 2005 needs to configure to allow incoming local and remote connections.
Firstly, ensure that SQL Server 2005 is configured properly to allow incoming connections on the instance of database server, else enable and turn on the local and remote connections setting.
- Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
- On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
- On the “Surface Area Configuration for Services and Connections” page, expand Database Engine tree, click Remote Connections.
- Select Local and remote connections, or Local connections only which applicable only if there is no remote system tries to connect to the SQL Server, useful when you just trying to connect and authenticate with the server after installing.
- Select the appropriate protocol to enable to local and/or remote connections environment. To ensure maximum compatibility, select Using both TCP/IP and named pipes.
- Click Apply button when done.
- Click OK button when prompted with the message saying that “Changes to Connection Settings will not take effect until you restart the Database Engine service.”
- On the “Surface Area Configuration for Services and Connections” page, expand Database Engine, then click Service.
- Click Stop button to stop the SQL Server service.
- Wait until the MSSQLSERVER service stops, and then click Start button to restart the MSSQLSERVER service.
Secondly, SQL Server Browser service has to be enabled to allow for local and remote connections if SQL Server 2005 is running by using an instance name and users are not using a specific TCP/IP port number in the connection string.
- Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Surface Area Configuration.
- On the “SQL Server 2005 Surface Area Configuration” page, click Surface Area Configuration for Services and Connections.
- On the “Surface Area Configuration for Services and Connections” page, click SQL Server Browser.
- Select Automatic as the Startup type to start SQL Server Browser service automatically every time system starts.
- Click Apply button.
- Click on Start button to start the service immediately.
- Click OK button.
Finally, if remote computer needs to connect and access SQL Server, an exceptions in Windows Firewall included in Windows XP SP2 (Service Pack 2), Windows Server 2003 and Windows Vista needs to be created. If you’re using third-party firewall system, the exception rules also needed to be created to allow external remote connections to the SQL Server 2005 and SQL Server Browser Service to communicate through the firewall, else connections will be blocked. Consult the firewall manual for more details. Each instance of SQL Server 2005 must have its own exception, together with an exclusion for SQL Server Browser service.
SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:
- Click Start button, then go to Programs or All Programs, then select Microsoft SQL Server 2005, followed by Configuration Tools. Click and run the SQL Server Configuration Manager.
- In “SQL Server Configuration Manager”, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
- On the “SQL Server Browser Properties” page, click the Advanced tab, locate the instance ID in the property list.
- Click OK button.
Then create an exception for SQL Server 2005 in Windows Firewall.
- Click on Start button, the click on Run and type firewall.cpl, and then click OK. For Windows Vista, type firewall.cpl in Start Search box and press Enter key, then click on Allow a program through Windows Firewall link on left tasks pane.
- In “Windows Firewall”, click the Exceptions tab, and then click Add Program.
- In the “Add a Program” window, click Browse button.
- Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open button. MSSQL.1 with is a placeholder for the instance ID that is obtained from previous procedure. Note that the path may be different depending on where SQL Server 2005 is installed.
- Click OK button.
- Repeat steps 1 through 5 for each instance of SQL Server 2005 that needs an exception.
- For SQL Server Browser service, locate the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, and click Open button.
- Click OK button.
Related posts:
- Uninstall and Remove Multiple Database Instances of Microsoft SQL Server 2005
- Connect to Remote Computer using Specific Non Standard Port using Remote Desktop Connection Client
- Easily Maintain Multiple Remote Desktop Connections with visionapp Remote Desktop (vRD)
- Microsoft Virtual Server 2005 R2 Download Available for Free
- Vista Remote Desktop Connection Client Slow Delay or Disconnect When Connect to Windows Server 2003 SP2 x64 Edition RDP
I faced same issue and after following many techniques I failed to get rid of this issue. I followed mentioned steps and got rid of this issue. Thanks alot.
Thanks a lot
Login Not Correct: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: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
Here definite way to solve the problem.
Open SQL Server 2008
At Server name select 'browse for more'
Go to Network Servers and select the listed server.
Connect using Windows Authentication mode and u are done!
In SQL Server 2008,
To enable remote connections,
open SQL Server Configuration Manager->SQL Server Network Configuration-?Protocols…-> Right click TCP/IP and enable it
I have followed all the steps but still I am getting same problem.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.)"
I do not have sql client installed so I can only test by making a connection through visual studio. It is giving me same error even after executing all the mentioned steps.
That was really helpful!! Thank you!!!!
I followed the solution step by step. But it didnt work me
Thank you very much, I found it very useful.
Thanks pall, this document helped me out several times now.
Hi
I got the reasons for the following error. This error is comming because you have to enable remote connection for sql server
Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under
the default settings SQL Server does not allow remote connections.
Regards
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, 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)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, 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)
Source Error:
Line 22: MyStr = "Server=localhost;Persist Security Info=False; Integrated Security=SSPI;database=MyDatabase;"
Line 23: Connect = New SqlConnection(MyStr)
Line 24: Connect.Open()
Line 25: Dim StrQry = "Select ProductType From ProductTyes"
Line 26: Command = New SqlCommand(StrQry, Connect)
Source File: C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2008WebSitesASP.NETProjectIndex.master.vb Line: 24
Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, 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)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +421
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +357
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +494
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
Index.Page_Load(Object sender, EventArgs e) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2008WebSitesASP.NETProjectIndex.master.vb:24
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Control.LoadRecursive() +131
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
——————————————————————————–
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Please help me. I've tried to follow step by step the direction above, but it is also get this problem. Would you please solve it successfully? Thank you so much.
i get the same problem with godaddy.com hosting
dn you know how i solved it on godaddy ?
I am trying to connect a sql server 2005 instance installed on XP SP2 from a sql server 2005 express edition on vista.
I'v done all the changes on both the machines, but my vista machine still does not connect with the sql instance on XP.
what could be the issue here ?
Thanks for your help…
It solved my problem..
It was really very useful
Thanks. It solved my problem.
Great article
men tengo un problema! yo uso el mumaker! y cuando establesco una conex remota! para el sql q lo tengo en el servidor en otra pc me sale ese mismo error! en esta pc donde tengo el maker no tengo ningun sql instalado solo los net.framework del 2.0 al 3.5 y aun asi me sale el error del sql 2005 cuando no lo tengo aqui e incluso en la pc donde tengo el server uso el sql 2003! no entiendo la vdd? crees q sea mi so? xq en esta pc tengo un skin el illuusion v2 y enla otra donde tengo el server el xp s2! espero tu pronta y grata respuesta
ขอบคุณมากจ้าาา (Thailand)
Thanks for the detailed explanation and step by step guide for connection problem
Thanks for detailes explanation on this very common and frequently faced problem by sql developers.
Thank you very much… Very useful solution.
simply superb! Thanks a lot.
GREAAATT succESS!
I restarted the service from services menu, for some reason that did not work. Your solution worked perfectly. Thanks.
for some reason this solution is working for me.
看了,好像没什么帮助
thanks a lot, helped me so much
It's Fabulous,
Thank You!
Muuuuchas Gracias
Thanks! Your solution worked fine for me! Appreciate all the help!
Thank you for this. Very useful.