Post reply

Issue with Proxy Account

  • July 21, 2010 at 2:18 am

    #142054

    Hi all,

    SCENARIO:

    I have created the CREDENTIAL using a SQL Login with sysadmin priviledge.

    I have created the proxy account with this credential.

    I have mapped the SQL Agent job executor account (also the owner of the job which has the SSIS subsystem) to the proxy account.

    But when I run hte job, it fails stating it requires a proxy account.

    What could be wrong here? Please share you thoughts

    Thanks

    John

  • July 21, 2010 at 3:26 am

    #1196027

    [p]Create using Windows login and enable xp_cmdshell if required.[/p]

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • July 21, 2010 at 3:46 am

    #1196035

    Hi,

    Thanks for responding.

    I have tried with windows login. But still gets the belowerror message.

    Message

    Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.

    Do I have to give any roles for this under priviledged login.

    As of now, it has SQL Agent Operator role in msdb database.

    Job is in this Win login's name. I'm trying to execute using Win login.

    Why do we need to do enabling xp_cmdshell in this scenario (though in my case it has already been enabled)

    Thanks

    John

  • July 21, 2010 at 3:59 am

    #1196038

    [p]You are welcome. [/p][p]Create credential using Windows admin login.[/p][p]I'm not sure about you scenario, so if you are running any operating system command, you can enable xp_cmdshell.[/p]

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • July 21, 2010 at 4:21 am

    #1196043

    Hi,

    Thanks

    I have created 2 windows users, WinLogin1 and WinLogin2.

    I have added the WinLogin1 to Local Adminidtratros group.

    Added both windows users to SQL Server.

    Mapped the WinLogin1 to 'sysadmin' role.

    Mapped the WinLogin2 to SQLAgentOperator database role in msdb database.

    Created the SQL Agent job with SSIS subsystem. Job is under the ownership of WinLogin2.

    Now created the CREDENTIAL with WinLogin1 as the Identity.

    Created SSIS PROXY with the above created CREDENTIAL.

    Added WinLogin2 as the pricipal for above created PROXY.

    RESULT:

    Tried to run the job, FAILED.

    Anything I'm missing?

    Thanks

    John

  • July 21, 2010 at 7:29 am

    #1196151

    John,

    I'm not sure this will help you as I'm not sure this is the same problem. The problem I had was a developer could not run a SQL Agent job referencing his SSIS package. I created a proxy for him as documented below and he was able to run it if he referenced the proxy.

    Step 1: setup access in msdb

    use msdb

    go

    EXEC sp_addrolemember 'SQLAgentOperatorRole', 'ad-group'

    go

    EXEC sp_addrolemember 'SQLAgentUserRole', 'ad-group'

    Step 2: create a proxy (using SQL Server Management Studio)

    Go to Security | Credentials

    Right-click | New Credential

    Create a name, use Admin-login as the “Identity”, then enter the password for Admin-login.

    Go to SQL Server Agent | Proxies | SSIS Package Execution

    Right-click | New Proxy

    GENERAL TAB:

    Proxy Name: specify a name

    Credential Name: use credential created above

    Check the boxes for the three SQL Server subsystems

    PRINCIPALS TAB:

    Click the 'add' button and select the name of the group(s) who needs access (ie ad-group)

    The developer needs to reference the proxy when executing the package

    HTH

    Cindy

  • July 21, 2010 at 12:21 pm

    #1196395

    Hi

    When you running the job, with what account are you logged on? What are your privileges?

    Does the job runs fine if it left to run as per the schedule?

    Thank You,

    Best Regards,

    SQLBuddy

Viewing 7 posts - 1 through 6 (of 6 total)

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