Linked Server Problem -- This is tough

I have a Replication subscription set up on ServerA to push to ServerB and I
was trying to set up a linked server to ServerB from ServerA. I got the
following error:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'ServerA' already exists.
So I ran microsofts suggested script at
http://support.microsoft.com/defaul...b;en-us;274098. I then adde
d
a loging using sp_addlinkedsrvlogin as suggested and I still could select
from ServerB. I was getting:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please help. I am lost. What exactly do I need to do now to be able to
select data from ServerB while I'm logged on to ServerA.
Thank you.
JamesI am assuming that this is a SQL2K server.
if it is then I would suggest the following
1) ensure that the sqlserver service account on server A is defined on
server B as a valid account(i.e. has access to server B)
2) ensure that the sql server service account on server A is trusted for
delegation - this is more important than (3) below
3) ensure that the sql server service account on server B is trusted for
delegation and this
4) ensure that the service account has been added to 'impersonate a client
after authenication' and 'access this computer from the network' as well as
all other permissions listed in
plus any other permissions listed in the kba(detailing permissions required
for sqlservice account if it's not in the Administrators group and built
in\admins has been taken out of logins) - sorry I cannot find the kba at the
moment
"Hex Caliber" <hex@.caliber.com> wrote in message
news:E83105D4-BF71-4900-9586-D9BCA34C01D8@.microsoft.com...
> I have a Replication subscription set up on ServerA to push to ServerB and
I
> was trying to set up a linked server to ServerB from ServerA. I got the
> following error:
> Server: Msg 15028, Level 16, State 1, Line 0
> The server 'ServerA' already exists.
> So I ran microsofts suggested script at
> http://support.microsoft.com/defaul...b;en-us;274098. I then
added
> a loging using sp_addlinkedsrvlogin as suggested and I still could select
> from ServerB. I was getting:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Please help. I am lost. What exactly do I need to do now to be able to
> select data from ServerB while I'm logged on to ServerA.
> Thank you.
> James
>

Linked Server Problem -- This is tough

I have a Replication subscription set up on ServerA to push to ServerB and I
was trying to set up a linked server to ServerB from ServerA. I got the
following error:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'ServerA' already exists.
So I ran microsofts suggested script at
http://support.microsoft.com/default...;en-us;274098. I then added
a loging using sp_addlinkedsrvlogin as suggested and I still could select
from ServerB. I was getting:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please help. I am lost. What exactly do I need to do now to be able to
select data from ServerB while I'm logged on to ServerA.
Thank you.
James
I am assuming that this is a SQL2K server.
if it is then I would suggest the following
1) ensure that the sqlserver service account on server A is defined on
server B as a valid account(i.e. has access to server B)
2) ensure that the sql server service account on server A is trusted for
delegation - this is more important than (3) below
3) ensure that the sql server service account on server B is trusted for
delegation and this
4) ensure that the service account has been added to 'impersonate a client
after authenication' and 'access this computer from the network' as well as
all other permissions listed in
plus any other permissions listed in the kba(detailing permissions required
for sqlservice account if it's not in the Administrators group and built
in\admins has been taken out of logins) - sorry I cannot find the kba at the
moment
"Hex Caliber" <hex@.caliber.com> wrote in message
news:E83105D4-BF71-4900-9586-D9BCA34C01D8@.microsoft.com...
> I have a Replication subscription set up on ServerA to push to ServerB and
I
> was trying to set up a linked server to ServerB from ServerA. I got the
> following error:
> Server: Msg 15028, Level 16, State 1, Line 0
> The server 'ServerA' already exists.
> So I ran microsofts suggested script at
> http://support.microsoft.com/default...;en-us;274098. I then
added
> a loging using sp_addlinkedsrvlogin as suggested and I still could select
> from ServerB. I was getting:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Please help. I am lost. What exactly do I need to do now to be able to
> select data from ServerB while I'm logged on to ServerA.
> Thank you.
> James
>

Linked Server Problem -- This is tough

