You may come to a situation when you need to update a password or some other kind of sensitive information in the
SSISDB catalog environment variables. Eg. password has changed etc. The easiest way to ensure that you change the password in all variables/environments would be listing all variables in the
SSISDB catalog that have the original value.
SSISDB provides view [catalog].[environment_variables] where you can list all variables and their values. Unfortunately for values which
Sensitive flag is set to True, the value is
NULL. So this view will not help you.
In case you are
db_owner in the
SSISDB catalog, you can list the content of the
[internal].[environment_variables] table. This table has two columns
[value] for normal values and
[sensitive_value] for variables which
Sensitive flag is set to True. Unfortunately the content of the
[sensitive_value] is encrypted.
The content of the sensitive_value is encrypted by symmetric key, which is protected by certificate. For each environment in
SSISDB there is generated an unique pair of symmetric key and certificate, which secures the content of sensitive variables.
As can be seen on the above image, the symmetric keys are named “
ID of the Environment. Similarly the Certificates are named “
ID of the Environment.
As mentioned above. The symmetric key used to encrypt sensitive content is protected by corresponding certificate and each certificate in the
SSIDB is protected by the database master key. In case you are
db_owner of the
SSISDB database, you have
CONTROL permission on the Certificate and you can utilize it to decrypt the symmetric key and utilize the symmetric key to decrypt the content of sensitive variables.
Decryption of Sensitive values
To decrypt the sensitive values we can use DECRYPTBYKEY function. But to use that we need to call OPEN SYMMETRIC KEY for each unique symmetric key as the sensitive variables in different environments are protected by different symmetric keys.
The easiest way to decrypt the sensitive values is to utilize DECRYPTBYKEYAUTOCERT. This allows automatic decryption of encrypted data by providing only ID of the certificate which protects the symmetric key.
Thanks to that we can easily write:
SELECT [variable_id] ,[environment_id] ,[name] ,[type] ,[sensitive] ,[value] ,[sensitive_value] ,DECRYPTBYKEYAUTOCERT(CERT_ID(N'MS_Cert_Env_' + CONVERT(nvarchar(20), [environment_id])), NULL, [sensitive_value]) AS DecryptedValue ,[base_data_type] FROM [SSISDB].[internal].[environment_variables]
With the above we can easily decrypt the
[sensitive_value], however the decrypted content is not directly readable as the decrypted value is returned as binary data. To read the content we need to cast the binary data to appropriate data type. Normally we would need to case the content by the variable type, but
SSISDB already has a function to get the content by its type. There is internal function in the catalog for that purposes:
[internal].[get_value_by_data_type]. This simplifies revealing of the content.
SELECT [variable_id] ,[environment_id] ,[name] ,[type] ,[sensitive] ,[value] ,[sensitive_value] ,[internal].[get_value_by_data_type](DECRYPTBYKEYAUTOCERT(CERT_ID(N'MS_Cert_Env_' + CONVERT(nvarchar(20), [environment_id])), NULL, [sensitive_value]), [type]) AS DecryptedValue ,[base_data_type] FROM [SSISDB].[internal].[environment_variables]
To simplify all the process you can utilize a sp_SSISListEnvironment stored procedure I have published on GitHub in my SQL Scripts repository.
The stored procedure allows listing environment variables and their values from multiple folders/environments and allows easy filtering based on folder/environment/variable names by utilizing wildcards as well it allows searching variables by the content (values).
By a simple call of the stored procedure we can easily receive similar results as above.
[sp_SSISListEnvironment] @folder = '%' ,@decryptSensitive = 1
For help and detailed usage you can invoke
sp_SSISListEnvironment without any parameters.
The scripts grants execution of the stored procedure to the
ssis_admin database role, so all
ssis_admin members are able to decrypt the sensitive content. You should modify the
GRANT in the script to meet your security requirements.