Press "Enter" to skip to content

Category: Security

Creating Server Roles Can Create Logins

Published 2022-09-07 by Kevin Feasel

Kenneth Fisher ran into an interesting issue:

One of my co-workers came to me the other day and told me that they found their network id as a login on one of our SQL Server instances. Why is that note worthy? We make a point of using AD groups if at all possible. He did a little bit more work and tracked it down to a series of commands that he had run recently. I’ll be honest with you, this was starting to feel like a couple of other cases where I’ve seen database objects and principals show up unexpectedly. One was when I created a database object without setting a default schema, and the other was when I created a database audit specification to audit a user that didn’t have an associated database principal (access was through an AD group).

This time around, it was creating a server role. Read on for Kenneth’s thoughts and how you can avoid accidentally creating logins for individual Active Directory users.

Comments closed

Principal Type not Supported Errors in Azure SQL DB/MI

Published 2022-09-07 by Kevin Feasel

Sabrin Alsahsah takes us through a few common causes of Azure Active Directory errors in Azure SQL Database and SQL Managed Instances:

We received some support cases when customers encounter the error below while trying to add an AAD security group to their Azure SQL Database or Azure SQL managed instance. In this blog article, we will be listing a few points to be checked to troubleshoot this error and can help you to identify the cause.

Msg 33130, Level 16, State 1, Line 1

Principal ‘XXXXXX’ could not be found or this principal type is not supported.

Read on for several reasons why things might be going wrong.

Comments closed

Cell-Level (aka Column-Level) Encryption in SQL Server

Published 2022-09-06 by Kevin Feasel

Etienne Lopes takes us through cell-level encryption:

Securing data has always been important but as time goes by, more and more data is available all around us, some of it is considered sensitive data and it becomes a major concern to protect it somehow, in fact in certain cases it is legally mandatory to comply with certain regulations (like GDPR). SQL Server offers a few options regarding data protection (either by means of encryption or obfuscation), cell level encryption being one of them.

In this post I’ll explain what is cell level encryption along with its use cases and I’ll use a thorough demo to show how to implement it in a database and how it works

Despite Always Encrypted being available, I’m more apt to use column-level/cell-level encryption because it’s easier to implement. Well, that and the lack of Always Encrypted in Azure Synapse Analytics.

Comments closed

PolyBase and Windows Authentication in SQL Server 2022

Published 2022-09-02 by Kevin Feasel

I have some bad news:

Back in the SQL Server 2019 days, I laid out a post which covered a really annoying bug around Windows authentication and PolyBase. The upshot is that SQL Server 2019 CU2 broke Windows authentication, meaning that you could not create external objects on a SQL Server instance while using a Windows authenticated session. In CU2, if you tried, you would get a Level 20 error which would kill your session.

Over the next several CUs, the SQL Server team improved things incrementally: first, the error that you got was a Level 16 error, so your statement would fail but the session would remain active. Then, in CU6, they made it so that if your SQL Server instance is on a server running on a domain, then you would be okay—you could create external objects while connected with a domain-authenticated Windows account.

I decided to give this a try in SQL Server 2022 RC0 and ran into some interesting results. 

Click through for those results.

Comments closed

Allow Azure AD B2B Users to Access Power BI Reports

Published 2022-08-29 by Kevin Feasel

Gilbert Quevauvilliers provides access:

I was recently doing some testing and when I went to share a Power BI report with an external (B2B) user I could not initially find out how to do this.

NOTE: I had already created my External (B2B) users, you can follow this guide below if you still need to create the external (B2B) users: Distribute content to external guest users with Azure AD B2B – Power BI | Microsoft Docs

I thought it would be good to share the steps on how I achieved this below.

Read on to see how. Also check out the insightful question in the comments section.

Comments closed

Automating Bring-Your-Own-Key Rotation for TDE in Azure SQL DB

Published 2022-08-26 by Kevin Feasel

Shoham Dasgupta announces a new preview program:

Transparent data encryption (TDE) in Azure SQL Database and Managed Instance helps protect against the threat of malicious offline activity by encrypting data at rest.  TDE with Customer-Managed Key (CMK) enables Bring Your Own Key (BYOK) scenario for data protection at rest, by allowing a key stored in a customer-owned and customer-managed Azure Key Vault to be used as the TDE Protector on the server or managed instance.

