5

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.

SteveC
  • 15,808
  • 23
  • 102
  • 173
Arne Lund
  • 2,366
  • 3
  • 26
  • 39
  • 1
    Is there a reason you cannot use SQL authentication instead of a trusted connection? This would just require you changing the connection string for this part of the test. Additionally, can you load the SQL data into memory before using impersonation so you don't have to access the disk for the sql executor? – Travis May 19 '11 at 18:30
  • 1
    Travis, my production server does not used mixed mode authenication at this time, but I think your suggestion is going to work. Can you post your comment as an answer, so that I can accept it? Thanks. – Arne Lund May 19 '11 at 19:20
  • Did you get any solution using Windows authentication? The answer marked is about use a SQL – Kiquenet Dec 14 '16 at 15:31

2 Answers2

3

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; ...");
Travis
  • 10,444
  • 2
  • 28
  • 48
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

Piotr Perak
  • 10,718
  • 9
  • 49
  • 86