Hi everyone,
I've got a clean SQL Server 2005 Enterprise Edition installation, with a domain account configured as the service account. My application log is now flooded with the following error message:
Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 5/25/2007
Time: 1:57:00 AM
User: TNG\TNG-SQL_Service
Computer: TNG-MOSSDB01
Description:
Login failed for user 'TNG\TNG-SQL_Service'. [CLIENT: <local machine>]
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 0d 00 00 00 54 00 4e 00 ....T.N.
0010: 47 00 2d 00 4d 00 4f 00 G.-.M.O.
0018: 53 00 53 00 44 00 42 00 S.S.D.B.
0020: 30 00 31 00 00 00 07 00 0.1.....
0028: 00 00 6d 00 61 00 73 00 ..m.a.s.
0030: 74 00 65 00 72 00 00 00 t.e.r...
And when I open up the SQL logs, I'm getting two errors - one is essentially the same as the above, and the other is:
Date 5/25/2007 1:51:00 AM
Log SQL Server (Current - 5/25/2007 1:51:00 AM)
Source Logon
Message
Error: 18456, Severity: 14, State: 16.
Now I've seen all kinds of posts about granting rights to master and such - and I have, but these errors are not going away. I've gone so far as to create new service accounts and even switch over to the local system account - and the error still occurs. Does anyone have any insight into other routes / approaches I can take with this?
Thank you,
Chris
From BOL topic Troubleshooting: Login failed for user 'x'
Additional Error Information
To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the SQL Server error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.
2
User ID is not valid.
5
User ID is not valid.
6
An attempt was made to use a Windows login name with SQL Server Authentication.
7
Login is disabled, and the password is incorrect.
8
The password is incorrect.
9
Password is not valid.
11
Login is valid, but server access failed.
12
Login is valid login, but server access failed.
18
Password must be changed.
Other error states exist and signify an unexpected internal processing error.
You may need to contact Support for this.
HTH,
-Steven Gott
SDE/T
SQL Server
|||Hi Steve,
Do you by chance have any information on State = 16?
Thanks,
Chris
It is related to not being able to establish a database session.
Check the default_database for your account and make sure it is master.
How are you changing the service account? Please make sure you use the tools provided in sql server because usinging windows scm will not do the whole job.
HTH,
-Steven Gott
SDE/T
SQL Server
|||Steve,
The default_database for the account is master, and the service accounts were set through the SQL Server Configuration Manager. I went back into it and reset all of them just in case - but still getting the error. Just doesn't make any sense. In fact, I'm also seeing the same error for another account - the service account I use with SharePoint (residing on another server). Same exact errors in the Event Log for the Server & the SQL Logs.
Are there any statements I can run against the master to see if maybe an entry is missing? I'm drawing at straws with this.
Thanks,
Chris
You can try the following statements to see if there is anything unexpected:
SELECT name, principal_id, type_desc, is_disabled, default_database_name, default_language_name
FROM sys.server_principals
WHERE name = 'login_name'
go
SELECT name, database_id, user_access_desc, state_desc, is_in_standby
FROM sys.databases
WHERE name = 'default_db_name'
go
SELECT suser_name(role_principal_id), suser_name(member_principal_id)
FROM sys.server_role_members
WHERE member_principal_id = suser_id( 'login_name' )
go
SELECT class_desc, suser_name(grantee_principal_id), permission_name, state_desc
FROM sys.server_permissions
WHERE grantee_principal_id = suser_id( 'login_name' )
Go
Pay attention to the default database, if the login is disabled and if it has at least CONNECT SERVER permission.
I hope this helps,
-Raul Garcia
SDE/T
SQL Server Engine
No comments:
Post a Comment