Press "Enter" to skip to content

Category: Security

Always Encrypted Powershell Cmdlets

Published 2016-10-10 by Kevin Feasel

Sanjay Mishra alerts us to new Powershell cmdlets for enabling Always Encrypted on columns:

The July 2016 release of SSMS (and later versions) introduced a set of PowerShell cmdlets through a new ‘SqlServer’ module. This pagedescribes the various capabilities that these cmdlets bring to the table. Of most interest to the specific scenario described above is the Set-SqlColumnEncryption cmdlet. In the post below, we will walk through the steps required to use this – first from a PowerShell session to test the capability, and then finally from a C# application which is using PowerShell Automation to invoke the cmdlets from an application.

As a side note it is worth knowing that the cmdlets in the ‘SqlServer’ PowerShell module can also be used for automating key setup and management (and are, in many ways, more powerful than SSMS – they expose more granular tasks, and thus can be used to achieve role separation and to develop a custom key management workflow – but that is likely a topic for a separate post!)

Sanjay also includes a sample Powershell script to show how it works.

Comments closed

Power BI Row-Level Security With External Users

Published 2016-10-07 by Kevin Feasel

Patrick LeBlanc shows how to implement row-level security within Power BI for people without direct access to an underlying Analysis Services cube:

Before I explain how to fix this, let’s take a look at what’s happening behind the scenes.

  1. When jdoe@adventureworks.com opens the dashboard a connection string is created including the effectiveusername property, which is expected behavior.

  2. The value specified for this property is jdoe@adventureworks.com.

  3. The connections string including the queries are sent via the On-Premises gateway to the SSAS server that hosts the data needed to view the report.

  4. Once the connection is established, using the username and password specified in the Data Source settings, all queries are executed usingjdoe@adventureworks.com.

Read on for the solution.

Comments closed

Finding All Sysadmins

Published 2016-09-30 by Kevin Feasel

Chris Bell has a Powershell script to find all sysadmins on a SQL Server instance:

The script below identifies the accounts on your SQL Server that have full sysadmin rights, either on their own or via an Active Directory Group.

To run this, you need a few things setup first.

  1. A file named Instances.txt that has each instance you are going to check on its own line. Just the name, nothing more. You can see the reference to the location at the beginning of the script, just change it to wherever you put your file.

  2. Rights to read the AD information for the domain. This way we can get the members of any groups granted access to your SQL environment.

Click through for the script.

Comments closed

TDE With Database Mirroring

Published 2016-09-27 by Kevin Feasel

I have a post on setting up database mirroring when the underlying database uses Transparent Data Encryption:

 Now it’s time to take some backups. First, let’s back up the various keys and certificates:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE [master]
GO
--Back up the service master key
--Note that the password here is the FILE password and not the KEY password!
BACKUP SERVICE MASTER KEY TO FILE = 'C:\Temp\ServiceMasterKey.key' ENCRYPTION BY PASSWORD = 'Service Master Key Password';
GO
--Back up the database master key
--Again, the password here is the FILE password and not the KEY password.
BACKUP MASTER KEY TO FILE = 'C:\Temp\DatabaseMasterKey.key' ENCRYPTION BY PASSWORD = 'Database Master Key Password';
GO
--Back up the TDE certificate we created.
--We could create a private key with password here as well.
BACKUP CERTIFICATE [TDECertificate] TO FILE = 'C:\Temp\TDECertificate.cert'
    WITH PRIVATE KEY (FILE = 'C:\Temp\TDECertificatePrivateKey.key', ENCRYPTION BY PASSWORD = 'Some Private Key Password');
GO

Click through for the details.

Comments closed

WOxCompliant Update

Published 2016-09-23 by Kevin Feasel

Chris Bell has an updated version of his WOxCompliant:

What changed?

  1. I fixed an issue that would cause a continual loop to occur and hang the script indefinitely. With this fix, my tests are returning results in just seconds now!

  2. Corrected various typos and details in the results

  3. If you had xp_Cmdshell active before the script, it used to turn it off at the end for compliance. Now the script checks and leaves it active if you had it active. It will still notify you of the results though

This is one of my favorite third-party scripts for configuring a database.

Comments closed

Firewall Configuration With Powershell

Published 2016-09-15 by Kevin Feasel

Slava Murygin gives an introduction to firewall configuration using Powershell:

The Script has list of adjustable filters:
$Direction – Direction of firewall rule: Inbound or Outbound;
$Action – Action rule performs: Allow or Block;
$Enabled – Status of a rule: Enabled – True or False;
$RuleGroup – Group rule has been assigned. By default script uses “$Null” variable, which filters all rules without assigned group. However you can specify a group a name if necessary;
$DisplayName – Name of a rule. By default I use an expression “*SQL*” to search for rules which have word “SQL” in their name. To retrieve all rules us “*”. To retrieve any particular rule use rule name.

He looks at viewing rules as well as creating, modifying, and deleting them.

Comments closed

Securing Elasticsearch And Kibana

Published 2016-09-13 by Kevin Feasel

Vikash Selvin shows how to secure instances of Elasticsearch and Kibana:

The most popular options for securing Elasticsearch and Kibana are compared in the table below.

Shield is a security plugin developed by the same company that developed Elasticsearch. It allows you to easily protect this data with a username and password while simplifying your architecture. Advanced security features like encryption, role-based access control, IP filtering, and auditing are also available when you need them.

NGINX is an open source web server. It can act as a proxy server and can do load balancing, among other things. In combination with LUA and external scripts, it can be used for securing Elasticsearch and Kibana. We will be using this approach in this tutorial.

Searchguard is an open source alternative for Shield. It provides almost all the same functionalities as Shield, except for some features like LDAP authentication. However, these features are available in the paid variant.

Click through for a detailed NGINX setup.

Comments closed

Data Masking And Row-Level Filtering In Hadoop

Published 2016-09-09 by Kevin Feasel

Syed Mahmood and Srikanth Venkat discuss two security features in Apache Ranger:

Dynamic data masking via Apache Ranger enables security administrators to ensure that only authorized users can see the data they are permitted to see, while for other users or groups the same data is masked or anonymized to protect sensitive content. The process of dynamic data masking does not physically alter the data, or make a copy of it. The original sensitive data also does not leave the data store, but rather the data is obfuscated when presenting to the user. Apache Ranger 0.6 included with HDP 2.5, introduces a new type of authorization policy called “Masking Policy” that can used to define which specific data fields are masked and what are the rules for how to anonymization or pseudonymize the specific data. For example, a security administrator may choose to mask credit card numbers when displayed to customer service personnel, such that only last four digits are rendered in the form of XXXX-XXXX-XXXX-0123. The same would be true of sensitive data such as social security numbers or email addresses that are masked to be rendered in a different formats based on data masking rules.

This is part one of a two-part series; part two will dig into the technical details.  I have to wonder if Ranger’s dynamic data masking is as easy to circumvent as SQL Server’s.

Comments closed

Ambari And Active Directory

Published 2016-09-08 by Kevin Feasel

Jon Morisi documents his efforts in getting Ambari to play nicely with Active Directory over Kerberos:

You then need to trust the certificate on all the linux hosts
From the IBM article:

  1. Create ‘/etc/pki/ca-trust/source/anchors/activedirectory.pem’ and paste the certificate contents

  2. Trust CA cert: sudo update-ca-trust enable; sudo update-ca-trust extract; sudo update-ca-trust check

  3. Trust CA cert in Java:

  4. mycert=/etc/pki/ca-trust/source/anchors/activedirectory.pem sudo keytool -importcert -noprompt -storepass changeit -file ${mycert} -alias ad -keystore /etc/pki/java/cacerts

  5. And at last, please make sure every node on your cluster has access to the ad host.

LDAP support is a key part of setting up a production Hadoop cluster.

Comments closed