SQL Connection DNS issue

[Olaf Doschke]

Now this is a hard nut to crack:

We had Problems to login to an SQL Server 2008 R2 Server via these two connection strings:

1. Driver={SQL Server Native Client 10.0};Server=servername;DATABASE=tempdb;Trusted_Connection=Yes
2. Driver={SQL Server Native Client 10.0};Server=servername;DATABASE=tempdb;Trusted_Connection=No;Uid=...;Pwd=...

But we could connect via the direct IP Adress instead of the server name.

So the issue for sure is a name resolving issue, DNS service. Indeed we found two DNS services running in the domain, but shutting the new (unintended) one down also doesn't resolve the issue. Just as a side note: The SQL Server is quite new on a new Win2008R2 Server.

So far this is not connected to SQL Server (pardon the pun), but now about the hard to crack nut - I wonder about the error messages I got from SQL Server:

1. Connection String (as above, via Windows Authentication) returns Error 18452 - "The user is not associated with a trusted SQL Server connection."

2. Connection String (as above, via SQL Server Authentication) Error 18456 - "Login failed for user '<user name>'."

In the second case the SQL Server Error log tells, error state is 8, password mismatch, but the same password works local and with a remote connection using IP-Adress instead of Servername.

So there are two questions:
1. How does DNS name resolving influence the domain association of the client trying to log in?
2. How does DNS name resolving influence the password transfered to SQL Server, so it's coming in wrong?

We can - for now - live with an IP in the connection string, as the server has a static IP, but it would be nice to know what's going on and how to resolve the issue (sorry for yet another pun).

Bye, Olaf.

 

[gmmastros]

Can you check a couple things for me?

---------

On the server, check to see if the SQL Browser service is running.

Click Start -> Run
Type Services.msc
scroll down to "SQL Server Browser"

Is the service configured to start automatically? Is it currently running?

---------

Are you using a named instance of SQL Server, or are you using the default instance?


Click Start -> Run
Type Services.msc
scroll down to "SQL Server"

If you see a service named "SQL Server", you are using a default instance. If you see "SQL Server (Instance Name)", then you are using a named instance.

-----------

Can you tell me what port SQL Server is configured to run on?

Click Start -> Programs -> Microsoft SQL Server 200x -> Configuration Tools -> Microsoft SQL Server Configuration Manager
Expand "SQL Server Network Configuration"
Click on "Protocols"

TCP/IP should be enabled. If not, enable it.

Double click on TCP/IP.
Click on "IP Addresses"
Scroll down to "IP All"
What value do you see for "TCP Dynamic Ports" ?




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

1. SQL Server Browser is deactivated, configured to not start, the SQL Server service runs.
2. It's the standard instance and there is no other.
3. Network configuration has TCP/IP enabled of course.

We already found out how DNS did go wrong:
servername was resolved to the long name servername.hamburg.setmics.de, as hamburg.setmics.de is our long domain name. So far I did only know the domain name was HAMBURG.

servername.hameburg.setmics.de of course is interpreted as an internet adress and so it was resolved to the IP adress of our internet homepage

http://www.setmics.de

This of course must fail bitterly. Now I don't understand, how I get responses from SQL Server at all, still. Secondary DNS is shut off, as said.

Issue is solved by adding entries in HOSTS files with the local IP, so DNS does not forward this to the internet. Wasn't my idea to name a domain this way.

Bye, Olaf.