Specifying windows account for SSIS SourceConnectionOLEDB

I’m starting an SSIS package from an .aspx using sp_start_job on the SQL-server.

This package is an import-job, where the destination database is on the same server as the job, so the DestinationConnectionOLEDB uses the same SQL-credentials as for running the job.
However, the source is on another server, and the SourceConnectionOLEDB requires a windows domain account for logon. (The package runs fine from Visual Studio, using my windows account).
I seem to have two possible solutions for the account in the SourceConnectionOLEDB.

  • either to pass the account of the user who is running the .aspx
  • or specify a fixed windows account and password in the package.

The last seems not possible to do in Visual Studio, as soon as I select Windows Authentication, the UserID and password field is disabled. Or is there a way of saving this info in the SSIS?

And if not, how do I pass the users windows credentials to the sp_start_job?

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

If I got it right, you have a SQL Server Agent job that runs an SSIS package, with a step of type "SQL Server Integration Services Package".
You cannot save AD user login info in SSIS (afaik), but you can specify which user runs the job step in SQL Server by setting up credentials and proxy to be used in that specific job step.

When you run the job, it gets executed as the SQL Server Agent Account by default, but you can change that by using credentials and proxy.

  • The credentials (Security – Credentials) allows you to specify a username and pw (even an AD user), to be used later by a proxy
  • The proxy (SQL Server Agent – Proxies) allows you to specify which credentials are available to different job step types.

Summary:

  1. Create the credentials object (Security – Credentials) for your AD user
  2. Create the proxy object (SQL Server Agent – Proxies)
    • use the credentials defined in point 1
    • enable the subsystem "SQL Server Integration Services Package"
  3. Configure the job step
    • in the "run as" dropdown you should see the proxy you just created

Of course in your package you must have set Windows authentication for the data source

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply