Error: 18456, Severity: 14, State: 16

Question

I have a dedicated SQL Server,
Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

that gets this error when an application tries to login:
Error: 18456, Severity: 14, State: 16.
2009-11-02 18:09:49.38 Logon       Login failed for user 'mysqluser'.
Where mysqluser is a SQL login, not a trusted login.  This box is not part of a domain, but is on an IP network with a domain.

I can login and issue queries against the default database and against master with this user using SSMS.  The app executes a login every 5 minutes against the server, but is always denied access.  The system Application event log and a SQL Profiler trace indicate that the app is trying to connect to the master database, which is not the default for this user, but is accessible.  The password has been checked numerous times in the app that does the connecting to no avail.

sys.server_permissions shows only a single G entry of CONNECT SQL
Granting dbo on master and the user db has no effect
Granting sysadmin server role has no effect

The app is a closed source app so I can't just take a look at what they're doing.

Answer 1

If you've permissioned the user on master and still get the error then you'll need to verify the application that is connecting because state 16 means the user is valid but not permissioned on that db. If you've given the user sa then sql won't even check permissions as sa has full rights on everything, so none of this seems logical...

You can try verifying that it is actually the master db it is complaining about - if you don't have too many dbs add the user to each db one at a time to see if the error goes away.

Also, double-check the process pid does correspond with the application in question by checking the clientprocessid column in your trace.

---

ajmer dhariwal || eraofdata.com

Answer 2

Thanks for the response.

There are other examples on the web of this issue combined with "still not working using sysadmin server role."

I have reconstructed the account twice

I have had the application try to use the used the sa account directly

The profiler trace column for clientprocessid is blank at login.

Granting CONTROL SERVER didn't have any effect (tip from web).

There are no other dbs other than msdb and model on this server

I think I'm just going to disable this app from accessing the db, since I have other reasons to believe that this vendor couldn't program their way out of a box.

Answer 3

Is it possible to add database parameter in the APP connection string? If it's through ODBC you can edit the connection string and change the database parameter to the user db

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 4

Did you check to ensure that the "default database" is set to the database the account is supposed to use and has permission on?
I would suggest that this application is actually trying to connect to a database specified in a connection string on the application side of the equation and that the db doesn't exist. Check with the vendor and/or verify the connection string used by the application.

I've seen this countless times and it's always the case. Typically a *nix system connecting to SQL where the vendor swears up and down they don't need a db created and need sa to set things up but in reality they need a db of a specific name and dbo. It's funny the first few times, then it's sad. Then you start to wonder why you aren't coding these crapplications and charging $$ for consulting.

...sigh

-Andrew