Régis Baccaro

Automating database tests with Visual Studio and Team Foundation Server

October 31, 2014 by

This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.

Post 1: Continuous Integration with SSDT and TFS

Post 2: Unit testing with SQL Server Data Tools

You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user:

  1. Unit tests
  2. Coded UI Tests
  3. Web performance tests
  4. Load tests
  5. Generic tests

These tests are often called smoke tests or build verification tests (BVT).

Software requirements

You will need Visual Studio 2010, 2012 or 2013 and at least Professional edition to be able to run unit tests. SQL Server Data tools integrated shell is not enough.

Configure and run scheduled tests after successfully building your Application

When creating Build Verification Tests you need to use Visual Studio, Version control and Team foundation Build (msbuild). First of all you need to write your tests, check them in with Version control. In the build definition file you can then choose which test to execute. You can then queue a build or use continuous integration to run the tests upon a successful build.

The steps to achieve that are simple:

  • Define and check in a Build Definition Test
  • Create a build definition
  • Add the computer to TestBuildController user group
  • Setup the build agent
  • Run the BVT build definition

Define and check in a Build Definition Test

For the purpose of this post we will be using the same project than in the previous posts. It is attached here

If you remember from previous post, I created a simple unit test to check if my continuous deployment script had worked correctly:

Simple test script
Figure 1 Very simple test script

Now I want this script to run as part of my check-in process after the database has been deployed to my integration instance.

Create a build definition

In the Build menu from the home page of Team Explorer click New Build definition:

You can also use the one from the previous blog post included in the project and edit it to suit your needs

Give a name and a description to your build. On the Trigger menu choose Continuous integration:

Continuous Integration
Figure 3 Output folder for the builds

Expand the Process menu to show all the details. In the Items to build menu make sure that all your projects or the solution are selected.

Under Basic -> Automated Tests -> Test Source click the ellipsis (…) and make sure that you’re test assembly is listed there. By default it specifies a pattern you can use for catching all dlls having “test” in their name. This search string will search recursively through directories to find all dlls matching the pattern in the binary directories.

Test settings in build definition
Figure 4 Test settings in build definition

So now when you check in, as part of the build process, the test will be executed.

The only problem remaining now is that the user executing the test will be your machine name in the domain, and it probably doesn’t have read permissions to whatever database you are going to run the test on. Resulting in the test result below:

Partially successful test
Figure 5: Partially successful test

Permissions required to perform unit tests in SQL Server Data Tools:

You must have the following permissions to perform unit tests on a database:


Execution context


Privileged context


Execute a test


Execute a pre-test or post-test


Run TestInitialize and TestCleanup scripts


Deploy database changes before you run tests   


Generate data before you run tests


As you can see there are two types of context database connection: Execution Context and Privileged Context. Where the privileged context connection requires more permissions due to the tasks performed.

Modifying Context permissions

To modify context permissions you need to right click on your test project and choose SQL Server Test configuration:

Modifying permissions context
Figure 6 Modifying permissions context

You will see a dialog where you can specify or create a connection to a database and a few other options, like deploying the database before you run the test. Which in our case is not necessary since we are already doing that as part of a continuous integration project.

SQL Server Test Configuration

1 is the Execution context and 2 if you specify it will be the Privileged Context (for running pre or post build scripts and/or deploying the database before running the tests).


If you use integrated authentication in your execution context it will use the identity of the user running the build controller, in my case it is my machine name on the domain. I probably don’t want to give access to the continuous integration database to machine user.

If you use a SQL Server user like I do in my example, its password will not be saved and you will end having to edit the app.config file of the test project to add the user password. This is one way to do it. But you probably either don’t want to user SQL Server users (remember those best practices) and you definitely don’t want to have its password in clear text in the app.config of your project!!

Using integrated security with unit tests

Your tests will always be run by the controller. In my case it has the same name than my machine name and it is running under NT AUTHORITY \ NETWORK SERVICE. It is a Windows Service running with configurable identity so changing it is simple.

Start the Team Foundation Server Administration Console and choose build configuration in the left pane:

Configuring the build agent service user
Figure 7: Configuring the build agent service user

Here you can see what users it is running as.

Click on properties to change it. Beware that you will need to restart the service in order to do that.

Pick a domain user who has access to your database.

By the way it is not necessary for the build agent to run as the same user than the one connection to Team Foundation Server. But I find it easier. If you want them to run under 2 different users just remove the check-mark when altering the user:

Changing the service agent identity
Figure 8 Changing the service agent identity

Now try to run your test again and it will run under this user. And succeed: D

Build succeeded

And showing that the test itself has passed!!

Test passed
Figure 9: Test results are green!

In the next post I will explain one technique for deploying several databases automatically under the build process. Obviously it is not possible with only one line of command arguments for the msbuild agent.

We will need to modify the deployment XAML file or use post build scripts.

Meanwhile happy testing!

Régis Baccaro
Continuous integration (CI), Data Tools (SSDT), Testing

About Régis Baccaro

Régis is a Principal Consultant at Rehfeld a/s working with databases and administrations since 1999. He has been working with Business Intelligence since SQL Server 2000 and with SharePoint since 2003. He specializes in mentoring, performance tuning and scalable architecture of datawarehouses and bi-solutions. His background and work experience mixes technology and business, providing him a unique edge to contribute to datawarehouse and SharePoint projects, being able to effectively interact with technical and business decision makers. Régis is a frequent speaker at SQL Server conferences and is a board member of the Danish SQL Server User Group and the founder of the SQL Saturday Denmark community event. In april 2014 Régis received the MVP award for SQL Server for his involvment in the product and the community. View all posts by Régis Baccaro