Software Development

Run the below command with sql login "mysqllogin"

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB.dbo.Brands out c:\export.txt -S PCKodyaz\yukon –U mysqllogin -P myloginpwd'

Exec master..xp_cmdshell @str

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

But when I login with a windows account "MyDomain\MyUserName" and run the same batch. My windows account is a system administrator on the SQL Server 2005 instance.

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB.dbo.Brands out c:\export.txt -c -S PCKodyaz\yukon -U mysqllogin -P myloginpwd'

Exec master..xp_cmdshell @str

The output of the sql batch was pointing of a successfull run

Starting copy...

109 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.): total 1

I was sure that I had configured the SQL Server 2005 weeks before and enabled the database instance to run the xp_cmdshell sql command by running the sp_configure stored procedure.

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'xp_cmdshell', 1

In fact, since as a system administrator (windows account) I could run the xp_cmdshell successfully. This was showing that xp_cmdshell was enabled.

So there had to be a problem with related to the sql login "mysqllogin"

On the master database, I run the below command

USE master;

GRANT EXECUTE on xp_cmdshell to mysqllogin

After the execute permission is granted to sql user mysqllogin, I ran the same t-sql command block with the user rights of mysqllogin user

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB.dbo.Brands out c:\export.txt -c -S PCKodyaz\yukon -U mysqllogin -P myloginpwd'

Exec master..xp_cmdshell @str

This time the returned error message was as follows and noting the proxy account information for the xp_cmdshell

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

I again logged in using my administrator windows account "MyDomain\MyUserName" and run the sp_xp_cmdshell_proxy_account stored procedure and created a proxy credential for xp_cmdshell

EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyUserName', 'myDomainPassword'

MS SQL Server 2005 uses the proxy account credentials to impersonate on behalf of the non-sysadmin users in order to access resources outside the SQL Server.

The syntax for the sp_xp_cmdshell_proxy_account system stored procedure is as

Here the account_name in my case 'MyDomain\MyUserName' is the Windows login which will be the proxy. And password is the password of the Windows login account.

To drop the proxy credential for xp_cmdshell, running the sp_xp_cmdshell_proxy_account system procedure without any parameter will be enough.

EXEC sp_xp_cmdshell_proxy_account NULL

After creating the xp_cmdshell proxy, I could successfully run the below sql command using my sql login mysqllogin

Declare @str nvarchar(4000)

Set @str =N'BCP KodyazDB..Brands out c:\export.txt -c -U mysqllogin -P myloginpwd -S PCKodyaz\yukon'

Exec master..xp_cmdshell @str

Starting copy...

109 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.): total 1