When using TDE with Customer-Managed Key, one of the important responsibilities that customers need to perform on a regular basis is key rotation, that is, rotating the TDE Protector on the server by switching to a new key (or new version of the earlier key) from Azure Key Vault. Key rotation is a critical activity for an organization that is required to meet security and compliance objectives.

Automated key rotation for Azure SQL Database and Managed Instance is now available in preview, simplifying key management responsibilities for customers.

Click through to see how this works.

Comments closed

Wrapping Get-Credential in Powershell

Published 2022-08-26 by Kevin Feasel

Stuart Ainsworth writes a script:

Just a quick blog; am working on a script that requires credentials to run against a REST API, and a developer wanted to convert that script to use command-line parameters. I built this script (and quick test) to show that the command-line parameters create the same object as the Get-Credential object.

The one thing that has me on edge a little is that the password comes in as plaintext. I do understand that sometimes this is the best route, though I’d put the pressure on devs to make sure there isn’t a route in which plaintext passwords get encrypted before transmission, even in a local environment like this.

Comments closed

The Importance of Dev Environment Security

Published 2022-08-24 by Kevin Feasel

Kenneth Fisher shares an opinion:

I was asked a rather interesting question during an interview recently. It went something like this:

Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?

And in general my response is “You give it to them. It’s too late to stop them at that point.”

Kenneth goes on to elaborate.

I prefer a compromise solution, which requires more than 2 environments. Dev is a sandbox. Developers don’t know what they need and generally need more space to work than the final product itself will need. A wide-open dev environment is something I don’t mind. In some organizations, a local dev environment may be sufficient for this task—that is, SQL Server (or whatever) installed on the developer’s personal machine or brought in via Docker container.

However, there must be at least one pre-production environment in which developers have prod-level rights. Ideally, it would not be the last step of integration prior to prod but instead something like a QA environment which is part of a CI/CD process.

I should also point out that module signing helps with this problem: stored procedures may contain rights that devs shouldn’t have but are necessary to the way an application functions. Access to execute those stored procedures is something the DBA team can control and put some limits around, so in Kenneth’s example, truncation could be handled via stored procedure. That procedure handles the truncation operation and opens the door to further checks, such as a list (or table) of tables which the app is allowed to truncate, logging of operations, etc.

Comments closed

Granular Permissions in SQL Server 2022

Published 2022-08-24 by Kevin Feasel

Andreas Wolter notes some permissions changes:

In the last 3 years, the SQL Security team has put more emphasis on enabling customers to use SQL Server while adhering to the Principle of least Privilege (PolP). As part of that effort, all new features in the next SQL Server release: SQL Server 2022, can be controlled with more granular permissions. SQL Ledger is a good example of such a new feature. It comes with separate permissions for creating (ENABLE LEDGER) vs dropping ledger tables (ALTER LEDGER); a separate permission to view ledger content (VIEW LEDGER CONTENT) for auditing purposes; and another independent permission to generate a ledger digest (GENERATE LEDGER DIGEST).

Besides new features, a lot of effort also went into improving the existing set of permissions, namely by making them more granular. This has happened in 3 distinguishable areas:

Check out the changes they’ve made.

Comments closed

The Mechanics of Transparent Data Encryption

Published 2022-08-22 by Kevin Feasel

Etienne Lopes takes us through the process of using Transparent Data Encryption:

Securing data has always been important but as time goes by, more and more data is available all around us, some of it is considered sensitive data and it becomes a major concern to protect it somehow, in fact in certain cases it is legally mandatory to comply with certain regulations (like GDPR). SQL Server offers a few options regarding data protection (either by means of encryption or obfuscation), TDE being one of them.

In this post I’ll explain what is TDE along with its use cases and I’ll use a thorough demo to show how to implement it in a database and how it works

I do tend to give TDE disrespect (disrespect that I think it deserves) but it does allow you to check a compliance box without enormous cost. The problem is, I don’t think it moves the needle in terms of proper security when the attacker has admin status on the machine hosting SQL Server and other techniques (e.g., encrypting backups, encrypting specific columns) are better at preventing security issues in other common data scenarios. I’m just not sure there’s a case where TDE helps and there isn’t already a better solution.

Comments closed