Getting "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" when attempting to setup log shipping on Linux for a SQL Server database

Question

We have a SQL Server instance installed on an AWS EC2 instance with a Linux operating system (no idea why this would be done) with hundreds of databases that need moving to a better place. In the meantime, we're trying to set up log shipping so we can replicate some of the more important databases.

We have a directory for the transaction log backups, /var/opt/mssql/tlogs, and I can create a SQL Server Agent job that will successfully backup the database to that location.

However, when we set up log shipping, targeting this same directory, and run the job in SQL Agent, it always fails. The step that fails is Log shipping backup log job step and the error is:

Executed as user: . The step failed.

Looking into the full error log, the actual errors logged are:

"Error: Could not retrieve backup settings for primary ID ''.(Microsoft.SqlServer.Management.LogShipping)" "Error: Failed to connect to server ip-.(Microsoft.SqlServer.ConnectionInfo)" "Error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.(.Net SqlClient Data Provider)"

I know this error well from Linked Servers, but I've never seen it come up with transaction log shipping before. With this being a Linux installation, it's incredibly difficult to find any useful guidance online for how to fix this problem.

The job is owned by "sa", which is the same as the backup job that DOES work. The Linux user is "mssql" and this user has been granted full rights to the backup folder. I have no idea how Linux deals with service accounts, but it seems like it just uses "mssql" for everything.

We've tried creating the job via the UI in SSMS, on the machine, remotely, with a SQL script from the Microsoft documentation, all fail the same way.

This is the basic script we used (the second part is irrelevant, as it just creates a schedule for 15 minutes, but we can run the job manually to watch it fail):

DECLARE @LS_BackupJobId AS UNIQUEIDENTIFIER;
DECLARE @LS_PrimaryId AS UNIQUEIDENTIFIER;
DECLARE @SP_Add_RetCode AS INT;

EXECUTE @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
    @database = N'rr_test_replication',
    @backup_directory = N'/var/opt/mssql/tlogs',
    @backup_share = N'/var/opt/mssql/tlogs',
    @backup_job_name = N'LSBackup_test_replication',
    @backup_retention_period = 4320,
    @backup_compression = 2,
    @backup_threshold = 60,
    @threshold_alert_enabled = 1,
    @history_retention_period = 5760,
    @backup_job_id = @LS_BackupJobId OUTPUT,
    @primary_id = @LS_PrimaryId OUTPUT,
    @overwrite = 1;

Edit: It should be noted that this failure happens while backing up the transaction logs, NOT when attempting to copy them or restore them onto a second server. We're testing this by attempting to ship logs to a second database on the same instance, but it's failing to run the job to backup the transaction logs due to the authentication error.

Answer 1

Whenever you get an error

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

That indicates that the client (in this case the primary server) tried to use Integrated Authentication with either Kerberos or NTLM, but the server (in this case the log shipping server) couldn't verify the login.

This could happen for a number of reasons:

• The most likely in your case: you are using an IP address instead of an FQDN.
  Kerberos uses SPNs to identify servers permitted to authenticate with a particular service, and although you can set up an IP address as an SPN manually, it's not advised. SPNs for the FQDN are managed automatically by SQL Server, so you should use that. Note fully-qualified, you need to use hostname.yourdomain.com.
  Do not use an alias unless you have used netdom /alias to assign it to the machine, a CNAME record is not enough.
• The two servers are using different service accounts. You'd need to have the same service account being used by both servers, which would need to be a gMSA domain account, for Kerberos to work.
  For NTLM (note that this is not advised), you can set up identical local service accounts with identical passwords.
• The servers are not in the same domain, and the two domains have no trust between them. Or one/both aren't even in a domain.
  For Linux, they would need to be domain-joined using something like realmd or krb5.
• One of the servers cannot contact the domain controller, or has a broken trust relationship with its domain. This is an existential issue for a domain-joined machine, and should be sorted pronto.

Alternatively, you can just set up an SQL Authentication login on the secondary, and change the parameter @monitor_server_security_mode to 0 and set @monitor_server_login and @monitor_server_password.

Answer 2

I have resolved similar problems if not this very one by running cliconfg on the client (in this case your server) and enabling TCP/IP protocol and explicitly removing named pipes. It is easy enough to try and you may be surprised.