XP_CMDSHELL
xp_cmdshell command is used to execute the dos commands from SQL Server.
What are the steps we have to use it:
By default, this option is not enable in SQL Server. DBA has to enable it explicitly.
As it is a server level property, enable it server level.
GUI:
In SQL Server 2005, we can use "SQL SERVER SURFACE AREA CONFIGURATION " tool which will be installed as part of CONFIGURATION TOOLS.
Click on "Surface Area Configuration for Features" and check the properties which you want to enable and Apply.
In SQL Server 2008, "SURFACE AREA CONFIGURATION" is replaced by "FACETS".
Right click on the Instance Name after connecting to OBJECT EXPLORER in the SSMS studio.
Click on FACETS to see the server level properties and change them according to the need of Project.
TSQL:
This part is same in 2005/2008 and higher versions of SQL Server.
sp_configure 'show advanced options' , 1
GO
Reconfigure
GO
sp_configure 'xp_cmdshell', 1
GO
Reconfigure
By default, this option is not enable in SQL Server. DBA has to enable it explicitly.
As it is a server level property, enable it server level.
GUI:
In SQL Server 2005, we can use "SQL SERVER SURFACE AREA CONFIGURATION " tool which will be installed as part of CONFIGURATION TOOLS.
Click on "Surface Area Configuration for Features" and check the properties which you want to enable and Apply.
In SQL Server 2008, "SURFACE AREA CONFIGURATION" is replaced by "FACETS".
Right click on the Instance Name after connecting to OBJECT EXPLORER in the SSMS studio.
Click on FACETS to see the server level properties and change them according to the need of Project.
TSQL:
This part is same in 2005/2008 and higher versions of SQL Server.
sp_configure 'show advanced options' , 1
GO
Reconfigure
GO
sp_configure 'xp_cmdshell', 1
GO
Reconfigure
What if, some user wants to access this CMD SHELL tool. How to provide access to User.
We have a create proxy account for running this tool in highly secured environment.
What is the purpose of Proxy account:
The User who wants to use xp_cmdshell will inherit the properties of this proxy account. Then only user can execute xp_cmdshell command.
How to Create Proxy Account:
Use Master
GO
Create credential ##xp_cmdshell_proxy_account## with identity ='Domain\username',secret='Password'
GO
Above command will create the proxy account in SQL Server only when that account already exists in the Domain of the server.
How to Drop Proxy Accounts:
If there is any issue Proxy accounts, then drop them and recreate it.
Use Master
GO
sp_xp_cmdshell_proxy_account NULL
GO
Above command will drop the proxies which are configured to run xp_cmdshell
How to add User to access this Proxy account.
Add user to the Master database of the server to access xp_cmdshell using Proxy account.
After adding the user in the master database, run the below query in the master database to provide privileges.
Use Master
GO
Grant Execute on [sys].[sp_xp_cmdshell_proxy_account] to [USERACCOUNT]
GO
Its all done now.
User can access xp_cmdshell tool from his credentials.
We have a create proxy account for running this tool in highly secured environment.
What is the purpose of Proxy account:
The User who wants to use xp_cmdshell will inherit the properties of this proxy account. Then only user can execute xp_cmdshell command.
How to Create Proxy Account:
Use Master
GO
Create credential ##xp_cmdshell_proxy_account## with identity ='Domain\username',secret='Password'
GO
Above command will create the proxy account in SQL Server only when that account already exists in the Domain of the server.
How to Drop Proxy Accounts:
If there is any issue Proxy accounts, then drop them and recreate it.
Use Master
GO
sp_xp_cmdshell_proxy_account NULL
GO
Above command will drop the proxies which are configured to run xp_cmdshell
How to add User to access this Proxy account.
Add user to the Master database of the server to access xp_cmdshell using Proxy account.
After adding the user in the master database, run the below query in the master database to provide privileges.
Use Master
GO
Grant Execute on [sys].[sp_xp_cmdshell_proxy_account] to [USERACCOUNT]
GO
Its all done now.
User can access xp_cmdshell tool from his credentials.
