Hi All,

I have a scenario related to SQL 2005 Security, I have created database "db1" as "sa" user.

Steps followed

1.Created a "dummy" login
2.Created a "dummy" user,mapped to "dummy" login for "db1" database and made database owner.
3.logged in as "dummy" login and created a stored procedure which contains EXEC xp_cmdshell

Statement and while executing the stored procedure, it is throwing below error.

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.


4. Next, what i done is, i have turned on xp_cmdshell feature and
created a proxy account using Windows Authentication Account.
5. I Again tried to connect to "dummy" login and tried to execute the stored procedure and now it worked.

Now my Question is, what does the below statement do.
EXEC sp_xp_cmdshell_proxy_account 'domain\username', 'domain_user_password'

What affect is there if i created this new account. I know this would create a Proxy Credential but i am wondering how would the "dummy" user can able to utilize this Proxy Account internally because externally/explicitly am not giving/executing any command to make use of this newly created proxy account.

Can anyone please elaborate on how internally this Proxy Account is able to be utilized by the user "dummy" when he is getting logged in.

Am just curious about what is happening internally?

In other words, just want to know how this proxy account is made available to "dummy" user when he is getting logged in.


Commands Used to replicate the scenario.

Step1 : Create the database as "sa" user

use master
go
CREATE DATABASE db1
go

Step2 : Create a login
-- create a login "dummy" as "sa" user
USE [master]
GO
CREATE LOGIN [dummy] WITH PASSWORD=N'dummy', DEFAULT_DATABASE=[db1], CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF
GO

Step3 : Create a User in "DB1" database and make him database owner.
USE [db1]
GO
CREATE USER [dummy] FOR LOGIN [dummy]
GO
USE [db1]
GO
EXEC sp_addrolemember N'db_owner', N'dummy'
GO

Step4 : login as "dummy" and create a stored procedure which uses xp_cmdshell and when you try to

execute the stored procedure.

It will throw an error.


CREATE PROC USP_TEST
AS
BEGIN
DECLARE @STR VARCHAR(100)
SET @STR = 'GUEST'
PRINT @STR

EXEC sys.xp_cmdshell 'dir c:\*.*'

END

EXEC USP_TEST
/*
GUEST
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the

'##xp_cmdshell_proxy_account##' credential exists and contains valid information.

*/

Step5 : Enable the xp_cmdshell feature as "sa" user


EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

EXEC sys.xp_cmdshell 'dir c:\*.*'



Step6 : Now login as Windows Authentication user and try to create a
proxy account


EXEC sp_xp_cmdshell_proxy_account 'GSPSTRAIL\Administrator', 'mychlocallogin'


Step7: login "dummy" user and again try to execute the stored procedure.
EXEC USP_TEST

Step8: To drop the proxy account, execute the below peice of code.
/*
-- To drop a Proxy Account
-- login as Windows Authentication and execute the below command
/*
EXEC sp_xp_cmdshell_proxy_account NULL
*/


Thanks in Advance.