Problem: Recently one of my user reported the issue that, He is getting password change message while login to SQL Server. But he does not want to change the password as he has already configured at the many places. While turning off the Password Expiration message, I got the below error.
The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON. (Microsoft SQL Server, Error: 15128)
To fix the issue you MUST have to change the password first without changing in password policy options. The good thing it that you can change the password to be the same as the existing one. -:)
But still you have to change the password -:) what a logic!!! You can do it from SSMS GUI as well, but I always prefer to use the scripts as it is giving me flexibility of customization.
That was the another reason I am in the support of open source software you have flexibility to add your ideas. Let’s go to fix the issue.
In the below step I am giving the same password, to fix “User Must Change Password” flag.
ALTER LOGIN [Login Name] WITH PASSWORD = 'Same Password'
Now let’s turn off the passowrd policy.
ALTER LOGIN [Login Name] WITH
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.
Msg 15421, Level 16, State 1, Line 1
The database principal owns a database role and cannot be dropped.
Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.</code
Error: Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.
Error clearly state that login is not associated with the trusted connection.
What is trusted connection and what is the advantage of it? Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.
Advantage of this process is that we don’t have to store credential with the connection string.
However sometimes you will see the error above error.
- Change the authentication mode to mixed mode from SSMS or Enterprise Manager
2. Check the user/login is associated with the appropriate SQL login
3. Host is in the same domain or Host machine is network, sometime rebooting of the host machine will resolve this issue.
Recently I received below error while executing XP_ReadErrorlog command from SSMS.
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line error log location not found
As per the above error message I have checked event viewer log and got the below information from event viewer log.
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user ‘domain\service-sql’..
I have opened error log file from log directory and observed the number of login service-sql with the state 11.
2009-09-30 22:45:31.69 Logon Login failed for user ‘domain\service-sql’. [CLIENT: 10.10.10.10]
2009-09-30 22:45:34.26 Logon Error: 18456, Severity: 14, State: 11.
Service-SQL account has sysAdmin rights on SQL Server and it is added through SQL Server Service – Production AD group. I can see this AD group has SysAdmin rights on SQL Server.
Resolution: Remove account from local administrator group and from SQL Server Logins. Add account again with the same privileges in local administrator group and SQL Server logins.