Press "Enter" to skip to content

Category: Security

Working with Secrets in Powershell

Published 2021-04-20 by Kevin Feasel

Jeffrey Hicks tries out the Secrets Management modules in Powershell:

So I’ve been kicking the tires and trying to do more with the Secrets Management modules from Microsoft, now that they are out of pre-release status. You can install the Microsoft.PowerShell.SecretStore and Microsoft.PowerShell.SecretManagement modules, you’ll need both, from the PowerShell Gallery. You can find extension modules that build on the Microsoft modules for working with other key vaults or secret store. Run find-module -tag secretmanagement to find additional modules. But what I want to talk about today relates to the Microsoft modules. Although, it might apply to you with any of the extension modules. The challenge is using the secrets management modules with a PowerShell profile script.

Read on for a challenge around running scheduled tasks which require secrets and a solution.

Comments closed

QUOTENAME and Dynamic SQL

Published 2021-04-20 by Kevin Feasel

Chris Johnson looks at one way to protect dynamic SQL statements:

I don’t have the usual aversion to dynamic SQL that you find a lot of developers have. I use it quite regularly as part of my ETL processes, usually to define how data will move from one stage to another. So, for instance, I might have a single Validate procedure that takes a number of parameters, including an import table name, and moves data from that import table to a valid table according to my validation rules. Or I might have a single SCDMerge procedure that takes a transformation view and merges that into a slowly changing dimension (although not using the actual MERGE function for various reasons). These procedures allow me to have confidence that data will always move from one stage to another in the same way, and saves me from writing essentially the same statement 50 times, and having to update it 50 times when I need to change the way we do something, and inevitably missing something and introducing some error.

This always feels like a pretty safe use of dynamic SQL to me, because it avoids some of the more common objections people like to raise to it:

Click through to see how QUOTENAME() can help sanitize user inputs. I personally prefer the route of using sp_executesql but QUOTENAME() can also do the trick.

Comments closed

Using AD Authentication on Linux when Connecting to SQL Server

Published 2021-04-16 by Kevin Feasel

Daniel Hutmacher shares some hard-earned wisdom:

I’m a complete beginner at Linux, so I should preface this post with the fact that these are my humble notes after hours of pulling my hair. It’s not really a fully-fledged how-to article, and there are lot of things I’m not covering. But I figured it may help someone out there at some point.

Also, different Linux distros and versions will behave differently, so your mileage will most likely vary.

For the purposes of this post, I’m on Red Hat Enterprise 8.3.

Note that this is using a Linux-based client, rather than talking about SQL Server on Linux.

Comments closed

Securing the sa Login

Published 2021-04-12 by Kevin Feasel

Chad Callihan has a few tips for making that sa login safer:

Every SQL Server install includes the sa login as a sysadmin. This can be good for consistency; however, that also makes it a prime target for attackers trying to get into your SQL Server. That is one of many reasons why you should make the following changes to protect your sa login from being used in an attack.

Click through for the list. Troll me says the best answer is to rename sa (like Chad mentions), create a new account called sa, and write a script to add a rule to your firewall banning the IP of anybody who logs in with this account.

Comments closed

Restoring a TDE Database to a Different Server

Published 2021-04-09 by Kevin Feasel

Chad Callihan reminds us of the other half of backups:

You setup TDE for your database. Great! Now you need to restore that database to a different server. Maybe you’re migrating off of an older server or maybe there is testing to be completed for an upcoming release. If we try to backup/restore or attach the database on a different server we’ll get some variation of an error stating “cannot find server certificate with thumbprint…” and the process will fail. There are a few steps to get the TDE database restored and we’re going to walk through them today.

Read on for those instructions.

Comments closed

Fixed Server and Database Roles in SQL Server

Published 2021-04-02 by Kevin Feasel

Greg Larsen takes us through the built-in set of server and database roles in SQL Server:

Managing Security for SQL Server is extremely important. As a DBA or security administrator, you need to provide access for logins and database users to resources within SQL Server. SQL Server has both server and database resources where access might need to be granted. Access to these resources can be granted to either individual logins or database users or can be granted to roles, for which logins or database users can be members. Granting access via a role is known as role-based security.

There are two types of roles: fixed or user-defined. In this article, I will discuss the different fixed server and database roles provided with SQL Server and how these roles can be used to support role-based security to simplify providing access to the different SQL Server resources. In a future article, I will discuss user-defined server and database roles.

Click through for more information. Or just give everybody sysadmin. I mean, that’s probably fine, right?

Comments closed

A Primer on Transparent Data Encryption

Published 2021-03-31 by Kevin Feasel

Matthew McGiffen walks us through the intention of Transparent Data Encryption:

Transparent Data Encryption (TDE) was introduced in SQL 2008 as a way of protecting “at rest” data. It continues to be available in all versions of SQL right up until the present, until recently it was only available in the Enterprise editions of SQL Server but from SQL 2019 it was made available in standard edition.

Read on for more detail.

Comments closed

Finding Securables for a Database Role

Published 2021-03-30 by Kevin Feasel

Jack Vamvas answers a question:

Question: I  need to extract the securables for a user created SQL Server database role. For example , the Explicit Permissions  including the Permission,Grantor,Grant,With Grant and Deny.  And also the Securables – Schema,Name,Type

How can I get this information via t-sql?

The only downside in Jack’s query is that it enumerates the securables for the principal. But if the principal is part of an Active Directory group (or multiple groups), this becomes more difficult.

Comments closed

Granular Permissions for Dynamic Data Masking

Published 2021-03-24 by Kevin Feasel

John Martin reviews a change:

All the way back with SQL Server 2016 Microsoft released the Dynamic Data Masking feature in the database engine. It seemed like a huge step forward and promised so much, but there were severe limitations around the way that we could control who sees what masked data. It was a case of you either got to see masked data wherever it was configured, or you saw clear data, there was no granularity. I wrote about this and a few other things to do with Dynamic Data Masking all the way back in August of 2016 when I was at SentryOne. You can check that post out here. Also, back then I created several Connect items (blast from the past there), one of which was pulled over to the user voice replacement where I was asking for the UNMASK securable to be made more granular, you can check that out here.

So, why I am I writing this post? Well, it seems that our (my?) request has been granted. At least in Azure SQL Database. On March the 17th this year a little announcement slipped out stating “General availability: Dynamic data masking granular permissions for Azure SQL and Azure Synapse Analytics“. So, has this delivered on what we wanted, to really help this feature live up to its promise?

Read on to see how it works and what John thinks of the whole thing.

Comments closed