Where are SQL 2008 Login passwords stored?

Question

I need to know in which file SQL Server 2008 actually stores the passwords. This is a question being asked by a security auditor.

I know I can get password hashes with LOGINPROPERTY('bbellomo', 'PasswordHash' ) but that doesn't help me, I need to know how they are stored.

Answer 1

Try to look into sys.server_principals user names and passwords are in the master database. But the passwords are not stored in plaintext.

Answer 2

The passwords aren't stored in plaintext. If the auditor really needs the file in which they're stored, point them at the master.mdf file. This is, of course, for SQL logins. For Windows logins, SQL doesn't even know what the password is; Active Directory handles the authentication.

Answer 3

The execution plan from 'SELECT password FROM syslogins' shows the passwords are stored in sys.sysxlgns in the master database.

Take a look at: http://msdn.microsoft.com/en-us/library/ms179503.aspx

Here are the passwords, in the only 2 formats SQL Server will show them to us:

SELECT name, password, LOGINPROPERTY(name, 'PasswordHash' ) hash FROM syslogins WHERE password IS NOT NULL ORDER BY name

You can probably convert between the formats easily, but I don’t know how off hand.