A recurring theme over the past several weeks (there are always recurring themes it seems) has been an issue that relates to SSIS. Clients will call for help with an SSIS package that has started failing and they are stuck trying to figure it out. They are all stuck at the same spot in the process – trying to get the package open. As it turns out somebody has decided to password protect the package in each case and the client is unable to open the package because they don’t know the password.
Inevitably they are all stuck at the following screen:
Granted this screenshot is for the password prompt for a project password – it really is the same issue if it is a package password. This seems like pretty good security right? If nobody knows the password then nobody can alter the package, right? At least that seems to be the prevailing course of thought.
Of course this introduces various other issues, one of which I alluded to already – a failing package. What if the requirements change? What if the package needs to be migrated? What if the package needs to be documented from stem to stern? Even better -what if this type of security is not all it is purported to be?
SSIS Security
Microsoft has provided various different “protection” levels for affecting sensitive information within a package. These levels can be set from within the GUI or from the dtutil utility. Here are some of those settings.
| Setting | Description | Value |
| ServerStorage | Rely on SQL Server database roles for protection. Only valid if saved to msdb and not to the file system. | |
| DontSaveSensitive | Suppresses the values of sensitive properties in the package when the package is saved. | 0 |
| EncryptSensitiveWithUserKey | Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. | 1 |
| EncryptSensitiveWithPassword | Uses DPAPI to to encrypt sensitive values in the package based on a user generated password. | 2 |
| EncryptAllWithPassword | Encrypt the entire password based on a user generated password. | 3 |
| EncryptAllWithUserKey | Uses a key based on current user profile to encrypt the package. Only the user that encrypted the package can open or run the package. | 4 |
Based on these descriptions, a look at the package contents (via notepad) would look something like this:
The package will have that ProtectionLevel value added to the xml of the package with the selected value based on the descriptions from the previous table. In the case of this image, it appears that the package in question has been set to “EncryptSensitiveWithPassword” which correlates to a value of 2. This is also what is causing that password prompt to be displayed when opening the package.
So What’s that SSIS Password?
First, a bit of a birdwalk. Some will say you don’t need that password. They say you can go 
ahead and just change the “ProtectionLevel” value in the project/package xml and all will be rosy good. That may or may not work. Try it at your own risk. That said, if your ProtectionLevel is at a value of 2, there is an alternative method you could retrieve that password. I am going to show you just how to do that.
This method is going to need to assume that the package is being executed via a SQL Agent job and that you also have access to view job properties on the server in question.
Let’s just jump straight to a script to help us with that.
DECLARE @jobname VARCHAR(128) = NULL
, @stepname VARCHAR(128) = NULL;
SELECT sj.name AS JobName
, sjs.step_name
, sjs.subsystem
, sjs.command
, LTRIM(RTRIM(SUBSTRING(sjs.command,
CHARINDEX('/DECRYPT', sjs.command, 1) + 9,
CHARINDEX('/', sjs.command,
CHARINDEX('/DECRYPT', sjs.command, 1)
+ 1) - CHARINDEX('/DECRYPT',
sjs.command, 1)
- 9))) AS PkgPassword
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE sjs.subsystem = 'SSIS'
AND sjs.command LIKE '%DECRYPT%'
AND sj.name = ISNULL(@jobname, sj.name)
AND sjs.step_name = ISNULL(@stepname, sjs.step_name);Take note here that I am only querying the msdb database. There is nothing exceedingly top secret here – yet. Most DBAs should be extremely familiar with these tables and functions that I am using here.
What does this show me though? If I have a package that is being run via Agent Job in msdb, then the sensitive information needs to be decrypted somehow. So, in order to do that decryption the password needs to be passed to the package. As it turns out, the password will be stored in the msdb database following the “DECRYPT” switch for the dtutil utility. Since I happen to have a few of these packages already available, when I run this particular query, I will see something like the following in my results.
Now, let’s go ahead and take one of those packages and test this out. I will just take the password that was listed for one of the packages, find the path of that package and then try to open the package using the password I just uncovered. Let’s keep it simple and say I am trying to open the first package called “Sports Adobe Ticket Sales Data Upload”.
After finding the package in question, I enter the password – shown here.
After I click the “OK” button, I am greeted with the following.
Tada! I now have full access to the package fully decrypted. If I need to change the ProtectionLevel, I can easily do that now as well. From here, I am set to proceed with troubleshooting the failure or any of the other reasons I noted for needing to gain access to the package.
Conclusion
Occasionally it becomes necessary to access an SSIS package that may be encrypted with a password. This is easily accomplished (unfortunately) if the package happens to be run via a job in SQL Server. While it is possible to gain access to the package, it would be far better to not need to circumvent the security to gain access. Instead, it would seem a much better idea to properly document the necessary passwords etc in a common vault so the appropriate people could access the package in the event of an emergency.
