Search code examples
sql-serverservice-broker

SQL Server 2012 Logon exception


I have 2 applications connecting to the same database, when starting each individually both are fine. When I start them in the same time one of them will get logon exceptions as you may see below, this happening in 99% of my tries (so very rarely they are fine together).

And it is always the same application that gets the exceptions (1):

  1. an application that's doing queries over the database
  2. an application that connects to the service broker (this one work in all cases)

The 1st app will crash if I delay the start of the 2nd. Here are some sql server logs:

2015-09-18 07:27:20.43 Logon       Error: 18456, Severity: 14, State: 38.
2015-09-18 07:27:20.43 Logon       Login failed for user 'domain\user'. Reason: Failed to open the explicitly specified database 'dbname'. [CLIENT: <local machine>]
2015-09-18 07:27:21.32 spid55      Setting database option ENABLE_BROKER to ON for database 'dbname'.

In SQL Server logs it is always like that, 2 line Logon error and then the ENABLE_BROKER line. I'm thinking that the service broker has something to do with the previous 2.

Details about envirenmont: SQL Server 11.0.3153.0 - 64 bit, Windows 7 (7601) running on a VM.


Solution

  • Finally i found what was causing this, it was the below alter database statement for which I don't see it's usage for now (while using the service broker it should be enabled by the db admin and not each time when the app is restarted). After removing that code everything seems fine.

    ALTER DATABASE [dbname] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; 
    

    Inherited code fun :-) (not)