Now select your domain account you created above.ĭ. Now when this opens the Database Role Properties – ssis_admin, where it says Members of this role, below this click on Add…Ī. NOTE : You will only see this role under the SSISDBĢ. In SSMS go to your SSISDB, then click on Security, Roles, Database Roles and then double click on ssis_admin role.ī. If this is not enabled then when the job runs it will fail.ġ. The next step is that your domain user needs to be able to read and write data into the SSISDB when running the job. Giving the domain account SSIS access in order to run You will now see your Proxy under the SSIS Package Execution As with our Example you can select it from the Add Principal Window.ĩ. Now click on Principals and add your SQL Login to the PrincipalsĪ. NOTE : We select this because we want it to run our SSIS Packages.ħ. From the list below select SQL Server Integration Services Packageī. Then finally it must be active on the following subsystems.Ī. As per our Example it was the following:ĥ. Then under Credential Name select the credential you created in the steps above.Ī. In our example I will give it the name of Proxy_Domain_UserNameĤ. In SSMS, click on SQL Server Agent, and then Proxies.ģ. Now the next step is where you create a proxy to be used within SQL Server Agent.ġ. Then click Ok to create your new Credential Then you will need to put in the password for your Domain account you selected.ī. NOTE 2 : Ideally you are going to have a domain account, where the password does not change.Ĥ. NOTE : The reason for this is so that you can ensure you select the correct user or Groupī. Click on Identity, which will open the Select User or GroupĪ. For the Credential name put in a name for your credential, I suggest using the same name as the domain name.ģ. In SSMS, click on Security and then right click on Credentials, click on New CredentialĢ. The next step is to create the credentials which will be then used in the Proxy Accountġ. You should also be able to see the SQL Server Agent. As with our example it was the AdventureWorks2012 databaseĬ. And then also see if you can access the database you granted access to.ī. If required you can now try and log into the SQL Server with your domain account.Ī. 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.Ħ. Under the Roles for our example we selected the following: 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. Otherwise when your SSIS Project runs it could potentially fail when trying to read a stored procedure or insert data into a table.ĭ. NOTE : If you are using the dbo schema, you would give the user db_ owner role for your database.ī. Next click on the User Mapping and allow this user to have the permissions required when it runs the SSIS Package.Ī. As with our example our Domain Account is the followingĥ. Then in your Login – new Window where it says Login name put in your Domain Account you are going to useĪ. In SQL Server Management Studio ( SSMS), click on Security, then Logins.ģ. This is so that they will be able to log into the SQL Server System.ġ. 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. NOTE : This is all completed on SQL Server 2012Ĭreating a Login for the user that is going to be used as the Credential and Proxy Account NOTE : When setting up all the SQL Settings below you need to have DBA Access 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. 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.