Press "Enter" to skip to content

Category: Security

Issues With TDE And Backup Compression

Published 2018-05-18 by Kevin Feasel

Ned Otter describes the troubled history of the union of Transparent Data Encryption and backup compression:

The history of TDE and backup compression is that until SQL 2016, they were great features that didn’t play well together – if TDE was in play, backup compression didn’t work well, or at all.

However, with the release of SQL 2016, Microsoft aimed to have these two awesome features get along better (the blog post announcing this feature interoperability is here). Then there was this “you need to patch” post, due to edge cases that might cause your backup to not be restored. So if you haven’t patched in a while, now would be a good time to do so, because Microsoft says those issues have been resolved (although that seems to be disputed here).

My sympathies definitely lie toward backup compression over TDE if forced to choose between the two.

Comments closed

Reverting After Impersonation

Published 2018-05-17 by Kevin Feasel

Kenneth Fisher shows a couple common issues when executing as another user or login:

The problem is that sometimes I’ll forget to revert back.

1
EXECUTE AS LOGIN = 'Doc';

Msg 15406, Level 16, State 1, Line 8
Cannot execute as the server principal because the principal “Doc” does not exist, this type of principal cannot be impersonated, or you do not have permission.

Hey! I know I Doc exists, and I’m sysadmin. How is this not working? Simple enough, I forgot to REVERT. There are two simple options here. I either completely forgot to run it, or I was in the wrong database.

Read the whole thing.

Comments closed

Keeping Database Role Information In Source Control

Published 2018-05-17 by Kevin Feasel

Louis Davidson has a post on handling database security in source control:

Yeah, things get messy, no matter what model you choose for securing your PROD data:

  1. Create one user and give it all rights to the database
  2. Create specific users and give them the least amount of rights to do what is must, and no more
  3. Somewhere in between the previous 2

Truly, #3 is generally the answer. Let’s say that you give the application all the rights that any user of the system can have, and let the application dole out the rights to individuals. This is not a terrible plan, but I dare say that many databases contain data, or utilities that it is not desirable to give to the users. (My utility schema generally has tools to maintain and release code, something that you don’t want general users to have access to. And lest you have a developer working “with” you like I once did, you don’t want the application to have access to the tools to disable all of the constraints in the database, even if you have ETL uses for that code.)

Check it out for some examples.

Comments closed

RDP Error: CredSSP Encryption Oracle Remediation

Published 2018-05-17 by Kevin Feasel

Kerry Tyler explains an error message popping up in RDP sessions:

In March, a vulnerability in CredSSP (Credential Security Support Provider) was patched, which would affect authentication via RDP (this is outlined in advisory CVE-2018-0886).  However, it was implemented in such a way that the behavior change didn’t have to be “honored” by either the server or the client involved in an RDP session.

The intent was that this would be controlled by GPO in enterprise environments, and a new GPO setting to activate or deactivate this behavior was released at the same time.

GPO settings have a default value, which they will use when nothing has been explicitly set for a particular setting. In this case, the GPO has three possible values: Force Updated Clients (for servers to only take connections from patched clients), Mitigated (for both, and on a workstation means that it won’t fall back to old/insecure behavior when attaching to unpatched servers), and Vulnerable (for both, and means what it sounds like–anything goes!).

In March, the default behavior was set to “Vulnerable”, which means everything kept working for everyone. But in the May security rollup, the default setting for that GPO was flipped to “Mitigated” if there was not an explicit setting for it…

If you get this error, the best thing is to patch the machines involved, but Kerry shows the workaround you can use if you need to use RDP in the meantime to connect to an unpatched machine.

Comments closed

Sign Those Stored Procedures

Published 2018-05-03 by Kevin Feasel

David Fowler shows how we can sign stored procedures to give users limited rights that they otherwise should not have:

One way that we can do this is by signing the procedure (you can also use this with functions and triggers) with a certificate or an asymmetric key.

In this post I’m only going to look into signing a stored procedure with a certificate but the same ideas can be applied for the other objects and with an asymmetric key.

So…

We’re going to create a certificate and sign our stored proc using that certificate.  We’ll then create a user based on the certificate and grant the new certificate user the appropriate permissions to run the stored procedure.

