Use of xp_cmdshell requires sa Permissions 1

[JohnBates]

hi DBAs and others,

We have a developer that has used the following in a stored proc to move a file:

SET @SQL = 'move ' + @FilePath + ' ' + @ArchivePath + 'inbound_' + Convert(nvarchar(20), @FileNumber) + '.txt'
EXEC master..xp_cmdshell @SQL

The problem is that to execute this sp, you have to either login as sa OR your login must have sys admin permissions ??

I guess I have a couple of questions:
1. Is there another way to move a file, other than using xp..cmdshell?

2. If we must use xp..cmdshell, how can I enable this user to execute this sp without logging on as 'sa' ?

Thanks, John

 

[mrdenny]

You can grant access to xp_cmdshell via Enterprise Manager or with "grant exec on xp_cmdshell to {username}".

You will need to setup the proxy account on the Job System tab of the SQL Agent properties.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JohnBates]

aha... I remember that little setting now.

I suppose it would be best to create a special account for the proxy account, rather than just using the account that MSSQLServer and SQLServerAgent start under ?

thanks for the suggestion ! John

 

[mrdenny]

That is correct. And be sure to give the account only the access is needs to the OS.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JohnBates]

.... still have not put this into effect.

After I specify the proxy acct in EM, do you have to stopr/restart SQL Server Agent or Service to put it into effect? I think doing so will knock connected users off until they reconnect ?

Thanks, John

 

[mrdenny]

I don't think that you do. If it didn't prompt you to then you shouldn't have to.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JohnBates]

mrdenny, you have pointed me in the right direction....but something is still amiss.

This is what I've done:
- created a Windows network account on our domain called MEM1\SQLServerProxy (MEM1 is the domain)
- On the SQL Server...Added Login MEM1\SQLServerProxy
- On the SQL server, in master db...
exec sp_grantdbaccess 'MEM1\SQLServerProxy', 'SQLServerProxy'
got "Granted database access to MEM1\SQLServerProxy"
(This step automatically created a user named SQLServerProxy in master)

- in master, grant exec on xp_cmdshell to SQLServerProxy
"The command completed successfully"

- last, thru Enterprise Manager, I changed the SQL Server Agent properties: in Job System, I unchecked the box beside 'Only users with SysAdmin provileges can execute CmdExec steps'
At the proxy account window, i entered
user name: SQLServerProxy and the password and Domain MEM1

- Rebooted server

I ran xp_sqlagent_proxy_account 'GET' ....
it displayed MEM1\SQLServerProxy so I know it is in effect now.

Here's the problem: Developers still cannot execute xp_cmdshell from Query Analyzer - they get "Execute permission denied on object 'xp_cmdshell'

I don't know what else to do. We use Mixed mode authentication.

Does anyone know of anything I left out ?
Thanks, John

 

[mrdenny]

I beleive that you still need to grant them the access to exec xp_cmdshell via grant exec ...

An article I read also says to make sure that the account the runs sql agent is in the local admins group.

Quote from

http://www.databasejournal.com/features/mssql/article.php/10894_3349561_2

executing xp_cmdshell extended stored procedure or ActiveX scripting and CmdExec jobs owned by users who are not part of SysAdmin fixed server roles (we will discuss server and database roles in our next article). In this case, you need to ensure that SQL Server Agent Service account has "Act as Part of the Operating System" and "Replace a Process Level Token" privileges, in order for jobs to execute in the security context of their owner's account. It also needs to be a member of the local Administrators group, in order to be able to retrieve SQL Agent proxy account (defiined on the Job System tab of the SQL Server Agent Properties dialog box in SQL Server Enterprise Manager) credentials stored locally in the form of LSA secrets (Windows-specific secure mechanism for storing credentials, which makes them accessible only to members of the local Administrators group).

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[JohnBates]

yep. I finally figured out that I need to grant, not only the special proxy account permission to exec xp_cmdshell, but the actual user's account as well.

Then I also had to add the proxy account and the user to the master database. Please give me your opinion of this step. Before I added the user and proxy acct, grant exec ....would fail with 'user or group abc not found'

Seems to be working now.
I appreciate your help Denny.
John

 

[JohnBates]

.... oh yeah. In this case, for now, my users are just running stored procs (that use xp_cmdshell) from Query Analyzer.
The quote you gave me will help when we start scheduling these procs as Jobs.
John

 

[mrdenny]

Yeah, that all sounds about right.
Glad we finely got it all working.

Denny

--Anything is possible. All it takes is a little research. (Me)