[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1575
  • Last Modified:

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

0
Jim P.
Asked:
Jim P.
  • 2
1 Solution
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now