I am setting up biztalk logshipping for Biztalk 2009 database. Following http://msdn.microsoft.com/en-us/library/aa560961.aspx article, I am doing the following to setup biztalk logshipping on destination server:

Enable Ad-hoc queries by:

sp_configure 'show advanced options',1
sp_configure 'Ad Hoc Distributed Queries',1
sp_configure 'show advanced options',0
  1. Execute LogShipping_Destination_Schema & LogShipping_Destination_Logic in master on destinations server
  2. Run:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = '', @nvcMgmtDatabaseName = '', @nvcMgmtServerName = '', @SourceServerName = null, -- null indicates that this destination server restores all databases @fLinkServers = 1 -- 1 automatically links the server to the management database

When I run this I am receiving the following error:

        Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

After some research I found some info :

Usually this error means that the SQL Server Service Principal Name (SPN) was not configured, and NTLM was not being used as an authentication mechanism.

SQl services are runing under different domain accounts. So, I asked the domain admin to create SPNs for the servers, SQL service accounts for beoth source and destination using name and FQDN. enabled computer name and service accounts for delegation. When I run the following:

select * from sys.dm_exec_connections

I can see all TCP connection being made via Kerberos but still same error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Any help please?

  • After the SPNs were created was the SQL Server restarted?
    – mrdenny
    Feb 22 '11 at 0:14
  • I cannot restart SQL server because it houses many other production databases. SPNs were created 5 days ago
    – Manjot
    Feb 22 '11 at 1:55

The above process creates linked server with integrated security for authorisation. Work around-change the security on linked server to use service account.

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.