locked
SQL Agent, credentials, proxies and SSIS RRS feed

  • Question

  • We are trying to get SQL Server set up so that non-admin end-users can own and execute jobs that execute SSIS packages. While I understand what I'm about to describe will not pass a good security audit, at this point we're just trying to get it working.

    There are three domain accounts involved here:

    1. The SQL Agent account: ABC\SQLAgent

    2. A domain user account with domain admin priviledges: ABC\Dave

    3. A domain user account that is basically just a member of Domain Users, ABC\Mary.

    The idea here is that we want Mary to be able to own and run a job, but run it as though she were impersonating Dave. Here's what we've attempted so far and the error we are receiving.

     

    1. Add the domain account ABC\Mary to the SQL Server instance.

    2. Map ABC\Mary to the msdb database with the default schema of 'dbo' and membership in the 'SQLAgentUserRole' database role.

    3. Create a credential called 'Dave's Credentials' using the identity of ABC\Dave and specify the domain password.

    4. Map Mary's account the the credential 'Dave's Credential'.

    5. Create a SQL Agent Proxy called 'Proxy via Dave' using the credentials 'Dave's Credentials'. Add the proxy to the

    SSIS subsystem.

    6. Create a SQL Agent Job owned by ABC\Mary containing one job step that executes an SSIS package.

    7. Modify the job step to run as 'Proxy via Dave'.

    8. Start the job at step 1.

     

    The job fails with the error:

     

    Unable to start execution of step 1 (reason: Error authenticating proxy ABC\Dave, system error: Logon failure: unknown

    username or bad password.). The step failed.

     

     

    The password for ABC\Dave has been dumbed-down to make typing it pretty much foolproof, so we're not suspecting a typo at this point. I should also add the protection level of the package is 'DontSaveSensitive'.

    Any ideas?

    Monday, February 7, 2011 7:03 PM

Answers

  • Okay, I'm not crazy.

    I just got off the phone with Microsoft. The problem was the username specified for our SQL Agent service account. 

    When viewing the properties for the Agent in SQL CM, the username for the service account was in the following format:

    someuser@somedomain.loc

    This is the way the username is formatted when you click the Browse button and then Check Names after finding the user (if you're using AD).

    The service account name has to be in the following format:

    SOMEDOMAIN\someuser

    The person I spoke to acknowledged this was a problem with SQL 2005, but admitted he had never seen it with 2008. I asked for a KB or bug number. If I get it, I'll repost it here.

    Manually typing the service account name in the correct format fixed the problem.

    • Marked as answer by D. Pendleton Tuesday, February 8, 2011 9:45 PM
    Tuesday, February 8, 2011 9:44 PM

All replies

  • Update:

    It appears that the none of the credentials/proxies/jobs we create will work. We've tried multiple different credentials, domain accounts and proxies based on them. 

    The only way we can get a job to run is if it is owned by [sa] and each step runs as the SQL Service Account. The error message is always the same: 

    Unable to start execution of step 1 (reason: Error authenticating proxy XXX\xxxxxxx, system error: Logon failure: unknown username or bad password.). The step failed.

    Where XXX\xxxxxxxx represents the domain account used in the credential. We have verified AD connectivity.

    Monday, February 7, 2011 10:08 PM
  • Looks alright.

    Couple of points:

    1. SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Log on as a batch job" permission on the computer on which SQL Server is running.

    Does Dave account have above permission?

    2. I think you should

    - create dave credential

    - create proxy account

    - put Mary under proxy account

    - then ask Mary to run the job, job should be owned by the proxy account.


    Sevengiants.com
    Monday, February 7, 2011 11:24 PM
  • Looks alright.

    Couple of points:

    1. SQL Server Agent proxies use credentials to store information about Windows user accounts. The user specified in the credential must have "Log on as a batch job" permission on the computer on which SQL Server is running.

    Does Dave account have above permission?

    2. I think you should

    - create dave credential

    - create proxy account

    - put Mary under proxy account

    - then ask Mary to run the job, job should be owned by the proxy account.


    Sevengiants.com

    This is what we have done. The accounts created have been granted "Log on as a batch job." (Except the Mary account, that is.)

    Same error, every time.

    Tuesday, February 8, 2011 6:57 PM
  • Okay, I'm not crazy.

    I just got off the phone with Microsoft. The problem was the username specified for our SQL Agent service account. 

    When viewing the properties for the Agent in SQL CM, the username for the service account was in the following format:

    someuser@somedomain.loc

    This is the way the username is formatted when you click the Browse button and then Check Names after finding the user (if you're using AD).

    The service account name has to be in the following format:

    SOMEDOMAIN\someuser

    The person I spoke to acknowledged this was a problem with SQL 2005, but admitted he had never seen it with 2008. I asked for a KB or bug number. If I get it, I'll repost it here.

    Manually typing the service account name in the correct format fixed the problem.

    • Marked as answer by D. Pendleton Tuesday, February 8, 2011 9:45 PM
    Tuesday, February 8, 2011 9:44 PM
  • I can confirm that changing sqlagent account to the form 

     

    SOMEDOMAIN\someuser 

     

    worked for me as well on SQL2008

    Friday, April 8, 2011 10:16 AM