SQL Server Performance Forum – Threads Archive

1st login always fails, 2nd one always succeeds

There is a SQL Server machine running Windows 2003 Standard Edition and SQL Server 2000 Standard Edition + SP3a. When I log to this machine and then try to connect to SQL Server, I have no problems. However, the first attempt to connect to SQL Server from any another computer (I tried to do this from two different machines) always fails. The second one always succeeds. Here’s what exactly happens:
1. I open Query Analyzer.
2. I type Login name, Password and hit Enter.
3. Nothing happens for quite a while and finally I get a message box saying:
ODBC: Msg 0, Level 16, State 1
[Microsoft][ODBC SQL Server Driver]Timeout expired
4. I press OK to close the message box.
5. I hit Enter one more time.
6. This time I connect without any delays. It seems like SQL Server machine is ‘sleeping’. My first login attempt wakes it up. During second attempt it is ready to accept a connection. Let me know if something is not clear here. — Marek Grzenkowicz

---

We had a client reporting something similar. With SQL authentication, "It it possible to log in to a workstation at the SECOND attempt – PROVIDED that the first attempt was with an incorrect password. If you try to log in at the FIRST attempt with your correct password, this will be rejected. It then appears necessary to use an incorrect password for the SECOND attempt, and then you will be allowed in using the correct password at the THIRD attempt." Annoying, but they decided to put up with it. Plus they didn’t want us to investigate on-site, so unfortunately I have no further details or explanations.

---

What is the authentication mode used to connect?
Any differences between those 2 client machines? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

SQL Server authentication. One of them runs Windows 2000 and the other one – Windows 2003.
But the behaviour is exactly the same – the first login attempt always fails. — Marek Grzenkowicz

---

Using the SQL 2000 Client Network Utility, change Named Pipes to be the first entry in the Enabled Protocols by Order list box on both the machines and also try for TCP/IP protocol. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Satya, I’m getting confused: the Client Network Utility is installed on workstation or server? Assuming it’s on workstation, is there a server-side setting for this as well? Our client application specifies either TCP/IP or Named Pipes on any connection string, so I would expect that to overrule anything that was set in general for the computer. Thanks,
Adriaan

---

As I mentioned, there are two clients and a SQL Server. I’ve realized that there are two Active Directory domains. SqlServer:
– Domain1
– Windows 2003 Client1:
– Domain1
– Windows 2003
– for no apparent reason I can now connect to SqlServer Client2:
– Domain2
– Windows 2000
– the first login still fails all the time; moreover, if I finally connect to SqlServer and leave Query Analyzer idle for some time, the SqlServer disconnects (but not always to make the whole situation even more bizarre) Anyway, do you think that the fact that SqlServer and Client2 are in 2 different domains may be the reason of this problem. — Marek Grzenkowicz

---

On the server you will see Client network utility alongwith Server network utility and using Server n/w utility ensure you’ve enabled similar netlibs. Any feedback from Network Admin for this behaviour from network side. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Is there a trust between the domains? Is it working correctly. Is the DNS between the domains working correctly. Sometimes you can get delays here. For example the connection to the sql server could take long time the first time (longer than the odbc timeout) and thus fail. But the next time you try it’s in the DNS cache and works directly. Sometimes you can see if there is an issue by logging in on your Client2 on domain2, but in the domain list (after pressing CTRL-ALT-DEL) select domain1 and login with an account from domain1 and see if it takes a long time. Another thing it could be that we had issues with are servers with multiple network cards. The request would come in on one card but then the server would try and respond back on another network card to the requesting client. After about 15-30 seconds Windows will notice that this doesn’t work and switch over and respond on the next network card. But before this is done the odbc timeout has already occured. But on the following request the route is cached and will work faster (until you restart the systems). To test this (if you have multiple network cards) ping the server from each client and ping all clients from the server by name to see that they all resolve eachother to the correct IP on correct network card. For example check that they all resolve to the "front end" IP where you run customer traffic and not to the "back end" IP where you run backup and management traffic. Because traffic on the management net is usually locked down. You can avoid these issues by verifying the order of your DNS suffixes and which cards should register themself in the DNS or not under TCP/IP properties on the network cards. For testing purposes you can add entries in the windows hosts file (C:WINNTsystem32driversetchosts) to see if this is the issue. You can also test using the entire DNS name of the server. From Client2 do not enter the sql server name as "myserver" byt "myserver.domain1.local" or whatever the domain is.

