Connecting to SQL Server fails in autostart service, works when starting manually. SQL Server not ready, yet service is started

Question

TL;DR - MSSQL Service claims to be ready (status = Started) while it's not really ready (see below strike-outed text)

I have the C# written windows service which installs itself as ServiceAccount.LocalSystem and specifies MSSQLSERVER in ServicesDependedOn.

Now I've created MS SQL user, granted it all the necessary permissions & roles (including db_owner on the needed database, and Connect / Login privileges).

When running the service manually (via net start or services.msc manager) it works corretly, i.e. it connects to the database. When I restart the machine, the service throws this exception:

Cannot open database "xxxx" requested by the login. The login failed.
Login failed for user 'xxxxx'.

What have I configured incorrectly? Again, when I run the service manually it works fine!! I've also tried logging in with this account via MS SQL Management Studio - and this works too.

---

as suggested in comments, I've tried waiting before trying to connect - 20sec Sleep doesn't solve the problem. I'm adding the state of MSSQLSERVER service (checked via ServiceController) to the log, and it is "Running". Everything seems to be fine, except that the Login fails when service is being auto-started

Ok, I've been tracking this down for a few hours. Here's what I've found:

SQL Server service (MSSQLSERVER) claims to be ready (status = Started) quite quickly (about 2-3 seconds after issuing the "net start" command). Unfortunately warming up (starting up databases, recovery and some other stuff) takes place later, and takes up to 2 minutes (120 seconds!!). Of course it rejects connections until it's warmed up.

I've ended up doing

try 
{
    connect;
} catch {
    RequestAdditionalTime(); // to avoid Windows Service timeout
    Sleep();
}

in a while loop.

I hate this kind of solutions but can't find anything cleaner.

If anyone knows how to do it properly, please answer.

Answer 1

You can use the above approach connecting to master and call the following

select state, databases.state_desc ,* from sys.databases


/*
0 = ONLINE
1 = RESTORING
2 = RECOVERING
SQL Server 2008 through SQL Server 2014
3 = RECOVERY_PENDING
SQL Server 2008 through SQL Server 2014
4 = SUSPECT
5 = EMERGENCY
SQL Server 2008 through SQL Server 2014
6 = OFFLINE
SQL Server 2008 through SQL Server 2014
7 = COPYING
Azure SQL Database
10 = OFFLINE_SECONDARY
Azure SQL Database
*/

or another approach is call the following checking for non NULL value back, Note that this should return NULL unless database is completely ready.

SELECT DATABASEPROPERTYEX('MyDatabaseName', 'Collation')

Answer 2

The only solution which works for me is not to relay on the service status, just to retry each ~10sec to connect to the server.