Setting a Proxy Account to run SqlServer Integration Services(SSIS) 2012 Packages

SQL Server

https://gqbi.wordpress.com/2014/01/30/setting-up-a-proxy-account-to-run-sql-server-integration-services-ssis-2012-packages/

Below are the steps that we had to do in order for us to create and setup a Proxy Account to run our SSIS Project. As well as allowing the domain user to have access to the job so that they could create and edit the job which ran the SSIS Project.

NOTE: When setting up all the SQL Settings below you need to have DBA Access

NOTE: This is all completed on SQL Server 2012

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

a.        

3.       Then in your Login – new Window where it says Login name put in your Domain Account you are going to use

a.        As with our example our Domain Account is the following

                                                               i.      DOMAIN\UserName

4.       

5.       Next click on the User Mapping and allow this user to have the permissions requiredwhen it runs the SSIS Package.

a.        NOTE: If you are using the dbo schema, you would give the user db_owner role for your database.

b.       Otherwise when your SSIS Project runs it could potentially fail when trying to read a stored procedure or insert data into a table.

c.        

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

                                                               i.      Under the Roles for our example we selected the following:

1.       

                                                              ii.      NOTE: I am not a SQL Server security expert so there might be another way to allow a user access to the SQL Server Agent.

e.       Then click Ok

6.       If required you can now try and log into the SQL Server with your domain account.

a.        And then also see if you can access the database you granted access to.

b.       As with our example it was the AdventureWorks2012 database

c.        You should also be able to see the SQL Server Agent.

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

a.        

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 passworddoes not change.

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

a.        

b.       Then click Ok to create your new Credential

Creating the Proxy Account

Now the next step is where you create a proxy to be used within SQL Server Agent.

1.       In SSMS, click on SQL Server Agent, and then Proxies.

2.       Right click and select new Proxy

a.        

3.       Now give your Proxy a meaningful name.

a.        In our example I will give it the name of Proxy_Domain_UserName

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

a.        As per our Example it was the following:

b.       DOMAIN\UserName

5.       Then finally it must be active on the following subsystems.

a.        From the list below select SQL Server Integration Services Package

b.       NOTE: We select this because we want it to run our SSIS Packages.

6.       

7.       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.

8.       Then click Ok.

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

a.        

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 SSISDBwhen 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 thendouble click on ssis_admin role.

a.        

b.       NOTE: You will only see this role under the SSISDB

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

a.        Now select your domain account you created above.

b.       As per our example it would be:

                                                               i.      DOMAIN\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 theProxy Account to run the job.

NOTE: Create this job using your domain account (DOMAIN\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

a.        

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.       

c.        You will see under Owner it will be greyed out and have your logged in domainaccount details (DOMAIN\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.

                                                               i.      With our example we put in the following:

1.       Load SSIS Parent – Data Load

                                                              ii.      Click on type and from the drop down select SQL Server Integration Services Package.

                                                            iii.      NOTE: If you do not select SQL Server Integration Services Package, in the next box below you will not have any options.

1.       This is because we created or Proxy earlier that ONLY hasaccess to SSIS

                                                            iv.      Then click on the drop down for Run as:

1.       Now here you will be able to select your Proxy Account you created earlier.

2.       As with our example we selected Proxy_Domain_UserName

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

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

2.       Then click on the ellipses under Package and select your SSIS Package that you want to run.

d.       Then click Ok

e.       

4.       Now click on Schedules and create your required schedule for your job.

5.       Once it is complete you should now see your job under the Jobs on SQL Server Agent

a.        

The final step now is to run your job as per the schedule or manually and see if it succeeds.

NOTE: It should work correctly, but if it does not then please let me know, so that I can check if I have missed any steps.

Resumen:

- crear credencial con usuario sysadmin de dominio o windows

- crear cuenta proxy asociada al usuario anterior (marcar SQL Server Integratron Services Package)

- configurar en el job de ejecución del paquete SSIS el "Run as:" poniendo la cuenta proxy