THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Everything you wanted to know about passwords and then some

Have you ever tried to make sense of how passwords are stored in SQL Server 2005?  It used to be a bit easier in SQL Server 2000.  Back then, the sysxlogins virtual table had a column called password which contained a hashed value of the SQL accounts’ passwords.  Windows’ accounts passwords were always NULL.  Going back to even earlier versions, one of the shameful secrets of SQL Server was that passwords were stored in a clear text file in the SQL Server directory.

Now, in SQL Server 2005, the same information is available from the system function LOGINPROPERTY with a property name of ‘PasswordHash’ as described here:

http://msdn2.microsoft.com/en-us/library/ms178593.aspx

and here:

http://msdn2.microsoft.com/en-us/library/ms345412.aspx.

In addition, here’s a great KB support article that explains how to move passwords between SQL Servers, even if they are different versions:

http://support.microsoft.com/default.aspx/kb/246133

Enjoy,

-Kevin

 

Published Wednesday, January 02, 2008 4:12 PM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement