Linked Server Problem

We migrated our production SQL Server 2000 database to a new machine and all seems to be working well except for our linked server on our SQL Server 2005 instance that points to our SQL Server 2000 database that was just moved. We get the following error when we try to query a table on the 2000 database. I have read this article in the Books online but that does not seem to apply to us ( http://msdn2.microsoft.com/en-us/library/ms175496.aspx ).

OLE DB provider "SQLNCLI" for linked server "SQLPRD-DBS" returned message "Communication link failure".

Msg 233, Level 16, State 1, Line 0

Named Pipes Provider: No process is on the other end of the pipe.

Msg 18456, Level 14, State 1, Line 0

Login failed for user 'sa1'.

Anyone have any ideas?

Thanks!!!

could be a permission problem with the linked server login

make use of the useslelf option of the addsqlserver login

to properly map users to the linked server

sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'

[ , [ @.useself = ] 'useself' ]

[ , [ @.locallogin = ] 'locallogin' ]

[ , [ @.rmtuser = ] 'rmtuser' ]

[ , [ @.rmtpassword = ] 'rmtpassword' ]

Arguments

[ @.rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @.useself = ] 'useself'

Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE.

A value of true specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @.locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @.rmtuser = ] 'rmtuser'

Is the user name used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

[ @.rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

examples

A. Connecting all local logins to the linked server by using their own user credentials

The following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts'

B. Connects a specific login to the linked server by using different user credentials

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts

by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

Linked Server loses login-mapping

I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.

Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...

"Access to the remote server is denied because no login-mapping exists."

I am confused with the error message as it happens only after some successful insertions....

Any help is apprecaited...

Thanks

Please refer to BOL. A couple of links that hopefully will help you to get started:

· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx

· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx

If you have additional questions we will be glad to help.

-Raul Garcia

SDE/T

SQL Server Engine

Linked Server loses login-mapping

I use linked serve with (Security :be made with specific security conterxt) to connect to my production server from a Data Archive server.

Stored Procedures loop throught the prodcution data to insert records into Archive tables...Howerver after few hundreds of iteration I get the following error...

"Access to the remote server is denied because no login-mapping exists."

I am confused with the error message as it happens only after some successful insertions....

Any help is apprecaited...

Thanks

Please refer to BOL. A couple of links that hopefully will help you to get started:

· sp_addlinkedsrvlogin http://msdn2.microsoft.com/en-us/library/ms189811(SQL.90).aspx

· Distributed Queries Stored Procedures http://msdn2.microsoft.com/en-us/library/ms174359(SQL.90).aspx

If you have additional questions we will be glad to help.

-Raul Garcia

SDE/T

SQL Server Engine

Linked Server and Alias

I have a production sever and a development server with Identical db's
(two physically seperate servers)
I also have a production reporting services box, and a development
reporting services box. Currently I archive data from production DB
to the prodcution RS server, so that the prod RS is our data
warehouse. We use views to point to our data on the reporting box
such as DBname.dbo.tablename from a central DataWarehouse database
while the actual data is in seperate db's depending on where it was
archived from
I want to be able to run reports from our development RS server
pointing to the development DB, not the development RS (I dont want to
keep a development copy of our entire data warehouse)
Can I create a linked server / alias combination so that on the
development RS box "DBname.dbo.tablename" would actually point to
DVServer.someotherDBName.dbo.tablename?
Can I do it without having to create a synoym for each object?
Thanks
> Can I do it without having to create a synoym for each object?
I have been asking for synonyms for other tiers of objects (e.g. databases,
servers) for some time. If you feel strongly about this, please vote:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288421
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311079

Linked Server and Alias

I have a production sever and a development server with Identical db's
(two physically seperate servers)
I also have a production reporting services box, and a development
reporting services box. Currently I archive data from production DB
to the prodcution RS server, so that the prod RS is our data
warehouse. We use views to point to our data on the reporting box
such as DBname.dbo.tablename from a central DataWarehouse database
while the actual data is in seperate db's depending on where it was
archived from
I want to be able to run reports from our development RS server
pointing to the development DB, not the development RS (I dont want to
keep a development copy of our entire data warehouse)
Can I create a linked server / alias combination so that on the
development RS box "DBname.dbo.tablename" would actually point to
DVServer.someotherDBName.dbo.tablename?
Can I do it without having to create a synoym for each object?
Thanks> Can I do it without having to create a synoym for each object?
I have been asking for synonyms for other tiers of objects (e.g. databases,
servers) for some time. If you feel strongly about this, please vote:
https://connect.microsoft.com/SQLSe...=2884
21
https://connect.microsoft.com/SQLSe...=3110
79

Linked Server and Alias

I have a production sever and a development server with Identical db's
(two physically seperate servers)
I also have a production reporting services box, and a development
reporting services box. Currently I archive data from production DB
to the prodcution RS server, so that the prod RS is our data
warehouse. We use views to point to our data on the reporting box
such as DBname.dbo.tablename from a central DataWarehouse database
while the actual data is in seperate db's depending on where it was
archived from
I want to be able to run reports from our development RS server
pointing to the development DB, not the development RS (I dont want to
keep a development copy of our entire data warehouse)
Can I create a linked server / alias combination so that on the
development RS box "DBname.dbo.tablename" would actually point to
DVServer.someotherDBName.dbo.tablename?
Can I do it without having to create a synoym for each object?
Thanks> Can I do it without having to create a synoym for each object?
I have been asking for synonyms for other tiers of objects (e.g. databases,
servers) for some time. If you feel strongly about this, please vote:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288421
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311079

Linked Server / "nt authority" error

The company just got a new SQL Server. *finally*, we now have a dev server
and a production server. But apparently, this huge multinational
corporation has no one that knows how to set one up.
We're trying to set each of them up with a link to the other via
"sp_AddLinkedServer"
Running the stored procedure to configure the link worked, and now, when
using EM to connect to HorSql02, we can see the link for HorSql01, but when
clicking on "tables" or "views" under the link in EM, everyone is getting
the same message:
Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
I can't understand why this is happening, since we all connect using Windows
Authentication, we all have (at least in the dev group) dbo permissions on
HorSql01, but this error appears to everyone that attempts to connect to
HorSql01 through the link inside HorSql02.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSenseiMike,
You have to map logins between both servers. See sp_addlinkedsrvlogin and
"Security Account Delegation" in BOL.
AMB
"Mike Labosh" wrote:

> The company just got a new SQL Server. *finally*, we now have a dev serve
r
> and a production server. But apparently, this huge multinational
> corporation has no one that knows how to set one up.
> We're trying to set each of them up with a link to the other via
> "sp_AddLinkedServer"
> Running the stored procedure to configure the link worked, and now, when
> using EM to connect to HorSql02, we can see the link for HorSql01, but whe
n
> clicking on "tables" or "views" under the link in EM, everyone is getting
> the same message:
> Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
> I can't understand why this is happening, since we all connect using Windo
ws
> Authentication, we all have (at least in the dev group) dbo permissions on
> HorSql01, but this error appears to everyone that attempts to connect to
> HorSql01 through the link inside HorSql02.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>