Press "Enter" to skip to content

Category: Security

Decrypting Always Encrypted Columns In SSMS

Published 2017-12-14 by Kevin Feasel

Monica Rathbun shows how to view Always Encrypted data within Management Studio:

Viewing decrypted data within SQL Server Management Studio (SSMS) is very easy. SSMS uses .NET 4.6 and the modern SQL Server client, so you can pass in the necessary encryption options. SSMS uses the connection string to access the Master Key and return the data in its decrypted format.

First create a new SQL Connection and Click Options to expand the window.

Then go to the Additional Connections Parameters Tab of the login window and simply type column encryption setting = enabled. Then choose Connect.

Click through to see the whole demo.

Comments closed

Protecting Sensitive Data In Docker

Published 2017-12-12 by Kevin Feasel

Jatin Demla shows how to create Docker secrets:

Managing the password, access tokens and private keys are being tedious in the application. Any small mistakes accidentally expose all the secret information. Even storing such thing in docker images can be easily accessible one should just run the image in the interactive mode container and all your application code is available in containers. Docker provides secrets to protect all secret data.

This blog explains the low-level of storage information as well as secured access to docker secret. so, let’s get started.

Read the whole thing, especially if you’ve gone container-happy.

Comments closed

API Security: Contrasting Token-Based With Key-Based Security

Published 2017-12-12 by Kevin Feasel

Vincent-Philippe Lauzon contrasts token-based security (like OAuth) with key-based security for locking down APIs:

Instead of giving a nice & neatly formatted pros & cons table where all the pros have a corresponding cons, let’s just discuss the major aspects:  security & complexity.

Basically, in general, OAuth is more secure but more complex for both clients (i.e. consumer) and services.

Why is OAuth more secure?  Relying parties never see credentials & secrets in an OAuth authentication scheme.  They see a token.  Token are revoked after a while ; often minutes, maximum a few hours.

Read on for more.  My preference is OAuth, but it’s not always trivial to set up.

Comments closed

SQL Vulnerability Assessment

Published 2017-12-12 by Kevin Feasel

Ronit Reger shows off the new SQL Vulnerability Assessment, available in SQL Server 2012 and later:

Not only does VA expose some of the possible security flaws you have in your database system, it also provides remediation scripts to resolve issues within a couple of mouse clicks. In addition, you can accept specific results as your approved baseline state, and the VA scan report will be customized accordingly to expect these values.

Ronit and Alan Yu also mention it being available via the latest version of Management Studio, 17.4:

The VA service runs a scan directly on your SQL database or server. VA employs a knowledge base of rules that flag security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. The rule base grows and evolves over time, to reflect the latest security best practices recommended by Microsoft.

Results of the assessment include actionable steps to resolve each issue and provide customized remediation scripts where applicable. An assessment report can be customized for each customer environment and tailored to specific requirements. This process is managed by defining a security Baseline for the assessment results, such that only deviations from the custom Baseline are reported.

VA is supported for SQL Server 2012 and later, and can also be run on Azure SQL Database.

This looks like a good reason to upgrade SSMS.

Comments closed

Data Breaches And Knowledge-Based Authentication

Published 2017-12-08 by Kevin Feasel

Jeff Mlakar summarizes Troy Hunt’s recent congressional testimony:

Lastly, there is a lack of accountability for the breaches. If you collect data about others you are responsible for it. Yet all too often organizations discover years later they suffered a massive data breach and then proclaim to the press that they were hacked by evil doers and caught unprepared.

Then they progress through the stages of data breach grief:

  1. OMG I just read the news and found out we’ve been hacked

  2. Turns out it was 4 years ago

  3. Blame evil hackers while proclaiming innocence as a naive victim

  4. The media turns up the heat – time to blame some systems administrator

  5. Offer your customers credit monitoring

  6. Acceptance

  7. Wait until the next hack then GOTO step #1

It will be interesting to see what (if anything) comes out of this.

Comments closed

Restricting Login Usage

Published 2017-12-07 by Kevin Feasel

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.

Comments closed

Protecting RDP With SSH

Published 2017-12-04 by Kevin Feasel

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.

Comments closed

Getting Started With Always Encrypted

Published 2017-12-01 by Kevin Feasel

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.

Comments closed

Fun With Dynamic SQL: Implicit Casting Can Allow SQL Injection

Published 2017-11-27 by Kevin Feasel

Remus Rusanu shows an example where implicit casting from NVARCHAR to VARCHAR can introduce a SQL injection vulnerability that you otherwise wouldn’t expect:

In both examples above the SQL executed apparently should had been safe from SQL injection, but it isn’t. Neither REPLACE nor QUOTENAME were able to properly protect and the injected division by zero was executed. The problem is the Unicode MODIFIER LETTER APOSTROPHE(NCHAR(0x02bc)) character that I used in constructing the NVARCHAR value, which is then implicitly cast to VARCHAR. This cast is converting the special ‘modifier letter apostrophe’ character to a plain single quote. This introduces new single quotes in the value, after the supposedly safe escaping occurred. The result is plain old SQL Injection.

Click through for the script.  The upside of this is that it’s entirely under your control and you should be able to get it right by using NVARCHAR consistently.

Comments closed

Using Let’s Encrypt Certificates To Encrypt SQL Server Connections

Published 2017-11-22 by Kevin Feasel

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.

Comments closed