Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

x-cmdShell access Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 5:55 AM
Points: 43, Visits: 118
Dears all
how can i restrics xp_CmdShell accesss to run some command?
for example xp-cmdshell can not run format syntax or delete format?
how is this possible?

Best Regards,
zohreh
Post #1353871
Posted Tuesday, September 4, 2012 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:16 AM
Points: 12,923, Visits: 32,282
no, if you grant xp_cmdshell permissions, you cannot restrict the commands that they might use when they construct the strings.
typically what you want to do instead is
1. Remove access to xp_cmdshell.
2. determine what it is they wanted to do via xp_xmdshell, and create CLR methods or restricted procedures which call xp_cmdshell on their behalf.

so if they did 4 certain things via xp_cmdshell, create four alternatives for them to use instead.

in that way, you can remove permissions to xp_cmdshell to everyone, except say a superuser that noone has the password for, with a disabled login.
then you can use WITH EXECUTE AS to give elevated permissions to just the code within a proc.
--first we need a sysadmin role with no login, which will be used
--for execution context in the DDL triggers or special elevated permissions functions.

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'superman' AND type = 'S') --'S' = SQL login
BEGIN
--create our super user
CREATE LOGIN [superman]
WITH PASSWORD=N'NotTheRealPassword',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON

--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'

--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
END
GO
USE [SandBox];
GO
CREATE USER [superman] FOR LOGIN [superman];
GO
USE [SandBox];
GO
--the EXECUTE AS must be a user in the database...not a login
CREATE procedure pr_CallBoostedSecurityProcess
WITH EXECUTE AS 'superman'
AS
BEGIN
--do elevated stiff like xp_cmdshell
dbcc freeproccache
END





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1353889
Posted Tuesday, September 4, 2012 8:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
Or you can avoid even CLR. Just remove xp_CmdShell access from the user, put the functionality into a stored procedure, use an EXECUTE as OWNER in the proc (the DB must be owned by "SA"), and grant EXECUTE privs to the proc to the user. The user can be as low as PUBLIC only privs and will be able to execute the proc but won't be able to execute xp_CmdShell directly.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353903
Posted Tuesday, September 4, 2012 11:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 7,135, Visits: 12,744
zsafakhah (9/4/2012)
Dears all
how can i restrics xp_CmdShell accesss to run some command?
for example xp-cmdshell can not run format syntax or delete format?
how is this possible?

Best Regards,
zohreh

You cannot restrict the commands issued through xp_cmdshell. It is best to avoid enabling xp_cmdshell on your instance for this and others reasons as you lose a lot of control over your instance once it is enabled. All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what. You can setup a SQLCLR method to execute in the context of the calling user so you maintain that traceability all the way through the call stack. xp_cmdshell is disabled by default. If you explain more about why you have enabled xp_cmdshell but still want to restrict it in specific ways then more guidance can be offered.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1354084
Posted Tuesday, September 4, 2012 2:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
opc.three (9/4/2012)
All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.


Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1354177
Posted Tuesday, September 4, 2012 2:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 7,135, Visits: 12,744
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.


Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat.

True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1354180
Posted Tuesday, September 4, 2012 11:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 5:55 AM
Points: 43, Visits: 118
Dears All,
thanks for you comments.
My Company bought a accounting software which need "sa" user for config software and need x-cmdShell sp for some actions.
i am DBA of my company and could not accept this because its had high risk.
what can i do for this action?

best regards,
zohreh
Post #1354330
Posted Wednesday, September 5, 2012 12:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 5:39 PM
Points: 286, Visits: 582
zsafakhah (9/4/2012)
Dears All,
thanks for you comments.
My Company bought a accounting software which need "sa" user for config software and need x-cmdShell sp for some actions.
i am DBA of my company and could not accept this because its had high risk.
what can i do for this action?

best regards,
zohreh


If you're really lucky you could do what I did 8 years ago and browbeat the company who sold the software to my company into making it 'enterprise ready' before I would let it go live They said "but it has to use 'sa'". I said "not in my environment it doesn't!" ....so they fixed it.

More realistically though when you say it needs 'sa' user do you mean that the whole app needs to run in the context of a login that is a member of sysadmin?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1354338
Posted Wednesday, September 5, 2012 5:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
zsafakhah (9/4/2012)
Dears All,
thanks for you comments.
My Company bought a accounting software which need "sa" user for config software and need x-cmdShell sp for some actions.
i am DBA of my company and could not accept this because its had high risk.
what can i do for this action?

best regards,
zohreh

There are only four things you can do.

1. Refuse to use it and demand a refund.
2. Do like Clare did and refuse to use it until they change it (beware of liars if they still use xp_CmdShell because there aren't many folks that know how to properly implement it without using "SA").
3. Do a deeper dive and find out how "SA" is being used and whether it is restricted to just "admin" functions and then protect the "admin/SA" password for it like you would for any other server. Also, test the software for SQL Injection to make sure that no outsiders can get in as "SA".
4. Set it up as the only application on a highly protected server in its own domain so that when someone does break into it, you can minimize the damage. That's provided that the information isn't sensitive. If the information is sensitive, then only options 1 and 2 are the right way to go. Since this is "accounting software", I'm thinking this is going to be anything but insensitive information.

In all cases, keep managemment informed and warned about just exactly what can happen if the "SA" user is hacked including the lawsuites that can occur by private citizens if private or other sensitive information is picked up by a hacker... externally or internally. It CAN be a cause of the whole company shutting down and that's not the worst case.



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1354453
Posted Wednesday, September 5, 2012 6:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 35,534, Visits: 32,117
opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.


Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat.

True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).


While I agree with all of that, I'll also state that someone with "SA" privs has many other unaudited methods for running command-line-level functionality. There's even a simple hack for doing it through OPENQUERY not to mention at least one task in SQL Jobs and SSIS that can do it without necessarily being traced. Besides, having the ability to trace such things is handy but definitely falls into the category of sifting through the manure after the horse has already left the barn.

I think it ironic that if your system is properly locked down, that users can run stored procedures that execute well protected predefined xp_CmdShell functionality without the users being able to see what's in the proc nor even any of the tables never mind being able to run xp_CmdShell directly.

On the "SA" not being trusted thing... the same holds true with any system. Windows, SharePoint, you name it. It's a real shame that it's come to this but if you have computers, someone needs to have deity privs to keep it working or to give someone else temporary privs to keep it working. If trust is really a problem, then you absolutely have to setup the "2 man rule" for all "SA" access where each of the two people only know half the "SA" password. Heh... but then there's those Windows guys... just as surly and untrustworthy.

To be sure, disabling xp_CmdShell and protecting it with another hack like Policy Managemment won't even slow someone with "SA" privs down in an attack.

Shifting gears a bit, I will absolutely agree that having a 3rd party application that requires "SA" privs is just silly and a totally unnecessary risk.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1354472
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse