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.