SQL Server Failure Audit.

Question

Post reply

SQL Server Failure Audit.

pskoasha

SSC Veteran

Points: 212
More actions
October 29, 2010 at 2:56 am

#157288

Hi All,
Firstly thanks for looking at this post.....i have a problem with SQL Security. i have two BES servers talking to one DB on a Named instance of SQL 2005.
Both Bes servers are identical and use one service account to communicate to the DB.
For one of the BES servers i get the following error:
Event Type:Failure Audit
Event Source:MSSQL$INSTANCE
Event Category:(4)
Event ID:18456
Date:29/10/2010
Time:09:49:52
User:USERNAME
Computer:IFS-WB-SQL-12
Description:
Login failed for user 'USERNAME'. [CLIENT: 10.6.11.25]
Date29/10/2010 09:55:51
LogSQL Server (Current - 29/10/2010 09:55:00)
SourceLogon
Message
Error: 18456, Severity: 14, State: 16.
The user has access to the DB as the other BES Server uses the same account.
Any ideas

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Marco V SSCrazy Points: 2939 More actions · Answer 1

You say it has the correct rights and the db login is nicely mapped to the db user

so what is the userlogin his default database and does the default db still exist?

grahamc SSCertifiable Points: 6762 More actions · Answer 2

pskoasha (10/29/2010)
Error: 18456, Severity: 14, State: 16.

State 16 means that the user does not have access to the requested database

Comments 1 & 3

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

pskoasha SSC Veteran Points: 212 More actions · Answer 3

hi it has access to the db and as per the original post the users is a service account which is used on two nodes of BES.

i have checked the db's it has access to and the exist...

Cheers

Pete

Marco V SSCrazy Points: 2939 More actions · Answer 4

hmm intresting default db is assigned and exist for login.

do you get also a login failed if you try to login with that account for example into SSMS on the sql instance?

pskoasha SSC Veteran Points: 212 More actions · Answer 5

no i can logon to SMS with the account fine no problems there....

Robbert Hof SSCrazy Points: 2778 More actions · Answer 6

To be sure that the user connects to the correct database, start a profiler trace including Errors and Warnings events. You should see the same error in the trace, with just above more information.