Press "Enter" to skip to content

Category: Security

Attacks on Row-Level Security

Published 2023-09-25 by Kevin Feasel

Ben Johnston continues a series on row-level security in SQL Server:

As mentioned in previous sections, RLS is an addition to security and should not be used as the primary method to limit access to data. It is a supplementary layer, useful in specific scenarios. There are also instances where RLS can be defeated by an unauthorized user. The attacks listed below are broken down into direct attacks, indirect attacks, and side-channel attacks. The categorizations could be changed, but the important part of each is the vulnerability discussed.

The one scenario I’m a bit surprised about is the divide by zero attack, as I had figured the filter predicate would apply before the computation leading to a divide by zero scenario.

Comments closed

Azure AD (or Entra ID) Authentication for SQL Server VMs

Published 2023-09-20 by Kevin Feasel

Deepthi Goguri enables Azure Entra ID security on a SQL Server VM in Azure:

To enable the SQL Server 2022 on a virtual machine to use Azure AD, we need below things:

Deepthi then includes the list of requirements, starting with a managed identity and moving on to permissions and other configuration. It looks like a fair number of steps, but it’s not that onerous a task once you get to it.

And this also gives me a chance to rant about Microsoft marketing a bit, as they are in the process of switching the name Azure Active Directory to Azure Entra ID. Granted, Azure Active Directory isn’t Active Directory (although it does perform a very similar task in a fairly similar way). So to remove confusion that I don’t think really existed, they changed the name to Entra ID. Except that most of the Microsoft documentation still says Azure Active Directory, and we have about a decade’s worth of blog content talking about Azure Active Directory, so when you go searching for the resolution to a problem, you’ll have to search for Azure Entra ID as well as its former name, which means people will still link the product to Azure Active Directory—at least, until the point when there’s enough blog content and documentation in place to replace the large majority of those existing blog posts—and so you renamed a product for no reason. Plus, they picked an ambiguous name that people will pronounce multiple ways: is the “ent” in Entra like “enter the dungeon” or Entra like “a delicious entrée”?

But then again, considering how many pronunciations of Azure there are, maybe this is the plan…

Comments closed

Azure Blob Storage Operating System Error 86

Published 2023-09-20 by Kevin Feasel

Jose Manuel Jurado Diaz 86’d that option:

Today, I worked on a service request that our customer got the following error message: Cannot open backup device ‘https://XXX.blob.core.windows.net/NNN/YYY.bak‘. Operating system error 86(The specified network password is not correct.). RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201). Following I would like to share with you some details why this issue and the activities done to resolve it. 

Read on to get a better understanding of what this error actually means and how you can fix it.

Comments closed

A Review of DataVeil for SQL Server Users

Published 2023-09-20 by Kevin Feasel

Brian Kelley tries out a product:

My organization typically moves production data to other environments. There are a variety of use cases:

  • Testing with the amount and frequency of production data.
  • Performing analytics on said data.
  • Delivering production-like data to a third party for their use.

We do not want to move production data around. Instead, we want to deliver “production-like” data for these use cases. Sometimes, we work with multiple systems integrated with each other, and in those cases, we need the data to match up. In other instances, we need the sensitive data, such as personal identifiable information (PII), to be altered so it’s no longer sensitive, but there’s no requirement for it to be consistent across systems.

Read on for Brian’s full review. I should also note that this is most definitely a paid product.

Comments closed

Power BI Shareable Cloud Connections and Multiple Connections

Published 2023-09-19 by Kevin Feasel

Chris Webb shows off an interesting aspect of a new feature:

A few weeks ago an important new feature for managing connections to data sources in the Power BI Service was released: Shareable Cloud Connections. You can read the blog post announcing them here. I won’t describe their functionality because the post already does that perfectly well; I want to focus on one thing in particular that is important for anyone using Power BI with Snowflake (and, I believe BigQuery and probably several other non-Microsoft sources): Shareable Cloud Connections allow you to have multiple connections to the same data source in the Power BI Service, each using different credentials.

Read on to see what Chris means and how you can take advantage of it.

Comments closed

Row Level Security Anti-Patterns and Alternatives

Published 2023-09-18 by Kevin Feasel

Ben Johnston tells us why we might not want to use row level security in SQL Server:

One of the primary reasons to implement RLS is to facilitate reporting and ease the administrative burden. This section covers some considerations for using RLS with the primary Microsoft reporting engines and gives you an idea of things to look for in your reporting engine. Some anti patterns and alternatives to RLS are also examined.

This article goes a long way toward explaining why I find row level security so rare in the wild and never implemented it myself: most databases I’ve worked with are either transactional or hybrid OLTP/OLAP, they’re mostly multi-tenant, and they’re accessed through service accounts. That’s just a no-go across the board.

Comments closed

Adding a Service Principal to a Fabric Workspace via API

Published 2023-09-14 by Kevin Feasel

Marc Lelijveld tackles a challenge:

Lately, I found myself struggling using the Power BI REST API to add a service principal to the (Fabric) workspace. After I engaged with some other folks, I managed to succeed. In this blog I will elaborate on the mistake I made and how I got it to work. For some this might be a less useful blog. Though, I still wanted to blog this even if it is for my own memory on how to do this.

Read on for the story.

Comments closed

A SQL Server Security Checklist

Published 2023-09-11 by Kevin Feasel

Hemantgiri Goswami has a list and checks it twice:

Last week, in my previous article on How to Secure SQL Server I have discussed a few points that can help you secure SQL Server. In this post, as promised I will share a SQL Server Security Checklist that I have used for many of my clients to help them achieve PCI compliance.

As you are aware, PCI is global payment security standard council. Following their standards help an organization achieve a compliance certificate that all the card data that is processed, store and transmit are maintained in secure environment.

The good news is that you can use the dbachecks suite to check many of these items.

Comments closed

Row-Level Security Performance and Troubleshooting

Published 2023-09-11 by Kevin Feasel

Ben Johnston digs into row-level security:

There are two main areas where RLS can impact performance. The first is the user or authentication lookup. Some kind of lookup must be performed in the access predicate to determine either the user name, group membership, or specific values in the session context. Considering that RLS is non-prescriptive, the lookup isn’t confined to these methods, but they are very easy methods to use and implement and are standard based on implementations I’ve seen.

The second area is the authorization lookup. The authorization lookup, checking if a user has access to particular rows, can have a much bigger impact on performance. This is also in the access predicate. Following the basic rules for performance and keeping lookups simple goes a long way to minimizing the impact of RLS on performance. The goal is to keep performance levels as close as possible to a table without RLS. If indexes and predicates are correct, RLS can improve performance in some situations due to the automatic filtering that happens.

Read on for Ben’s thoughts on the topic.

Comments closed

Don’t Try These with SQL MI and Private Endpoints

Published 2023-09-08 by Kevin Feasel

Zoran Rilak wraps up a series on Azure SQL Managed Instance and its support for private endpoints:

The first two installments of this mini-series discussed a couple of basic and advanced scenarios involving private endpoints. Today we’ll look at some ways private endpoints cannot be used to implement scenarios where one might expect otherwise.

Read on for four of these in total, laying out things you cannot do via private endpoint to a SQL Managed Instance. In fairness, Zoran also provides what I would consider reasonable work-arounds for each of those: have a VM jumpbox in the same virtual network for DAC connections, peer your virtual networks for replication, and so on.

Comments closed