Press "Enter" to skip to content

Category: Security

Identity As A Service

Published 2016-10-11 by Kevin Feasel

Cristian Satnic argues that we should look at Identity as a Service solutions for our applications:

What exactly is Azure Active Directory B2C?

  • Cloud identity service with support for social accounts and app-specific (local) accounts

  • For enterprises and ISVs building consumer facing web, mobile & native apps

  • Builds on Azure Active Directory – a global identity service serving hundreds of millions of users and billions of sign-ins per day (same directory system used by Microsoft online properties – Office 365, XBox Live and so on)

  • Worldwide, highly-available, geo-redundant service – globally distributed directory across all of Microsoft Azure’s datacenters

I am a big fan of OAuth and making it easy for line-of-business developers to deal with authentication (lest they get harebrained ideas like rolling their own encryption algorithms).

Comments closed

Auditing In Power BI

Published 2016-10-11 by Kevin Feasel

Ginger Grant shows off some of the auditing capabilities within Power BI:

As you can see by looking at the available Power BI options, there are a number of options to choose from. If you select the top item PowerBI activities, then everything gets selected. After doing that click outside of the menu for the menu to go away. Select a date and time range of your choosing, select specific users if you wish, then click on the Search button. Depending on how big your date range is, this may take some time to load. Once you see the results, you have the ability to filter as well.

Another day, another two dozen new Power BI features…  This one’s a good one.

Comments closed

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