Environment

Revealing content of encrypted SSISDB Environment variables – sp_SSISListEnvironment

Pavel Pawlowski SSIS, T-SQL, Uncategorized , , , , , , , ,

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.

Revealing sensitive_value

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 “MS_Enckey_Env_” + ID of the Environment. Similarly the Certificates are named “MS_Cert_Env_” + 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]

Result:
SSISDBDecrypted

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]

SSISDBDecryptedRevealed

sp_SSISListEnvironment

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

Produces:

sp_SSISListEnvironment

 

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.

Advertisement

Clone / Copy SSIS Server Variables Among Environments – sp_SSISCloneEnvironment on GitHub

Pavel Pawlowski SQL Server, SSIS, T-SQL, Uncategorized , , , , ,

You have SSIS projects deployed in the SSISDB catalog. You are using environments and environment variables for passing parameter values to SSIS Executables. This is a very common scenario.

You come to a situation that you need transfer the SSIS projects, environments and varaibles among different SSIS environments (DEV/TEST/QA/PROD). You already have bunch of environment variables defined in one of your environment and you need to transfer those to other environment.

Unfortunately there is no easy way to clone, copy or script the varaibles to transfer them to different environment. If you do not script them during creation in the SSMS, there is no option in SSMS to script them out. If you do not want to type all the varaibles manually again, you have to script those in some way.

Anyway the proper scripting or clonning is not completely trivial in case sensitive varaible values are being used as SSISDB uses multiple symmetric keys and certificates for the encryption and each Environment in the SSISDB has its own key and corresponding certificate. When cloning such sensitive value, the value needs to be decrypted with the source environment symmetric key and re-encrypted by the destination environment symmetric key.

sp_SSISCloneEnvironment

sp_SSISCloneEnvironment is a custom stored procedure which takes care about cloning the SSSI server variables among different environments in the same SSISDB catalog as well as it allows generate a script, which can be re-applied on even oon other SSIS Instance.

The stored procedure takes care about sensitive values re-encryption by proper certificate when clonning. As mentioned above, SSIS generates a separate symmetric key and certificate for each environment. To ensure proper future functionality, the stored procedure takes care about decryption of the sensitive variables when reading from current environment and re-encryption by proper destination environment symmetric key.

The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys. Only in areas where it needs to create a folder or environment it switches the context to the caller of the stored procedure as the procedures which are taking care abut the folder and environment creation must be executed under windows credentials.

The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.

Be carefull, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.

The complete source code for the sp_SSISCloneEnvironment is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneEnvironment
   @sourceFolder                = 'Source Folder Name'
  ,@sourceEnvironment           = 'Source Environment Name'
  ,@destinationFolder           = 'Destination Folder Name'
  ,@destinationEnvironment      = 'Destination Environment Name'
  ,@autoCreate                  = 1 --Auto Create Destination
  ,@printScript                 = 1 --Specifies that Create Script should be output instead of actual execution
  ,@decryptSensitiveInScript    = 1 --Specifies that the sensitive values should be decrypted in the create script

Currently when cloning the environment variables the destination environment must be empty if script is not being generated and the procedure is running in the non-interactive mode.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and environment. By simple modification of those variables the script will generate the variables different environments.

Parameters

The procedure has several parameters where some are required and some optional

The core parameters are @sourceFolder, @sourceEnvironment and @destinationFolder. The @destinationEnvornment, @autoCreate, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source environment is located

@sourceEnvironment

Specifies the name of the source environment from within source folder to be scripted

@destinationFolder

Name of the destination folder to wich the environment varaibles should be clonned.

@destinationEnvironment

Name of the destination environment to which the varaibles should be conned. This parameter is optional and if not provided, then the name of the source environment is used.

@autoCreate

Specifies whether the destination foolde and destination environment should be automatically crated if they do not exist. If the script is being generated then a statement which checks existence of the folder and environment are included in the script and missing folder and/or environment is automatically created if needed by the script.

Default value is 1 = true

@printScript

Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.

Default value = 0, this means that the script executes within the SSISDB catalog and automatically clnes the variables

@decryptSensitiveInScript

Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, howver they are marked sensitive and will be encrypted after applying the script.

If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values priorexecution of the script.

Sample Output

Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1

RAISERROR(N'sp_SSISCloneEnvironment v0.20 (2016-10-11) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'===================================================================', 0, 0) WITH NOWAIT;

DECLARE @destinationFolder nvarchar(128) = N'TEST' --Specify Destination Folder Name
DECLARE @destinationEnvironment nvarchar(128) = N'ClonedEnvironment' --Specify Destination Environment Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
    RAISERROR(N'Creating missing Folder [%s]', 0, 0, @destinationFolder) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[create_folder] @folder_name = @destinationFolder
END

--Checking for destination environment existence
IF NOT EXISTS(
    SELECT 1
    FROM [SSISDB].[catalog].[environments] e
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE f.[name] = @destinationFolder AND e.[name] = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Creating missing Environment [%s]\[%s]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT
    EXEC [SSISDB].[catalog].[create_environment] @folder_name = @destinationFolder, @environment_name = @destinationEnvironment, @environment_description = N''
END

--Checking for variables existence in destination eivnironment
IF EXISTS (
    SELECT 1
    FROM [SSISDB].[catalog].[environment_variables] ev
    INNER JOIN [SSISDB].[catalog].[environments] e ON e.environment_id = ev.environment_id
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE
        f.name = @destinationFolder AND e.name = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Destination Environment [%s]\[%s] is not empty. Clear all variables prior clonning environment.', 15, 2, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
    RETURN;
END

--Environment variables creation
DECLARE @var sql_variant

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V1]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bit, N'1');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V1', @data_type=N'Boolean', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V10]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is string parameters');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V10', @data_type=N'String', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V11]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is sensitive string'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V11', @data_type=N'String', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V12]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2010-12-31T00:00:00'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V12', @data_type=N'DateTime', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V13]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'99.990000000000000000'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V13', @data_type=N'Decimal', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V14]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'56'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V14', @data_type=N'Int32', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V2]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(tinyint, N'5');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V2', @data_type=N'Byte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V3]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2016-01-01T00:00:00');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V3', @data_type=N'DateTime', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V4]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'12.390000000000000000');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V4', @data_type=N'Decimal', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V5]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'156.987');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V5', @data_type=N'Double', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V6]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'123456');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V6', @data_type=N'Int32', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V7]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bigint, N'987654321');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V7', @data_type=N'Int64', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V8]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(smallint, N'9');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V8', @data_type=N'SByte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V9]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'56');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V9', @data_type=N'Single', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''