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
- Execute LogShipping_Destination_Schema & LogShipping_Destination_Logic in master on destinations server
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?
The above process creates linked server with integrated security for authorisation. Work around-change the security on linked server to use service account.