Solved

SQL authentication issue

Posted on 2008-06-17
14
294 Views
Last Modified: 2010-04-21
I am using a third party EDI application and currently running it on my SQL server until I am ready to deploy it to a work station within the network.

I am suddenly getting the following connection error:

[DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied 0 Connection

To date, I have had no issues connecting, either on the machine itself, or from within my network.

At first I thought it was the EDI app. Then I tried to create a new DSN on the server itself and got the same basic error.

Here are some details:

1. I can log into the SQL Mgmt Studio from the server itself. No trouble.

2. I can access the SQL DB via apps within the network. No trouble there either.

3. I have a remote ODBC SQL connection for Access from my home network. It works too.

4. SQL version is 2005

5. In the logs I did notice a few days ago the SQL port was being attacked. I just realized the firewall was set to port 1433 open to any IP. I just locked that down.

6. I also noticed that the EDI developer installed SQL Express, after I had installed SQL2005. I read this could be an issue but felt I may have been chasing my tail.

Anyway, I think I can end around this issue by installing the EDI program on a network workstation, where it will evenually need to be anyway.

But I am really concerned that there is a bigger issue here.

Thanks!

0
Comment
Question by:jklinger1975
  • 7
  • 6
14 Comments
 
LVL 76

Expert Comment

by:arnold
Comment Utility
The bigger issue being you have two applications that try to bind to port 1433.
Even though SQL 2005 might use the default instance and t he express install is using an servername/instance, they will still try to use TCP port 1433.  It is not an issue of having sql2005 and SQL 2005 express, the issue is on what IP/PORT are the services configured.

Add a second IP to the server and bind the Express version to the new IP and you should be fine.
 
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Try using SQL Config Manager to check what ports are being used for each service. That should definitely help. Don't use dynamic ports, but if you can tell your apps to connect on a different port, then it may well be worth considering.

Rob
0
 
LVL 1

Author Comment

by:jklinger1975
Comment Utility
Well, I tried both reassigning the port and IP for SQL Express.

And now it seems I can no longer authenticate at all to the SQL server. I do not believe the two events are related. I think basically all authentication turned off after restarting the server.

So now the issue escalates because the SQL server has the main biz file on it.

Any more thoughts??
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
Look at netstat -ano and see whether there is a process listening on 1433 or on the port you specified for the second instance.
Did you configure sql authentication or windows and sql authentication.  If the latter, try connecting with windows credentials when you run the SSMS as administrator.
You can use the sql server surface area configuration and/or the sql configuration to alter the authentication mode.
Are you specifying a specific port when trying to connect?
0
 
LVL 1

Author Comment

by:jklinger1975
Comment Utility
Sorry for the delayed response. Long day. Here is an update:

I installed SQL on another machine and migrated over the db's. Fortunately they are all running fine. So there is no DB corruption as far as I can tell.

On the original machine, I removed SQL Express and bounced the box. SQL2005 now only one running but unfortunately errors continue.

Is it possible for SQL authentication to completely corrupt? Errors I am getting are that database server cannot be found and sometimes invalid login.

Not sure what netsta is.... SQL is running is mixed authentication mode.

I may try a total reinstall. However it would be good to know why this happened.
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
The error you have does not include a reason for the error so it is difficult to say whether the issue is because of a typo in the connection string, a firewall that prevents access,etc.
0
 
LVL 1

Author Comment

by:jklinger1975
Comment Utility
arnold -

Actually it is in the original post.

[DBNETLIB][ConnectionOpen (Connect()).] SQL Server does not exist or access denied 0 Connection

I get this error when I try to connect to a previously (successfully) configured ODBC connection.

Something I was starting to look at yesterday that I though was odd....

That previous System DSN (call it HFT) worked when I first set it up. In the connection properties I defined the driver as "SQL Server"

When I go into set up a new ODBC I now see another driver choice called SQL Native Client. This one works!!

It's as if all of my connection used to use the drive called SQL Server and suddenly now they all want to use this new SQL Native Client.

Weird....Since the connection previously worked and there have been no infrastrucutre changes, I can pretty much assure that it is not a connection string or firewall issue.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 76

Expert Comment

by:arnold
Comment Utility
The error is from the DNETLIB and not an error that is generated by the SQL server.
Does the DSN use windows authentication or does it use SQL authentication?  Go through the DSN configuration and make sure to test the client connection.

http://www.experts-exchange.com/Software/Server_Software/Web_Servers/Microsoft_IIS/Q_22522271.html
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3423395&SiteID=17

I think, SQL Native Client is part of the SQL 2005 feature set that can handle mirrored databases.
0
 
LVL 1

Author Comment

by:jklinger1975
Comment Utility
SQL uses mixed authentication (windows and sql)

Both used to work

And now neither works.

I will check the link you suggest.
0
 
LVL 1

Author Comment

by:jklinger1975
Comment Utility
In link 1 it is suggesting a possible name resolution issue. That has been checked. I cannot connect via server name or IP. And when I ping, I get the expected results.

Link 2 gave me a page error.

In testing the DSN I am confirming my driver belief.

If I create a new DSN, in ODBC connections and use the SQL Native Client, I have no trouble. However, I have never used this before.

For all apps and connections, I previously used the driver SQL Server. It always worked. Now it does not.

So the question... why did they switch?? Very odd.
0
 
LVL 76

Expert Comment

by:arnold
Comment Utility
Who switched, MS?  Check with the application vendor to see whether they have any known issues with their application and your setup i.e. sql2005.
Prior to this issue, did you apply patches/service packs, updates on the servers or the local computers?  It is possible that one of them interferes.

When creating the DSN, did you test the DSN? and is the current DSN Successfully connects?
SQL Native client provides mirror database automatic failover.  This is target for small businesses who can purchase a clustered server + SAN solution to maintain their systems/aplications up and running 365/24x7 in the 99.9999 server/application up time.
0
 
LVL 1

Author Comment

by:jklinger1975
Comment Utility
I did uninstall ACT. That was the only change. However there is a sql component involved.
0
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
Comment Utility
Are you sure that by uninstalling ACT you did not remove an item on which this application relies?

You have to determine whether the application with which you are having difficulties relies on an ACT SQL instance.
0
 
LVL 1

Author Closing Comment

by:jklinger1975
Comment Utility
Without any other logical explanation, I am going to have to assume that it is the case.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now