MS SQL login periodically doesn’t work

Posted on

Question :

  • Instance info: Microsoft SQL Server 2017 (RTM-CU13) Standard Edition (64-bit) on Windows Server 2019 Datacenter
  • Location: AWS

A Java app on another AWS server logs into the SQL instance using a local SQL account, and 95% of the time, the login connects, and everything is fine. The other 5% I get following error:

“Login failed for user ‘Blah.’ Reason: Failed to open the explicitly specified database ‘Blah_Database.’ [CLIENT: xxx.xxx.xxx.xxx]”

I know the 2 IP’s that the login request is coming from and the connection string names the database which is present. The permissions are set correctly.
I’ve captured the event running a Profiler Trace looking for Event Class “Audit Failed Login.” But, This doesn’t give me any more info than the SQL error log.

Any other event classes I can add to the profiler to gather more info?

I’ve verified the the database Auto-Close feature is off. I’ve also increased the remote login timeout to 30.

Answer :

Perhaps AWS has some stuff similar to Azure Serverles’ functionality? I.e., it takes a while to spin up the database. I would increase the connection timeout drastically as a first test and then take it from there.

@Alonzo, once it happened to me that we upgraded a software through a setup.exe file.

The installation was fine but because there was a load balancer involved the upgrade actually took place only on 4 databases out of 5.
In the end 80% of our transactions were successful and 20% were failing for no reason.

It took us some time to understand that we had to manually apply that upgrade also on the 5th database.

So check if you find yourself in this scenario.

Leave a Reply

Your email address will not be published.