Jason
asked on
SQL 2008 [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Hi,
I have a user that is unable to open an Excel document properly. It has a bunch of connections it uses that go back to an SQL server on the network called LS1. It was working correctly until we added TCP and Named pipes using the cliconfg.exe to fix the connection that broke between their ERP software and the database. It did fix the ERP but this spreadsheet spits out [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. It then prompts for SQL server login and populates the Server box with the correct LS1\MSSQL_08 but both Trusted Connection and putting in his credentials spits out:
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
The weirdest part(s) is that he can connect to the server using SQL Server Management Studio just fine and a user in an office next door to him can access the Excel doc without receiving any errors at all and she had the same tcp and named pipes alias added to her machine as described above. I'm lost.
Any help would be greatly appreciated.
I have a user that is unable to open an Excel document properly. It has a bunch of connections it uses that go back to an SQL server on the network called LS1. It was working correctly until we added TCP and Named pipes using the cliconfg.exe to fix the connection that broke between their ERP software and the database. It did fix the ERP but this spreadsheet spits out [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. It then prompts for SQL server login and populates the Server box with the correct LS1\MSSQL_08 but both Trusted Connection and putting in his credentials spits out:
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
The weirdest part(s) is that he can connect to the server using SQL Server Management Studio just fine and a user in an office next door to him can access the Excel doc without receiving any errors at all and she had the same tcp and named pipes alias added to her machine as described above. I'm lost.
Any help would be greatly appreciated.
ASKER
I take that back. The girl next door to him was on it all morning but is getting the same error now.
What is it exactly you configured with cliconfig? Did you only define an alias? If so, that should not change anything. However, if you have added Named Pipes in preference over TCP, it might make a difference.
In the users computers run a PING LS1 to see if reaches the server. If so, then run a telnet command to verify if it reaches the SQL Server instance.
Two qustions for you
Whats the verion of the sql, and is?
Did you clear the cliconfg or is it enabled on the client?
Whats the verion of the sql, and is?
Did you clear the cliconfg or is it enabled on the client?
Open cliconfig and remove any entries on the tab "Alias".
If that does not help, open Registry, export
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver\Client
and post here.
If that does not help, open Registry, export
HKEY_LOCAL_MACHINE\SOFTWAR
and post here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for jhill777att's comment #a40778885
for the following reason:
Fixed it.
Accepted answer: 0 points for jhill777att's comment #a40778885
for the following reason:
Fixed it.
Named Pipes - cannot recommend their use at all. Unreilable, a lot of issues with establishing connections, name resolution and speed ...
Your solution is based on you having defined unnecessary alias for your server, with specified protocol Named Pipes, which forced connecting to this server with named pipes, so you had to adjust your client application to use them as well. The true solution should have been removing the alias, as we recommended.
Suggested solution is a workaround that leaves the true problem in place.
ASKER
Well then I guess we need a different resolution otherwise that is what has to be done.
https://support.microsoft.com/en-us/kb/888228
I did remove the alias. Didn't work.
Regedit:
https://support.microsoft.com/en-us/kb/888228
I did remove the alias. Didn't work.
Regedit:
In your solution you forced protocol "named pipes" in the correction string (https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28v=vs.110%29.aspx) . Definitely not something to recommend, especially without understanding why. If removing the alias did not work, going to the root of the problem would involve finding out how it was trying to connect and where, possibly by using network monitor. Also checking what protocols are enabled at the server, such as if you did not disable tcp/ip at the server.
...also, should have checked at the first tab of client utility, if tcp/ip is enabled in the client to begin with.
ASKER
Named pipes and TCP/IP is enabled on the client.
Is tcp/ip enabled on the server?
https://msdn.microsoft.com/en-us/library/ms191294(v=sql.100).aspx
https://msdn.microsoft.com/en-us/library/ms191294(v=sql.100).aspx
,also, once again:
========
If that does not help, open Registry, export
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver\Client
and post here.
========
========
If that does not help, open Registry, export
HKEY_LOCAL_MACHINE\SOFTWAR
and post here.
========
You posted on one branch, not even obvious which. Is there anything under db-lib? Is there port number specified for tcp/ip? in client utility, is TCP/IP among the network libraries on the last tab?
The error message "sql server does not exist or access denied" is very well known, and there's extensive troubleshooting literature, starting with
https://support.microsoft.com/en-us/kb/328306
The error message "sql server does not exist or access denied" is very well known, and there's extensive troubleshooting literature, starting with
https://support.microsoft.com/en-us/kb/328306
by the way, one more possibility (from the above-mentioned article), especially since another user experienced the same, is checking the firewall, if 1433 did not suddenly become blocked.
Verify that tcp/ip is enabled on the server.
The reason to do all this, and to find why tcp/ip is not working, rather than simply leave np: in the connection string, is because this means that you forever will be using only named pipes in all future solutions in all database-aware applications.
The reason to do all this, and to find why tcp/ip is not working, rather than simply leave np: in the connection string, is because this means that you forever will be using only named pipes in all future solutions in all database-aware applications.
if you click "Properties..." on the 1st screenshot from the prev. message, it will show the port.
If you know the port the server is listening at, run telnet from command prompt:
telnet LS1 1433
assuming that LS1 is the name of the server and 1433 is the port it's listening at. The screen should immediately become fully black. If it hangs at connecting phase, then it's network issue, possibly firewall is blocking.
telnet LS1 1433
assuming that LS1 is the name of the server and 1433 is the port it's listening at. The screen should immediately become fully black. If it hangs at connecting phase, then it's network issue, possibly firewall is blocking.
ASKER
> so I can't check telnet as it's surely blocked and the user is "working" so I can't take over his computer at the moment.
psexec \\<user's computer name> cmd.exe
(https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx)
psexec \\<user's computer name> cmd.exe
(https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx)
ASKER
Fixed it and hasn't had problems since.
ASKER
Provider=SQLOLEDB.1;Integr
**Above, I substituted "Companyname" for our name.**