May 23, 2018 at 1:59 am
I have a need to provide a password to an SSIS package so that the package can be deployed to multiple servers and connect to different destinations in each case. Historically I would have completed this task via Integrated Authentication, but in my new case the destination is not on the domain and so a username/password combo is required.
I've read that it's not a good idea to use the Package ProtectionLevel values of EncryptSensitiveWithUserKey, EncryptSensitiveWithPassword, EncryptAllWithPassword or EncryptAllWithUserKey as they introduce other issues, which leaves only the DontSaveSensitive value. This ensures that values such as passwords are not saved with the package and therefore need to be provided at runtime.
Due to company policy I am forced to use a Package Deployment model rather than a Project Deployment model should this make any difference. I'm similarly restricted to SQL Server 2012 features unless a good case can be made for moving to a newer version.
I've considered passing the password in when calling the package from an SQL Job but am not sure I like the idea of leaving the password as plain text in the Job. This may ultimately be what I have to do if I can't find a better option.
I've considered adding a variable to the config file but this too leaves the password in a plain text format.
I know that it's possible to encrypt sections of a web.config file for web sites to hide sensitive data, but I don't know if this is possible with an SSIS package config file.
My question is, what is considered to be the best approach to providing a password to an SSIS package at runtime which best protects the password?
May 23, 2018 at 2:24 am
Who are you trying to protect the password from? I would put the username and password in the datasource for the SQL Job, it actually doesn't show in there once entered. Going back to my original point, the only person that should be able to manage and edit jobs is the DBA anyway.
Another option is to encrypt the package so that even to view the properties, you would need a password, this way, anyone without the password wouldn't be able to view the data sources that way.
Your DBA will definitely need to know the password, else they are likely (if they are nay good) to refuse to support it.
May 23, 2018 at 4:14 am
Rick-153145 - Wednesday, May 23, 2018 2:24 AMWho are you trying to protect the password from? I would put the username and password in the datasource for the SQL Job, it actually doesn't show in there once entered. Going back to my original point, the only person that should be able to manage and edit jobs is the DBA anyway.Another option is to encrypt the package so that even to view the properties, you would need a password, this way, anyone without the password wouldn't be able to view the data sources that way.
Your DBA will definitely need to know the password, else they are likely (if they are nay good) to refuse to support it.
Rick,
Our concern is that should our systems be compromised that the password will become known to attackers thus giving them additional attack vectors. The username and password are currently low privilege, but experience tells me that this creeps with time and that it may be of more an issue in the future.
Would you mind expanding on your comment "I would put the username and password into the datasource for the SQL Job"? Are you referring to the 'Connection String' values on the [Data sources] tab of the Job Step Properties? I've not had reason to use this in the past, but a *very* quick check of it shows that it might be what I'm looking for. I'll need to research this a bit more to see how it works and if it is the correct solution for my scenario. It'll have the same issues as passing in a value to the package from the job, but as you point out only the DBA should have access to it.
Thanks,
Mike
May 23, 2018 at 4:19 am
MikeRen - Wednesday, May 23, 2018 4:14 AMRick,
Our concern is that should our systems be compromised that the password will become known to attackers thus giving them additional attack vectors. The username and password are currently low privilege, but experience tells me that this creeps with time and that it may be of more an issue in the future.
Would you mind expanding on your comment "I would put the username and password into the datasource for the SQL Job"? Are you referring to the 'Connection String' values on the [Data sources] tab of the Job Step Properties? I've not had reason to use this in the past, but a *very* quick check of it shows that it might be what I'm looking for. I'll need to research this a bit more to see how it works and if it is the correct solution for my scenario. It'll have the same issues as passing in a value to the package from the job, but as you point out only the DBA should have access to it.
Thanks,
Mike
Yes, that's exactly what I am referring to. It also means that the devs do not need to know the passwords for other environments, just the DBA/deployer.
May 23, 2018 at 4:20 am
Thom A - Wednesday, May 23, 2018 2:29 AMI have to ask, but why why is your company's policy to not use SSISDB? It is far superior to file system and MSDB deployment methods.
The short answer is - Legacy code.
The longer answer is that my company doesn't have a dedicated DB team and so the role of DBA is shared amongst a team of analysts and programmers. Since there is no 'expert' we try to keep the expectation on the team low. We had a DTS environment which was generally understood, but switching to SSIS meant new knowledge and the simplest path was to go down the file system route. If there are good reasons to use SSISDB then I could make a case for the switch but it'd require some pain on the team.
It's for this reason that I'm looking for the current best practice for storing passwords as I know that we'll be stuck with it for some time.
May 23, 2018 at 4:35 am
MikeRen - Wednesday, May 23, 2018 4:20 AMThom A - Wednesday, May 23, 2018 2:29 AMI have to ask, but why why is your company's policy to not use SSISDB? It is far superior to file system and MSDB deployment methods.The short answer is - Legacy code.
The longer answer is that my company doesn't have a dedicated DB team and so the role of DBA is shared amongst a team of analysts and programmers. Since there is no 'expert' we try to keep the expectation on the team low. We had a DTS environment which was generally understood, but switching to SSIS meant new knowledge and the simplest path was to go down the file system route. If there are good reasons to use SSISDB then I could make a case for the switch but it'd require some pain on the team.
It's for this reason that I'm looking for the current best practice for storing passwords as I know that we'll be stuck with it for some time.
Might be worth mentioning then that when you deploy to the SSISDB the protection level is removed (not from the project, but the deployed forms). The packages themselves on the server are encrypted, so you can't then query the database to get the sensitive data out.
I realise you say you've not got the expertise, however, SSISDB is a huge improvement on the old filesystem and msdb deployment models. I really recommend you consider it and training is that high either ; really just just the initial set up that needs some knowledge (and make sure you keep a backup of your SSISDB key!!!). After it's set up, the developers will find deploying much more of a "joy" than using a filesystem method. Agent jobs are much easier to manage as well; as the parameter mapping is vastly easier. I really can't think of any cons to using SSISDB over MSDB or filesystem deployment for SSIS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 24, 2018 at 11:10 am
I never recommend that userids or passwords be placed in SSIS or any program. Both userid and password should be encrypted and passed in to the process or looked up in a protected table. A SSIS Script Task can be used to decrypt the userid and password to be used as a connection expression variable, although preferably not called userid or password. The userid and password would only appear in clear text in memory which reduces the chance of exposure. Passwords should be changed periodically and userids may change as people leave or change position in a company. If the company is a public company placing userids and passwords in a program could create an audit flag.
May 25, 2018 at 1:11 am
DennisR - Thursday, May 24, 2018 11:10 AMI never recommend that userids or passwords be placed in SSIS or any program. Both userid and password should be encrypted and passed in to the process or looked up in a protected table. A SSIS Script Task can be used to decrypt the userid and password to be used as a connection expression variable, although preferably not called userid or password. The userid and password would only appear in clear text in memory which reduces the chance of exposure. Passwords should be changed periodically and userids may change as people leave or change position in a company. If the company is a public company placing userids and passwords in a program could create an audit flag.
I couldn't agree more and this is exactly why I was trying to find a better way to achieve my goal. What you describe is what I was thinking of but I couldn't find a suitable example on the web. Do you know of an example of how to encrypt a password and decrypt it within the package?
The reason I'm having to use a password at all is because the remote system is not on our domain, but I've since found out that we are AD Federated with that organisation and therefore I might be able to use Integrated security and thus not require a password at all (a much preferable situation). I've had a steer towards using Credentials and Proxies so that I can ensure the Job Step runs as a user which will be authenticated at the other end. I'm investigating this now, but if you have an example of encrypting/decrypting then I'd welcome the opportunity to take a look.
May 25, 2018 at 1:56 pm
MikeRen - Friday, May 25, 2018 1:11 AMDennisR - Thursday, May 24, 2018 11:10 AMI never recommend that userids or passwords be placed in SSIS or any program. Both userid and password should be encrypted and passed in to the process or looked up in a protected table. A SSIS Script Task can be used to decrypt the userid and password to be used as a connection expression variable, although preferably not called userid or password. The userid and password would only appear in clear text in memory which reduces the chance of exposure. Passwords should be changed periodically and userids may change as people leave or change position in a company. If the company is a public company placing userids and passwords in a program could create an audit flag.I couldn't agree more and this is exactly why I was trying to find a better way to achieve my goal. What you describe is what I was thinking of but I couldn't find a suitable example on the web. Do you know of an example of how to encrypt a password and decrypt it within the package?
The reason I'm having to use a password at all is because the remote system is not on our domain, but I've since found out that we are AD Federated with that organisation and therefore I might be able to use Integrated security and thus not require a password at all (a much preferable situation). I've had a steer towards using Credentials and Proxies so that I can ensure the Job Step runs as a user which will be authenticated at the other end. I'm investigating this now, but if you have an example of encrypting/decrypting then I'd welcome the opportunity to take a look.
Just about any C# code can be run in a script task, search the web for C# AES encryption. If I identify anything specific then how you were encrypting/decrypting would be public knowledge. This can also be done with the EncryptByPassPhrase and DecriptByPassPharase -
DECLARE @VARB1 varbinary(max);
DECLARE @VARC1 varchar(max);
select @VARB1 = EncryptByPassPhrase ( 'MyFavoritePassPhrase', '12345mypassword' )
select @VARB1 ENC1
,CONVERT(varchar(max), @VARB1 ,2) ENC1_C
,len(CONVERT(varchar(max), @VARB1 ,2)) ENC1_C_LEN
SELECT @VARC1 = CONVERT(varchar(max), @VARB1 ,2) + '15'
SELECT cast(DECRYPTBYPASSPHRASE ('MyFavoritePassPhrase', convert(varbinary(max)
, '0X'+substring(@VARC1,1,len(@VARC1)-2), 1)) as varchar(800)) myPW
This alters the binary value from the Encrypt process by adding a x'15' so when stored it cannot be decrypted directly. More complex alteration may be used on the encryption value. The decrypt code removes the added x'15'. The password has an added prefix of 12345 so even if decrypted it is not valid until the 12345 is removed. The PassPhrase may be dynamic, perhaps including the userid or department. The encryption is triple DES so is not the current standard of AES. IBM believes all current encryption methods will breakable in seconds with quantum processors and the NSA is considering lattice based encryption which may survive quantum attacks for a few years. Is a C# encryption more secure than a SQL statement? Both can be placed in a SSIS package so it really depends on how secure the SSIS package and how secure the source control repository is. For one application I deleted the source code since, like this application, it was for access security and not data security and was one way encryption. Note that SSIS can be used to run a Windows program and return a value which would be the most secure method since examining the SSIS code would not expose the method used to provide the clear password.
May 29, 2018 at 12:59 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply