MS SQL login periodically doesn't work

Database Administrators Asked by Alonzo Hess on December 20, 2020

  • 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:]"

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.

2 Answers

@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.

Answered by Francesco Mantovani on December 20, 2020

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.

Answered by Tibor Karaszi on December 20, 2020

Add your own answers!

Related Questions

ERROR: no schema has been selected to create in

3  Asked on February 2, 2021 by emanuele-paolini


ORACLE ASM present one or more LUNs?

1  Asked on January 31, 2021 by miguel-ramires


Order by certain enum values first

1  Asked on January 30, 2021 by milkncookiez


I have 600% high CPU usage mysqld

1  Asked on January 30, 2021 by alfredo


How to create a SELECT statement involving a subtype

1  Asked on January 26, 2021 by elephantcoder


How to move SSISDB database

2  Asked on January 24, 2021 by jac


Ident authentication failed for user “postgres”

1  Asked on January 23, 2021 by lloyd-thomas


Creacte Mysql Database copy

1  Asked on January 20, 2021 by viktor-mandrika


Fetching Remote Encryption Key for MariaDB

1  Asked on January 14, 2021 by vince-kronlein


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP