//
you're reading...
SQL Server

Delegating XP_CMDSHELL (Without Giving Away the Farm)

I recently had a requirement for an application account to run a stored procedure that utilized xp_cmdshell. The first question is can the functionality be accomplished without enabling xp_cmdshell, unfortunately not. The next question was OK, now how to do that without granting sysadmin to the application service domain account.

Here’s what I came up with it allows for the delegation of rights to execute the xp_cmdshell functionality indirectly via a stored procedure without conveying the rights to directly execute xp_cmdshell to the user account.

1) The first step is to create a domain account to act as the proxy account. For demonstration purposes lets call it “yourDomain\TaskRunner

2) Now we must build the Login and proxy account using the domain account created in the previous step. USE [master];
CREATE LOGIN [yourDomain\TaskRunner] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
CREATE USER [yourDomain\TaskRunner] FOR LOGIN [yourDomain\TaskRunner] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_xp_cmdshell_proxy_account ‘yourDomain\TaskRunner’,’thep@ssw0rd’;

3) Next we must grant rights to execute xp_cmdshell GRANT EXECUTE ON xp_CmdShell to [yourDomain\TaskRunner]; GO — check the results EXEC sp_helpuser [ITIC\task.runner1];
GO

4) Create store procedure utilizing xp_cmdshell.
— CREATE PROCEDURE XYZ
WITH EXECUTE AS OWNER
— AS
— EXEC xp_cmdshell ‘DIR C:\’;
–;
–GO

5) Finally grant execute to your stored procedure to a database user, database role, or public.
GRANT EXECUTE ON dbo.XYZ TO PUBLIC;
GO

— If you get the following error:
— The server principal "sa" is not able to access the database under the current security context.

— Then mark the databases involved in the stored proc as Trustworthy

— ALTER DATABASE xyz SET TRUSTWORTHY ON

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: