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;