Every SQL Server DBA should know how to do this, but in my experience, it’s a small percentage who do.

Comments closed

Rotating Encryption Keys When Using Always Encrypted

Published 2018-05-02 by Kevin Feasel

Ed Leighton-Dick explains how to rotate your encryption keys when using Always Encrypted in SQL Server:

In my last post, I talked about the process of rotating your encryption keys. It’s just one of those routine maintenance tasks that need to be done from time to time in order to keep your encryption strong for the long haul. One type of rotation I didn’t address in that post was rotation for Always Encrypted, SQL Server’s newest form of encryption.

If you recall, Always Encrypted has two associated keys: a Column Master Key and a Column Encryption Key. The Column Encryption Key (CEK) is a symmetric key, stored in SQL Server. Like other symmetric keys, the CEK is not changed during a rotation. The Column Master Key (CMK), on the other hand, is a certificate, similar to the certificates we’ve used for transparent data encryption and for in-column encryption, and it therefore needs to be rotated regularly. The biggest difference is that the CMK is stored outside of SQL Server, in the Windows certificate store by default, so DBAs may need assistance from their Windows administrators or security administrators.

Read on to see what you need to do.

Comments closed

Using Have I Been Pwned In R

Published 2018-04-19 by Kevin Feasel

Maelle Salmon shows us how to use the HIBPwned library in R:

The alternative title of this blog post is HIBPwned version 0.1.7 has been released! W00t!. Steph’s HIBPwned package utilises the HaveIBeenPwned.com API to check whether email addresses and/or user names have been present in any publicly disclosed data breach. In other words, this package potentially delivers bad news, but useful bad news!

This release is mainly a maintenance release, with some cool code changes invisible to you, the user, but not only that: you can now get account_breaches for several accounts in a data.frame instead of a list, and you’ll be glad to know that results are cached inside an active R session. You can read about more functionalities of the package in the function reference.

Wouldn’t it be a pity, though, to echo the release notes without a nifty use case? Another blog post will give more details about the technical aspects of the release, but here, let’s make you curious! How many CRAN package maintainers have been pwned?

Read on to find out that answer.

Comments closed

Limitations Of Object-Level Security In Tabular Models

Published 2018-04-18 by Kevin Feasel

Teo Lachev gives us the skinny on object-level security in an Analysis Services Tabular model:

Object-level security (OLS) is a frequently requested security feature when implementing semantic models. For example, a current project disallows some sales persons to see sensitive measures, such as Margin and Profit. In Multidimensional, modelers could use cell security to meet such requirements with the risk of compromising performance. Starting with SQL Server 2017 (compatibility level 1200), Tabular supports object-level security to disallow access to entire tables or specific columns without a performance penalty. As it stands, OLS has the following limitations (for a full list, review the documentation):

Click through for the list of limitations.

Comments closed

Synchronizing User Logins Across SQL Server Instances

Published 2018-04-18 by Kevin Feasel

Hamish Watson shows how easy it is to synchronize SQL authenticated logins using dbatools:

When building new servers the most important thing after restoring and securing the database is syncing up the users. This is especially important for Availability Groups as SQL Authenticated users required the SIDS to be the same.

In the past I had some very long winded code that would do the sync – it was a mixture of TSQL and PowerShell. It worked but you know – it was cumbersome.

Read on to see how life gets easier with dbatools.

Comments closed

SQL Server And STIGs

Published 2018-04-17 by Kevin Feasel

Mohammad Darab has a quick summary of the Department of Defense’s STIG overview for SQL Server 2016:

To make it easier for people in charge of “STIG’ing” their SQL Server 2016 environment, this blog is aimed to go over the newest MS SQL Server 2016 STIG Overview document (Version 1, Release 1) that was released on 09 March 2018. If you want to read through the whole document you can download it here. Otherwise, below is my summation of the relevant sections.

This overview document was developed by both Microsoft and DISA for the Department of Defense.

The entire overview document is 9 pages (including title page, etc.)

Click through for Mohammad’s summary.  Also check out Chris Bell’s sp_woxcompliant.

Comments closed