please help adding a linked server

Question

please help adding a linked server

ericb1

SSCrazy

Points: 2354
More actions
April 26, 2012 at 8:22 am

#257113

Any help is greatly appreciated. I'm going from a SQL2008, trying to create a linked server to a remote SQL2005 server, with SQL authentication username/password. I can log in to the server manually, so I know the username/pwd is correct.
I'm trying to create a stored procedure, using this:
EXEC sp_addlinkedserver @server='SERVERNAME',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='192.168.xx.xx',
@provstr = 'DRIVER={SQL Server};SERVER=XXXXXXX;UID=XXXXXX;PWD=XXXXXXXX';
This creates the linked server, but doesn't add the option "Be made using this security context" with the UID and PWD. So then I tried adding this:
EXEC sp_addlinkedsrvlogin 'SERVERNAME', 'false', NULL, 'XXXXXX', 'XXXXXXX'
And that does add the context to it, but I can't use the linked server, it gives me a error of "Login Failed. Invaid Connection String attribute".
So I'm still doing something wrong, any help is greatly appreciated, thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 1

ericb1 (4/26/2012)
Any help is greatly appreciated. I'm going from a SQL2008, trying to create a linked server to a remote SQL2005 server, with SQL authentication username/password. I can log in to the server manually, so I know the username/pwd is correct.
I'm trying to create a stored procedure, using this:
EXEC sp_addlinkedserver @server='SERVERNAME',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='192.168.xx.xx',
@provstr = 'DRIVER={SQL Server};SERVER=XXXXXXX;UID=XXXXXX;PWD=XXXXXXXX';
This creates the linked server, but doesn't add the option "Be made using this security context" with the UID and PWD. So then I tried adding this:
EXEC sp_addlinkedsrvlogin 'SERVERNAME', 'false', NULL, 'XXXXXX', 'XXXXXXX'
And that does add the context to it, but I can't use the linked server, it gives me a error of "Login Failed. Invaid Connection String attribute".
So I'm still doing something wrong, any help is greatly appreciated, thanks!

Parameters are around the wrong way, maybe?

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'

[ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]

[ , [ @locallogin = ] 'locallogin' ]

[ , [ @rmtuser = ] 'rmtuser' ]

[ , [ @rmtpassword = ] 'rmtpassword' ]

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MIA-SQL1', @locallogin =NULL,

@useself = N'False', @rmtuser =N'RemoteUserName', @rmtpassword=N'Pa$$w0rd'

You have:

EXEC sp_addlinkedsrvlogin 'SERVERNAME', 'false', NULL, 'XXXXXX', 'XXXXXXX'

Try or using parameter names:

EXEC sp_addlinkedsrvlogin 'SERVERNAME', NULL, 'false', 'XXXXXX', 'XXXXXXX'

Fitz

seth delconte SSCertifiable Points: 6388 More actions · Answer 2

The "Be made using the login's current security context" option means that the linked server will use the current Windows credentials, and log into SQL Server using Windows Authentication. You don't want/need this, since you are explicitly defining SQL Server login credentials in the provider string (your first example). Your second example won't log in correctly because you are trying to use Windows Authentication.

Check this link out:

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

_________________________________
seth delconte
http://sqlkeys.com

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 3

You may also try using GUI to see the options you didn't get here....

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 4

In GUI, "Be made..." options are used for logins not defined on above list. If you use a login present in list, then connection will be made using that else "Be made..." options will come into play.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

ericb1 SSCrazy Points: 2354 More actions · Answer 5

thanks for the replies! I'm trying to create the linked server as part of a stored procedure, that 1) creates the linked server 2) updates a table and then 3) removes the linked server, so it has to be done programitaccly, not with the gui

I'd like it to use the SQL credentials I supply, not use the current users security context. So either the option to "be made using this security context:" that allows for you to put in the username/password, or the remote server login mapping credentials. Whichever one would work for this instance.

thanks again!