Press "Enter" to skip to content

Category: Security

TDE Encryption Scan Internals

Published 2019-12-11 by Kevin Feasel

On the Microsoft Tech Community blog, goramesh shares with us how the initial encryption process works for Transparent Data Encryption:

Now, once encryption is turned ON for a database, all the existing user data on the data files should be encrypted. To do this, SQL Server starts something called a TDE Encryption Scan. It is basically a scanner, which goes through each page of each data file to ensure its encrypted. When the scanner completes its scan across all the files, that’s when we say that the database is ‘encrypted’. How the TDE Encryption scan works is crucial because of the effects it can have on the user workload. Let me explain. 

Read on for the explanation.

Leave a Comment

Changes to EC2 Metadata Service

Published 2019-12-03 by Kevin Feasel

Praveen Sripati takes a look at changes to the AWS EC2 Instance Metadata Service following attacks against Capital One and dozens of other organizations:

Captial One Bank (1) and 30 different organizations were hacked around end of July, I have written a blog (1) around the same time on how to recreate the hack in your own AWS account and also a few mitigations around the same. Now, AWS has made a few changes to the AWS EC2 Instance Metadata Service (IMDS) around the same (1, 2). AWS re:Invent 2019 session (1) around the same has also been planned on December 5th, 2019. Will update with the link once the recording of the session has been uploaded.

The old/existing approach is called IMDSv1 and the new one IMDSv2. Although IMDSv1 solves a few problems like not storing the access keys on the EC2, it bought its own headaches which lead to the hacks.

Click through to see what these problems were and how they led to IMDSv2.

Leave a Comment

Visual Tools and Dimension Security Slowdown in SSAS

Published 2019-11-25 by Kevin Feasel

Chris Webb hits an interesting edge case with SQL Server Analysis Services Multidimensional:

Recently I was involved in troubleshooting a mysterious Analysis Services Multidimensional performance problem for a customer: the team worked out that certain queries run by certain users were extremely slow, and that these users were members of roles where dimension security was applied, but the amount of slowdown – queries going through the role were taking over 10 minutes compared to a few seconds when run as an administrator – was unlike anything I had seen before. It turned out that the cause was having the Enable Visual Totals box checked on every attribute on the dimension where security was applied, not just the attributes whose members were secured.

Read on for a reenactment of the problem.

Leave a Comment

MSDTC and the Firewall

Published 2019-11-25 by Kevin Feasel

Josh Smith shows how you can enable MSDTC in a buttoned-down environment:

This is just a fancy way of saying you need to be better friends with who ever is managing your enterprise firewall. I hadn’t had to touch the DTC until a recent vendor insisted their application wouldn’t work without it (despite their only having a single data store). The MSDTC was developed to coordinate transactions that would span multiple machines and was originally introduced in SQL Server 2000.

In theory it’s not super complicated: just enable the DTC service/communication on the servers in question and turn on some built in firewall rules on the servers right? Almost.

Read on for the full set of instructions.

Leave a Comment

Named Volumes in Docker with SQL Server 2019

Published 2019-11-20 by Kevin Feasel

Andrew Pruski takes us through one of the biggest changes with SQL Server 2019 in containers:

I’ve seen a few people online asking how to use docker named volumes with the new SQL Server 2019 RTM images. Microsoft changed the way SQL runs within a container for the RTM versions, SQL no longer runs as the root user.

This is a good thing but does throw up some issues when mounting volumes to create databases on.

Let’s run through what the issue is and how to overcome it.

Click through to see what you need to add to your DOCKERFILE to get things working.

Comments closed

Your Power BI Administrator’s Privileges

Published 2019-11-18 by Kevin Feasel

Melissa Coates goes into exactly what it is that a Power BI admin can see and do:

I wrote about (and updated) this topic previously, but this is so important that it warrants revisiting. So let’s have a quick chat about what privileges a Power BI administrator has with respect to accessing data throughout the Power BI tenant.

All metadata throughout the tenant is available to the Power BI administrator (ex: if they want to enumerate a list of workspaces, reports, dashboards, etc using the APIs). So, metadata is easily discoverable but — technically speaking — a Power BI administrator cannot access datasets in Power BI unless they have permission to that workspace. However…

Read the whole thing.

Comments closed

Errors with SQL Server TDE and Azure Key Vault

Published 2019-11-12 by Kevin Feasel

Amit Banerjee takes us through troubleshooting issues when using Azure Key Vault as the key storage mechanism for Transparent Data Encryption:

The first one was a 404 error. When I looked the application event log, I saw the following error:

Operation: getKeyByName
Key Name: ContosoRSAKey0
Message: [error:112, info:404, state:0] The server responded 404, because the key name was not found. Please make sure the key name exists in your vault.

The simple reason for the above error is that I was using an incorrect key name or the key didn’t exist in my Azure Key Vault. So the remediation is to check if the key exists in your Azure Key Vault. If not, then create the key.

Read on for additional errors you might run into, as well as a link to an Azure Data Studio notebook to set this up yourself.

Comments closed

Securing Data on ElasticMapReduce

Published 2019-11-11 by Kevin Feasel

Duncan Chen takes us through data encryption options when using ElasticMapReduce:

Data encryption is an effective solution to bolster data security. You can make sure that only authorized users or applications read your sensitive data by encrypting your data and managing access to the encryption key. One of the main reasons that customers from regulated industries such as healthcare and finance choose Amazon EMR is because it provides them with a compliant environment to store and access data securely.

This post provides a detailed walkthrough of two new encryption options to help you secure your EMR cluster that handles sensitive data. The first option is native EBS encryption to encrypt volumes attached to EMR clusters. The second option is an Amazon S3 encryption that allows you to use different encryption modes and customer master keys (CMKs) for individual S3 buckets with Amazon EMR.

Click through for more details on each.

Comments closed

Azure AD Logins for Managed Instances

Published 2019-11-04 by Kevin Feasel

Mirek Sztajno announces a new feature for Azure SQL Managed Instances:

We are happy to announce a general availability (GA) for Azure AD server principals (Azure AD logins) for SQL managed instance (MI). This feature allows Azure AD users to create logins in the master database for MI, grant MI server level permissions for these logins and create Azure AD users with     logins for individual MI databases.

Additionally, enabling Azure AD logins allow users to execute many MI features supported for SQL logins (see the documentation at the end of this blog).

Read on to learn more about this feature.

Comments closed

Scripting Out Linked Servers with Actual Passwords

Published 2019-10-29 by Kevin Feasel

Ajay Dwivedi shows how you can script out a linked server creation statement which includes actual passwords:

For moving Logins/Users, Microsoft provided revlogin script which made it easy for migration of logins without need to know about passwords. But, there is no easy approach for migration LinkedServers with the actual password. This is where dbatools cmdlet Copy-DbaLinkedServer becomes very handy. But, what about the situation where we have to script out LinkedServer beforehand.

For this reason, based on the blog post of Antti Rantasaari, and using his code as the base script, I have created a cmdlet Get-LinkedServer with SQLDBATools module which accepts SqlInstance name as a parameter along with -ScriptOut switch, and gives Drop/Create statements for linked servers present on that local/remote SqlInstance.

As a quick note, SQLDBATools is not the same as dbatools.

Comments closed