1

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
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure
go
sp_configure 'show advanced options',0
go
reconfigure
go
  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?

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

1 Answer 1

1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .