Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
According to this MSDN SQL Server blog, 16 means, "the incoming user does not have permissions to log into the target database." That doesn't really explain why the error would fix itself without any changes, though. –  Matthew Flaschen Aug 2 '10 at 16:25
    
Yes, the login does have access to the master database and the document7 database. I have verified this by using SQL Server Managment Studio using that login. I am fully about to access both databases without any problems. –  Mindy Aug 4 '10 at 15:02

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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. "

share|improve this answer
    
Thanks for your help. I saw that post when I was trying to solve this error and attempted the suggestions without success. 'App_Login' has access to the default database, 'master' and 'document7'. In addition, the login works when logging into management studio and I can verify from management studio that it does have access to those databases. It actually works almost all of the time for the application as well. If it didn't have access then I should be able to reproduce the problem. –  Mindy Aug 2 '10 at 16:44
    
Have you checked if the maximum number of concurrent connections allowed in the server settings is set to some number(right click on instance name in SSMS-->connections)? 0 is for unlimited. –  DaniSQL Aug 2 '10 at 18:42
    
I had not checked that. Unfortunately, it is set to zero so that isn't it. –  Mindy Aug 2 '10 at 20:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.