question

AGMBD-6027 avatar image
0 Votes"
AGMBD-6027 asked CarrinWu-MSFT commented

How to pass encrypted password in SSIS connection string project parameter

Hi Team,

I have created SSIS project with some packages inside the project. Project has "project.params" parameters where i have parameterized the whole connection string.
My connection manager is using the parameterized connection string from variable.

Project.params


variable name : MY_DB_CONN_STRING
value : Data Source=MYSERVER\MYINSTANCE ; Initial Catalog=MYDB ; PROVIDER=SQLNCI11.1 ; User Id=testuser; Password=testpassword ; Auto Translate=False;

Problem: In above connection string if i am giving the password as plain text, the connection manager test connection is successful and the package is executing fine.

But when i am giving the encrypted password as below, the connection manager is not able to make the connection.

variable name : MY_DB_CONN_STRING
value : Data Source=MYSERVER\MYINSTANCE ; Initial Catalog=MYDB ; PROVIDER=SQLNCI11.1 ; User Id=testuser; Password= iCeE6v2qsdhksjdsjdgusGgUYuU/Utfreknjksdhksgdhsg; Auto Translate=False;

I am calling the .ispac file from a .bat file as below from command line and passing the project param connection string.

--------TestJob.bat--------------------------------------------
dtexec.exe /Project "d:\applications\mytestproject.ispac" /Package TestMain.dtsx
/SET \Package.Variables[$Project::MY_DB_CONN_STRING];\"" Data Source=MYSERVER\MYINSTANCE ; Initial Catalog=MYDB ; PROVIDER=SQLNCI11.1 ; User
Id=testuser; Password= iCeE6v2qsdhksjdsjdgusGgUYuU/Utfreknjksdhksgdhsg; Auto Translate=False;"\"
----------------------------------------------------

When passing the encrypted password in above command, the connectionmanager is not able to make the connection.

Please advice how to pass encrypted password from .bat file as a project param.








sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AGMBD-6027, we have not get a reply from you. Did any answers could help you? If there have any answers helped you, please do "Accept Answer". If not, please let us know. By doing so, it will benefit for community members who have this similar issue. Your contribution is highly appreciated. Thank you!

0 Votes 0 ·
RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered AGMBD-6027 commented

I separate the password from the rest of the connection string. So I configure my connection manager, even entering the password.

Then I add an expression to the connection manager, setting the password to my password parameter.

You can also change the server name, user name and database in the same way.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Russel.
will create seperate variable for server, user and password.
But even after that i need to provide the password as encrypted value.

project.params


server_name = MY_SERVER
user_name = MY_USER
user_password = MY_PASSWORD_PLAIN_TEXT

with above plain text password, the connection manager is happy and connection successfull.


project.params


server_name = MY_SERVER
user_name = MY_USER
user_password = wuetwuetw7686dsbisutKGbiuyiug

with above encrypted password, connection manager is not able to make connection.


Q: Do i need to decrypt the password some how at the very start of package and set the decrypted password value to connection manager.
If yes, then how can i decrypt the password in ssis package?




0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT commented

Hi @AGMBD-6027,

Welcome to Microsoft Q&A!

Are you are trying to protect the data in SSIS package? If yes, you could set a protection level that helps protect just sensitive data or all the data in the package. Please refer to USING SENSITIVE PARAMETERS (SSIS SERIES) to get the details.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Carrin,
I am trying to protect the password which is exposed in batch file. Is there any way we can pass encrypted password to db connection string.
Thanks,
AG

0 Votes 0 ·

Hi @AGMBD-6027, it seems that you can't use any package protection levels to encrypt the passwords. You may remove database connection passwords from configuration file and move them into SSIS package code. Please refer to Encryption of Plain Text Passwords in SSIS Configuration Files and this similar thread to get more details.

0 Votes 0 ·