Running as a windows user in Visual Studio Team Services

Question

I need to build a Visual Studio project in Team Services and then run some tests. The tests make REST API calls and then verify that the REST API calls worked by directly accessing the database. The database is a Microsoft SQL Server database that only allows for Windows Authenticated users to access it. It is running on an in-house server. So it works fine when I run the build on my machine locally, but in Team Services, the tests fail because they are not able to access the database. I'm wondering if it possible to run the tests as a user or to somehow work around this problem.

Any suggestions are welcome, but please note that I absolutely need to be able to get direct access to the database to verify the API's.

Answer 1

Building project through VSTS build, it uses vsts agent account (could be specified when setup vsts agent).

First, if you are using your vsts agent to build and test and there is the windows account in that machine could connect to SQL Server and read data, you could impersonate user to access data from SQL Server.

Simple Code:

public static class Helper
{
    [DllImport("advapi32.dll", SetLastError = true)]
    public static extern bool LogonUser(
        string lpszUsername,
        string lpszDomain,
        string lpszPassword,
        int dwLogonType,
        int dwLogonProvider,
        out IntPtr phToken);
}

--

IntPtr userToken = IntPtr.Zero;

        bool success = Helper.LogonUser(
          "[user name]",
          "[domain]",
          "[password]",
          2, //2
          0, //0
          out userToken);

        if (!success)
        {
            Assert.Fail("Logon user failed");

        }

        using (WindowsIdentity.Impersonate(userToken))
        {
          //TODO connect to your sql server database 

}

Secondly, if you are using Hosted Agent, the only way is that you need to use SQL Server account to access SQL Server database, also make sure, your SQL Server instance could be accessible from Hosted agent.