Press "Enter" to skip to content

Category: Security

An Overview of CLR

Published 2023-05-24 by Kevin Feasel

Chad Callihan gives us the primer:

I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a vague error in the application and still didn’t know the exact cause. I fired up an XEvents session to find the query causing the issue and found a query failing with the following error:

Msg 6263, Level 16, State 1, Procedure dbo.MyProc, Line 60 [Batch Start Line 14]
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.

Incidentally, my single biggest gripe around CLR integration in SQL Server (and something I complain about almost every time someone brings up the feature) is that they really messed up in picking names. CLR has Safe and Unsafe modes.

What the terms mean:

  • Safe = managed mode. Your .NET language of choice (at the time, VB.Net or C#) handles pointers for you, so you can’t dereference a null pointer. Well, you still can but will instead get an “Object reference not set to an instance of an object” exception rather than some potentially unexpected behavior
  • Unsafe = developers control pointers. There were some things, especially in 2005, that made sense to create in Unsafe mode because it was considerably faster

What far too many DBAs interpreted it as: “I don’t want unsafe code running in my systems! Ban CLR!”

This fundamental misunderstanding of terms killed a smart integration and capability for extremely fast functions—for example, the fastest way to split a string in T-SQL is via CLR, and there’s a lot of impressive functionality you can get at near-native speeds (i.e., if the SQL Server team wrote the code as a function instead of you, allowing them to optimize the database engine for that function) with a few lines of code. But the leads at most places saw the word “Unsafe” and nope’d out.

Comments closed

Backup Encryption Performance

Published 2023-05-22 by Kevin Feasel

Matthew McGiffen runs some tests:

Unlike TDE, there is some extra CPU overhead when you take an encrypted backup as the data has to be encrypted before being written to disk – whereas with TDE the data is already encrypted. Backup times however are unlikely to be affected significantly as the bottleneck is usually going to be the time it takes to physically write the data to disk. The CPU processing should take a fraction of that time.

Matthew’s tests are on a ~9GB database, which is large enough to estimate differences without having us wait all day to compare.

Comments closed

Managing Database Privileges in Postgres

Published 2023-05-19 by Kevin Feasel

Ryan Booz limits database access:

We discussed how this can be done manually with a GRANT command each time an object is created, however, that is time consuming to manage and easy to miss a detail.

Instead, PostgreSQL provides a method for setting default privileges which are granted on behalf of the object owner as database objects are created. Using default privileges, a role can prepare the database ahead of time to ensure that consistent access privileges are applied while easing the management burden over time.

But how do you go about creating a set of roles and default privileges that will provide the right level of control and access? Let’s dig a little deeper.

RBAC on groups is definitely the way to go, and Ryan shows us how.

Comments closed

Encrypting SQL Server Backups

Published 2023-05-18 by Kevin Feasel

Matthew McGiffen lays out the requirements:

When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.

From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.

Click through for a primer on the topic.

Comments closed

TDE and Database Backups

Published 2023-05-16 by Kevin Feasel

Matthew McGiffen shares some advice:

Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.

Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.

Comments closed

Updates to Always Encrypted since 2016

Published 2023-05-10 by Kevin Feasel

Aaron Bertrand notes some changes:

In 2015, during the SQL Server 2016 beta, I explored a new feature is this article, Always Encrypted. This feature finally allowed us to encrypt data at rest and on the wire, and I showed how beneficial this was and how much more secure your data could be. I also explained that, as a new feature, some limitations made it difficult to use and, sometimes, impossible to adopt.

Several major versions of SQL Server later, how has this feature evolved, and is it easier to use today?

Read on for the answer. Aaron also covers secure enclaves, a big topic for Always Encrypted users.

Comments closed

Performance Overhead of TDE

Published 2023-05-10 by Kevin Feasel

Matthew McGiffen answers an age-old question:

Microsoft states that enabling TDE usually has a performance overhead of 2–4%. That doesn’t sound like very much, and personally I wouldn’t let it bother me if I want to make sure my data is encrypted at rest. However, you may have heard other sources saying that it’s actually a lot more than that – and the performance impact is a high price to pay for the level of protection offered. So, what’s the truth?

It turns out the answer is a bit more complex than simply saying “x%,” though as a first approximation, I’d still say that the 2-4% is a good starting point. For what would move you off of that 2-4%, read the whole thing.

Comments closed

Connecting Power BI to ADX via Private Endpoint

Published 2023-05-09 by Kevin Feasel

Dany Hoter keeps it all on the Azure backbone:

The PBI developer creating datasets and reports need to connect to the ADX cluster using Power BI desktop.

To establish such a connection, the user’s IP address should be allowed access to the private end point.

The access should be tested using Kusto Web explorer (KWE) to make sure that the cluster can be reached.

If KWE can connect , Power BI desktop should also connect successfully and a report using the cluster in Direct Query or import can be created.

That’s the goal, and Dany shows us the way to do it.

Comments closed

Scanning for Startup Procedures in SQL Server

Published 2023-05-08 by Kevin Feasel

Steve Steadman reminds us of a SQL Server feature:

The Scan For Startup Procs feature in SQL Server allows you to specify a list of stored procedures that will be automatically executed whenever the database engine starts. This can be useful in certain scenarios, such as when you want to perform tasks such as restoring a database or performing maintenance tasks when the database engine starts.

“Scan for startup procs” is a configuration option in Microsoft SQL Server that determines whether the server should scan for and execute stored procedures that are marked as “startup procedures” when the server starts up.

I’ve used this to good effect in the past, but there is a fundamental problem with this approach: it’s easy to forget about these, potentially leading to a difficult search for why some action took place. If you only let sysadmins add or change startup stored procedures, then I’d consider this just as little a security risk as xp_cmdshell: if the attacker already has sysadmin, the attacker can simple enable the feature, so there’s no real value to denying yourself the capability if it makes sense in your environment.

Comments closed