2 connection strings in database unit testing

If you’ve had a chance to play with database unit testing in Team Edition for Database Professionals, you have probably noticed in the below Database Test Configuration dialog that we allow you to specify up to 2 separate connection strings for use in running your database unit tests.

 

 

 

The first connection string is the execution connection string. This connection string is used to execute the test script of your db unit test. This connection string should have the same credentials that you would expect your users to have. This is important for ensuring that the appropriate permissions have been applied in your database.

 

We also optionally support a second validation connection string that is expected to be of higher privileges. This is so that when you are doing validation in your unit tests, you have full access to your database. A common scenario here is when you are testing, for example, an AddEmployee stored procedure. In many organizations the average user is expected to perform CRUD operations using stored procedures and doesn’t have access to the underlying tables. Such stored procedures invocations should occur with those appropriately restricted privileges. However, it would be useful to do verification using a higher privileged account to verify that a row was in fact added to the Employees table.

 

To do this, you would do your verification in your post-test script, which uses the validation connection string. In addition, the pre-test script uses this connection as well. Similarly the test initialize\cleanup scripts use the same connection.

 

If you choose to use the automatic deployment and data generation capabilities, the higher privileged connection will be used as these operations typically require a greater level of privileges than your typical user.

 

This only works if you are using SQL Server authentication. Windows Authentication would result in essentially the same credentials being used for both connections. In a later post I’ll show you how you can perform impersonation to test using another user’s Windows Authentication credentials.

 

Of course the validation connection string is optional. If you don’t specify a validation connection string, the execution connection string will be used in all cases.

 

Sachin Rekhi