A common administrative task that you need to deal with when managing an Always On Availability Group solution is to recreate SQL Server logins on all replicas. That’s just a fact. Because at any given point in time, an automatic failover can happen and move the Availability group to a different replica. If you don’t have the same SQL Server login across all replicas, you’ll have issues with application authentication.
You’ve probably seen the Microsoft KB article 918992 (How to transfer the logins and the passwords between instances of SQL Server). Or the Copy-DbaLogin PowerShell cmdlet from dbatools. Or the Transfer Logins Task in SSIS. Or the SQL Server Management Studio Availability Group Add-in from SQLSkills. They’re all great tools in and of themselves. And automating them is even better.
But…
Most of the resources available online talk about the inconsistencies with the SID values across Availability Group replicas and how to address those. Sure, you want to synchronize the SQL Server logins across Availability Group replicas in a way that works. But the one thing that is rarely covered is the security implications of doing so. Let me explain.
The Property Owner and the Apartment Building Manager
Let’s say you own an apartment building and you hired someone to manage the individual units. In order for the manager to get into the apartment units and do maintenance, you give him the keys to the doors. With the keys, the manager can easily get in and out of the apartment units. Every unit has its own key. The fastest way to give the manager the set of keys is to have them duplicated. Sounds familiar?
This is exactly what these tools focus on – duplicating the keys a.k.a. the SQL Server logins. When you duplicate the keys, you know that whoever holds them can access every apartment unit. Besides, that’s what the manager is supposed to do. And it’s a great way to make sure that the manager can do exactly what he was hired to do.
Until the manager is no longer allowed to. Maybe he or she decides to pursue a different career and no longer wants to manage apartment units. How do you deal with the keys afterwards? Do you just take one of the keys? Or do you take all of them? How do you know you’ve taken all of them?
The Missing Piece
And that’s the problem with most of the how-to instructions about synchronizing SQL Server logins across all replicas. They assume that all you have to do is create the login and synchronize it across replicas. They fail to look at the login throughout the application’s lifecycle. And, no, just like the apartment building manager, that SQL Server login isn’t going to be there forever. Maybe that login decides it doesn’t want to be with SQL Server anymore and moves to Oracle (I haven’t seen anyone do that before).
I learned this the hard way more than a decade ago. I was managing a vendor application that needed sysadmin privileges to access SQL Server. That in itself was already a red flag for me. I pushed really hard to reduce the permissions for the login. It took some time before the vendor finally gave in. But not without severe damages.
The vendor was using the login to take database backups behind our backs, using their app. They would copy the database backup and restore it on a development environment. If you think that’s no big deal, then you haven’t dealt with personally identifiable information (PII) just yet. We don’t just take a copy of the data in production and move it around. We follow strict security procedures to do so. Not only did the vendor violate a security requirement, they also messed with the database’s recovery objective by breaking the log sequence chain of the log backups. And I was in trouble for not knowing. Because I was supposed to be taking care of the databases. If that happened today, I would have lost my job if that caused a major security breach.
That’s why I am against automating synchronization of SQL Server logins across Availability Group replicas. Don’t get me wrong. I love automation – anything that can help me NOT to do repetitive and boring tasks. But when we’re simply automating without taking into account the security implications of the automated tasks, we are making ourselves the source of vulnerability. Security, performance, and availability (S.P.A.) should be a priority of every database administrator. And that includes dealing with SQL Server logins when replicated across all Availability Group replicas.
It’s not enough to simply recreate the SQL Server logins using the tools mentioned above. A proper change management process should define how the SQL Server logins need to be managed throughout the application’s lifecycle. A security officer needs to evaluate the permissions and risks before creating and synchronizing the login. Even when the login needs additional privileges, a security review should be conducted. And when the time comes to remove the login, your change management process should verify that it gets deleted on all Availability Group replicas.
Don’t just rely on regular security audits. Proactive security should be a part of your operational processes – including synchronizing SQL Server logins on all Availability Group replicas.
Additional Resources
- Defense In Depth and Why Proactive Security Should Be Included In Every High Availability Strategy
- Regularly Treat Your Databases to a S.P.A.
- Change Management: The Little-Known Secret In Operational Efficiency
Schedule a Call
