SQL Server Linked Server Best Practices - Step by Step

Question

Hello and thanks for stopping by. I'm an accidental DBA looking for some guidance in creating Linked Servers the correct way. MS provides complete descriptions of the datatype and descriptions of all of the various parameters for sp_addlinkedserver, sp_addlinkedsrvlogin and sp_serveroption but no guidance on HOW to align the various options as Best Practices for a given situation.

I have examples from the other DBA's who simply used the 'sa' password but my research indicates I should be using bespoke logins tailored to their Linked Server use. The Problem is I'm so far unable to find the right combination and sequence (order of ops) to correctly create all of the parts and pieces resulting in a Linked Server that allows limited communication between two servers.

Goal: Create a Linked Server between a Source server that will allow a job step from the source server to check certain conditions and if TRUE, invoke sp_start_job on Destination server. ...and nothing more.

On advice, I've created two SQL Auth Logins of the same name/pw on both Source and Destination, both with limited 'public' permissions.

I've created Linked Servers attempting to map the local login to the remote login (thinking if I got that far, I'd carefully tinker with the permissions of the Destination login to find the permission to allow it to exec sp_start_job).

But so far, my only reward has been a series of failure notices of various types.

There are a TON of online documents explaining what each various proc/param does but I'm having a difficult time finding some sort of over-view explaining how combinations of procs/params lead to different desired outcomes.

I'm hoping for some useful advice, reference to some 'yet to be discovered' tutorial or maybe even a Step by Step instruction on how to achieve my goal and develop a little self respect. (so far, this task has done nothing but bruise my ego!)

Thank you for your time.

Answer 1

I would start by using Windows Auth and @useself='True', which will use the source server's SQL Server Agent Service Account to make the remote connection. If you get a login failure, look in the target server's SQL Server Error Log to determine what Windows principal to create a login for on the target server. So the linked server definitaion would be something like:

USE [master]
GO

EXEC sp_addlinkedserver @server = N'TargetServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'targetserver.mydomain.com'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'TargetServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'data access', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC sp_serveroption @server=N'TargetServer', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO

Impersonating the caller is more complicated with remote connections to the SQL Server where the Linked Server is defined, as you have to properly configure Kerberos Constrained Delegation. But when used from a SQL Agent Job, it's not a "double hop" and so is a really simple configuration.