Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Question

Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents

Viewing 15 posts - 121 through 135 (of 168 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

I don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion. 🙂

And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.

Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.

Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.

Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.

Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.

Regards

Lempster

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 2

Lempster (8/23/2012)
I don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion. 🙂
And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.
Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.
Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.
Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.
Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.
Regards
Lempster

Very cool feedback all around.

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. 😀 It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charles Kincaid SSChampion Points: 13593 More actions · Answer 3

As for not having the source code, that might not be as big of a thing as you think. If you can extract the assembly as a file then you can the go ask the good folks at Red Gate. They have a fine product that helps with Dot Net assemblies.

ATBCharles Kincaid

Rich Mechaber SSChampion Points: 11130 More actions · Answer 4

Jeff Moden (8/23/2012)
As a bit of a sidebar, I use XP_CmdShell to run PowerShell. 😀 It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).

Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?

Thanks very much,

Rich

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

rmechaber (8/24/2012)
Jeff Moden (8/23/2012)
As a bit of a sidebar, I use XP_CmdShell to run PowerShell. 😀 It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).
Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?
Thanks very much,
Rich

I have a demo script at home that I could share. I'll post it tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robert.baker 21596 Grasshopper Points: 10 More actions · Answer 6

Hi there

Sorry if this came up already, I skimmed through the thread and couldn't see anything, but I have upgraded a 2005 installation which had this CLR assembly installed for copying files. I set up the same assembly from an asymmetric key in SQL 2008 R2 but when I try to use it I get error:

The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.

Anybody have any pointers as to what permissions I need to set - would this be a permission problem for the login I created against the key?

Rich Mechaber SSChampion Points: 11130 More actions · Answer 7

Jeff Moden (8/24/2012)
rmechaber (8/24/2012)
Jeff Moden (8/23/2012)
As a bit of a sidebar, I use XP_CmdShell to run PowerShell. 😀 It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).
Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?
Thanks very much,
Rich
I have a demo script at home that I could share. I'll post it tonight.

Hi, Jeff, any luck running down that demo? I'd very much appreciate it!

Rich

robert.baker 21596 Grasshopper Points: 10 More actions · Answer 8

I resolved my error:

The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.

when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.

Sorry for polluting the conversation with newbie stuff

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

rmechaber (10/3/2012)
Jeff Moden (8/24/2012)
rmechaber (8/24/2012)
Jeff Moden (8/23/2012)
As a bit of a sidebar, I use XP_CmdShell to run PowerShell. 😀 It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).
Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?
Thanks very much,
Rich
I have a demo script at home that I could share. I'll post it tonight.
Hi, Jeff, any luck running down that demo? I'd very much appreciate it!
Rich

My apologies. I lost track of this thread. I'll try to post the demo tonight. Yeah... I know... said that before and, again, apologies for not following up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 10

robert.baker 21596 (10/3/2012)
I resolved my error:
The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.
when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.
Sorry for polluting the conversation with newbie stuff

Nah... it's good stuff. Thank you for taking the time to post back. It'll help others.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 11

Here's the code example that I promised. The two user names for this example must be created as Windows Users from the Windows admin tools. Both users are simple users WITHOUT Windows Admin privs. One is there for the correct type of xp_CmdShell proxy and the [font="Arial Black"]password should be protected from non-DBA users as should any other password used by a system[/font] even though it only has PUBLIC privs. The other user login is just for test purposes. The names of both will be painfully apparent.

The last section of this code is the real "meat" of the code. If you first run all of the other sections to set things up correctly and then run each commented subsection in that last section one at a time, you'll see that the TestDummy user can execute a stored procedure that uses xp_CmdShell but cannot execute xp_CmdShell directly.

This also gives a hint on how to lockdown an entire system. NO ONE but DBAs need to have or should have SA privs. Even the proxy created in the code below has only PUBLIC privs for a server/database role.

Note that you will have to make some changes in the following code. For example, you'll need to replace "yourdomainname" with your domain name (machine name if you're doing this from a desktop box with the Developer Edition).

--===== Make sure none of the test objects I use exist ahead of time so that we can see that this all actually works

SELECT '****************************** Making sure the things we need don''t already exist. ******************************';

USE MASTER;

DROP DATABASE MyTester; --BE REAL CAREFUL HERE!!! Drops the database I tested against

EXEC sp_xp_cmdshell_proxy_account NULL; --Drops the cmd shell proxy just to be sure.

DROP USER [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\TestDummy]; --Drops the login I used for my Windows TestDummy user just to be sure one doesn't exist.

DROP USER [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

DROP LOGIN [yourdomainname\SqlCmdUser]; --Drops the login I used for my Windows SqlCmdUser user just to be sure one doesn't exist.

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Recreate my test database and the user which only has "public" privs.

-- I believe the DEFAULT_SCHEMA is important here.

SELECT '****************************** Creating [MyTester] DB and TestDummy. ******************************';

CREATE DATABASE [MyTester];

GO

USE [MyTester];

CREATE LOGIN [yourdomainname\TestDummy] FROM WINDOWS WITH DEFAULT_DATABASE=[MyTester], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\TestDummy] FOR LOGIN [yourdomainname\TestDummy] --This just maps the database for the user

GO

--===== This just displays how limited the TestDummy user is

EXEC sp_helpuser [yourdomainname\TestDummy];

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now we build the Login and proxy account using the SqlCmdUser I built in Windows on my box at home.

