September 4, 2012 at 7:14 am
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
September 4, 2012 at 7:38 am
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
September 4, 2012 at 8:06 am
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
Change is inevitable... Change for the better is not.
September 4, 2012 at 11:44 am
zsafakhah (9/4/2012)
Dears allhow 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
September 4, 2012 at 2:31 pm
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
Change is inevitable... Change for the better is not.
September 4, 2012 at 2:38 pm
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
September 4, 2012 at 11:57 pm
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
September 5, 2012 at 12:44 am
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?
September 5, 2012 at 5:54 am
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
Change is inevitable... Change for the better is not.
September 5, 2012 at 6:21 am
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
Change is inevitable... Change for the better is not.
September 5, 2012 at 7:09 am
Jeff Moden (9/5/2012)
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.
Not sure when using Policy Management to maintain a consistent set of configurations across an enterprise became a hack, but OK.
That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 5, 2012 at 8:21 am
opc.three (9/5/2012)
That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.
First, done properly, there is no additional risk. The only time such a risk occurs is if an application or non-DBA users are allowed to have "SA" privs. If such a thing happens, then you have [font="Arial Black"]much [/font]more to worry about than the use of xp_CmdShell. Even if you were to rename or even delete the DLL for xp_CmdShell, there are still super easy hacks to get to the command line if you have "SA" privs. In fact, following the rules to correctly instantiate the use of xp_CmdShell (and it's not obvious in Books Online) will make you tighten your security to what it actually needs to be.
Examples of why I'd want to use xp_CmdShell are two fold.
The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.
The second is security. It's a lot easier to keep one system secure than it is two. To use your own words, it cuts the "additional risk" in half simply because there's one server instead of two.
I won't get into intangibles such as not having to hire programmers that know a certain language to deal with all the scripts that developers tend to write in SSIS because they may not know how to do something in SSIS (or that SSIS simply can't do it) or the fact that those scripts are frequently written in T-SQL anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2012 at 9:13 am
Jeff Moden (9/5/2012)
opc.three (9/5/2012)
That aside, none of what you said speaks to why one would want to incur the additional risk of having xp_cmdshell enabled.First, done properly, there is no additional risk. The only time such a risk occurs is if an application or non-DBA users are allowed to have "SA" privs. If such a thing happens, then you have [font="Arial Black"]much [/font]more to worry about than the use of xp_CmdShell. Even if you were to rename or even delete the DLL for xp_CmdShell, there are still super easy hacks to get to the command line if you have "SA" privs. In fact, following the rules to correctly instantiate the use of xp_CmdShell (and it's not obvious in Books Online) will make you tighten your security to what it actually needs to be.
Your assertion about the "non-DBA" may be where we begin to digress. Internal threats are analogous to high blood pressure: they are silent killers. The less exposed a server that holds a business' most valuable commodity can be made to be the better chance the business has to survive a disgruntled, malicious or careless employee. Your argument has not addressed the fundamental fact that having xp_cmdshell enabled increases the known attackable surface area of the database server. All other workarounds and undocumented hacks aside, why expose a convenient way for a user to access a server's file system and possibly network file systems when they may not otherwise have direct access to the server itself?
Examples of why I'd want to use xp_CmdShell are two fold.
The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.
This sounds like a bit of a pre-judgement. Why not?
Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities. I am sure selling another Windows license is not a bad side-effect for Microsoft, but it makes independent actors in the environment easier to manage, tune, secure and recover. It sounds like you have your ETL staging databases, where the processing of large data files into staging tables and further transformations of that data are taking place, sitting on the same instance as application-facing OLTP databases. How do you distribute your workload across multiple servers with everything in T-SQL sitting on one server? How do you tune so memory required for ETL does not flush buffer pool memory needed for OLTP databases?
The second is security. It's a lot easier to keep one system secure than it is two. To use your own words, it cuts the "additional risk" in half simply because there's one server instead of two.
Well if I may, to paraphrase your words: in a "properly locked down system" the risk is actually lower with two servers than with one since no application code will ever access the file system of the SQL Server.
I won't get into intangibles such as not having to hire programmers that know a certain language to deal with all the scripts that developers tend to write in SSIS because they may not know how to do something in SSIS (or that SSIS simply can't do it) or the fact that those scripts are frequently written in T-SQL anyway.
All I can say is that SSIS has been out for 7 years and PowerShell for 6. You might be the exception but from hanging out on these and other forums I see that at one time or another most DBAs and DB developers have run into a problem where one of those tools (and I'll even include VB Script as a legacy throw-in even though I would not recommend it for new development) appeared to be better suited to solve the issue than anything that could be done in T-SQL, even when considering the augmentation of the language with Windows Batch.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 7:22 am
Sorry, lost track of this thread...
opc.three (9/5/2012)
Jeff Moden (9/5/2012)
Examples of why I'd want to use xp_CmdShell are two fold.
The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.
This sounds like a bit of a pre-judgement. Why not?
Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities.
Sounds like you answered your own question, Orlando.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 19, 2012 at 7:44 am
Jeff Moden (9/19/2012)
opc.three (9/5/2012)
Jeff Moden (9/5/2012)
Examples of why I'd want to use xp_CmdShell are two fold.
The first is cost. It's not likely that I'd ever allow the SSIS server (for example) to live on the same server as my production databases. They'd have to live on separate servers. If I can do everything from T-SQL using the occasional DOS command to move files, an ETL system would cost a lot less on an existing server instead of having to fire up a special server just for SSIS.
This sounds like a bit of a pre-judgement. Why not?
Regarding cost, it was possible with previous versions, but SQL Server 2012 makes it even easier for us to run SSIS packages on an application server which makes sense because it is just another programming language in my view. The direction is clear, separate server responsibilities.
Sounds like you answered your own question, Orlando.
Not quite. I am by no means saying that SSIS and a database engine should never run on the same server Jeff. There is benefit to having a database on the same server where SSIS is running to remove one network hop between your SSIS and the database holding your staging tables. All that was said was that if you needed to scale out your SSIS workload it has become much easier and you do not need a database engine installed on every server where you want to run SSIS.
You never answered my question about where your staging tables reside in relation to your OLTP tables. Using only T-SQL and xp_cmdshell how do you get to a place where you can separate your staging databases from your OTLP databases?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply