Press "Enter" to skip to content

Category: Security

Always Encrypted With Powershell

Published 2017-01-06 by Kevin Feasel

Jakub Szymaszek shows how to configure Always Encrypted support from Powershell:

Note: In a production environment, you should always run tools (such as PowerShell or SSMS) provisioning and using Always Encrypted keys on a machine that is different than the machine hosting your database. The primary purpose of Always Encrypted is to protect your data, in case the environment hosting your database gets compromised. If your keys are revealed to the machine hosting the database, an attacker can get them and the benefit of Always Encrypted will be defeated.

That’s a good warning.

Comments closed

SSRS Data Preview

Published 2016-12-30 by Kevin Feasel

Kathi Kellenberger points out a potential risk with the new Data Preview functionality in SQL Server Reporting Services 2016:

One of the features that took me by surprise is the ability to view data directly from a shared dataset. This feature is called Data Preview, and is available to anyone who has permission to view the dataset and the security at the data source works out. I’m not sure how often shared datasets have been used in previous versions of SSRS. They were not actually needed in many cases, and I generally recommended them for datasets that would be frequently reused such as common parameter lists. This advice will have to change with 2016, because shared datasets are required for the new KPI reports and Mobile Reports.  Stored credentials will be used in the data sources in many cases, because Kerberos delegation is not supported yet with Mobile Reports.

This is a potential data leakage scenario, so if you have potentially sensitive data sets, you’ll want to read this post.

Comments closed

Unencrypted Backups With TDE

Published 2016-12-29 by Kevin Feasel

Steve Jones shows what you need to do to take an unencrypted backup on a database with TDE configured:

When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

The issue here is the DEK still exists in the source database.

Read the whole thing for the solution.

Comments closed

Windows Virtual Accounts

Published 2016-12-29 by Kevin Feasel

Wayne Sheffield describes virtual accounts and how SQL Server can make use of them:

SQL Server will use these groups in many places so that permissions are granted to the group, instead of the actual service account. This simplifies things greatly if you change the service account – SQL Server Configuration Manager will just change the member of this group instead of having to hunt down and change everywhere that it knows that permissions are needed for the service account. Using these groups instead of the service account will simplify your life also if you ever change the service account – all those specific permissions that you granted on local resources (paths, registry, etc.) would have to be changed. Using the group, it will still have the same permissions.

I consider virtual accounts—particularly when you stick to using the virtual account itself rather than a domain account—to be a really good security feature, as it prevents system administrators from getting lazy and using the same service account everywhere.  This in turn blocks an attacker from using a pass-the-hash strategy to pivot from one SQL Server instance to another.

Comments closed

Getting Finer-Grained Security In Spark

Published 2016-12-22 by Kevin Feasel

Vadim Vaks explains how to get finer-grained permissions within Spark using Ranger and LLAP:

With LLAP enabled, Spark reads from HDFS go directly through LLAP. Besides conferring all of the aforementioned benefits on Spark, LLAP is also a natural place to enforce fine grain security policies. The only other capability required is a centralized authorization system. This need is met by Apache Ranger. Apache Ranger provides centralized authorization and audit services for many components that run on Yarn or rely on data from HDFS. Ranger allows authoring of security policies for: – HDFS – Yarn – Hive (Spark with LLAP) – HBase – Kafka – Storm – Solr – Atlas – Knox Each of the above services integrate with Ranger via a plugin that pulls the latest security policies, caches them, and then applies them at run time.

Read on for more details.

Comments closed

Understanding Data Gateways

Published 2016-12-22 by Kevin Feasel

James Serra walks us through the different data gateways available in Azure:

On-premises data gateway: Formally called the enterprise version.  Multiple users can share and reuse a gateway in this mode.  This gateway can be used by Power BI, PowerApps, Microsoft Flow or Azure Logic Apps.  For Power BI, this includes support for both scheduled refresh and DirectQuery.  To add a data source such as SQL Server that can be used by the gateway, check out Manage your data source – SQL Server.  To connect the gateway to your Power BI, you will sign in to Power BI after you install it (see On-premises data gateway in-depth).

Click through for more details on additional gateways.

Comments closed

Parameterizing Always Encrypted Statements

Published 2016-12-19 by Kevin Feasel

Jakub Szymaszek shows off Parameterizing for Always Encrypted in SSMS 17.0:

First thing to note is that SSMS has rewritten the query as a parameterized statement. The literal, used to initialize the @SSN variable in the original query, is being passed inside a parameter, with an auto-generated name (@pdf9f37d6e63c46879555e4ba44741aa6). This allows the .NET Framework Data Provider for SQL Server to automatically detect that the parameter needs to be encrypted. The driver achieves that by calling sp_describe_parameter_encryption that prompts SQL Server to analyze the query statement and determine which parameters should be encrypted and how. Then, the driver, transparently encrypts the parameter value, before submitting the query to SQL Server for execution via sp_executesql. SQL Server can now successfully execute the query.

Read the whole thing.  Setting this up does obviate part of a benefit to using Always Encrypted:  the ability completely to lock out a database administrator from certain pieces of data.

Comments closed

Hadoop And Active Directory

Published 2016-12-16 by Kevin Feasel

RK Kuppala explains how to integrate a Hadoop cluster with Active Directory:

This post explains kerberizing an existing Hadoop cluster using Ambari. Kerberos helps with the Authentication part of enterprise security (while authorization, auditing and data protection being the remaining parts).

HDP uses Kerberos, which is an industry standard for authenticate users and resources and providing strong identity for users. Apache Ambari can kerberize an existing cluster by using an existing MIT key distribution center (KDC) or Microsoft’s Active Directory.

This was a lot easier than I expected.

Comments closed

SQL Server Port Changes

Published 2016-12-09 by Kevin Feasel

Steve Jones shows how to change the port of your SQL Server instance:

Notice that I have multiple instances here, so I need to choose one. Once I do, I see the protocols on the right. In this case, I want to look at the properties of TCP/IP, which is where I’ll get the port.

If I look at properties, I’ll start with the Protocol tab, but I want to switch to the IP Addresses tab. In here, you can see I’ll see an entry for each of the IPs my instance is listening on. I can see which ones are Active as well as the port. In my case, I have these set to dynamic ports.

My rules of thumb, which might differ from your rules of thumb:  disable the Browser, don’t change off of 1433 for a single instance, and hard-code ports if you happen to be using named instances.  There’s a small argument in favor of “hiding” your instance by putting it onto a higher port (i.e., 50000+), but that’s not a great way of protecting a system, as an attacker can run nmap (or any other port scanner) and find your instance.  The major exception to this is if you also have something like honeyports set up.  In that case, changing the port number can increase security, and will almost definitely increase the number of developers who accidentally get blackholed from the server.

Comments closed