If you are receiving the following error related to connection to SQL Server, this blog is for you.
TITLE: Connect to Server
——————————
Cannot connect to Database Server.
——————————
ADDITIONAL INFORMATION:
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: 1326)
I suggest that you read previous article first as that describes how to fix the problem and has worked in many cases.
SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server
Now if SQL Server can be connected perfectly from local system, but cannot be connected from a remote system, in that case firewall on the server where SQL Server is installed can be issued.
Follow instructions of this article to fix the issue.
Go to control panel >> Firewall Settings >> Add SQL Server’s Port to Exception List.
Now try to connect to SQL Server again. It will allow you to connect to the server successfully. Please leave a comment with your experience.
Reference: Pinal Dave (https://blog.sqlauthority.com)
141 Comments. Leave new
Hello,
Just to add 2 cents to your notes,
I came across same problem last week, and I came to know that we have to do the following things in order to solve the above issue,
1. Disable windows firewall, or make an exception and add port number for SQL, this works well if you have only one instance of SQL Server, if you have more than one SQL Server instance then I guess you have to enable all other ports used by SQL Server.
2. In “Client Network Utility” tool of SQL Server add the server name of ip address of the server you are trying to register in the Alias section and most important choose which type of protocol you want to choose, either named pipes or TCP/IP
3. If you are using 2005, then use SAC to enable, allow remote connections to server and to use both TCP/IP and named pipes to connect.
Please correct me if I am wrong.
Thanks,
Imran.
I’ve chenged the TCP/IP address of the SQL protocols and now i can’t remember it can you please help me how to recover it i get no connectivity error
There are couple of to troubleshoot this problem. The one you should use depends on how your databse server was configured.
For example, if you configure the database engine to use dynamic port allocation, make sure that sql browser is runnning.
SQL browser provides the connection information about the database engine to the the client.
If the sql browser is not running and you have restarted sql server and port 1433 is being used by other applications,
database engine will be allocated a different port number. Imagine that the client has been configured to use port 1433
to connect and the database engine is using a different port number. The client wont be get the connections properties to the database engine from sql browser
because sql browser is not running.
To avoid this type of problems, when the database engine has been configured to use dynamic ports, make sure that your sql browser
is always running.
Sorry–
There are couple of ways to troubleshoot this problem. The one you should use depends on how your databse server was configured and some other factors as well.
For example, if you configure the database engine to use dynamic port allocation, make sure that sql browser is runnning.
SQL browser provides the connection information about the database engine to the the client.
If the sql browser is not running and you have restarted sql server and port 1433 is being used by other applications,
database engine will be allocated a different port number. Imagine that the client has been configured to use port 1433
to connect and the database engine is using a different port number. The client wont be get the connections properties to the database engine from sql browser
because sql browser is not running.
To avoid this type of problems, when the database engine has been configured to use dynamic ports, make sure that your sql browser
is always running.
I swear, I have absolutely exhausted every concievable solution to this problem, and I’m still stuck.
I’m getting this error in CRM 4.0 when embedding an ASP page with a gridview of information from a stored procedure on my database.
I’m pretty sure it’s Kerberos related, because the page connects fine on my development workstation, but fails on the CRM web server (where there’s a double-hop between my credentials and the SQL server) It’s been broken for weeks now. Here is a (partial) list of the fixes that have NOT worked:
Surface Area configuration: Both Named Pipes and TCP/IP enabled.
SQL Server Browser is running
Added the server name, and the server\instance into my HOSTS file on the server.
Changed my connection string to the IP address, just the hostname, and the hostname\instance name
Added SPNs for every possible permutation of: hostname, host\instance and IP address.
Confirmed that both the NT AUTHORITY\NETWORK SERVICE and HOSTNAME$ accounts have connect rights to the database.
The final weirdness: I wrote another page and put it into another web application on the same web server. It DOESN’T have the error, but it doesn’t work either. The gridview just comes through as an empty tag. Other ASP controls, like labels and buttons, work fine.
At this point, I will give either pinky to get this working.
Forgot: the windows firewall is disabled, and besides, the app works when running in the ASP development server of VS 2005 n my workstation, and of course, all of CRM works (although it mostly uses web services).
I’m getting this same error message ONLY when I specify the IP address as the server name. Everybody else in the office can hit the SQL Server — using the IP address — on my partner’s PC except me! And I can connect if I use the machine name instead of the IP address.
This just started this week, after I installed SQL Server 2008 on MY machine — my partner’s box, the one I’m trying to connect to, is still 2005. It worked fine with EITHER machine name or IP address before that.
It’s not a firewall issue, since everybody else can connect to his server using machine name _OR_ IP address. I can only connect using machine name.
It’s not a protocols issue on his server, because — again — everybody else can connect to him except me. And I ONLY get that error message if I specify the IP address instead of the machine name.
And yes, the IP address is correct. :)
Anyone have a clue?
provider: Named Pipes Provider, error: 40 – Could not open a I was getting this error:
connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I changed the protocol from Named Pipe to TCP /IP and it worked. Thanks.
I was getting this error:
provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)
I changed the protocol from Named Pipe to TCP /IP and it worked. Thanks.
Thanks for the tip about the firewall Dave. I was struggling with this.
Dear sir, i have read your threads for following error :
“An error has occured whil establishing a connection to the server. When connecting to SQL Server 2005, this failure may be casued by the fact that under the default settings dose not allow remote connection.(Provider : Named Pipes Provider, error:40-Could not open a connection to SQL Server)(Microsoft SQL Server Error : 5)”
so please send me possible solutions which i can try.
regards,
ritesh
Dear sir,
i agreed with Ritesh… i m facing same problem, so please send me solutions.
i have read your threads for following error :
“An error has occured whil establishing a connection to the server. When connecting to SQL Server 2005, this failure may be casued by the fact that under the default settings dose not allow remote connection.(Provider : Named Pipes Provider, error:40-Could not open a connection to SQL Server)(Microsoft SQL Server Error : 5)”
so please send me possible solutions which i can try.
regards,
yatin
I had a similar problem. What I did were change the port address, disable firewall and configure the Network.
Thanks this post help me to solve the problem.
I’ve a named instance (say, INST1) with the port changed to 1432
Under “Connection to server”
SERVERNAME,1432 ==> Every client works fine
SERVERNAME\INST1 ==> Some machines cannot make connection with this server name (seems cannot resolve the port no.)
It makes me confuse since not all clients cannot make connection with SERVERNAME\INST1
Hello Sir,
I am facing one small problem in one of my dummy SQL setup that I have prepared for TPC-E benchmark.This is just to have testrun before I go ahead with the actual test with real setup.
Just to give a brief : I have different Database server and Client server .Setup is prepared as mentioned below.Now I am trying to use SNAC tool to establish connectivity between the two .But MDAC is working fine.My trouble epicenter is Server-Client Connectivity using SNAC tool.
I have a separate Server installed with SQL Server 2008 , service also started , created one database through TPC-E scripts, installed SNAC tool.
I hv separate Client machine on same network ( both can reach each other over TCP/IP) , installed SNAC tool.
Now I am getting an error: “Message:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ” , whenever I start some TPC-E scripts on Client machine to start work on Database server.
In the process I tried some DSN configuration as described here . I tried to define it on both Server and Client .But this is not working.
Have also tried with Aliases creation .Referring to .
What settings to be made on both Server side and Client side to overcome this issue. Any extra components I need install on Client side or Server side.
One more thing I hv observed is that SNAC version on both Client and server are different , can this be a problem. Is it required to be same.
Any relevant inputs on this will be helpful.
At the end I should tell you that I am pretty new to this SQL server domain , so my level of struggle has been quite at a higher scale all through the project run.Hope this will over soon.
Thanks & Regards,
Sudipt.
After all the steps mentioined above, you might still not be able to connect for SQL2005 Express.
One of the trick for SQL2005 express is that when you connect the server, you can’t just type the server name but have to type “servername\EXPRESS” instead. This applies to both SQL Server Management Studio and the code.
Errors:
An error while executing your request.
A network related or instance-specific error occurred while stabling 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: name pipes provider, error: 40 could not open a connection to sql server.
error: Msg 17, level 16, state 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB] SQL Server does not exit on access denied how to solve
Dear sir,
I got this error while configuring transactional replication between two different SQL Server 2008.
I have two SQL 2008 Servers, connected via internet such as A and B.
I configure Publisher on Server A and try to add the subscriber the server B, but unable to connect the server B
error 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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)”
When I use the Management studio of Server A to connect the server B the connectivity is done successfully,
But while adding subscriber it does not work..
Please help me in this regards.
Hi,
I’ve installed SQL Server 2005 and ‘Query Express’ to access it on the same PC. When I’m getting this connection failure error 40 message & I’ve applied all the advise given on above but nothing seems working. I feel exhausted now. Please adivse.
Many Thanks.
Hi All
I have a SQL Server 2000 running on Win2k3.
I am trying connecting to SQL locally on the Enterprise Manager on the machine but
I get this error: “A Connection could not be established to local
Reason: Encryption not supported in sql server
Connectionopne (prelogin(handshake)).”
The odd thing is that I can connect to that SQL Server from other
remote machines through Enterprise Manager, I just can’t connect
locally. All help is appreciated.
Thanks
Hi All,
I was able to solve it. We just need to uncheck the Encryption support in Client Network Utility. But now I am not able to connect to other sql server DB on other machine, but still I can’t connect to this DB from other machinet. I am getting error
“Msg 17, Level 16, State 1
[ODBC SQL Server Driver][DBNETLIB]Sql Server does not exist of access denied
Please help me to troubleshoot this.
Thanks
Mithalesh