error creating a linked server from sql server 2005 to 2000

Question

Post reply

error creating a linked server from sql server 2005 to 2000

RP_DBA

SSCertifiable

Points: 5116
More actions
December 3, 2009 at 8:21 am

#385883

Ok, I'm trying to create a linked server connection going from a 2005 to a 2000 server. This is what I'm doing
exec sp_addlinkedserver 'svrname', 'SQL Server'
go
exec sp_addlinkedsrvlogin
@rmtsrvname = 'svrname'
,@useself = 'false'
,@locallogin = 'sql2005login'
,@rmtuser = 'sql2000login'
,@rmtpassword = 'pwd'
go
and here's what I'm getting
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sql2000login' mapped to local user 'sql2005login' from the remote server 'svrname'.
any ideas?
_____________________________________________________________________
- Nate
@nate_hughes

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Do you have the SQL login -- 'sql2005login' at both the Instances?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

RP_DBA SSCertifiable Points: 5116 More actions · Answer 2

It's actually a Windows account 'domain\sql2005login' but yes, it's on both instances.

_____________________________________________________________________
- Nate

@nate_hughes

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 3

Try it with "@locallogin = NULL" and let me know if it helps!

My blog

RP_DBA SSCertifiable Points: 5116 More actions · Answer 4

ok, changed to

exec sp_addlinkedsrvlogin

@rmtsrvname = 'targetsrvname'

,@useself = 'true'

,@locallogin = NULL

,@rmtuser = 'sql2000login'

,@rmtpassword = 'pwd'

and here's what I'm getting

OLE DB provider "SQLNCLI" for linked server "targetsrvname" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

_____________________________________________________________________
- Nate

@nate_hughes

Lowell SSC Guru Points: 323518 More actions · Answer 5

RP this is the syntax i've always used,a dn works without any problems for me:

here i'm connecting to a SQL instance named "DBSQL2K5", but for fun, I'm aliasing it as MyLinkedServer; they can both be the same name, of course.

--#################################################################################################

--Linked server Syntax for SQL Server With Alias

--#################################################################################################

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'DBSQL2K5', @provider = N'SQLOLEDB';

-- Add Default Login (if applicable)

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',

@useself = N'False',

@locallogin = NULL,

@rmtuser = N'sa',

@rmtpassword = 'NotARealPassword';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RP_DBA SSCertifiable Points: 5116 More actions · Answer 6

Nope, same thing.

Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sql2000login' mapped to local user '(null)' from the remote server 'targetsrvname'.

this server was recently upgraded (new server w/ fresh install) and then the master db was re-attached, could this have anything to do with it?

_____________________________________________________________________
- Nate

@nate_hughes

Lowell SSC Guru Points: 323518 More actions · Answer 7

can you connect with SSMS to the server 'targetsvrname' as the user sql2000login?

if the server was rebuilt, the login sql2000login might not exist and need to be re-added.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

RP_DBA SSCertifiable Points: 5116 More actions · Answer 8

Yep, login works. it was the 2005 box (source) that was upgraded.

_____________________________________________________________________
- Nate

@nate_hughes

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 9

I notice that you changed the @useself parameter to 'true'. It should be 'false'

,@useself = 'true'

Change it and try again.

My blog

RP_DBA SSCertifiable Points: 5116 More actions · Answer 10

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'targetsrvname',

@useself = N'False',

@locallogin = NULL,

@rmtuser = N'sql2000user',

@rmtpassword = 'pwd';

on execute generates

Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sql2000user' mapped to local user '(null)' from the remote server 'targetsrvname'.

_____________________________________________________________________
- Nate

@nate_hughes