4

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.

1 Answer 1

5

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.

3
  • Thanks David, I used your advice to create a Linked-Server, tested the connection and received 'NT AUTHORITY\ANONYMOUS LOGON' error. I queried sys.dm_exec_connections and observed auth_scheme=NTLM, I used setspn -L on the Svc account and identified several Production Servers listed in the output but not the Test server I was hoping to connect to. This led to a conversation with one of our SysAdmins who's researching now. Do you have any suggestions? What a great learning experience! Dec 26, 2019 at 19:58
  • Test the linked server from an RDP session or a SQL Ajent Job to avoid the Kerberos Double Hop. Dec 26, 2019 at 20:44
  • Thanks David. Using your guidance I found the answer. Our company will be reviewing a few policies including how we authenticate our service accounts. Dec 30, 2019 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.