---

Thanks, Argyle, for all your tips. Unfortunately, I did not manage to test all of them today. I guess my problem is not caused by slow DNS, because there is no difference if I connect using IP address or server name (but please correct if I am wrong – I am not very good at networking stuff). Then, I wanted to test your suggestion about two network cards that may be the reason of my problem. While doing this, I turned off one of the cards. The one that I need to connect to this machine. A stupid mistake. And now there nothing I can do, since the machine is 200 km from where I am. I will get back to this topic tomorrow. — Marek Grzenkowicz

---

I solved the problem. After I added an entry like this:
xxx.xxx.xxx.xxx server_name
to hosts file on the client machine, there is no problem with logging to the server. But can anyone explain me why it helps?
Without this entry in hosts file, the first login attempt always fails, no matter if I use server name or its IP address. I thought that hosts file would be used only while logging by server name. — Marek Grzenkowicz

---

I’m certainly not a networking expert, but if the hosts file solves the problem then I would think that there is either a problem with the configuration of the workstation regarding DNS, or the DNS service that is handling the request is somehow not synchronized. Both DNS and the hosts file handle TCP/IP name resolution.

---

I agree with you and I used nslookup.exe to examine this issue. I also talked to the administrator and he told me that he is indeed experiencing problems with DNS (don’t ask me why he didn’t tell me this earlier!). However, I still cannot understand why DNS has to be used when I connect by IP address. — Marek Grzenkowicz

---

If I remember correctly SQL Server will try and do a reverse lookup on the IP if you just specify the IP address. So DNS can be an issue here as well.

---

Interesting point, Argyle. Someone suggested to me that using the IP address instead of the server name on a connect string would improve response times, but if I understand you correctly it will in addition make SQL Server double-check its own IP address – and that would be a certain amount of overhead, correct?

---

Sorry about being so meticulous, but can you explain why? — Marek Grzenkowicz

---

You were right. This is what Peter Yang from microsoft.private.directaccess.sqlserver has told me: When using IP address, MDAC will attempt to determine the host name of the IP address using a reverse lookup (from IP to DNS name). — Marek Grzenkowicz

---

There was an issue with this and MDAC 2.6 as well FIX: Connection to SQL Server Database Using IP Address Is Unusually Slow
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q300420

---

More results of my investigation (again this is what Peter Yang from microsoft.private.directaccess.sqlserver has told me): Based on my research with a netmon trace, SQL client ususally resolve
remote DNS name to IP address first, and also try to require remote
server’s WINS name etc via Netbios 137 naming resolution port. Then it will
try to connect remote server’s 1433 port if it is a default instance. Also, as I mentioned, the MDAC 2.6 version and later of the SQL Server
Network Library, Dbnetlib.dll, attempts to determine the host name of the
IP address using a reverse lookup if IP address is used. If the client
computer has a slow Domain Naming Service (DNS) server, or no DNS server,
and the local HOSTS or LMHOSTS file on the computer does not contain the
host name for the IP address of the SQL Server, the reverse lookup will
time out after five seconds. The DNS Server, being unable to resolve the name locally, truncates the
domain name from the host name and forwards the query to the configured
WINS Server(s). The DNS Server uses a source port of UDP 53 (DNS) and a
destination port of UDP 137 (NetBIOS Name Service). It also encrypts the
name that it sends to the WINS Server. — Marek Grzenkowicz

---

]]>