We released the simple and flexible COPY statement in preview to help data engineers onboard and build their data pipelines. The COPY statement requires users to specify credentials for authentication against external storage accounts where these credentials typically differ across database environments such as development, testing, and production. Building and deploying data pipelines securely with COPY credentials using a stable continuous integration and deployment (CI/CD) process has never been easier with the integration of Visual Studio SQL Server Data Tools (SSDT), Azure DevOps and Azure Key Vault.
This blog outlines how to build a basic stored procedure with the COPY statement in SSDT and deploy the stored procedure in a CI/CD pipeline without storing any credentials within your database project.
1. Set up your CI/CD pipeline with SSDT and add a Stored Procedure containing your COPY statement:
In this example, the stored procedure uses dynamic SQL to retrieve and load the time of ingestion in a table column. The current session user is also specified in the COPY LABEL hint for monitoring purposes. Sqlcmd variables are used to avoid storing credentials in plain text in the stored procedure definition within SSDT.
2. Add a task in your Azure DevOps pipeline to extract secrets from Azure Key Vault as pipeline variables. These pipeline variables can be used as sqlcmd variables in the deployment task.
In this example, the pipeline pulls the $(DataLakeSecret) secret from Azure Key Vault and passes the secret to the DataLakeSecret sqlcmd variable for the deployment – this is the SAS token within your COPY statement.
For each successful check-in, COPY credentials in your stored procedure will now be dynamically applied and updated securely from Azure Key Vault based on the target environment within your CI/CD pipeline.
The flexibility of using SSDT, Azure DevOps, and Azure Key vault enables you to extend this process to:
- Storage locations paths if you load from different accounts in each of your environments
- External table details such as your Master key, Database scoped credential, and External data source
- Database connection string details (usename, password, etc) and avoid having them in plain text within your Azure DevOps pipeline
- Database users and logins set up via SSDT pre-deployment scripts where passwords are stored in Azure Key vault for different database environments
