[SOLVED] Executing Sql with Integrated Security via TeamCity fails at user login

Executing Sql with Integrated Security via TeamCity fails at user login

I have an MSBuild script which uses the MSBuildExtensions SqlExecute command similar to the following:

<Target Name="DoSqlStuff">
    <SqlExecute TaskAction="Execute"
                UseTransaction="True"
                ConnectionString="Data Source=$(Sql_ServerName); Initial Catalog=$(Sql_DatabaseName); Integrated Security=SSPI;"
                Files="@(sqlFiles)"
                CommandTimeout="1800">
    </SqlExecute>
</Target>

If I run this from the command line (with all the correct variables and whatnot) then it works just fine.

I have a TeamCity build agent set up to be the same machine that can successfully run this MSBuild script (with all the same variables entered) from the command line, and I get the following error:

[MSBuild output] C:\BuildAgent\work\c5affefc453a825c\ContinuousBuild\Deploy.proj(22,3): error : SqlException: Login failed for user 'ALLIANCE\DANIELM$'. [C:\BuildAgent\work\c5affefc453a825c\ContinuousBuild\Deploy.proj.teamcity]

I added some debug output to this task:

<Message Text="Current user is $(USERNAME)" />
<Message Text="Current domain is $(USERDOMAIN)" />

When run via cmd, the user name is danielm

When run via TeamCity, the user name is danielm$

The domain is the same for both.

What am I doing wrong, or what next steps do I need to perform to get more info?

Solution

After an embarrassing amount of time, I figured this one out.

The TeamCity Build Agent service was set to log on as a local system account.

I went into Services, right-clicked on the TeamCity Build Agent -> Properties.

"Log On" tab, then click the radio button for "Log on as 'This Account'" and set my credentials.

Setting the service to log on as my user account gave the MsBuild script the right permissions for logging in with integrated security.

Eventually, we'll create a user for this purpose when this is ready to go live.