By: Tim Ford | Comments (2) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Security
Problem
In my previous tip, I showed how you could dynamically execute a SQL Server Integration Services (SSIS) package from T/SQL. What I did not discuss were the processes by which you could reduce the security risk that enabling xp_cmdshell produces, and in doing so what the ramifications are on security inside the database being accessed via SSIS. It's time now to take care of those topics!
Solution
For tips on how to enable xp_cmdshell please see my previous tip or this tip on how to enable xp_cmdshell. For the purpose of this discussion we are instead going to look at what's next - how do you better-secure the SQL Server instance after enabling xp_cmdshell.
The process we're going to employ is the creation of a proxy account by which xp_cmdshell will be run. This will allow xp_cmdshell to be run without the need to employ an account with System Administrator privileges or elevated domain or local administrator rights, vastly reducing the risk of privilege elevation to the domain and a hacker's paradise happening in your datacenter.
Step one is to create a login you'll assign as your proxy. The important thing here is to ensure it is not a domain admin!
USE MASTER;
GO
CREATE LOGIN [fake_domain\shellProxyUser]
FROM WINDOWS;
After doing so you'll need to create a proxy for the xp_cmdshell to run as, since this is going to be solely a domain user account without local admin rights to the SQL Server you'll greatly reduce the exposure an out-of-the box activated xp_cmdshell instance provides:
EXEC
sp_xp_cmdshell_proxy_account
'fake_domain\shellProxyUser','reallystrongpassword'Now it is simply the matter of creating users in your master database (where xp_cmdshell resides) and assigning execute rights to the xp_cmdshell extended stored procedure. I prefer to do this via a Database Role membership as outlined below. The code below assumes you already have a valid login created on the SQL Server instance:
--Create the database
role and assign rights to the role
CREATE
ROLE [CmdShell_Executor]
AUTHORIZATION [dbo]
GRANT EXEC ON
xp_cmdshell TO
[CmdShell_Executor]
--Then once done create users and assign to CmdShell_Executor
CREATE
USER [fake_domain\kalamazoo_users_group]
FROM LOGIN
[fake_domain\kalamazoo_users_group];
EXEC sp_addrolemember
[CmdShell_Executor],[fake_domain\kalamazoo_users_group];
CREATE
USER [fake_domain\sheldoncoop]
FROM LOGIN
[fake_domain\sheldoncoop];
EXEC sp_addrolemember
[CmdShell_Executor],[fake_domain\sheldoncoop];
CREATE
USER [fake_domain\oliveford]
FROM LOGIN
[fake_domain\oliveford];
EXEC sp_addrolemember
[CmdShell_Executor],[fake_domain\oliveford];
CREATE
USER [fake_domain\seattle_user_group]
FROM LOGIN
[fake_domain\seattle_user_group];
EXEC sp_addrolemember
[CmdShell_Executor],[fake_domain\seattle_user_group];While this gives you a more secure execution of xp_cmdshell it will now cause failure when the package executes. What's that I say? Yes - failure. This is because an out-of-the-box enablement of xp_cmdshell runs under a System Administrator account, I've just shown how to avoid the "sledgehammer approach" of using a System Admin level login for one that will operate with the barest of user rights.
This issue can be overcome quite easily. You only need to grant the login defined in the call we originally made to sp_xp_cmdshell_proxy_account the appropriate rights to any securables touched in the SSIS package. This underlies yet another reason to use Database Roles in your SQL Server databases. If you have a role defined with all the grants required to run the SSIS package you simply need to add the proxy account as a member to the Database Role - otherwise you're about to (possibly) spend some time granting rights to tables, views, stored procedures, and the like to the proxy account.
So to summarize the process for reducing the possibility of rights escalation to your domain from improper use of xp_cmdshell:
- Create a login from an Active Directory account that is a domain user, but not either a domain or local administrator
- Execute sp_xp_cmdshell_proxy_account using the login's credentials you just created to create a non-system administrator proxy for xp_cmdshell
- Create a database role and grant execute rights to xp_cmdshell to that database role
- Add the necessary members to that role for anyone you are going to allow to run xp_cmdshell
- Finally, you'll need to ensure the proxy account has all necessary rights for running the code inside of the SSIS package being called
Next Steps
- Learn the various techniques for enabling xp_cmdshell with this tip.
- Learn how you can run a DOS command from SQL Server without using xp_cmdshell
- Read about how you can execute command line scripts via SQL Agent jobs here.
- Review the previous tip in this series
About the author
Tim Ford is a Senior Database Administrator with MindBody.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
