Developping for the translation industry RSS 2.0



 Tuesday, 02 December 2008

Did you change the “sa” password recently? As a DBA, you should be aware that there is a great security risk linked to the sa account. You should always use strongs password for this account and change the password frequently.

You can easily check when the “sa” password was last changed in SQL Server 2005 by executing the following T-SQL code:

SELECT LOGINPROPERTY ('sa', 'PasswordLastSetTime')

The LOGINPROPERTY function gives you lots of information on the logins properties and password policy information for these logins.

Another thing you can do with this function is to look for security attacks. For example if you want to look for brute-force or dictionnary attack on the “sa” account, you can use the following query:

SELECT LOGINPROPERTY ('sa', 'BadPasswordCount')

This will return the number of failed consecutive attempts to login since the last successful login. So if this value goes over a certain value, you can easily see that something might be wrong.

Here is the complete list of properties you can query for using the LoginProperty function:

BadPasswordCount
Returns the number of consecutive attempts to log in with an incorrect password.

BadPasswordTime
Returns the time of the last attempt to log in with an incorrect password.

DaysUntilExpiration
Returns the number of days until the password expires.

DefaultDatabase
Returns the SQL Server login default database as stored in metadata or master if no database is specified. Returns NULL for non-SQL Server provisioned users; for example, Windows authenticated users.

DefaultLanguage
Returns the login default language as stored in metadata. Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users.

HistoryLength
Returns the length of time the login has been tracked using the password-policy enforcement mechanism.

IsExpired
Returns information that will indicate whether the login has expired.

IsLocked
Returns information that will indicate whether the login is locked.

IsMustChange
Returns information that will indicate whether the login must change its password the next time it connects.

LockoutTime
Returns the date when the SQL Server login was locked out because it had exceeded the permitted number of failed login attempts.

PasswordHash
Returns the hash of the password.

PasswordLastSetTime
Returns the date when the current password was set.

 

Other posts:

Differences between temporary tables and tables variables

How to insert a file in an image column in SQL Server 2005

How to add a row number in an SQL Query

Tuesday, 02 December 2008 10:40:46 (Eastern Standard Time, UTC-05:00)  #    Comments [0] -
Security | SQL

Navigation
Advertisement
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

© Copyright 2017
Stanislas Biron
Sign In
Statistics
Total Posts: 135
This Year: 0
This Month: 0
This Week: 0
Comments: 1
All Content © 2017, Stanislas Biron