3

Configuration:

  • LSPrimary01 - Log Shipping Primary
  • LSSecondary01 - Log Shipping Secondary
  • OddServer03 - This server is not part of the LS configuration, the odd man out
  • Sean\LSServiceAccount - The domain user service account for log shipping on all servers

A colleague of mine and I were configuring Log Shipping in a test environment. We took a full COPY_ONLY backup of a database on LSPrimary01, a log backup, copied to LSSecondary01 and restored the backup and log WITH NORECOVERY. Then used the GUI on LSPrimary01 to configure Log Shipping. We already configured file sharing and proper permissions for Sean\LSServiceAccount.

The following error was the result:

  • START OF TRANSACTION LOG BACKUP
  • Error: Could not retrieve backup settings for primary ID'cb1564b4-4ffd-a42d'.(Microsoft.SqlServer.Management.LogShipping)
  • Error: Failed to connect to server OddServer03.(Microsoft.SqlServer.ConnectionInfo)
  • Error: Login failed for user 'Sean\LSServiceAccount'.(Net SqlClient Data Provider)
  • END OF TRANSACTION LOG BACKUP

I ran the following query to verify my Log Shipping configuration:

SELECT 
      [primary_database]
      ,[backup_directory]
      ,[backup_share]
      ,[backup_retention_period]
      ,[monitor_server]
      ,[user_specified_monitor]
      ,[monitor_server_security_mode]
      ,[last_backup_file]
      ,[last_backup_date]
  FROM [msdb].[dbo].[log_shipping_primary_databases]

Everything in the result set was correct, save monitor_server, it had the value of OddServer03! As many of you know, when configuring Log Shipping through the GUI, it is damn near impossible to "accidentally" configure this option.

We never intended having a monitor server in the mix. We use Quest Spotlight to monitor the aforementioned servers which will yield alerts when jobs fail or when log shipping gets behind.

One other perplexing matter, the job that is getting created is LSAlert_OddServer03 instead of LSAlert_LSPrimary01.

Why is the service account trying to authenticate to a server that isn't part of the configuration?

Improve this question
5
  • 1
    Looks like OddServer03 was added as monitor server in LS configuration. If it is the required configuration, can you check Sean\LSServiceAccount login is there on all 3 servers and has sa rights. If it isn't required did you try to remove the monitor server and check?
    – SQLPRODDBA
    Commented Mar 17, 2017 at 19:56
  • It was never configured to be the monitored server, so I don't know how it got there. We've completely torn down LS and rebuilt it w/o a monitor, but it still shows OddServer03 as the monitor server. The same service account is on all 3 servers with proper permissions. That is the 2nd perplexing issue, how that service account is failing authentication!?
    – AKDiscer
    Commented Mar 17, 2017 at 20:25
  • Did your primary server get renamed recently?
    – SQLPRODDBA
    Commented Mar 18, 2017 at 17:19
  • none of the servers have ever been renamed, same names since inception.
    – AKDiscer
    Commented Mar 18, 2017 at 20:01
  • Last thing I would check is the LSBackup job command in LSBackup SQL Server agent job. Does it have primary server name after -server parameter?
    – SQLPRODDBA
    Commented Mar 20, 2017 at 6:18

1 Answer 1

Reset to default
0

My suspicion would be that even after removing the Log Shipping configuration the Log Shipping system tables are not being cleaned up properly. I would suggest the following steps from blog.sqlauthority.com for your situation:

1. Update the arguments for the command below and execute on LSPrimary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'Primary_Database_Name'
,@secondary_server = N'Secondary_Server_Name'
,@secondary_database = N'Secondary_Database_Name'
GO

2. Update the arguments for the command below and execute on LSSecondary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_database @database = N'Primary_Database_Name'
GO

3. Run the query from your question again and examine the results.

If there are still improper entries, consider manually removing them. An example of something you could do would be this:

SELECT * INTO Tempdb.dbo.TemporaryCopy_log_shipping_primary_databases
FROM msdb.dbo.log_shipping_primary_databases 
--please note this only lasts until the database engine is restarted!

UPDATE dbo.log_shipping_primary_databases
SET monitor_server = 'LSPrimary01'
WHERE monitor_server = 'OddServer03'

Before running your own version of the commands above, which will try to clean out the system tables, it's worth considering whether you want to try skipping the GUI when trying to reset the Log Shipping configuration. If you want to try that, I would recommend scripting out your current configuration (Right-click your database in SQL Server Management Studio --> Choose Properties --> Click Transaction Log Shipping in left sidebar -> Click Script Configuration button on the bottom right).

You can then examine the results for what commands it uses as well as whether you see it inserting the OddServer03 value anywhere. At that point, you could clean everything up as described in the 3 steps above and then add Log Shipping back via the script you extracted and updated as needed. That would give you 100% control over what is actually executing and it would be less likely for the OddServer03 to somehow end up in the configuration.

NOTE: If any of the above links break, use Google to search for them: - dba.stackexchange.com site for the title of 'Can I deactivate log shipping jobs without raising errors' - blog.sqlauthority.com site for the title of 'Removing Multiple Databases from Log Shipping'

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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