Complex Constrained Security

I was reading about Kerberos and authentication with SPNs recently. It’s a topic that seems to make sense and appears orderly, but when I’ve had issues with SPNs, it feels like voodoo and black magic sometimes might be needed to get things working. As I read through the document, trying to ensure I would learn a bit more about how impersonation and delegation work, I noticed this sentence:

“As a security best practice, Microsoft recommends constrained delegation over unconstrained delegation.”

That seems reasonable to me. We ought to limit where users can connect to specific systems to ensure good security. This makes perfect sense where we have systems like web servers or application servers and we should limit delegation to specific databases servers. This wouldn’t prevent all security breaches, but it would limit the scope of many.

The complexity comes when we start to have multiple servers that might connect to multiple back ends, especially as we grow our architectures to include additional HA nodes with Availabilty Groups. Tightly linking security complicates the configuration and requires that our sysadmins setup new machines and properly add new delegation targets as machines change. DevOps and configuration as code can help here with ensuring that we always add the required security changes to the right machines.

That still doesn’t make it easy to manage a tight security environment without lots of resources. As we rotate or retire machines, we need cleanup of the security settings that refer to these objects. If we rotate host machines, which is usually rare, we need to remember to update out configuration scripts to work with new machines and accounts. If we add nodes, we need additional lines in scripts. If we move to containers for database servers, this might require even more changes.

None of these items is complex, but when you must repeat them for many systems, many accounts, and on a semi-rare basis, they add some overhead that is both tedious and difficult to keep up with for a staff. This is especially true as staff turns over. Do you want to let the new people know that they need to make all these updates while handling their “normal work”? I could see all these details becoming a chore because we’re human, we’re flawed, and we make mistakes.

I like the idea of tighter security, but at a scale, at random times, in between all the other tasks we must complete, the tools and techniques we have don’t make this something that seems manageable. I don’t have solutions, but I think that we do need some better tools that ensure security can be both flexible and convenient, while enforcing the principle of least privilege. The management of systems at scale is helping (forcing?) companies rethink some security tools and features, but there is still work to be done to ensure our employees will correctly and consistently configure security.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.

4 Responses to Complex Constrained Security

  1. While all of this can be scripted, it’s not the easiest thing. This is one area of AD I wish they would greatly improve.

  2. way0utwest says:

    Ha! I hope never to have to configure this again.

    But if i do… I’ll call and be more likely to ask about the wife and kids.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.