Linked server and replication

Hello,

I have one server using SQL Server Express (Server1\SqlExpress - Sql Authentication) and another one using Sql Server 2005 (Server2 - Windows Authentication)

My Server2 is the publisher for a replication and Server1\SqlExpress is the client for this replication.

For any reason that has been discussed in details with Raymond Mak in some other threads, some triggers and stored procedures cannot be replicated via the replication, so I use a post replication script.

To avoid having to maintaint coherent my source control, my master DB and the post replication script I wanted to create a script that will extract from the master DB the source code of the problematic triggers and stored procedures.

To do that, here the kind of script I use (example for the stored procedure):

DECLARE @.query NVARCHAR(max)

select @.query = routine_definition from openquery(MASTER,

'

SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES

WHERE routine_name = N''spU_GUI_AppliquePerteAgrement''

')

EXEC (@.query)

So my script will be run on the subscription client and will get the script to the master db using a linked server. So, here is the step I have followed :

Create a domain user (let's call it localAdmin)

Set localAdmin as a local administrator for Server1 and Server2

Update the properties of my subscription to impersonate localAdmin on the publisher and on the subscriber

Create a linked server on Server1, linked to Server2. (for security, I have tried either "Be made without using a security context" and "Be made using the current security context")

Then I am able to run my script on Server1\SqlExpress but when I launch the replication, here is the error I receive : "Login failed for Autorite NT \ Anonymous Login"

Do you have any idea of what can happen ?

Thanks,

Pierre-Emmanuel

When you configure to use "Be made using the current security context", SQL Server uses windows NT kerberos delegation to authenticate client to the linked server. If the delegation configuration is incorrect, you will get error ""Login failed for Autorite NT \ Anonymous Login".

You can check you configuration against the recommendations,

(1) http://msdn2.microsoft.com/en-us/library/ms189580.aspx

(2) SQL Server setion in http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

If using client account to authenticate to the linked server is not a requirement, you can configure to use SQL login for the linked server configuration. "Be made using this security context".