-- IMPORTANT!!! A step we cannot skip is that we have to build a user from the SqlCmdUser login.

-- NOTE THAT THIS MUST BE A SINGLE USER AND NOT A WINDOWS GROUP!

SELECT '****************************** Building/Granting Proxy user stuff. ******************************';

USE [master];

CREATE LOGIN [yourdomainname\SqlCmdUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

CREATE USER [yourdomainname\SqlCmdUser] FOR LOGIN [yourdomainname\SqlCmdUser] WITH DEFAULT_SCHEMA=[dbo];

EXEC sp_xp_cmdshell_proxy_account 'yourdomainname\SqlCmdUser','SqlCmdUser';

--===== Very important here... we have to grant access to xp_CmdShell to the new Window's user...

GRANT EXECUTE ON xp_CmdShell to [yourdomainname\SqlCmdUser];

GO

--===== This just displays how limited even the SqlCmdUser is!!!!

EXEC sp_helpuser [yourdomainname\SqlCmdUser];

GO

-----------------------------------------------------------------------------------------------------------------------

-- ********** NOTE THAT EVERYTHING ABOVE IS AS WE HAD IT BEFORE! **********

-- ********** NOTE THAT THE ONLY THING WE HAVE TO DO IN THE STORED PROCS (SEE BELOW

-- ********** IS TO INCLUDE "WITH EXECUTE AS OWNER"

-- heh... And Bob's your Uncle!

-----------------------------------------------------------------------------------------------------------------------

--===== Create a stored procedure in the new "MyTester" database that uses xp_CmdShell.

-- Keep in mind that, right now, we're signed in as a member of "dbo".

USE [MyTester];

GO

DROP PROCEDURE dbo.GetDirInfo;

GO

CREATE PROCEDURE dbo.GetDirInfo

WITH EXECUTE AS OWNER

AS

EXEC xp_cmdshell 'DIR C:\';

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

;

GO

--===== Give the general puplic privs to run the sproc.

GRANT EXECUTE ON dbo.GetDirInfo TO PUBLIC

;

GO

-----------------------------------------------------------------------------------------------------------------------

--===== Now, show that the "TestDummy" user can execute the proc but not xp_cmdshell itself.

-- Simulate logging in as a user with low privs...

EXECUTE AS LOGIN = 'yourdomainname\TestDummy'

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This works... (which is what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of dbo.GetDirInfo **********'

EXEC dbo.GetDirInfo

SELECT ORIGINAL_LOGIN(), SUSER_NAME(), SUSER_SNAME(), USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER;

-- This doesn't... (which is also what we want)

PRINT REPLICATE('=',80);

PRINT '********** Testing execution of xp_CmdShell directly **********'

EXEC xp_cmdshell 'DIR C:\'

;

GO

--===== Test complete... go back to normal.

REVERT

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

Jeff, I (and many others) have said it before, and I'll say it again: your generosity is much appreciated! Thanks, I'm looking forward to digging through this!

Rich

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

rmechaber (10/4/2012)
Jeff, I (and many others) have said it before, and I'll say it again: your generosity is much appreciated! Thanks, I'm looking forward to digging through this!
Rich

Thank you for the kind words, Rich. When you've completed your look-see, I'd love to hear your feedback on the method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rich Mechaber SSChampion Points: 11130 More actions · Answer 14

Hi, Jeff, thanks very much for digging up and posting this. I think I follow the logic here, but I'm getting errors.

First off, I did enable xp_cmdshell on the server. Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine.

But, when I run the first test, it errors out trying to run the stored proc as my TestUser. In fact, I cannot run the proc when executing as sysadmin!

When run as sysadmin:

--Succeeds (not in Jeff's original post):

use master;

exec xp_cmdshell 'dir c:\';

--Fails (even though we are executing as sysadmin)

use MyTester;

exec GetDirInfo;

The returned error on the second part is:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

I'm running this on a home machine. Any ideas?

Thanks,

Rich

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 15

rmechaber (10/6/2012)
Hi, Jeff, thanks very much for digging up and posting this. I think I follow the logic here, but I'm getting errors.
First off, I did enable xp_cmdshell on the server. Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine.
But, when I run the first test, it errors out trying to run the stored proc as my TestUser. In fact, I cannot run the proc when executing as sysadmin!
When run as sysadmin:
--Succeeds (not in Jeff's original post):
use master;
exec xp_cmdshell 'dir c:\';
--Fails (even though we are executing as sysadmin)
use MyTester;
exec GetDirInfo;
The returned error on the second part is:
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
I'm running this on a home machine. Any ideas?
Thanks,
Rich

Let the troubleshooting begin. Sorry for the obvious questions but we have to start somewhere. This all works on every system I've tried it on (2005 and 2008 including servers and my home system) and I'm shooting in the dark on your system because I can't actually see it.

1. When you say you created the logins, did you create the SqlCmdUser and TestDummy user as Windows users in Windows first?

2. Also, you say you "Then I created the DB, logins, users, proxy, and set permissions. All that seemed fine", Did you run the script in the order given?

3. Did you make any changes to the script other than the domainname?

4. Really stupid question but have to ask, are you sure you ran the whole script up to...

-----------------------------------------------------------------------------------------------------------------------

--===== Now, show that the "TestDummy" user can...

5. When you ran as the user that has SA privs, are you sure you were executing as that user and not the TestDummy? Did you try REVERT before you ran the test as the SA user?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)