Can run tests from MSBuild but not from TeamCity (Login to SQL Server denied)

I'm getting an unusual error when building a Visual Studio 2010 solution via MSBuilt using MSTest. If I enter a file name (e.g. "TestResults.trx") in the Build Runner | MSBuild | MSTest Settings | Results file setting, most of my integration tests fail with the following error:

  System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: Cannot open database "NatureShopTest" requested by the login. The login failed.
  Login failed for user 'NT AUTHORITY\SYSTEM'..

If I clear the "Results file" field then the unit tests pass???

This is strange for two reasons. Firstly I wouldn't expect that field to alter the security context that TeamCity used to MSTest. Secondly, The System account has sysadmin access control to both the master database and the database that is being used to run various tests.

Anyone have any idea what could be going on here?

Cheers,
James

10 comments

So it seems the above was a decoy. If I ommit the results file then all it does is hide the actual errors... the unit tests still fail it's just the failure goes unnoticed by TeamCity... so now I'm back to square one which is that when team city tries to run my unit tests via MSTest I get login failures connecting to the SQL Server database using the System account.

However, if rather than checking "Enable MSTest tests" in the MSBuild options I instead run the following task from within the MSBuild file iteself then the tests run and all of them pass (i.e. no issues connection to the DB when the Build.xml file is run by TeamCity).

      <Target Name="Test">
           <!-- Run unit tests... spit results out to results file -->
           <Exec Command='$(MsTestExe) /testcontainer:$(InfrastructureTests) /testcontainer:$(DataLinqTests) /testcontainer:$(CoreTests) /runconfig:localtestrun.testrunconfig' /> 
     </Target>     



This leads me to believe that TeamCity uses a different security context to run the build file (MSBuild) than the one it uses to execute the unit tests. It still doesn't make sense though since the Local System account definitely has access to the database in question, as well as to the master database (and the temp database for that matter).

Confused :-(

James
0

Please try running TeamCity build agent windows service under some real account with administrative privileges.

0

Unfortunately it still fails:

Initialization method NatureShop.Core.Tests.Services.CurrencyServiceTests.InitializeTest threw exception. System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: Cannot open database "NatureShopTest" requested by the login. The login failed.
Login failed for user 'NT AUTHORITY\SYSTEM'..
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)


It's a bit unusual, but the account that the build agent runs as doesn't seem to affect the account that is used to run mstest from that build agent (I'm not sure how that works).

0

Also, just to make sure, I ran ssms via PsExec as the "NT Authority\System" user. I then executed the following command:

  SELECT SYSTEM_USER

Which returned (as expected) a single record/column "NT AUTHORITY\SYSTEM"

I then executed the command "Use NatureShopTest" (which is the name of the database that the integration/unit tests will use) and everything worked fine.

The above proves that the NT AUTHORITY\SYSTEM user definitely does have access to the necessary databases... so the error message in the logs is, quite simply, erroneous.

0

This is what I suggested you to change. Please open 'Services' snapshot, find TeamCity Build Agent  service, open it's properties and select there another user account. After changing this you will need to stop and start build agent service to make apply changes.

0

Hi Eugene,

I tried that but I still get the same error. Essentially, the user that I run the TeamCity Build Agent as seems to have no effect whatsoever. No matter what user I specify there (I've tried 4 different users, restarted the service each time and even tried restarting the machine ones) I still get the same error. Furthermore, ALL of the users I've tried (including NT AUTHORITY\SYSTEM) have access to the SQL Server and are in the sysadmin role for the databases that the unit/integration tests are using (master and NatureShopTest).

James

0

OK, so I finally figured out what was going wrong...

Firstly, I looked in the logs for the build to check exactly what command was being issued to run MSTests and found the following:

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\MSTest.exe" /runconfig:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\localtestrun.testrunconfig /testcontainer:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\NatureShop.Core.Tests\bin\Staging\NatureShop.Core.Tests.dll /testcontainer:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\NatureShop.Data.Linq.Tests\bin\Staging\NatureShop.Data.Linq.Tests.dll /testcontainer:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\NatureShop.Infrastructure.Tests\bin\Staging\NatureShop.Infrastructure.Tests.dll /resultsfile:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\TestResults.trx

I then ran cmd.exe in the security context of the NT AUTHORITY\SYSTEM user (using PsExec) and executed that exact same command... it fell over, but not for the reason I thought - it fell over because the TestResults.trx file already existed. If I changed the command to the following (simply changing TestResults.trx to TestResults2.trx) then everything worked fine and all tests executed and passed successfully:

"C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\MSTest.exe" /runconfig:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\localtestrun.testrunconfig /testcontainer:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\NatureShop.Core.Tests\bin\Staging\NatureShop.Core.Tests.dll /testcontainer:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\NatureShop.Data.Linq.Tests\bin\Staging\NatureShop.Data.Linq.Tests.dll /testcontainer:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\NatureShop.Infrastructure.Tests\bin\Staging\NatureShop.Infrastructure.Tests.dll /resultsfile:C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\TestResults2.trx

Which gave me an idea and so I went back to the build log and made sure I was viewing "All Messages" instead of just the errors... after locating the call to MSTest, voila! There was that same bloody error, just before the TestResults.trx file was imported:

The results file "C:\TeamCity\buildAgent\work\997fcad12fa08c08\src\TestResults.trx" already exists. Please specify a different results file or verify the existing file is no longer needed and delete it.

Only this didn't tip TeamCity off... it didn't consider that a problem and simply ploughed ahead with it's next task, which was to import the (by now very, very, very old) TestResults.trx file which, obviously, contained some errors relating to problems connecting to SQL Server as the NT AUTHORITY\SYSTEM user. So basically, no matter what I did, I always got the same error message back because it was always importing the very same TestResults.trx file. Grrr!!!!

Anyway, glad to have it sorted... hopefully this topic can help someone else find similar errors in the future. Maybe there is even a way to have TeamCity catch errors like that when MSBuild doesn't execute properly because the results file already exists, but out of the box it certainly doesn't and I'm not sure what config changes would be required in order to have it do so.

Cheers,
James

0

I've added a warning message for this case. It will remove existing .trx file before mstest is started.

0

Cool, cheers Eugene... hopefully that saves someone else a bit of pain in the future :)

0
I'm getting the same error with VSTest. Log below:
 
VSTest
TestInitialize threw exception. System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: Login failed for user  at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
 
I tried deleting trx file from TestResults folder, without success. Please help.
 
Thanks,
 
Dave
 
0

Please sign in to leave a comment.