Accessing password_hash by users in SQL Server 2005

In SQL Server 2000 we had a view that would show the user credentials and the password hash. The reason we need this is that we use SQL Server authentication on the database. To test users, we have a login with little access, and it should be able to see the view and compare the password supplied against what is in the database, and then let the code handle a graceful exit if the password is invalid. I am trying to do this with SQL Server 2005, and I am running into trouble. I am trying to do this with a function, since there I can set the EXECUTE AS clause (in theory) and leverage the privlidges of a specific user in the database. Here is an example function:

CREATE FUNCTION check_acct.fn_allusers (@.test int)
RETURNS @.users table (username varchar(50), passwd varbinary(256))
WITH EXECUTE AS caller
AS
BEGIN
INSERT @.users
select name, password_hash from sys.sql_logins
RETURN
END
GO
GRANT SELECT ON fn_allusers TO user_acct

I cannot get to all rows in the sys.sql_logins table unless I first:

GRANT VIEW ANY DEFINITION TO CHECK_ACCT

then when I call this function from CHECK_ACCT, I am able to see the data. If I change the WITH EXECUTE AS 'CHECK_ACCT' and execute thsi function from USER_ACCT, I do not get the same results. Do functions not inherit VIEW DEFINITION proivlidges? Any suggestions on how to do this but limit access to these secure objects to this one function?

You're seeing the expected behavior, as I understand it. As you know, the "AS CALLER" reference will run the proc statements as the account that runs the proc, in which case that account needs the perms you're trying to use.

This may have to do with the scope of the call. If you'll look up the EXECUTE AS (Transact-SQL) statement in Books Online (make sure you have the February 2007 version loaded), you'll see a note about calling out of the current database, which you're doing. If you're in a database and you call sys.sql_logins, you're hitting the master database, which is outside the scope of the current database. I wonder if adding "USE master" would work?

|||

The permission needed to see the password hashes is CONTROL SERVER. See BOL for detailed information (http://msdn2.microsoft.com/en-us/library/ms187113.aspx).

The execution token established by EXECUTE AS is (by default) only trusted on the database where the execution context switch took place, and all server scoped privileges for the impersonated context are stripped down to DENY ONLY. This is explained in better detail on BOL: Extending Database Impersonation by Using EXECUTE AS (http://msdn2.microsoft.com/en-us/library/ms188304.aspx).

In this case, it may be simpler to sign the SP and grant CONTROL SERVER via the signature instead of via an impersonated context.

-- Removed the EXECUTE AS clause

--

CREATE FUNCTION check_acct.fn_allusers (@.test int)

RETURNS @.users table (username varchar(50), passwd varbinary(256))

BEGIN

INSERT @.users

select name, password_hash from sys.sql_logins

RETURN

END

GO

-- In order to see the password_hash, you require quite elevated privileges: CONTROL SERVER

-- I strongly recommend storing a backup of the signing private key in a safe place and then remove the copy from the database

--

CREATE CERTIFICATE [cert_fn_allusers] WITH

SUBJECT = 'Signing certificate for check_acct.fn_allusers'

go

ADD SIGNATURE TO [check_acct.fn_allusers] BY CERTIFICATE [cert_fn_allusers]

go

-- A copy of the certificate should be in master

--

CREATE LOGIN [cert_fn_allusers] FROM CERTIFICATE [cert_fn_allusers]

go

GRANT CONTROL SERVER TO [cert_fn_allusers]

go

As an additional suggestion I would recommend using the password hashes exclusively for porting/updating SQL Server accounts between multiple SQL Server instances.

I hope this information will be useful. Let us know if you have further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

One thing that was brought to my attention is that I should have emphasized more is that potentially the usage of the passwords hash you mentioned on your problem description is dangerous.

The reason why only CONTROL SERVER permission giving access to the password hash is for protection against an attacker with lower privileges to obtain the password table and crack it offline. I strongly recommend allowing SQL Server itself verify the passwords of your users instead of doing this job on a separate code.

Additionally by allowing SQL Server to verify the password, you will be able to use the infrastructure to detect failed attempts (i.e. detect a brute force attack) and make use of the password policy functionality.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine