SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel


In this post i am trying to look into steps to follow while running SSIS package using SQL Authentication and DontSaveSensitive as ProtectionLevel.

I created a simple SSIS package using a connection manager going to SQL Server using SQL authentication, I have my ProtectionLevel as EncryptSensitiveWithUserKey

clip_image001

clip_image003

My package has an OLEDB Source (using connection manager above) which queries a table in database and dumps few rows in flat file.

clip_image005

clip_image006

With above settings ,when I execute my package, Its works like a charm.

clip_image007

Now, I changed the ProtectionLevel  to DontSaveSensitive

clip_image008

And package failed

clip_image009

With following error

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at Package, Connection manager "runeet2k8.sa": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E4D  Description: "Login failed for user ‘sa’.".

Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: The AcquireConnection method call to the connection manager "runeet2k8.sa" failed with error code 0xC0202009.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "Package.dtsx" finished: Failure.

What we see above is expected as we have sensitive information in our package (password for SQL account) and have chosen DontSaveSensitive as protection level. The affect is Password being cleared from our connection manager hence login failure.

For this situation, We would have to save this password somewhere outside the package. I chose to save it in configuration file as shown below. I used XML Configuration file

clip_image011

clip_image012

I had to chose correct connection manager, Its called runeet2k8.sa in my case

clip_image013

This is the XML configuration file that BIDS wrote for me

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

 

I have to go in and type password value.

 

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="FAREAST\runeetv" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                 <ConfiguredValue>Password</ConfiguredValue>

      </Configuration>

</DTSConfiguration>

Now i have a package with ProtectionLevel as DontSaveSensitive and password value for connection manager saved in a configuration file outside the package. Package executed absolutely fine after this.

clip_image014

I went ahead and set this package as Integration Services Job under SQL Agent.

clip_image015

Executed the job and it ran successfully.

clip_image016

One important thing to note is that if you are moving your package from your development machine to say test server for execution. You would have to move your configuration file as well and put it at same path on target machine where its placed on your development box.

For example, on my box, the config file PackageConfig.dtsconfig  is placed at D:\Cases\Integration Services Project1\PackageConfig.dtsconfig and if I am moving my package from my development machine to somewhere else, I would have to move my config file as well and place it at location D:\Cases\Integration Services Project1\ on that machine.

If you don’t have dtsconfig at expected location, Job would fail.

If you done want to create same complex folder path for dtsconfig on target machine as its on your dev box (eg: D:\Cases\Integration Services Project1\) You could place this dtsconfig file anywhere you want and give the pointer of this path from Configuration tab  under you SSIS job step.

clip_image001[4]


Comments (19)

  1. SoniaLive says:

    Really Nice.

    It solved our problem.

  2. Gary says:

    Beware – This all works seeminlgy however did you realize that deploying a package with package configuration turned on means that Sql Agent ignores it when running? Above basically saved your package with the password embeded in the package – it's actually ignoring the configuration files at runtime. Test this by changing the config file and re-running – it ignores the change. However if you deploy a package with configuration file turned off (thereby triggering sql agent ability to configure at runtime), then Sql agent won't read the password.

  3. Shilpi says:

    Its Really very nice

    I am beginner to SSIS. I was stuck in ths problem from last 6 days.

    Now it solved my problem.

    Thanks

  4. Victor says:

    My english is not very good, but i want to thank you. I search for this in many, many webs, without results. I´m beginner in dtsx, and this entry help me a lot!!!

    Regars

  5. Radek says:

    God bless you brother!

    That was such a pain in the ass…

  6. Bruce says:

    thanks for sharing ,it really works!

    i have try my best to search so many soluations,but this is the best way !

  7. Vinodh Madhavan says:

    Thanx a lot … It helped me as well…

  8. Khoa Nguyen says:

    Thanks a lot! It solved my problem!

  9. ms says:

    Now let's say instead of XML configuration I want to use SQL server configuration. And the configuration table will reside in a database that can only be accessed using SQL server authentication. How can I accomplish that??

    Thanks

  10. Samuel says:

    Thanks a ton…this solved my problem when deploying my package in SQL Agent

  11. Kalaiselvan says:

    Thanks a lot I struggle this issue for 2 days. Your suggestion is worked out

  12. Priyanka says:

    Hi

    I am trying to use Http Connection Manager to read from a Sharepoint location. Since Win auth is not allowed we are using credentials. But the connection is acquired while running from my local since it uses my win cred but it fails when I give service account credentials.The service account has access to Sharepoint.Any help is highly appreciated

  13. Douglas Glasser says:

    Hi I also have to thank you for this post! I have read about a dozen articles on accomplishing this task and nothing worked for me. Other articles were supposed to help but it seems that the author became distracted before showing the actual solution. My scenario was that I was connecting to multiple linked servers via SQL Auth for source data and then writing to a local SQL Server via Windows Auth. Great job and thank you!

  14. Stranger says:

    Hi, I'm wondring if there is a way to :

    1 – keep protection level as "EncryptSensitiveWithPassword"

    2 – keep the password crypted into the package

    3 – externalize the other parameters of connexion string into an XML conf file

    Is it possible?

  15. Guaroa Mendez says:

    Thanks so much, I have 2 weeks trying so run a package from a job, now it's working!

  16. Vincent says:

    Thank you so much this was a big problem for me 3 days trying and this helped .:)

  17. vinay says:

    Thanks a lot

  18. Ameya says:

    Brilliant!! Thank you so much for such a well explained blog!! I almost lost the hope of running SSIS packages with SQL Auth .Great job! Thanks again!

  19. Jose Ignacio De Los Reyes says:

    Excelente tu aporte solucionado el problema

Skip to main content