Category: Security

Each year Verizon, in conjunction with the VERIS Community Database initiative, releases the annual data breach investigations report. This year’s report is based on analysis of 42,068 security incidents, including 1,935 confirmed data breaches. Within this free report, readers are provided incident analysis universally and by industry, detailed insights, and tips to mitigate cyber security threats. For data professionals, the data breach report is one of those “must at least skim” resources to understand the changing nature of threats that you are most likely to face to help you prepare and prevent them.

Click through for Jen’s summary, and I recommend you check out the report as well.

Comments closed

TDE As Security Theater

Published 2017-11-09 by Kevin Feasel

Randolph West argues that Transparent Data Encryption is not security theater:

The short version is that our data, log and backup files are encrypted at rest (i.e., on the storage layer), so that an attacker cannot simply copy and attach the data and log files, or restore a backup, without having access to the master key. If backup tapes or drives are stolen, the data on those devices cannot be recovered.

We can also use what is known as a Hardware Security Module (HSM) to provide keys to secure the database. This is a dedicated physical or virtual device, separate from SQL Server, that generates keys for various services in an organization.

Read on for Randolph’s argument. For the counter-argument, check out Simon McAuliffe’s article on the topic. Over the past couple of years, I’ve grown much more sympathetic to the idea that TDE’s primary use case is in weeding out the rabble-rousers with nary a clue, but it’s not very helpful against a knowledgeable attacker with administrative access to your SQL Server.

Comments closed

Dynamic Row-Level Security And Power BI

Published 2017-11-09 by Kevin Feasel

Reza Rad shows how to build role-based filters in Power BI and use those to implement row-level security:

I have written while ago, about how to implement a dynamic row level security in Power BI. This post is an addition to that post. I’ve had a lot of inquiries that; “What If I want users to see their own data, and the Manager to see everything?”, or “How to add Manager or Director Level access to the dynamic row level security?” This post will answer this question. In this post, you will learn a scenario that you can implement a dynamic row level security. In this scenario, everyone will see their own data, but the manager will see everything. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Read on to see one method of solving this dilemma.

Comments closed

Security Issue In Oracle Identity Manager

Published 2017-11-03 by Kevin Feasel

Oracle has a security advisory with a CVSS base score of 10.0 (which is pretty awful):

This Security Alert addresses CVE-2017-10151, a vulnerability affecting Oracle Identity Manager. This vulnerability has a CVSS v3 base score of 10.0, and can result in complete compromise of Oracle Identity Manager via an unauthenticated network attack. The Patch Availability Document referenced below provides a full workaround for this vulnerability, and will be updated when patches in addition to the workaround are available.

Due to the severity of this vulnerability, Oracle strongly recommends that customers apply the updates provided by this Security Alert without delay.

Catalin Cimpanu explains:

The affected product is Oracle Identity Manager (OIM), a user management solution that allows enterprises to control what parts of their network employees can access. OIM is part of Oracle’s highly popular Fusion Middleware offering and is one of its most used components.

Oracle describes the issue — tracked under the CVE-2017-10151 identifier — as a “default account” vulnerability, an umbrella term that’s usually used to describe accounts with no password or hardcoded credentials (a.k.a. backdoor accounts).

“This vulnerability is remotely exploitable without authentication, i.e., may be exploited over a network without requiring user credentials,” Oracle said in a security alert.

Oracle has patched this. If you have it installed, please update ASAP.

Comments closed

Decrypting SSIS Passwords

Published 2017-10-31 by Kevin Feasel

Jason Brimhall shows how to decrypt your Integration Services package’s password if you have a SQL Agent job set to execute that package:

Take note here that I am only querying the msdb database. There is nothing exceedingly top secret here – yet. Most DBAs should be extremely familiar with these tables and functions that I am using here.

What does this show me though? If I have a package that is being run via Agent Job in msdb, then the sensitive information needs to be decrypted somehow. So, in order to do that decryption the password needs to be passed to the package. As it turns out, the password will be stored in the msdb database following the “DECRYPT” switch for the dtutil utility. Since I happen to have a few of these packages already available, when I run this particular query, I will see something like the following in my results.

