Monday, October 21, 2013

Tips for securing xp_cmdshell (when you really really have to use it)

If you have ever read anything about securing SQL server one of the most common threats that is called out is the use of xp_cmdshell. While it is generally accepted that this is a bad thing to enable within a production environment, there are some ligament cases where business, application, or probably more accurately legacy processes, require the use of xp_cmdshell. When we do enable this feature it is important to ensure that security is strictly locked down to prevent unwanted access to sensitive areas on the server or greater still, malicious actions on local and network components.

I was recently asked what my recommendations would be for securely implementing xp_cmdshell, and in my experience here are the steps to perform this:

Step 1
By default xp_cmdshell will execute under the context of the SQL Server service account, therefore the first step in reducing any risk is ensure that the account used for the SQL Server service is aligned to best practises. Such recommendations include the use of an account which:
    - Is not a local or domain administrator
    - Is a domain account where network resources may be required by aspects of the SQL environment (e.g. copying backup files to a network path)
    - Has minimal local and domain privileges
    - Has been configured using the SQL Server Configuration Manager

To completely secure the environment a separate account should also be used for the SQL Agent service with the same recommendations.

Step 2
Ensure that only the required users are members of the SQL Server SysAdmin server level role. Any members of this role will be able to execute xp_cmdshell and therefore allowed access to all aspects of the server that the SQL Service Account can access.

Step 3
Create a xp_cmdshell proxy account following the instructions at
This proxy account should be a unique domain user and separate to the SQL Server Service Account. It will be used when non-sysadmin SQL Logins execute xp_cmdshell and therefore an even restricted Access Control List (ACL) can be configured on the SQL Server and Network resources for that specific account. This will assist in significantly reducing the footprint area which is vulnerable to threat by xp_cmdshell.

Step 4
Grant the required permissions to specific non-sysadmin SQL Logins who require the ability to execute xp_cmdshell using the syntax: GRANT exec ON xp_cmdshell TO ''
This will ensure that only the SQL Logins which you have configured as either members of the SysAdmins built-in role, or explicitly granted execute permissions will be able to access the system via xp_cmdshell.
To view which SQL Logins have been granted permissions for xp_cmdshell run the following TSQL:

USE master;
SELECT AS [schema_name]
, AS [object_name]
, sys.database_permissions.permission_name
, sys.database_permissions.state_desc
, AS [granted_to_principal_name]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
        SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
        FROM sys.objects
        SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
        FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
WHERE = 'sys'
AND = 'xp_cmdshell'
, sys.database_permissions.class;

Final Step
Start reviewing the processes, code, and reasons for using xp_cmdshell to determine if a more secure method could be used (e.g. PowerShell) to achieve the same outcome. Some might argue this should be the first step, but lets be realistic, you cannot always change an applications behavior especially if it is provided by a 3rd party so while this step definitely needs to be performed it may be the most difficult and longest of them all.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.


  1. Denne replika af parisiske familiesko er af perfekt kvalitet,billige Balenciaga sko meget behagelig at bære, iført skoene, når du rejser, ikke træt. Jeg anbefalede denne hjemmeside til mine gode venner.billige Balenciaga sandaler damer De er meget glade for at købe sådanne billige parisiske sko.

  2. En el momento en que abrí la caja, me sorprendió que solo fuera una réplica, pero se veía exactamente igual a la original y la calidad era buena.Réplica de relojes Rolex Creo que este debería ser el mejor reloj de réplica que he comprado. Comparto este sitio web con mis amigos.Réplica de relojes explorer Son muy felices, el precio es barato, puedo comprar un reloj tan bueno.

  3. Christian Louboutin’s replica sneakers collections are a perfect marriage of Parisian replica Boots glamour and Italian craftsmanship. The story of the signature rouge reflects the designer’s flamboyant and creative personality – when working in his studio in the ‘90s, he grabbed a bottle of red nail polish from his assistant Sarah and painted a shoe’s sole. Don’t miss our edit of the iconic ‘So Kate’ and ‘Pigalle’ pumps.

  4. Short for Monestier-de-Clermont, cheap moncler jackets uk an Alpine town near Grenoble,replica man moncler jackets France, Moncler was founded in 1952 by René Ramillon as an outdoor-gear company, producing sleeping bags, tents, and other mountain-climbing necessities. Today, the French-Italian label is best known for its lightweight, super-warm quilted down jackets, as well as ready-to-wear and accessories hardy enough to take on harsh winter weather.

  5. Cartier is the renowned French jeweller and watchmaker with swiss replica watches a history of pioneering design and a drive for excellence. replica rolex watches The first references to Cartier wristwatches date back to 1888. Late in the 19th century it was pocket and chatelaine watches that were most popular, but Louis Cartier saw a future in timepieces that could be worn on the wrist.