April 6, 2010 at 11:32 am
I am currently using xp_cmdshell in my backup stored procedure to delete files, so I must enable xp_cmdshell on my SQL Server 2005 instances.
For developers would be permissiopn level changed after that? What exacly could happend on security level after xp_cmdshell was enabled.
Thank you
April 6, 2010 at 12:27 pm
Any command that you can run from the command line on the server, can now be ran in that stored procedure, or query. They can go as far as taking down the server, or just taking it, and everything on it. Look up "command line commands" in the Windows Help
If you want to be ISO 9002 compliant, you cannot have xp_cmdshell enabled.
Andrew SQLDBA
April 6, 2010 at 12:40 pm
For specifics on xp_cmdshell implications on SQL 2005 see the Books Online reference at:
http://msdn.microsoft.com/en-us/library/ms175046(SQL.90).aspx">
http://msdn.microsoft.com/en-us/library/ms175046(SQL.90).aspx
If your developers have sysadmin access, then they will be able to use xp_cmdshell to run any DOS command they want to using the credentials of the SQL Server 2005 service. (or agent for job executions)
Note that this includes creating local accounts and making existing or new accounts part of the local "Administration" group.
This security "back door" can expose your system to compromise, depending on which accounts are in the sysadmin group.
Enabling xp_cmdshell is a risk, restricting sysadmin access is one way to manage that risk.
April 6, 2010 at 12:50 pm
They don't have sysadmin access level, I want to know if you enable is command is any access level changes for users? Thank you
April 6, 2010 at 12:53 pm
Krasavita (4/6/2010)
They don't have sysadmin access level, I want to know if you enable is command is any access level changes for users? Thank you
No, no SQL Server access is changed, except for the grant on the xp. But still, if you can avoid giving the right out, make every effort. The xp_cmdshell enabled, security is largely out the door.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 6, 2010 at 1:05 pm
This link is not working, do you know where I can find out, what happend when you enable this command?
April 6, 2010 at 1:09 pm
Krasavita (4/6/2010)
This link is not working, do you know where I can find out, what happend when you enable this command?
When you enable xp_cmdshell what will happen is that the user will be able to run anything on the server from the command line. However, in the database, if they are db_datareader, they will still only be db_datareader.
Enableing xp_cmdshell gives a user full access to the DOS prompt. Hopefully, you have ACL's and the SQL Servcie account is (1) a domain acccount, not local service and (2) not a local admin
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 6, 2010 at 1:24 pm
Thank you
April 6, 2010 at 10:52 pm
GregoryF (4/6/2010)
Krasavita (4/6/2010)
This link is not working, do you know where I can find out, what happend when you enable this command?When you enable xp_cmdshell what will happen is that the user will be able to run anything on the server from the command line. However, in the database, if they are db_datareader, they will still only be db_datareader.
Enableing xp_cmdshell gives a user full access to the DOS prompt. Hopefully, you have ACL's and the SQL Servcie account is (1) a domain acccount, not local service and (2) not a local admin
hiii GregoryF ,
how can db_datareader can have access to xp_cmdshell command,
as per my knwldg. min. permission required for xp_cmdshell execution is sysadmin
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
April 7, 2010 at 5:23 am
sanketahir1985 (4/6/2010)
GregoryF (4/6/2010)
Krasavita (4/6/2010)
This link is not working, do you know where I can find out, what happend when you enable this command?hiii GregoryF ,
how can db_datareader can have access to xp_cmdshell command,
as per my knwldg. min. permission required for xp_cmdshell execution is sysadmin
If an explicit grant is made on the XP, anyone can have access. I presume that the poster was planning on granting access to the XP to someone who's permissions are below dbo
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 7, 2010 at 5:49 am
but this is not applicable to xp_cmdshell
to execute xp_cmdshell u must be sysadmin
else even if u grant permission to nonsyadmin user for xp_cmdshell, u won't get permission.
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
April 7, 2010 at 6:16 am
sanketahir1985 (4/7/2010)
but this is not applicable to xp_cmdshellto execute xp_cmdshell u must be sysadmin
else even if u grant permission to nonsyadmin user for xp_cmdshell, u won't get permission.
I beg to differ.
For 2008:
http://msdn.microsoft.com/en-us/library/ms175046.aspx
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
For 2000:
http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
Important If you choose to use a Windows NT account that is not a member of the local administrator's group for the MSSQLServer service, users who are not members of the sysadmin fixed server role cannot execute xp_cmdshell.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 8, 2010 at 7:24 am
GregoryF (4/6/2010)
... Hopefully, you have ACL's and the SQL Servcie account is (1) a domain acccount, not local service and (2) not a local admin ...
I usually use a new Windows account, local to the server, and not a domain account. Minimally privileged, (only privs are those assigned as SQL installer creates the instance), definitely not a local admin, and xp_CmdShell is disabled.
My theory is that a local account can't "no way" hop to another server via the network. Why would one prefer a domain account as SQL startup account, when locking down a SQL instance?
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
April 8, 2010 at 7:30 am
Mike Hinds (4/8/2010)
GregoryF (4/6/2010)
... Hopefully, you have ACL's and the SQL Servcie account is (1) a domain acccount, not local service and (2) not a local admin ...I usually use a new Windows account, local to the server, and not a domain account. Minimally privileged, (only privs are those assigned as SQL installer creates the instance), definitely not a local admin, and xp_CmdShell is disabled.
My theory is that a local account can't "no way" hop to another server via the network. Why would one prefer a domain account as SQL startup account, when locking down a SQL instance?
Why would one prefer a domain account?
1) Backing up across the network
2) ACL's limiting access to drives and directories on the local machine (local system has full access)
3) Allowing servers to communicate
4) Plenty of reasons to run SQL under a domain account (a different account per server for best security)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 8, 2010 at 7:52 am
GregoryF (4/8/2010)
Why would one prefer a domain account?1) Backing up across the network
2) ACL's limiting access to drives and directories on the local machine (local system has full access)
3) Allowing servers to communicate
4) Plenty of reasons to run SQL under a domain account (a different account per server for best security)
All valid, excellent points. I (usually) don't need (1) or (3).
If I choose to set up (4) as you've said, I need to involve our IT security dept to create the service accounts, rather than creating the accounts myself. I have over 200 SQL instances, so I weigh the need for additional bureaucracy (create a ticket, write up a turnover, wait for three approvals, wait for the actual work to be done) vs. the actual payoff in realized security. I am using domain accounts for clusters (currently we have 7).
For (2) I will need to do some study. It seems like the install process (or Configuration Manager if I change service accounts) gives me the same RSOP, but I could be educated to believe differently.
Thanks for sharing your thoughts and advice.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply