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.
JasonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JasonAuthor Commented:
It has several connections.  As an example, one of the connection strings looks like this:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ActiveM_Companyname;Data Source=LS1\MSSQL_08;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LFSERVERONE;Use Encryption for Data=False;Tag with column collation when possible=False

**Above, I substituted "Companyname" for our name.**
0
JasonAuthor Commented:
I take that back.  The girl next door to him was on it all morning but is getting the same error now.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Two qustions for you
Whats the verion of the sql, and is?
Did you clear the cliconfg or is it enabled on the client?
0
Vadim RappCommented:
Open cliconfig and remove any entries on the tab "Alias".

If that does not help, open Registry, export
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client
and post here.
0
JasonAuthor Commented:
Fixed it.  Had to change the Data Source to Data Source=np: LS1\whateverthepipeswereincliconfig.

Thanks for your posts.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JasonAuthor Commented:
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.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Named Pipes - cannot recommend their use at all. Unreilable, a lot of issues with establishing connections, name resolution and speed ...
0
Vadim RappCommented:
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.
0
Vadim RappCommented:
Suggested solution is a workaround that leaves the true problem in place.
0
JasonAuthor Commented:
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:  Regedit
0
Vadim RappCommented:
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.
0
Vadim RappCommented:
...also, should have checked at the first tab of client utility, if tcp/ip is enabled in the client to begin with.
0
JasonAuthor Commented:
Named pipes and TCP/IP is enabled on the client.
0
Vadim RappCommented:
0
Vadim RappCommented:
,also, once again:

========
If that does not help, open Registry, export
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client
and post here.
========
0
JasonAuthor Commented:
I posted the regedit stuff above.  Here is the protocols.
Protocols
0
Vadim RappCommented:
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
0
Vadim RappCommented:
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.
0
Vadim RappCommented:
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.
0
JasonAuthor Commented:
Protocols.JPG
0
JasonAuthor Commented:
General.JPGDb-library.JPG
0
Vadim RappCommented:
if you click "Properties..." on the 1st screenshot from the prev. message, it will show the port.
0
Vadim RappCommented:
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.
0
JasonAuthor Commented:
Port.JPGI'm vpn'd right now 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.  As far as he's concerned, it's good enough for now.
0
Vadim RappCommented:
> 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)
0
JasonAuthor Commented:
Fixed it and hasn't had problems since.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.