Category: Security

Kenneth Fisher shows how to prevent people from using those high-power application accounts:

Anyone of these would cause you to fail a security audit. All of them together? Not good.

So how do we fix it? Well, the best possible method is to not give your developers the password. Use config files containing an encrypted copy of the password and you can dramatically limit knowledge of the password. However, that isn’t necessarily a quick or easy solution (modifying the app to use a config file at all for example). So what to do in the meantime?

The simplest thing to do is to create a logon trigger to control where this account can come from. Before we start if you are going to use a logon trigger make sure you know how to log in and disable it if there are any mistakes.

The logon trigger is hardly perfect, but it does help at the margin.

Chrissy LeMaire has a two-parter on enabling SSH tunneling on Windows 10.  First, if you are using the Fall Creators Update:

Gotta say I’m super thankful for Chris K’s blog post “Enabling the hidden OpenSSH server in Windows 10 Fall Creators Update (1709) — and why it’s great!“, otherwise this would have taken me far longer to figure out.

So next, Run PowerShell As Administrator, then generate a key.

cd C:\windows\system32\OpenSSH
ssh-keygen -A

Alternatively, if you are not using the Fall Creators Update:

First, bash for Windows must be setup. This requires Windows 10 or Windows Server 2016.

Note: this was written for Windows 10 pre-1709. Apparently, the new update contains a ton of changes. Developer mode is not required and you install your Linux distro from the Windows Store. Seems that it may even include Open SSH right out the box. I’ll test on Tuesday and let you all know. Till then, here is how to do it if you’ve got Windows 10 without Fall Creators Update (FCU).

Doing this limits the ability of an attacker to snoop on your RDP traffic.

Monica Rathbun kicks off a series on Always Encrypted:

There are two possibilities Deterministic and Randomized.

MSDN defines Deterministic encryption as always generates the same encrypted value for any given plain text value. Which means that if you have a birthdate of 01/03/1958 it will always be encrypted with the same value each time such as ABCACBACB. This allows you to index it, use it in WHERE clauses, GROUP BY and JOINS.

Randomized encryption per MSDN- uses a method that encrypts data in a less predictable manner. This makes Randomized encryption more secure, because using the example above each encrypted value of 01/03/1958 will be different. It could be ABCACBACB, BBBCCAA, or CCCAAABBB. All three encrypted values are subsequently decrypted to the same value. Since the encrypted value is random you cannot perform search operations etc. as you can with Deterministic.

Part 1 is about building the certificates and keys needed to encrypt data.

Daniel Hutmacher walks through the process of setting up a certificate on a SQL Server to enable connection encryption:

Based on a real-world scenario I encountered recently, here is the premise for this post. I’m putting it here at the top, so I won’t have to expand my post into a gazillion permutations for all imaginable types of scenarios and situations. However, I think you’ll be able to adapt the workflow to your particular setup.

• SQL Server is running on an Azure VM with a connection to the Internet.

• Stand-alone SQL Server – no clustering, no availability groups.

• SQL Server has its own service account.

• No web server installed on the machine.

• I don’t have an Enterprise CA.

• I can’t/won’t install certificates on my clients’ computers and servers.

Daniel has done yeoman’s work with this.  I highly recommend giving it a read.

Kellyn Pot’vin-Gorman talks about Dynamic Data Masking:

Anyone with the unmask privilege or DB_OWNER will be able to view the data.  As many development and testing environments grant higher privileges to the users and in SQL Server, it’s not rare for a developer to be the DB_OWNER, (I used to come across this all the time when recoveries were performed by the wrong OS user) this leaves this data still quite vulnerable.  I do like that if you were to take a backup and recover it with masking, the obfuscated data is what is recovered physically.  I’m more concerned about those odd environments where compliance hasn’t been put in place on owners of the database that would still view the originally masked data, but unmasked.

Performance isn’t impacted, (i.e. no referential integrity concerns or execution plans) as the optimizer  performs all steps against the real data, which leads me to wonder what happens with some of the newer monitoring tools that state they can display SQL and bind variable data without accessing the database directly.  Would they “sniff” the masked data or unmasked?  Would it matter who the OS User or roles in the database?

The important thing here is that DDM isn’t really a security product.  It’s a something-or-another product that might be useful to stop shoulder surfing but pretty much nothing else.

Jen Underwood picks out some interesting tidbits from the Verizon 2017 Data Breach Investigations Report:

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.

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.