Cannot query containerised SQL Server database from Azure DevOps Pipeline integration test

Viewed 244

I have an integration test that starts a Linux-based Docker container with SQL Server and a restored database. The test runs a simple select count(*) query and passes every time when running locally. When the test runs as part of an Azure DevOps Pipeline the test fails with the following error:

System.Data.SqlClient.SqlException : Cannot open database "A" requested by the login. The login failed. Login failed for user 'sa'.
  Stack Trace:
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()

When I change the test to query the master database instead of our own database, the query runs successfully and the test passes.

The core of the test is as follows:

using (var connection = new SqlConnection(connectionString.ConnectionString))
{
   await connection.OpenAsync().ConfigureAwait(false);

   using (var command = new SqlCommand("select count(*) from [table];", connection))
   {
      var rowCount = (int)await command.ExecuteScalarAsync();
          
      rowCount.Should().BeGreaterThan(0);
   }
       
   connection.Close();
}

This connection string works:

var connectionString = new SqlConnectionStringBuilder
{
    DataSource = "localhost,32808",
    UserID = "sa",
    Password = "P@ssword!23",
    InitialCatalog = "master",
    ConnectTimeout = 120,
    ConnectRetryCount = 3 
};

This one fails:

var connectionString = new SqlConnectionStringBuilder
{
    DataSource = "localhost,32808",
    UserID = "sa",
    Password = "P@ssword!23",
    InitialCatalog = "A",
    ConnectTimeout = 120,
    ConnectRetryCount = 3 
};

Initially, the test was failing with a connection timeout. I added a longer timeout and the exception changed to this one. There's nothing obvious missing from the security settings: sa is the dbo of the A database. Also, the test passes locally using the same Docker image.

Any ideas about how to solve this, or get additional feedback via logging, etc. are gratefully received.

SQL Server 15.0.4023.6; Ubuntu 18.04.4; full Azure build agent specs here;

1 Answers

It turns out that the key to this problem was how the test code was checking the readiness of the SQL Server instance in the container.

The code tries to create a connection to the SQL Server instance running in the container using the master database. What I didn't know, was that while the master database might be ready and accepting connections, other databases might not be. I changed the code to try and connect using the actual database that the test wants to use, rather than master and this appears to work.

The local tests must've been passing locally because the container was able to complete initialisation consistently quicker in that environment compared with the Azure DevOps environment.

Related