Understanding VSTS-Build for SQL Server Projects

In this article, we will explore the issues that developers face when integrating their SQL server projects with Visual Studio Team Services (VSTS) and how to overcome them.

Introduction to SQL Server Projects in VSTS

When building a SQL server project in Visual Studio, it’s not uncommon for developers to encounter challenges integrating it with Visual Studio Team Services (VSTS). In this article, we will delve into the specific issue of VSTS-Build not working for SQL server projects and provide solutions to resolve this problem.

The Problem: SQL71589 Error

When attempting to build a SQL server project in Visual Studio, developers may encounter the following error:

SQL71589: Master Key must be created before a database scoped credential.

This error message indicates that the VSTS-Build process requires the creation of a master key before it can proceed with building the SQL server project.

Understanding Database Scoped Credentials

To understand this error, let’s first explore what database scoped credentials are. In VSTS, a database scoped credential is used to securely authenticate users and connections to databases. It provides an additional layer of security by separating user credentials from connection strings.

When creating a new SQL server project in Visual Studio, the VSTS-Build process creates a default database scoped credential. However, this credential may not be suitable for all scenarios. In some cases, developers may need to create custom database scoped credentials with specific identities and secret values.

Resolving the SQL71589 Error

To resolve the SQL71589 error, you will need to create a master key and a database scoped credential using T-SQL scripts. Here’s an example of how to do this:

Creating a Master Key

-- Create a db master key if one does not already exist, using your own password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD='&lt;EnterStrongPasswordHere&gt;';

Replace <EnterStrongPasswordHere> with a strong password of your choice.

Creating a Database Scoped Credential

-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL AppCred WITH IDENTITY = 'dbUserNameFooBar',   
    SECRET = '&lt;EnterStrongPasswordHere&gt;';

Replace <EnterStrongPasswordHere> with the secret value you want to use for your credential. Also, update dbUserNameFooBar with the desired identity name.

Using Environment Variables in VSTS-Build

To make this process easier and more secure, you can store sensitive information such as database credentials and master keys using environment variables in your VSTS project. Here’s how:

In your TFS project, go to Settings > Service connections.
Click on the “+” icon next to “Environment Variables” and add a new variable named DB_MASTER_KEY_PASSWORD with the value of your master key password.
Add another environment variable named DB_CREDENTIAL_SECRET with the value of your database scoped credential secret.

Once you’ve created these environment variables, you can reference them in your T-SQL script using the following syntax:

CREATE MASTER KEY ENCRYPTION BY PASSWORD=ENV('DB_MASTER_KEY_PASSWORD');

CREATE DATABASE SCOPED CREDENTIAL AppCred WITH IDENTITY = 'dbUserNameFooBar',   
    SECRET = ENV('DB_CREDENTIAL_SECRET');

Conclusion

In this article, we’ve explored the challenges of integrating SQL server projects with VSTS and provided a solution to resolve the SQL71589 error. By creating master keys and database scoped credentials using T-SQL scripts and storing sensitive information in environment variables, developers can overcome common integration issues and build secure SQL server projects.

Step-by-Step Solution

To implement this solution, follow these steps:

Create a master key: Run the following command to create a new master key in your SQL server database.

– Create a db master key if one does not already exist, using your own password. CREATE MASTER KEY ENCRYPTION BY PASSWORD=’<EnterStrongPasswordHere>';

2.  **Create a database scoped credential**: Run the following command to create a new database scoped credential in your SQL server database.
    ```sql
-- Create a database scoped credential.
CREATE DATABASE SCOPED CREDENTIAL AppCred WITH IDENTITY = 'dbUserNameFooBar',   
    SECRET = '&lt;EnterStrongPasswordHere&gt;';

Store sensitive information: In your TFS project, create new environment variables named DB_MASTER_KEY_PASSWORD and DB_CREDENTIAL_SECRET to store the values of your master key password and database scoped credential secret.
Reference environment variables in T-SQL script: Update your T-SQL script to reference the environment variables for creating a master key and database scoped credential.

Advanced Troubleshooting

In some cases, you may encounter issues integrating your SQL server project with VSTS even after implementing the above solution. To troubleshoot these issues further, follow these steps:

Check VSTS-Build logs: Review the build logs in VSTS to identify any errors or warnings that might be related to the integration issue.
Verify database credentials: Double-check your database credentials and master key password to ensure they are correct and securely stored in environment variables.
Test standalone connection: Create a new connection string using the DB_CREDENTIAL_SECRET environment variable to test the standalone connection to your SQL server database.

By following these steps, you should be able to resolve integration issues between VSTS-Build and your SQL server project.

Last modified on 2024-03-19