January 31, 2022 at 2:39 pm
SQL Server Strange behavior:
I have been Getting the following error massages non stop after full backup fails. this massage gets generated for each databases in this Availability Group. The job runs every night, and it call SSIS package. Both the SSIS and Job seems okay and the tricky part is that job fails once in a while like once in 10 days or so.
[298] SQLServer Error: 4060, Cannot open database "testDB" requested by the login. The login failed. [SQLSTATE 42000]
[298] SQLServer Error: 18456, Login failed for user 'Test\test-user-sql'. [SQLSTATE 28000]
Server; SQL Server 2016 AG with 2 replica synchronized.
More than 10 databases configured in this AG
Backup preference; any replica
Did I miss something here?
Thank you
Razel.
January 31, 2022 at 2:49 pm
My opinion - check the logs. The message "the login failed" makes me think that the account was locked out or otherwise disabled. If it is an AD account, if the account gets locked out by any process (SQL or otherwise) it will be locked out by SQL as well. If your IT team has set up an auto-unlock policy, it could be that the account is unlocked by the time the job runs again.
Now, I say check the logs because the logs will indicate WHY you couldn't log in with that account. It MAY be that the account was locked or that the link to AD was down (doubt that would cause a failure, but I'm not sure how you have things configured), or could be something else entirely (like something took testDB offline).
Now, checking the logs is only useful if you have failed logon logging turned on. I HIGHLY recommend you have that enabled on all instances as the impact is low as it only logs on a failed login which should be infrequent. If you have frequent failed logins, you should investigate that and remedy it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply