WARNING: While Linked Servers are an integral part of letting SQL (and other) Servers communicate with each other, casual use of them or incorrectly setting them up can lead to MAJOR security vulnerabilities.  

In this post, we’ll look at setting up Linked Server definitions to let two (or more) servers in an AlwaysOn Availability Group talk to each other and check up on job statuses of jobs running on each other. The idea, though, is that once SERVERA can see and query data on SERVERB (and/or vice versa), then we can set up a regularly scheduled SQL Server Agent Job that can run on one of the servers (we’ll establish how to determine this in a subsequent post) and then ensure that all SQL Server Agent Jobs specified on SERVERA are exactly mirrored or duplicated/synchronized on SERVERB (or vice versa).

Part 1: Introduction
Part 2: Putting AlwaysOn into Context
Part 3: Defining Batch Jobs
Part 4: Synchronizing Server-Level Details
Part 5: Setting Up Failover Alerts
Part 6: High-Level Options and Rationale for Handling Batch Jobs
Part 7: Detecting Primary Replica Ownership
Part 8: Dynamic Detection Gotcha #1
Part 9: Dynamic Detection Gotcha #2
Part 10: Dynamic Detection Gotcha #3
Part 11: A Fork in the Road
Part 12: Issues with Enabling and Disabling Batch Jobs
Part 13: Creating and Populating a Batch Job State Table
Part 14: Establishing Synchronization Checks

This won’t be the same as ensuring or enforcing that Jobs HAVE to be changed on both servers at the same time, but it will help make it a lot easier to avoid situations or scenarios where an admin might come out, tweak the schedule or details of a particular job on the ‘Primary’ server, and then forget to make the same changes on the Secondary Server—because having a regularly scheduled job that can run ‘checkups’ on synchronization of job details will, at least, then alert everyone that there’s a synchronization problem/concern in scenarios where a job is only changed on one server and not the other.

A Note on Using Aliases for Linked Servers

In this, and subsequent, posts I’ll be using a simple abstraction or alias that helps make code a bit easier to maintain when it comes time to query objects on remote servers. The idea is that instead of having SERVERA have a linked server definition to SERVERB where it calls the server by name (i.e., a Linked Server called ‘ServerB’), we’ll end up using an alias or abstraction (PARTNER) that will help make code easier to maintain. Or, in other words, SERVERA will have a definition for a linked server ‘connection’ to SERVERB, but will call it 'PARTNER' and SERVERB will, in turn, have a connection back to SERVERA and will call that' PARTNER', as well. And the idea here is that we can then write code to deploy on both SERVERA and SERVERB that references code on 'PARTNER'—instead of having to hard-code our logic/code to access the actual machine name.

So, for example, if we wanted to query data in/against our partner server’s msdb database, the fully qualified name for that table/data would be something like the following:

-- Code run on SERVER_A:SELECT job_id, name FROM PARTNER.msdb.dbo.sysjobs;-- Code run on SERVER_B:SELECT job_id, name FROM PARTNER.msdb.dbo.sysjobs;

Whereas, if we’re not using this aliasing technique or abstraction, we’d have to have code that looked like the following:

-- Code run on SERVER_A:SELECT job_id, name FROM SERVERB.msdb.dbo.sysjobs;-- Code run on SERVER_B:SELECT job_id, name FROM SERVERA.msdb.dbo.sysjobs;

Obviously, that’s not a huge change—but it does simplify things a tiny bit. Sadly, though, this approach doesn’t work when you have 3 or more servers (meaning you’ll have to account for hard-coding server names or some other nomenclature into your code).  

Setting-Up Linked Servers

Otherwise, assuming you’re dealing with a 2-node system, you should be able to easily create Linked Servers (called 'Partner') on both of your servers by running the following code:

USE [master];GOEXEC master.dbo.sp_addlinkedserver @server = N'PARTNER', @srvproduct = N'', @provider = N'SQLNCLI', @datasrc = N'tcp:OTHER_SERVER_NAME_HERE', @catalog = N'master';EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PARTNER',@useself = N'True',@locallogin = NULL,@rmtuser = NULL,@rmtpassword = NULL;GOEXEC master.dbo.sp_serveroption @server = N'PARTNER', @optname = N'rpc', @optvalue = N'true';GOEXEC master.dbo.sp_serveroption @server = N'PARTNER', @optname = N'rpc out', @optvalue = N'true';GO

Where, obviously, you’ll have to replace the “OTHER_SERVER_NAME_HERE” place-holder with the names of your actual servers when running the code above on each server (e.g., on SERVER1 you’d specify ‘SERVER2’ and/or on NNN02 you’d specify NNN01, and so on).

NOTE too, that the above scripts enable RPC and RPC out—meaning that they’ll allow the local server to make RPC (Remote Procedure Calls) against the remote server—such that we can run/execute stored procedures on the remote server as needed. Granted, this IS a security concern—so, the question of who or what will be able to use this functionality becomes our next concern.

Reviewing the Security Configuration for your Linked Servers

If you crack open the Server Objects > Linked Servers node on your servers after running the code above, you’ll see the definition for a new Linked Server called Partner:

Figure 1: Definition for a new Linked Server called Partner

Furthermore, if you right click on PARTNER and select Properties, then switch to the Security tab, you’ll see a configuration that looks as follows:

Figure 2: Linked Server Properties

Where it’s important to call out that the highlighted option is the SECURE option, that you’ll want to use by default. (And that you’ll only want to change/modify when/if you a) absolutely have to and b) FULLY understand what you’re doing in terms of security implications.)

In short, the 'Be made using the login’s current security context' means that if I’m logged in as 'sa' on SERVERA, I’ll have that same context over on SERVERB when I log in there. Likewise, if I’m logged in as DOMAINMike on SERVERA, and connect to SERVERB, I’ll then be logging in as DOMAINMike (provided Kerberos is working correctly). The idea, then, is that I should ‘carry’ whatever permissions I have on the first server over to the second, and so on. But the assumption is that I already HAVE the same permissions on both servers. If I didn’t or don’t, then I might be able to CREATE the linked server—but won’t be able to connect. More importantly, this also means that logins that don’t have significant permissions on SERVERA won’t be able to ELEVATE permissions when connecting to SERVERB.

Unfortunately, however, I commonly see Linked Servers setup where the last option is picked (‘Be made using this security context’) where ‘sa’ + password is specified—meaning that ANYONE or ANYTHING that can connect to SERVERA (even as a lowly user with mere ‘public’ access to only a blank or inconsequential database) will end up being sysadmin (full permissions) against SERVERB because the linked server was set up COMPLETELY wrong/stupidly.

Again, don’t mess around with these options unless you fully know what you’re doing. The setup/script I’ve provided above is safe. Whatever permissions you have on SERVERA remain as-is; likewise, whatever permissions (or lack thereof) you have on SERVERB also remain as-is. All the script I’ve provided above does is ‘link’ the two servers and let you run code/queries from one server to another—assuming you’ve got sufficient permissions.

Some Notes on Kerberos and More Complex Security Needs

Of course, YOU might have sufficient permission to do quite a bit on Servers A and B. But your job will end up running under the context of the SQL Server Agent. So, you’ll need to make sure that it has sufficient permissions on both servers as well. Or, more specifically, you’ll potentially want to have your SQL Server Agent account run as a LEAST PRIVILEGE domain account (i.e., an account/login created at the domain level that is NOT part of any group/role and which is being used to run the SQL Server Agent on BOTH servers in your Availability Group). Of course, in some security circles, running something like the SQL Server Agent on multiple machines under the same account would be a worst practice (i.e., you might need/want a SEPARATE account on each server)—at which point you’re going to need to somehow let the SQL Server Agent talk to the msdb database on remote/linked servers as well.

Then, there’s also the fact that Kerberos sometimes doesn’t always work perfect out of the box.

All of which is to say, that getting this stuff to work as expected, isn’t always as trivial as merely setting up a linked server and hoping that contexts work transparently.

To check and see whether things look like they’ll work correctly (at least for you and your security context), expand the ‘PARTNER’ node and make sure you can drill all the way down into the System Catalogs > msdb database and see the msdb ‘system’ tables on the remote server as follows:

Figure 3: Remote Server View

And, of course, make sure you do this on/from both (all) servers where you’ve set up Linked Servers. (You might be surprised but it’s not that uncommon for ONE server to be able to see the other server just fine, but not have that same configuration reciprocated.)

If you’re not able to see into the msdb, then it’s either because your current account doesn’t have sufficient permissions (not too likely if you’re setting up linked servers), or because Kerberos or something else is busted. To troubleshoot you might want to try the following links:

The Biggest Mistake: Service Principal Names
Register a Server Principal Name for Kerberos Connections

And, if the articles above don't work, I’d recommend the idea of buying a couple of chickens from Costco, and sacrificing them to whatever being you worship in hopes that something else will come about—as troubleshooting Kerberos issues is one of the ugliest and most painful things you can do when it comes to SQL Server (simply because EVERYTHING has to be perfectly aligned and any slight slipup or hiccup with SPNs (even in terms of the order in which they’re defined) can break Kerberos entirely).

Otherwise, if you simply can’t get Kerberos to work for you (or, frankly – later on: your SQL Server Agents(s)), then you might want/need to look at another approach. This is a more advanced approach—and it’s NOT the only approach/technique you could use here, but it's the safest (which is why I’m outlining it here). In effect, what you’ll be doing is creating a specialized, one-off, least privilege, account for your Job Synchronization Checkups—and then running those jobs via SQL Server Agent Proxies. To do this:

At this point though (i.e., once you’ve enabled a way for the servers to talk to each other), you’ll be ready to set up some regular jobs/logic to check on and verify that jobs are synchronized as needed, which we’ll tackle in the next post.