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.
The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.
CI/CD for your SQL Server database
Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control. Try it free
How to track every change to your SQL Server database
See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more
A great deal of the confusion that occurs when a database application is developed comes from a poor understanding of the basics of data. Here, Joe Celko gives a broad coverage of the difficulties you're likely to meet when handling data in databases More »
When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. More »
Database DevOps is not without its challenges. When SQL Provision is introduced into the tool chain it can enable organizations to reduce the burden on network resources, minimize administrative tasks and accelerative database delivery updates. Tony Davis explains how.. More »
Automate your workload and manage more databases and instances with greater ease and efficiency by combining metadata-driven automation with powerful tools like PowerShell and SQL Server Agent. Automate your new instance-builds and use monitoring to drive ongoing automation, with the help of an inventory database and a management data warehouse. Get your copy from Amazon today.
Yesterday's Question of the Day
(by Steve Jones):
How many indexes must a memory optimized table have?
A memory-optimized table must have at least one index.
Ref: Indexes on Memory-Optimized Tables - click here
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.