Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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