Home / server / Questions / 693213
Accepted
Jensen
Asked: 2015-05-20 23:20:49 +0800 CST

SQL Server 2008 R2 connection sometimes fails with 'incorrect login'

  • 772

As part of our testing suite, next to the Unit Tests which mock everything and don't require a database connection, we also have Integration Tests which do require a database.

The Integration Tests are required because we are working with a lot of legacy code and it provides us the possibility to perform high level tests.

THE SETUP

The database is a SQL Server 2008 R2, running on a Windows Server 2008 R2 system with all the latest Windows Updates. Both for the OS and the SQL Server.

The VM which runs the database server is part of our build infrastructure and is newly created, based on an image of course, every morning at 6AM and destroyed at 10PM. So I know that the SQL Server Agent and Service are essentially new and started every day. The first build occurs at 7AM, which gives the machine plenty of time to start and load all services.

The database server is configured to allow an unlimited number of connections and Named Pipes and TCP connections are enabled.

The connection to the database is made by the sa user.

We have a trimmed down snapshot of our production database, a.mdf, which contains all the tables, views, stored procedures and a minimum set of data required to perform the tests.

When an integration test runs, the test setup copies a.mdf to the DATA folder of our SQL Server installation as b.mdf. b.mdf is then attach to the database using the following command:

CREATE DATABASE Foo ON (FILENAME = N'Path\To\b.mdf') FOR ATTACH

Tests run, perform database operations, and in the test tear down of the test fixture the database is detached and the b.mdf file is removed.

The following two commands are executed individually to perform the detach:

ALTER DATABASE Foo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
EXEC master.dbo.sp_detach_db @dbname = N'Foo'

So, in practice, I have a set of test fixtures with the following layout:

Setup();
Test_1();
Test_2();
Test_3();
TearDown();

Each Setup creates a new database, runs all the tests, and removes the database so the next text fixture starts with a clean, fresh database.

In total I have around 50 text fixtures, each containing 10 tests. So that's 50 times a database is attached and detached and around 500 tests are run.

THE PROBLEM

The last couple of weeks I see an increase in the number of failed builds related to the Integration Tests. I know that my tests are OK because the entire setup works perfectly on my local machine, and on the machines of the other developers. It's just the build server that reports a problem:

SetUp Error : Namespace.Class.Method
SetUp : System.Data.SqlClient.SqlException : Cannot open database "Foo"    requested by the login. The login failed.
Login failed for user 'sa'.

Obviously, I Googled and yes, the login is correct. I know that because it's not always the same test that fails. If I run the entire test suite 10 times, it will fail 8 out of 10, but the test which reports a failure is different each time. The error message is the same, saying it cannot login and sometimes it also reports that there is no process at the other end of the pipe.

I also checked that named pipe and TCP connections are enabled, I checked the number of allowed connections, ... I checked the ERRORLOG file, but it doesn't contain anything directly related to my database.

My guess is that for some strange reason it happens to fast, or to slow and it's not capable of properly attaching or detaching the database, or is it the SINGLE_USER call that causes a problem. From what I've gathered, if one test fails due to the login, the b.mdf file cannot be removed because the file seems to be in use.

So my question is: is there anything else I can try? Is there an error log file or specific message which can provide me with more insight? Is there something I can do to check whether or not the attach and detach were successful? (Is it possible that a failed detach causes the login problem?) Is the detach operation asynchronous, and therefore is it possible that it's not completed yet when the next call is made?

windows-server-2008-r2

1 Answers

  1. Best Answer

    First issue: login failed error.
    Your database is most likely not fully initialized yet when the tests run.
    You should catch this in your procedure, an easy way to do this is to query the master database to see if the target database is up and running.

    IF (select name from sys.databases
where name = 'foo' and state_desc = 'ONLINE' and is_in_standby = '0') IS NOT NULL
PRINT 'database not found';

    Second issue: no process at the other end of the pipe.
    The error actually behind this is often obscured if you don't connect through TCP/IP.
    You could try enabling direct IP connections, or you could focus on the other errors, it's likely they're what's causing this one.

    • 1