I have a Replication subscription set up on ServerA to push to ServerB and I
was trying to set up a linked server to ServerB from ServerA. I got the
following error:
Server: Msg 15028, Level 16, State 1, Line 0
The server 'ServerA' already exists.
So I ran microsofts suggested script at
http://support.microsoft.com/default.aspx?scid=kb;en-us;274098. I then added
a loging using sp_addlinkedsrvlogin as suggested and I still could select
from ServerB. I was getting:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Please help. I am lost. What exactly do I need to do now to be able to
select data from ServerB while I'm logged on to ServerA.
Thank you.
JamesI am assuming that this is a SQL2K server.
if it is then I would suggest the following
1) ensure that the sqlserver service account on server A is defined on
server B as a valid account(i.e. has access to server B)
2) ensure that the sql server service account on server A is trusted for
delegation - this is more important than (3) below
3) ensure that the sql server service account on server B is trusted for
delegation and this
4) ensure that the service account has been added to 'impersonate a client
after authenication' and 'access this computer from the network' as well as
all other permissions listed in
plus any other permissions listed in the kba(detailing permissions required
for sqlservice account if it's not in the Administrators group and built
in\admins has been taken out of logins) - sorry I cannot find the kba at the
moment
"Hex Caliber" <hex@.caliber.com> wrote in message
news:E83105D4-BF71-4900-9586-D9BCA34C01D8@.microsoft.com...
> I have a Replication subscription set up on ServerA to push to ServerB and
I
> was trying to set up a linked server to ServerB from ServerA. I got the
> following error:
> Server: Msg 15028, Level 16, State 1, Line 0
> The server 'ServerA' already exists.
> So I ran microsofts suggested script at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;274098. I then
added
> a loging using sp_addlinkedsrvlogin as suggested and I still could select
> from ServerB. I was getting:
> Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> Please help. I am lost. What exactly do I need to do now to be able to
> select data from ServerB while I'm logged on to ServerA.
> Thank you.
> James
>

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".

Linked Server - Security Problem

Hi
We have set up Transactional Replication between two servers.
However the linked server that is created at the subscriber does not work be
default.
I therefore selected the last option "Be made using this security context"
and entered the sa username and password.
This solves the problem - linked server can then connect.
But what I want to know is how to get the first option working by specifying
a local login and remote user and remote password.
I have tried all users on the servers and in different combinations with
impersonate options ticked on some! but could not get it working.
So for now it is running using the sa username and password.
How does login-mapping work? why can I not get it to work?
Please advise
The security context used is whatever security context you configured your
distribution agent to connect to the subscriber with. By default this will
be the Publisher SQL Server Agent account if you are using a push
subscription.
When you created your subscription you define this in the Distribution Agent
dialog box, in the Connect to subscriber section.
"I.W Coetzer" <IWCoetzer@.discussions.microsoft.com> wrote in message
news:ACE5DB2B-1A03-48EC-BA15-3005F0C41A16@.microsoft.com...
> Hi
> We have set up Transactional Replication between two servers.
> However the linked server that is created at the subscriber does not work
> be
> default.
> I therefore selected the last option "Be made using this security context"
> and entered the sa username and password.
> This solves the problem - linked server can then connect.
> But what I want to know is how to get the first option working by
> specifying
> a local login and remote user and remote password.
> I have tried all users on the servers and in different combinations with
> impersonate options ticked on some! but could not get it working.
> So for now it is running using the sa username and password.
> How does login-mapping work? why can I not get it to work?
> Please advise
|||Hi
Our Sql Server Agent is set up to use a domain account.
ourdomain\our_special_account
All of our sql servers on the same domain (even in different locations) use
this special domain account that our administrators created for us.
I can not seem to create a linked server using this domain account.
Therefore I set it up to use the sa account of the server.
all our servers also have the same sa sql account and password because these
servers were created from the same virtual machine templates ...
"Hilary Cotter" wrote:

> The security context used is whatever security context you configured your
> distribution agent to connect to the subscriber with. By default this will
> be the Publisher SQL Server Agent account if you are using a push
> subscription.
> When you created your subscription you define this in the Distribution Agent
> dialog box, in the Connect to subscriber section.
> "I.W Coetzer" <IWCoetzer@.discussions.microsoft.com> wrote in message
> news:ACE5DB2B-1A03-48EC-BA15-3005F0C41A16@.microsoft.com...
>
>