March 7, 2005 at 5:03 pm
Hi All,
I need some information on using a Proxy Account for SQL Server Agent; firstly I need to tell you why we are looking at using a proxy account. We have a website that need to import data from two servers, this is done be using a DTS Package. The website was using xp_cmdshell but using that proc is to much of a security problem. So we created a stored proc that starts a SQL Job which start the DTS package. But to be able to let the web user start the SQL Job we have to setup a Proxy account that has rights to start the SQL Jobs. So this is why I need to find some information on the Security issues with using a proxy account.
What are some of the security problems with using the proxy account?
How have other people make it safe to use?
Thanks
Craig
March 8, 2005 at 10:26 am
Basically, it's a single account. If any user has privileges to execute xp_cmdshell and that user is not a member of the sysadmin fixed server role, then the action will be executed as the proxy account. This can cause a problem if the proxy account has more rights than the user executing xp_cmdshell normally has.
Also, xp_cmshell can be used for reconnaisance should the SQL Server be compromised. In order to see/start the job, the web user must own the job or be a member of the TargetServersRole (this role is undocumented and unsupported). That means the web user can modify the job. If so, the web user could potentially add a step that did something like:
net group "Domain Admins" /domain
And if the proxy account is a domain account, the attacker now knows what user accounts are in the domain admins group.
Would it be better to set up a polling job? Here's how that would work. There's a status table that gets checked by the polling job. When the web user requests the import, an entry gets inserted into the status table. The polling job would see that entry, erase it, and then kick off the DTS package. There would be no tie from the web user to xp_cmdshell, no reason for the proxy account, and no job ownership by the web user. This, of course, depends on how frequently such an import can be requested.
K. Brian Kelley
@kbriankelley
March 8, 2005 at 1:52 pm
Craig,
I have the following procedure in the master database. Since this is in master I can grant the user permission to execute this procedure without giving them permission to execute xp_cmdshell. All I pass is the name of the DTS package to run. I then send an email when the DTS completes successfully which tells them the package is finished.
This has worked great for me as some of my users (non sys admins) need to be able to execute specific packages on demand.
CREATE PROCEDURE dbo.up_RunDTS
@DTSName varchar(75)
AS
DECLARE @cmd varchar(200)
SET @cmd = 'dtsrun /S /E /N "' + @DTSName + '"'
--print @cmd
EXEC master..xp_cmdshell @cmd
GO
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply