Sql-server – MS SQL login periodically doesn’t work

profilersql server

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

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