sp_addlinkedserver using SQL Authentication

I have SQL Server 2005 Standard that I want to link SQL Servers across domains -- in other words windows authentication and named pipes are out. I'm doing this because I want to centralize some logging and reporting from many disparate SQL Server.

I'm trying the code below to do this -- but SQL keeps doing it at native client and gives me an error in the provider string when I test it. I'm trying to avoid having to build ODBC calls on all these machines.

Any suggestions?
GO
/****** Object:  LinkedServer [LOGSERVER]    Script Date: 12/16/2009 09:31:21 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LogServer', @srvproduct=N'192.x.x.x', @provider=N'SQLOLEDB',
	 @datasrc=N'192.x.x.x', @provstr=N'Provider=SQLOLEDB;Server=192.x.x.x;UID=UserName;PWD=*******',
	 @catalog=N'Logging'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LogServer',@useself=N'False',
	@locallogin=NULL,@rmtuser=N'Username',@rmtpassword='*********'

Open in new window

LVL 38
Jim P.Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't have the sql servers under my hands right now, but according to the docs:
http://msdn.microsoft.com/en-us/library/aa259589%28SQL.80%29.aspx

EXEC master.dbo.sp_addlinkedserver @server = N'LogServer', @provider=N'SQLOLEDB', @datasrc=N'192.x.x.x',  @catalog=N'Logging'

should do it?!
0
 
Jim P.Author Commented:
Had to add a @srvproduct as well. ;-) If I can just avoid the typo on the password in the future as well. :-(

Thanks a3!
EXEC master.dbo.sp_addlinkedserver @server = N'LogServer', @provider=N'SQLOLEDB',@srvproduct=N'192.x.x.x',
	 @datasrc=N'192.x.x.x',  @catalog=N'Logging'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad I could help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.