asked on

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim P.

ASKER

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

Guy Hengel [angelIII / a3]

glad I could help :)