2

Login failed for user '*****'. Reason: Failed to send an environment change notification to a log shipping partner node while revalidating the login.

I'm seeing a bunch of these errors on my SQL Server 2008 R2 - SP3 database, on average about every 5 seconds. It seems to be affecting the connection pools as well.

This seems pretty straight forward except that log shipping is NOT enabled on this server for any databases. Log shipping was used for a database which was restored to this server, I'm pretty sure the log shipping wasn't properly broken before the backup/restore. However, the offending login is different from the login used for the restored database, so it doesn't seem that the problem is directly related the restored database.

I've run through all of these procedures: http://msdn.microsoft.com/en-us/library/ms189071.aspx and queried the server to find any traces of log shipping and have found nothing.

I've been trying to resolve this issue for the past week and have hit a dead-end.

Thanks

  • Do you see any more messages before and after this message can you post that as well. There must be some error and severity number also with error you posted – Shanky Oct 24 '14 at 21:33
  • What do you see here: SELECT * FROM msdb.dbo.log_shipping_primary_secondaries;? If you're not using log shipping for anything, feel free to delete any rows found there. Something is telling SQL Server that this database is involved with log shipping. Also, what is the default database for this specific login? – Aaron Bertrand Oct 24 '14 at 22:28
  • 1
    (Oh, and you should also check your connection strings. Do they have things like failover partner? This could actually be what is telling SQL Server to go validate the login on the partner (and yes, I realize that is for mirroring, but this wouldn't be the first time SQL Server's error messages could be misleading).) – Aaron Bertrand Oct 24 '14 at 23:30
  • 1
    @DavidFlahive Aaron Bertrand has blogged about this error as the error could have to do with UAC, or that the domain controller could not be reached, or that the domain account could not authenticate against the log shipping partner, or that the log shipping partner was down. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts, and validating that the partner instance is accessible. – Kin Shah Oct 25 '14 at 1:53
  • @Shanky - The only other errors I'm seeing are Error 18456 (same error number) State 46, which has to do with the default database not being specified. However, there are few of these and the timestamps don't indicate that it would be causing the State 51 error. – David Flahive Oct 25 '14 at 17:13
0

I guessed at several things in the comments above, but the one that hit home was the connection string having the failover partner attribute.

This could actually be what is telling SQL Server to go validate the login on the partner (and yes, I realize that is for mirroring, but this wouldn't be the first time SQL Server's error messages could be misleading).

It is possible that the login no longer has access to the database on the partner (e.g. their rights have been removed) or that the database is offline or no longer exists. Which database? Could be the login's default database (check both instances), the database specifically hard-coded into the connection string, or possibly a database mentioned in a logon trigger or classifier function.

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.