Storing sensetive information in SSIS Packages

[Former Member]

Hi, all!

We use SAP BPC 7.0 sp 06 on SQL Server 2008. We designed packages on test enviroment for load data from SAP BI system to BPC using SAP BI Connector 1.0 and it works well. But when I copy this package from test enviroment to prod server sensetive information disappeare (password to SAP BI system) - I save package with ProtectionLevel - EncryptSensetiveWithUserKey (all standart BPC packages designed with this option). I can only change ProtectionLevel to EncryptSensetiveWithUserPassword. But I cannot run it from BPC with this option. And after copiing to prod server I must loggin in BIDS on prod server change ProtectionLevel to EncryptSensetiveWithUserKey and resave it to BPC folder. It's not suitable way for me.

Does anybody try use Package Configuration such as XML file SQL Sever store? I tried use SQL Server store (save password to SAP BI system in SQL table) but it fails after one load succes load and login to SAP BI system blocked (3 unsucsses entering of password) even at test enviroment.

Thank all for help.

[Former Member]

Hi, again!

BPC service which execute packages don't understand PackageConfiguration Variables.

[Former Member]

Could anybody help with the way of saving passwords to External system in SSIS without any changing in package on prod server ?

Best regards, Iaroslav

[Former Member]

Iaroslav,

If you login to BIDS in your DEV environment with BPC sysadmin user (the one you used to install BPC in PROD) and save a copy of your package as <location/name> in File System using "Encrypt all data with user key" then BPC should be able to run with package with no problems, since it would run with the same credentials.

This is a theory and might not work in BPC, but worse trying....

More info on protection levels in SSIS is here: http://msdn.microsoft.com/en-us/library/ms141747.aspx

Regards,

Akim

[Former Member]

Hi!

I tried but then I run this package with sysadmin rights it faild. I tried open copy of this package in BIDS on PROD server with sysadmin rights and field password empty.

[Former Member]

Hm... Without troubleshooting, the easiest workaround would be using configuration file or table then...

Create Package Configuration (either file or table); add ConnectionString property of your BI connection to that configuration; save it.

Here is a tricky part... if it's a table configuration, then connection to that that configuration table should be trusted and your bpc sysadmin user should have access to that table. With XML file: it should be saved into the same path in your PROD as in DEV.

You'd have to manually modify ConnectionString property in your configuration file/table and add Password at the end of connection string in the ConfiguredValue column... something like:

Password=YOUR_SQLSERVERUSER_PASSWORD;

The password in your package would still be blank, but package won't fail if it's able to read configuration file/table.

Just make sure table/file is secured from unauthorized access. And again, I haven't tested this with BPC

Regards,

[Former Member]

I tried use SQL table to save Password - the same in Test and Prod server, and I modified connection script at package run (I wrote about before) . And BPC doesn't understand, or doesn't allow to use this configuration value. It only works after I open this package in BIDS on prodserver and resave it.

[Former Member]

Iaroslav,

I don't have SQL Server 2008 environment... However, I've created a test package in SQL 2005 on my AppServer with a simple task to query table using SQL login and send an email; added package configuration (table); modified password for SQL login in that table... in the package I have 2 connections:

1. Trusted connection to SSIS configuration database (BPC sysadmin has to have rights to read from that table)

2. SQL Server authentication connection (stored in the config)

It's BPC v7.0 SP06.

I ran the package in BlDS on the app server - worked fine.

Copied dtsx to my BPC DB server (webfolders\....). Added package in BPC; ran it and it worked just fine.

Maybe SQL login you are using has password locked or your BPC admin account (the one you used to install BPC with) doesn't have access to SSIS Config table? Unless, for whatever reason, BPC with SQL Server 2008 doesn't want to read config...

[Former Member]

Akim,

I have done the same, but it works only then I reopen package in BIDS on PROD server, and after that it works. My Test and Prod server have the same sysadmin - sysadmin in win-domen in wich my servers included.

I'll try again.

[Former Member]

Hi!

BIDS won't save passwords becose it sensetive information. But we can insert password by hand in XML file or SQL Table. To keep security restriction better way create table with SSIS configuration variables in DB of our Application Set and backup this table to prod server, and only administrators of SQL Server able to see this password.

Thanks all for help!

[former_member204026]

Hi,

SQL Server allows the configuration to be stored in the XML as well as the SQL Server database. Please find the Microsoft link for the same. http://msdn.microsoft.com/en-us/library/ms141682.aspx. Package Configurations, from SQL Server 2005, facilitate the changing of properties of package components at run time, by applying values stored outside the package (it is possible to use environment variables, registry entries, XML-formatted files, or variables contained in a parent package for this purpose).

You should be able to store the package information using these methods. When you store the configuration in the SQL Server it stores the configuration in a table in the data base specified in the connection. While moving you code to production you should be moving this table along with production credentials.

Hope this helps.