Security issue with DTSRUN and SQL Server Agent. 1

[gradley]

I am trying to execute a DTSRUN command from SQL Server Agent for a user that does not have sysadmin rights. Here is the message I receive when I view the job history for this job:

"Non-SysAdmins have been denied permission to run CmdExec job steps. The step failed."

I've heard that Cross-database ownership chaining can be used to address this issue but I'm not sure how to implement this logic, or how vunerable it makes your server.

Any help would be appreciated.

Thank you

Gordon Radley

 

[mrdenny]

I do believe that this will help you out

http://support.microsoft.com/?id=264155.

Denny

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

 

[gradley]

Thanks mrdenny!

This was a big help. It took me awhile to realize that in SQL Server 2000, SQLAgentCmdExec is a user (service) account that is not visible anywhere in Windows and that any user account name (other than SQLAgentCmdExec) will work fine for the proxy service.

I have added this user to the administrators group but denied logon locally. I don't believe i've compromised the security in any way.

Thanks again.

 

[mrdenny]

I'd restrict the access to the account your using for the proxy. With the account having admin rights to the OS now any user can connect to the SQL Server and execute xp_CMDShell 'net localgroup Administrators username /add' and make them selves an admin on the server.

Denny

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

 

[gradley]

Thanks for the heads-up.

It looks like i'm unable to execute xp_cmdshell "EXECUTE permission denied" unless I connect as a system administrator account remotely (through Query Analyzer or otherwise). Because I have denied local logon to the proxy account and have not specifically given this new account acces to SQL server (other than through the BUILTIN\Administrators), I don't see how someone can connect and use this xp_cmdshell procedure.

Please let me know if i'm overlooking something here.

thanks again.

 

[mrdenny]

I'm just working on the side of caution. In case someone changes the local logon permission.

Denny

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

 

[gradley]

I appreciate that!

Thanks again.