AWS Secrets Manager is a widely used secrets and key management solution offered by Amazon. By leveraging its security features, you could store sensitive SSIS connection information in the secrets manager and make such information available for runtime job execution. To be more specific, ETL processes often involve using sensitive connection information in SSIS packages in order to make connections or facilitate service calls. Such sensitive information includes user passwords, client secrets, API tokens, etc., and in most cases, you would want this information to be securely saved.
In our SSIS development practice, you can use the SSIS package's ProtectionLevel setting to safeguard such information. When the ETL packages are ready for unattended job executions, you would need to deploy the packages to a runtime integration server. If your organization adheres to high-security standards, you may be asked to store such sensitive information in a central location for enhanced security and centralized management.
With that being said, AWS Secrets Manager by Amazon can be the perfect solution for such a credential storage repository, allowing you to read and create key secrets in your AWS instance that can later be used in your integration job execution. In a nutshell, leveraging this method for your ETL connection configuration offers several benefits, including:
- A centralized repository for sensitive credentials
- Rotate the secrets or keys as necessary using AWS provided mechanisms
- Lower chances of secrets/keys being leaked
- Eliminate the need to store such sensitive data within the code level
- Re-use encrypted connection properties throughout your package or process
In this blog post, we will show you how you can use KingswaySoft components to connect with AWS Secrets Manager and retrieve sensitive connection information. Then feed the information via Dynamics 365 CDS/CRM Connection Manager through parameterization in order to achieve a secure runtime connection. The same practice can be used for various other services as necessary. We will be using the following KingswaySoft components:
- AWS Secrets Manager Connection Manager
- AWS Secrets Manager Task
- Dynamics 365 CRM connection manager and its corresponding components (This is an illustrative example for this blog post; you can benefit from using the same strategy for any other connection that might have sensitive credentials involved)
AWS Secrets Setup
The first thing to do is to create a Secret within AWS. For this, log into the AWS instance using your tenant. From there, search for Secrets Manager and click on it. If you do not find it as shown below, click on "All Services" and you should find it there.
On this page, click the "Store a new secret" button to create your own Secret, and provide the required information.
This will open up a window to choose the type of secret. For our current case, we need to store a Client Secret value for the Dynamics 365 CE/CRM Service. Therfore, we choose "Other type of secret", and switch to plain text. Note that if you have a service that requires a key value, you can leave it as default.
Once done, the platform will take you through the next steps, in which you can add a name to your secret. This should be a recognizable name, as this is what you would use in the AWS Secrets Manager Task discussed below in order to retrieve the secret value.
Now, on the next page, you may choose to enable the rotation of your secret. Rotation is the process of periodically updating a secret. When you rotate a secret, you update the credentials in both the secret and the database or service. In Secrets Manager, you can set up automatic rotation for your secrets if necessary, and if the service you work with mandates it. To rotate a secret, Secrets Manager calls a Lambda function according to the schedule you set up. You can set a schedule to rotate after a period of time, for example, every 15 days, or you can create a cron expression. Note that, if any rotation step fails, Secrets Manager retries the entire rotation process multiple times.
In our case, we have chosen not to have rotation enabled. Once all necessary information has been provided, store the secret. Next, we move on to the SSIS package to use the above-created secret in our integration.
ETL Process Design
In your SSIS package, the first step would be to create an AWS Secrets Manager connection manager. For this, you can enter your authentication details, as shown below. For information on how to configure the connection manager for AWS Secrets Manager, please refer to our Online Help Manual.
Then, in the Control Flow, add the AWS Secrets Manager Task from the SSIS toolbox, then open it, and choose the connection manager that has just been created. The AWS Secrets Manager Task supports multiple types of actions. For our use case, we will be using the "Get Secret" task. The remaining actions, "Create Secret", "Delete Secret", "Restore Secret" and "Update Secret," can be utilized to set or delete secrets within the AWS Secrets Manager, which are not the focus of this blog post, but they can be useful in some other integration scenarios. After selecting the "Get Secret" action, click on the drop-down for Secret Name and choose the secret that you stored in the Secrets Manager from the list.
Once done, this can now be stored as an SSIS variable. Please note that in the list, you will see both variables and project parameters, and you could use either depending on the use case. However, since our case involves setting the secret dynamically at runtime for the connection manager, we choose a variable, as shown below.
With the above, the task will retrieve the secret value from the AWS Secrets Manager and store it in the @[User::SecretValue] variable during runtime. The next step is to parameterize the D365 CRM Connection Manager. To achieve this, we work with the ConnectionString and the Client Secret property separately. We have a blog post that provides detailed information on how to parameterize the connection manager; please find the link here. When parameterizing, the same variable @[User::SecretValue] can be assigned to the ClientSecret property.
Now, it is time to finish up the design. The AWS Secrets Manager Task can be connected in series using precedence constraints to any data flow that uses the D365 CRM components.
When the package is executed, the AWS Secrets Manager task will retrieve the secret "ClientSecret" from the AWS Secrets and assign it to the variable @[User::SecretValue] at runtime. This variable is used in the connection parameterization, thereby authenticating the Dataverse/CRM connection manager when it starts its execution. Once the package execution is complete, the variable value will be flushed out, and the secret value is not stored anywhere in the code/design.
Conclusion
By using such a design, you can ensure that your secrets and keys are stored securely and centrally within AWS Secrets Manager, and won't be leaked without proper authentication. You can also use this to reuse and parameterize connection managers and eliminate the need to store sensitive credentials within the package or your infrastructure.
We hope this has been helpful!