SQL Server Agent Using a Proxy Account in 2012

When I created a SQL Server Agent job to execute a SSIS package, it failed because of  “Non-Sys Admins have been denied permission to run DTS Execution job steps without a proxy account.

When we try to execute a SSIS package from a SQL Server Agent job it fails with the message “Non-Sys Admins have been denied permission to run DTS Execution job steps without a proxy account.” if the account under which SQL Server Agent Service is running and the job owner is not a sys admin on the box or the job step is not set to run under a proxy account associated with the SSIS subsystem below the error.

Message: Non-SysAdmis have been denied permission to run the DTS execution job without proxy Account

For such a scenario, SQL Server Agent allows creating a proxy account which defines the security context for the job step. This means each job step of the job can be run under a different security context using different proxies. SQL Server Agent impersonates the credentials (Windows User accounts) associated with the proxy when the job step is executed if the job step is set to run under that proxy. You can create a proxy and grant access to as many of the available subsystem as needed. Also a single proxy can be used by more than one job step to run under it if all of them require the same level of permissions.

I am going to create a job with a single job step which will execute a SSIS package using a proxy account (the job owner is not a sysadmin). This might be very helpful if the job step needs to have additional permissions than available to the account under which SQL Server Agent service is running (by default a job step runs under the security context of the account under which the SQL Server Agent service is running).

Creating a Login for the user that is going to be used as the Credential and Proxy Account

The first thing that you need to do is to put the Domain account you are going to use, as a login on your SQL Server system.

This is so that they will be able to log into the SQL Server System.

1.           In SQL Server Management Studio (SSMS), click on Security, then Logins.

2.           Right click and select New Login

3.           Then in your Login – new Window where it says Login name put in your Domain Account you are going to use. As with our example our Domain Account is the DaminName\Username

4.           Next click on the User Mapping and allow this user to have the permissions required when it runs the SSIS Package

NOTE: If you are using the dbo schema, you would give the user db_owner role for your database.   Otherwise when your SSIS Project runs it.

Then click on MSDB because your user will also require access to be able to see, create and start and stop SQL Server Agent Jobs.

6.       If required you can now try and log into the SQL Server with your domain account. And then also see if you can access the database you granted access to. As with our                                           example it was the AdventureWorks2012 database.

Creating the Credentials

The next step is to create the credentials which will be then used in the Proxy  Account

1.    In SSMS, click on Security and then right click on Credentials, click on New Credential

2.    For the Credential name put in a name for your credential, I suggest using the same name as the domain name.

3.    Click on Identity, which will open the Select User or Group

a.        NOTE: The reason for this is so that you can ensure you select the correct user or Group

b.       NOTE 2: Ideally you are going to have a domain account, where the password does not change.

4.       Then you will need to put in the password for your Domain account you selected.

Then click Ok to create your new Credential

 

Creating the New Proxy Account

The next step is  create a proxy to be used within SQL Server Agent.

1.       In SqlServer Management Studio, click on SQL Server Agent, and then Proxies.

2.       Right click and select new Proxy

3.      Now give your Proxy a meaningful name. In our example I will give it the name of

Proxy_ssis

4.     Then under Credential Name select the credential you created in the steps above.

5.     Then finally it must be active on the following subsystems. From the list    below select SQL Server Integration Services Package

 

6.       Now click on Principals and add your SQL Login to the Principals

a.        As with our Example you can select it from the Add Principal Window.

7.       Then click Ok.

8.       You will now see your Proxy under the SSIS Package Execution

Giving the domain account SSIS access in order to run

The next step is that your domain user needs to be able to read and write data into the SSISDB when running the job. If this is not enabled then when the job runs it will fail.

1.     In SSMS go to your SSISDB, then click on Security, Roles, Database Roles and then double click on ssis_adminrole.

2.       Now when this opens the Database Role Properties – ssis_admin, where it says Members of this role, below thisclick on Add…

a.        Now select your domain account you created above.

b.       As per our example it would be: DOMAINName\UserName

c.       Then click Ok.

d.       You should now see Member under the Members of this Role

3.       Then click Ok.

Creating the Job with your Proxy Account

Now the final step is to create your new job, where you use your SSIS Package and then use the Proxy Account to run the job.

NOTE: Create this job using your domain account (DOMAINNAME\UserName).

·         This is to ensure that you have all the correct permissions in place.

1.       In SSMS go to your SQL Server Agent, right click on Jobs and select New Job

2.       Now under the New Job window give your Job a name.

a.        As with our Example we gave it the name of SSIS Proxy Data Load

b.        You will see under Owner it will be greyed out and have your logged in domain account details (DOMAINNAME\Username)

3.       Click on Steps in the left hand side.

a.        Then click on New at the bottom to create a new step.

b.       This will open the New Job Step Window

c.        In here put in your Step name.   Click on type and from the drop down select SQL Server Integration Services Package.

This is because we created or Proxy earlier that ONLY has access to SSIS Now here you will be able to select your Proxy Account you created earlier

1.      Now under Package ensure that you have your SSIS Catalog selected.

2.      Then put in your SSIS SQL Server Name and if required Instance Name

3.       Then click on

No Comments

Leave a Comment

Your email address will not be published.