SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

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 from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

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

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
SQL Change Automation

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

SQL Source Control

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

Featured Contents

 

Stairway to Data, Level 1: The Basics

Joe Celko from SQLServerCentral.com

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 »


 

Free eBook: SQL Server Transaction Log Management

Press Release from Redgate

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 »


 

Import data dynamically using R and Python in Power BI Desktop

Additional Articles from MSSQLTips.com

Learn how to import data into Power BI using either R or Python scripts. More »


 

How SQL Provision completes the Database DevOps story

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 »


 

From the SQLServerCentral Blogs - Using Docker to run Integration Tests for dbachecks

Rob Sewell from SQLServerCentral Blogs

My wonderful friend André Kamman wrote a fantastic blog post this week SQL Server Container Instances via Cloudshell about how... More »


 

From the SQLServerCentral Blogs - CLR Strict Security in SQL 2017

Wayne Sheffield from SQLServerCentral Blogs

Locking down CLR I recently ran into SQL 2017’s new CLR Strict Security. I was working with a client where we... More »

Question of the Day

Today's Question (by Steve Jones):

I run this code:

>>> North
['Ravens', 'Steelers', 'Browns', 'Bengals']
>>> North.sort() 

Now, what is the value of North?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Python.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Expert Scripting and Automation for SQL Server DBAs

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

Yesterday's Question (by Steve Jones):

How many indexes must a memory optimized table have?

Answer: 1

Explanation:

A memory-optimized table must have at least one index.

Ref: Indexes on Memory-Optimized Tables - click here


» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Development

Best way to track "errors" in Stored Procedure - Hi Guys, I need advice. I have almost 30 Update statements with different logic in one stored procedure. If I...

Which years are similar. - For fun and learning only. =================== =================== Which years are similar to a specific year. For example from which years can the calendar be...

What it means to atomize values in 1st normal form - Suppose we have the following table.  We have everything for the address packed into one cell.  Also, we have two...

Any video courses that teach Execution Plans? - Hi, I am  aware that  Redgate  has a free  ebook  that  explains  execution  plans.  I was wondering  if there were  video ...

Storage of "Do Not Show This Again" Flags - More of a generic theortical question rather than coding... For our application, we now want to introduce the concept of "Do...

When does SQL decide to load all of an index in to memory? - We're having a bit of a performance issue with one of our tables, which seems to be behaving in a...

Question about installation - Hi, I installed the development copy of SQL Server on my Surface Pro (I have the latest one), and I installed...


SQL Server 2016 : SQL Server 2016 - Administration

upgrade to 2016 causing performance problems - Hi all Experts, We have strange problem (at least for our case), we have database in 2012 which was restored...

Backup and log - Hello, since 2 years, I manage a database with a lot of transactions Recovery mode is Full Full backup once a week ...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

MySQL - Hello Community, It is my opinion that I have some SQL and SQLserver skills. Apart from the SQL, I have no...

fetch rows when condition macthes - Hi I have a scenario where a table has column which is either Error or Info or warning.  I need to fetch...

Sort already comma separated list - Hi I have values like this in a column which basically is the exact same thing if sorted . ORM;


SQL Server 2014 : Administration - SQL Server 2014

Error Joining Databases to an AG - Twice now I have received an error joining a database to an AG.  I take a full backup and a...

ODBC Driver 17 for SQL Server - Linked Server failure - I have a problem where my linked server works for about 12 minutes and then fails.  Sorry I do not...

Copy Backup File - Hi Experts, We have backup job taken every 20 mins(trn) every 1 hour(diff) and weekly full. These backups are taken to...


SQL Server 2014 : Development - SQL Server 2014

Tuning Query In Stored Procedure - Below table contains five millions of records. My Table Structure CREATE TABLE .(   (50) NOT NULL,   (2) NOT NULL,  ...


SQL Server 2012 : SQL Server 2012 - T-SQL

findeing matching names from one database to another - Is there a way of writing a query that lists only matching names from one database to another both on...

Question on Grouping -Working number of days - Hi All, I am trying to work out the number of days for each (CODE) from the Code (REQUEST DATE) till...


SQL Server 2008 : SQL Server 2008 - General

Case in a where clause? - Hi Can someone let me know what I;m doing wrong here? I thought this was correct but keep getting a syntax error Thanks WHERE([Admission...


SQL Server 2008 : SQL Server 2008 Administration

Activity Monitor not starting, always being paused - I am running SQL2008 Server Standard edition on Server 2008 x64. When I try to run the Activity Monitor in...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
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.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com