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

3
  • Thanks John, I know the IP that the login is coming (there are two actually) and they specify a database in the connection string. My problem is that the database is there and it works 95% of the time. – Alonzo Hess Nov 3 '20 at 14:44
  • Is there a load balancer that 95% of the time point to a DB that exist and sometimes, 5% of the time to a DB that was maybe deleted but the IP address is still in the load balancer informations? Just guessing. – Francesco Mantovani Nov 5 '20 at 14:01
  • Interesting idea. I'll check on that. – Alonzo Hess Nov 5 '20 at 16:48
1

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.

0
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.