SQL Server 2005 random login failures for windows forms application

Question

One of our VS .NET 2008 windows forms applications runs from multiple client machines. The application randomly has login failures from random machines and from random places in the code. The login works almost every time but about once every fifteen minutes an error shows up in SQL Server error logs from a random user. We are running a clustered SQL Server 2005 system and the database is in 2005 (90) mode.

The following error appears in the SQL Server error logs:

Login failed for 'App_Login'. [CLIENT: XXX.XXX.X.XXX] Error: 18456, Severity: 14, State: 16.

The following error is what the client sees:

System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

The connection string used by the application is:

Data Source=XX.XXX.XXX.XXX;Network Library=DBMSSOCN;Initial Catalog=document7;User Id=App_Login;Password=PASSWORDHERE;

(NOTE: XXX.XXX.X.XXX and XX.XXX.XXX.XXX are the IP Addresses)

• The 'document7' and 'master' databases are in multi-user mode.
• The login has access to 'document7' and to 'master'.
• The clients sometimes run into the error but retrying the same action after a failure is successful without any changes.
• I am able to use 'App_Login' using the management studio without problems. I can access the 'document7' database and the 'master' database without problems.
• We have other applications that use the same connection string and have no problems.
• I have tried the login with a default database of 'master' and 'document7' with the same result in both cases.

Thank you for your time and any assistance!

Answer 1

In this case, I think that SQL Server's error is not as revealing as the client error. The client error states that the connection already existed and was severed which I think is the more accurate description of what is happening. This blog post explains the problem.

Basically, a connection in the pool is was severed for some reason (maybe inactivity or maybe network blip) and the client doesn't know that the connection was severed until it runs another query using it. That explains why this problem only occurs with our Windows Form's application because this application would leave connections inactive for much longer periods than the other applications. Also, the web application lives in the same physical location as the database server so won't have as many connection problems over the network.

To fix the problem, I just need to check for the error and re-run the query a second time. The query should work the second time because it will automatically recieve a new SPID and a brand new connection.

Answer 2

check this blog. Make sure you check out the comments and discussions below of one of which says :

"(Error: 18456, Severity: 14,) State: 16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master. "