Post reply

SQL Server Agent account for job

  • April 3, 2013 at 10:34 am

    #275942

    Hello - I have a developer that wants to set up a job to run ActiveX Script.

    They can't see anything under "Run As"

    according to

    http://msdn.microsoft.com/en-us/library/ms187668.aspx

    Set the proxy account for the job step. Members of the sysadmin fixed server role may also specify the SQL Agent Service Account

    Adding a proxy means I need to go through the windows security team - which may not get approved.

    Is my only choice to make this login sysadmin? I don't want to do that either for security reasons.

    Am I missing a possibility? Does the account need sysadmin after the job is created?

    Thanks

    Dave

  • April 3, 2013 at 11:34 am

    #1603313

    Hi Dave,

    This is how we do it...

    -Create a credential (SSMS> Security> Credential) with a Windows Account that we name and designate for the task... i.e. service account. Make sure that Credential has only the permissions needed to complete the task

    -Create the Proxy (SQL Agent> Proxies> ActiveX Script) selecting the Credential above and active to the ActiveX Subsystem

    *** you've probably already done the above***

    -Go to the Principals Page on the proxy account and assign a the Principal (i.e. login... and that can be Windows or SQL authentication, likely of your developer) that will own the job. So when the job gets executed under the context of that owner, the proxy takes over and runs the ActiveX.

    -Make sure that Principal (i.e. login of the developer, has the appropriate role membership in MSDB (i.e. SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole)

    Then when you create the job and make the owner the develper's login. ActiveX Step Type will be available, and drop-down Run As: setting to the Proxy you created.

    Hope that helps you out!

    Cheers,

    Todd

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • April 3, 2013 at 11:43 am

    #1603314

    Ah, almost forgot... no sysadmin needed.

    Give minimum permissions required to the developer login, and the Credential that will be used by the Proxy.

    NO SYSAMIN TO ANYONE EVER!

    Except for me!

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • April 3, 2013 at 12:11 pm

    #1603321

    OK - thank you - the developer was using SQL Server authentication - so it seems there is no way to do this without a windows account.

    If that is true - what privlages would be needed for the windows account? I want to know the correct thing to ask my windows admin - would the new window account be a local windows account? a local admin? I know not a domain admin...

    I'm very green when it comes to the levels of windows authentication on a server.

    Thanks for your help

    Dave

  • April 3, 2013 at 12:25 pm

    #1603326

    Ok makes sense... developer as SQL authentication. All those rights are within you're control.

    However, regarding what permissions the Credential (Windows Account you will use to actually execute the ActiveX)... well, that all depends on what the ActiveX script will do.

    What is he doing with the script?

    Manipulating files on a local directory, in a network share, calling an external program?

    In all cases, whatever that script will try to do is what it needs permissions to do.

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • April 3, 2013 at 12:28 pm

    #1603328

    Hes sending e-mail

    Type= ActiveX Script

    Language=JScript

    var ret = "";

    var xml = CreateObject("msxml2.serverxmlhttp");

    xml.setOption(2, 13056);

    xml.open("POST", "http://172.31.62.50/aspsoft/SendEmail.ashx?rnd=" + Math.random(), false);

    xml.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");

    xml.send();

    xml = null;

    Thanks

    Dave

  • April 3, 2013 at 12:28 pm

    #1603329

    Super curious now... what is he trying to accomplish with an ActiveX script?

    Hopefully something external of SQL Server!

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • April 3, 2013 at 12:31 pm

    #1603331

    Maybe investigate Database Mail functionality... keep it all within SQL then?

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • April 3, 2013 at 12:32 pm

    #1603332

    Or tell your server admins that it just needs permissions to send an email...

    seems like a reasonable enough request.

    Todd Carrier
    MCITP - Database Administrator (SQL 2008)
    MCSE: Data Platform (SQL 2012)

  • April 3, 2013 at 12:33 pm

    #1603333

    I will try that - thank you.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply