CODEWITHSUNDEEP
asp.netsql-serverssis
John Martin Kvalsund
John Martin Kvalsund

Reputation: 35

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.

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?

Upvotes: 0

Views: 780

Answers (1)

Giovanni Luisotto
Giovanni Luisotto

Reputation: 1400

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

Upvotes: 1

Related Questions