By: Jeremy Kadlec | Comments (2) | Related: > Security
Problem
Gaining insight into my SQL Server standard and Windows logins has historically been a challenge in terms of determining password changes, failed login attempts, etc. I have noticed that you have experienced the same issue with your tip entitled 'When was the last time the SQL Server sa password changed' and I have noticed some information in the forums on the topic as well. With SQL Server 2005 can I gain any more insight into the SQL Server standard logins part of the equation? I know I can talk to my Network Admin counter parts for some of the Windows related login information. Any and all recommendations would be appreciated.
Solution
As a matter of fact with the SQL Server 2005 built-in function LOGINPROPERTY, this command can help to address these issues and a few more when SQL Server is installed on a Windows 2003 server. The best way to show the value in this SQL Server function is by outlining the problems it addresses then provide code snippets to take advantage of these features. Here are the problems that the SQL Server 2005 LOGINPROPERTY function addresses for standard logins:
- Date when the password was set
- Locked out standard login
- Expired password
- Must change password at next login
- Count of consecutive failed login attempts
- Time of the last failed login attempt
- Amount of time since the password policy has been applied to the login
- Date when the login was locked out
- Password hash
LOGINPROPERTY Code Snippets
In the code snippets below we are going to use the sa login as an example since it is created when SQL Server 2005 is installed with Windows and SQL Server authentication:
|
Date when the password was set |
| SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime'); GO |
|
Locked out standard login |
| SELECT LOGINPROPERTY('sa', 'IsLocked'); GO |
Result Set Legend
|
|
Expired password |
| SELECT LOGINPROPERTY('sa', 'IsExpired'); GO |
Result Set Legend
|
|
Must change password at next login |
| SELECT LOGINPROPERTY('sa', 'IsMustChange'); GO |
Result Set Legend
|
|
Count of consecutive failed login attempts |
| SELECT LOGINPROPERTY('sa', 'BadPasswordCount'); GO |
| Time of the last failed login attempt |
| SELECT LOGINPROPERTY('sa', 'BadPasswordTime'); GO |
|
Amount of time since the password policy has been applied to the login |
| SELECT LOGINPROPERTY('sa', 'HistoryLength'); GO |
|
Date when the login was locked out |
| SELECT LOGINPROPERTY('sa', 'LockoutTime'); GO |
|
Password hash |
| SELECT LOGINPROPERTY('sa', 'PasswordHash'); GO |
LOGINPROPERTY Special Notes
According to SQL Server 2005 Books Online "The values of the PasswordHash and PasswordLastSetTime properties are available on all supported configurations of SQL Server 2005, but the other properties are only available when SQL Server 2005 is running on Windows Server 2003 and both CHECK_POLICY and CHECK_EXPIRATION are enabled." So if you run into an issue i.e. NULL result set validate the Windows operating system and the settings for the specific login before digging too far into the issue.
Next Steps
- With the new SQL Server 2005 standard login functionality available be sure to take advantage of it when the needs arise. I can distinctly remember many customers being frustrated with the standard SQL Server login features as compared to password policies for Windows logins. Microsoft listened and delivered a number of new features to help manage standard SQL Server logins.
- In terms of password changes, using the LOGINPROPERTY function is now a much easier means to determine if passwords have not changed recently. If you have a policy related to changing passwords, be sure to run the appropriate scripts from this tip to validate the policy is being met with your standard logins.
- Check out the related security and compliance tips on MSSQLTips.com.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
