Press "Enter" to skip to content

Category: Security

Security Concepts: Who? and Where?

Published 2023-07-14 by Kevin Feasel

Kenneth Fisher is trying to figure out where he left his keys:

I was having a conversation with some friends the other day and Jen McCown (blog|twitter) asked about SQL Server security references and “What’s something that’s really difficult in SQL Server Security.” As happens sometimes I started thinking about this in the back of my head and I realized something. The two absolute hardest things that people run into with security (at least in my ever so humble opinion) are

  • Who: or Who am I when I try to access a resource?
  • Where: or Where am I when I try to access a resource? And Where is that resource?

Read on for Kenneth’s thoughts.

Comments closed

Users and Role Members for Azure SQL Databases

Published 2023-07-13 by Kevin Feasel

Peter Schott makes a list:

I ran into a concern to quickly audit all current users and role members for a set of Azure SQL databases, spread across multiple resource groups. Without an easy CMS concept or a way to quickly loop through an unknown set of servers, resource groups, and databases, that can be a little challenging. I have an account to use that should have access to all databases (but doesn’t) so put together some PowerShell that I could run locally to get that information and send the results to Excel.

Click through for a SQL script to get the data and a Powershell script to run this for each database and export the results into different tabs in Excel.

Comments closed

VBS Enclaves for Always Encrypted in Azure SQL DB Elastic Pools

Published 2023-07-11 by Kevin Feasel

Pieter Vanhove makes an announcement:

A few months ago we announced the support for virtualization-based security (VBS) enclaves in Azure SQL Database. This announcement brings numerous advantages, including robust confidential queries and seamless cryptographic operations, to all Azure SQL Database offerings, independent from the underlying hardware. You can use the feature with any compute tier (provisioned or serverless), purchasing model (vCore or DTU), compute size and region that aligns with your workload needs. And, since VBS enclaves are available in existing hardware offerings, there is no additional cost.

In addition to this preview, we are excited to announce the preview of VBS enclaves in Azure SQL Database elastic pools!

Read on to learn more about how to enable enclaves and add a database to an elastic pool.

Comments closed

Finding Active Security Roles in Power BI

Published 2023-07-05 by Kevin Feasel

Marco Russo and Alberto Ferrari retrieve security roles:

Security roles automatically restrict the data visible in a Tabular model. However, for a more personalized user experience, report authors may desire to further customize elements of the report. Examples may include changing the colors of visuals or adding and modifying report information, all based on the active security roles.

This article explores how to display active security roles in card visuals. We will also outline how to implement a measure to determine if the current user belongs to a specific security role, and provide more tools for customizing the user experience based on active security roles.

Click through for the article.

Comments closed

Protecting Kubernetes Services

Published 2023-06-30 by Kevin Feasel

Boemo Mmopelwa gives us an idea of Kubernetes service types and how to secure them:

A Kubernetes service is a logical abstraction that enables communication between different components in Kubernetes. Services provide a consistent way to access and communicate with the application’s underlying components, regardless of where those components are located.

In Kubernetes the default type is ClusterIP. Services abstract a group of pods with the same functions. Services expose pods and clusters. Services are crucial for connecting the backend and front-end of your applications.

This is different from your containerized applications that you can deploy on Kubernetes

Comments closed

Preventing Accidental Azure Changes with Resource Locks

Published 2023-06-28 by Kevin Feasel

Khushbu Ghandi puts a padlock on it:

Resource locks are just locks that we can associate to different scopes in Azure allowing us to override permissions at that resource scope and down. When we talk about the scope of the resource lock, we can lock subscriptions, we can lock resource groups and individual resources, and the lock restrictions that we have based off the type of lock we select will apply to all users and roles that have access to that resource. Also, it’s worth noting that locks are inherited by child resources. So, if we apply a lock on a subscription, it is inherited by all the resource groups that have been created under that subscription along with the resources that will be created under the resource groups.

Resource locks come with their own considerations, and Khushbu dives into those. This is a concept I like more in theory than in practice, save for pretty stable systems where you keep things running 24/7.

Comments closed

Indexes and Stats on Tables with Always Encrypted

Published 2023-06-21 by Kevin Feasel

Matthew McGiffen gives us the low-down:

In a previous post we looked at executing queries against columns encrypted using Always Encrypted. In this short post we look at the ability to be able to have (or not) indexes on those columns. This information is relevant to the case where you are using Always Encrypted without enclaves, we’ll look at working with enclaves later on.

Click through to see how this all works.

Comments closed

Microsoft Defender for Azure SQL Slowdown

Published 2023-06-19 by Kevin Feasel

Iain Evans finds a hidden cost:

To find the root cause, we conducted a configuration comparison of the servers against an Aireforge configuration snapshot to check for any changes. We immediately noticed that an extended event session called SQLAdvancedThreatProtectionTraffic had recently been deployed. This session is deployed by default with Azure Defender for SQL Server. More info: https://learn.microsoft.com/en-us/azure/defender-for-cloud/defender-for-sql-introduction#advanced-threat-protection

Read on to see what the symptoms were and what kind of impact this had.

Comments closed

Reviewing Always Encrypted Queries with Extended Events

Published 2023-06-16 by Kevin Feasel

Matthew McGiffen takes a look:

In previous posts we’ve looked at a number of aspects of Always Encrypted and how it works. I think it’s very useful to understand how it actually works in practice, so in this post we’re going to look at what happens in the background when you execute a query.

I’m continuing on from the examples in the last few posts on this subject. So if you want to repeat the examples for yourself you’ll need to have followed steps from Setting Up Always Encrypted and Executing Queries Using Always Encrypted.

Read on to see what actually happens when you perform a simple INSERT or SELECT operation and there are Always Encrypted columns involved.

Comments closed

Side Channel Attacks on Dynamic Data Masking

Published 2023-06-15 by Kevin Feasel

Ben Johnston does some testing:

This is the third part of a series on SQL Server Dynamic Data Masking. The first part in the series was a brief introduction to dynamic data masking, completing solutions, and use cases. The second part covered setting up masking and some examples. This part starts exploring side channel attacks against dynamic data masking.

This article will show that are plenty of security concerns with using Dynamic Data Masking as a general-purpose security tool, but don’t let it completely keep you from using it. The point of the article is to show you the weakness of the tool, particularly for ad-hoc uses with users who might be keen to snoop around the data in ways you might not expect.

I’m not very big on Dynamic Data Masking at all, and this is a big part of why. That said, if your end users don’t have the ability to send arbitrary queries along (e.g., they can only execute stored procedures and none of those stored procedures let you send in arbitrary T-SQL), then it’s not that bad.

Comments closed