September 3, 2015 at 3:08 pm
I log in as myself using Windows authentication, and I'm in the sysadmin role, and this is within a dev instance running on my PC. I execute:
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N'newpassword'
GO
It's runs without error, but when I try to login with the 'sa' account to the same database using SQL Server authentication within SSMS, I get the following error:
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)
I can print out the error stack if you think it adds something, but I'm guessing that there's something happening here that's obvious to a SQL Server DBA admin.
Thanks,
--=Chuck
September 3, 2015 at 3:16 pm
To decode the actual reason the login failed, you'll have to look in error log (not client message) for the State code.
http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients. In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem. To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written. An example of an entry is:
2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.
2006-02-27 00:02:00.34 Logon Login failed for user '<user name>'. [CLIENT: <ip address>]
ERROR STATE ERROR DESCRIPTION
2 and 5: Invalid userid
6: Attempt to use a Windows login name with SQL Authentication
7: Login disabled and password mismatch
8: Password mismatch
9: Invalid password
11 and 12: Valid login but server access failure
13: SQL Server service paused
18: Change password required
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 3, 2015 at 3:38 pm
Right there in the error log. Thanks!
Login failed for user 'sa'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]
--=Chuck
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply