Using SSIS packages to get around SQLs Permission issues caused by connecting to a remote server with linked servers

The other day we were faced with the double hop sql error. Basically we needed to connect to a sql server with a service account that accessed linked servers but we were not able to connect to the box directly. The only way we could get past this issue was:

Create an ssis package that calls a wrapper sproc, edit the wrapper sproc to do any execution I needed to run under that service account, and set that account to run under a proxy.

First create an SSIS package, connect it to your database (right click connection manager) and either use “execute T-SQL Statement Task” as seen here or “Execute SQL Task” you can see the execute statement is just a simple wrapper sproc. 

 Next we must set up the database to create the proxy under the service account. Here is where you give a common name in the Credential name, and the username and password in identify and password field.

 Now we must create a proxy for SSIS package, proxy name can be anything you wish; Credential Name was the name you gave in the previous step. Make sure you select SQL Server Integration Service Package (SSIS).

Last step is to create a job using the newly created proxy to run the SSIS package. Here we are clicking on new job. In steps we are creating new step. In steps we are calling making sure the type is SQL Server Integration Service Package. The Run as should see the Proxy name you set in the drop down. If you do not see it, double check you selected SQL Server Integration Service package. Then set the source and call you your SSIS Package

 Now you can fire off this task and it will complete with the rights of the service account. Now all you have to do is modify the wrapper sproc while the ssis package remains untouched.