Running NUnit integration tests with different credentials

Question

I am running integration tests with C#, NUnit, and SQL Server 2008 r2 dev edition database. Setting up my fixture includes creating a new database and loading test data, so I need dbo privileges for that.

However, I want to run the tests themselves with less privileges. I have another AD account I can authenticate with, and I do can run some T-SQL using impersonation as described here: http://support.microsoft.com/?scid=306158, as follows:

public static bool ExecuteFileAs(string fileName, string connectionString, 
                                  string user, string domain, string password)
{
    using(new Impersonator(user, domain, password))
    {
        using(var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            return SqlFileExecuter.RunSql(connection, fileName);
        }
    }
}

When I hit the breakpoint inside this code snippet, and start the Profiler, I see another connection open with the username I submitted to it, so impersonation really works. Unfortunately, I cannot run all the tests impersonating at the end of fixture set up, and ending it at fixture tear down. At the end of set up I execute the following:

        impersonator = new Impersonator("username", "DOMAIN", "pwd");

As soon as the first unit test begins, I am getting this error listing one of the dlls used in this test: System.IO.FileLoadException: Could not load file or assembly '...' or one of its dependencies. Access is denied. I have granted that other account full access to the directory with all my binaries, which did not help.

Any suggestions are welcome.

Edit: my workstation is still running XP.

Answer 1

One possible solution is to not use Windows authentication, use a SQL user instead. This would just be a change of connection strings.

Additionally, if the error is related to IO permissions, you can try doing all the IO before you impersonate the user. Using http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx instead of SqlFileExecuter you can directly execute blocks of T-SQL against a SQL Server (unlike ADO.NET). SMO is slightly different than how you're doing it above...

var s = new Server(connectionString);
s.ConnectionContext.ExecuteNonQuery(@"CREATE DATABASE test; GO; Use test; ...");

Answer 2

Try to check if nothing else is locking same file without share-read access. Also enable bind error logging. Type fusion in win7 start menu and run it as admin so that settings are not grayed out. Then set 'log bind failures to disk' and specify location for the log file. If You don't get any logs check in registry that HKLM\Software\Microsoft\Fusion\ForceLog = 1