That’s a clever solution. I get the feeling that I should be a bit perturbed by how simple this is, but I don’t; the real sensitive data is still secure.

Comments closed

Finding AD Group Members With Powershell

Published 2017-10-25 by Kevin Feasel

Amy Herold has a quick script to find which Active Directory users are in particular AD groups:

There was something that popped up today that called for a PowerShell script and the Get-ADGroupMember cmdlet – get a list of users from a list of groups. Some users are in there more than once so this needs to be a distinct list, unless you are into manually cleaning up things like this, and then I will be sad for you. Because that is kinda sad.

I originally wrote a script with two arrays (one for the initial list and one for the de-duped list of users), but even though this is quick and dirty, that was a little too dirty. Enter the Group-Object cmdlet – it takes this list of names and groups them. No black magic this time. Just a cmdlet, that comes baked into PowerShell giving me what I need.

Click through for the script.

Comments closed

Temporal Table Permissions

Published 2017-10-17 by Kevin Feasel

Kenneth Fisher shows us the permissions needed to create temporal tables:

Msg 13538, Level 16, State 3, Line 6
You do not have the required permissions to complete the operation.

Well, that’s not good. What permissions do I need exactly? Well, again, according to BOL I need CONTROL on the table and its history table. For those that don’t know CONTROL is the top level permission for any object. You can do anything at all with it.

Read the whole thing. I gather the reason for requiring this level of access is that you don’t want people to go monkeying with data collected for auditing purposes.

Comments closed

Azure Database-Level Firewall Rules And Geo-Replication

Published 2017-10-12 by Kevin Feasel

Arun Sirpal explains that you don’t need to create database-level firewall rules in Azure on secondary databases when using Active Geo-Replication:

The main purpose of this post today is to discuss this point – If you have an Azure SQL Database involved in Active Geo Replication and opt to use database level firewall rules do you need to create the rules in both the primary and secondary database?

I thought so, but I was wrong. I connect to my primary database and run the following (obfuscated) .

Read on for Arun’s demonstration.

Comments closed

Backup Compression And Encryption

Published 2017-10-10 by Kevin Feasel

Arun Sirpal shows the combined effects of backup encryption and backup compression in SQL Server 2017:

Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t? You will get the dreaded thumbprint error.

Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint ‘0x78FAB5A2A5D593FD3C4E163C90B745F70AB51233’. Msg 3013, Level 16, State 1, Line 25

RESTORE DATABASE is terminating abnormally.

So make sure you respect this certificate (and the key) and back it up and re-create them on the target server for a successful restore.

In SQL Server 2016 and 2017, there’s no reason not to encrypt backups; the marginal cost is practically nil even if you’re low enough on disk space that you need to do backup compression.

Comments closed

Active Directory On CentOS

Published 2017-10-05 by Kevin Feasel

Drew Furgiuele shows how to configure a box running CentOS to work with Active Directory:

Before we start though, there’s a few things you’re going to need to have already set up:

An Active Directory Domain to test in, and rights to administer it. Since we’re going to be creating (and possibly deleting, if there are errors) computer objects and a service account, you’ll need a domain account with adequate permissions.
My example assumes you have a Microsoft DNS server running alongside your domain services. It is possible to use a separate DNS server to get this to work, but you might need some additional network configuration (see below). Also, depending on your environment, you might need a reverse lookup zone defined. If you notice long ping times or other weird lookups, I’d set one up in your DNS.
A machine (virtual or otherwise) that is running CentOS 7 or later (and this guide was written and tested against CentOS 7). For this demo, we’ll be using the Server (minimal install) installation option. If you’re new to Linux, you might opt a desktop version (server with a GUI). When you download a CentOS disk image to install it, you get all these options on the default media; you won’t need separate downloads

There are a few more prereqs, so read the whole thing. This route is easier than Ubuntu, as Drew notes.

Comments closed