Press "Enter" to skip to content

Category: Security

Enabling SQL Server SSL Encryption With Powershell

Published 2017-08-23 by Kevin Feasel

Tracy Boggiano shows how to create and put into place SSL encryption certificates on SQL Server instances using Powershell:

Next we take the exported pfx file and copy it locally to the temp folder of each machine and import into the local certificate store.  Then we edit the registry with the thumbprint of the certificate.  After that you will have to restart SQL Server to get the changes to take effect.  We also clean up after ourselves and delete the pfx from the temp folder.

Note: To make this safe for production I commented out the restart of SQL Server.  Also, Get-CmsHosts cmdlet can found here.

If you’re dealing with sensitive information, enabling (and forcing!) SSL encryption is one of the easiest effective ways of securing an instance; in this case, it’s securing data in transit from SQL Server to and from the client.

Comments closed

Synchronizing Logins And Jobs

Published 2017-08-23 by Kevin Feasel

Ryan Adams shows five methods for synchronizing SQL logins and a couple ways of synchronizing SQL Agent jobs between instances of SQL Server:

Robert Davis wrote a great script back when he published his Mirroring book.  I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins.  His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”.  It turns out that the publisher didn’t get the right script version published with the book.  That’s when this post from Robert with the full script was born…

Transferring Logins

I also wrote about it HERE.

This script creates a stored procedure to handle the move and also uses Linked Servers.  If you can’t have linked servers in your environment this is not a good choice for you.  However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer.  It breaks the rule but it does it fast enough maybe no one will notice.

Read the whole thing.

Comments closed

CLR Strict Security Done Easier

Published 2017-08-22 by Kevin Feasel

Solomon Rutzky continues his CLR Strict Security series with an easier way of creating a secure assembly:

This solution is easier than Solution 1:

8 steps instead of 22!

No extra Project

However, a very small amount of risk was added by overriding the default MSBuild workflow for SSDT. This risk can be eliminated if Microsoft provides a pre-defined Target for the appropriate event. Please upvote my suggestion to have this happen: Add MSBuild predefined Targets for “BeforeSqlBuild” and “BeforePublish” to SSDT SQL Server Data Projects.

ALSO: Even though we did not sign the assembly with a Strong Name Key, it is still probably a good idea to do that.

If you use CLR, this is worth the read.

Comments closed

Talking To Secure Hadoop Clusters

Published 2017-08-18 by Kevin Feasel

Mubashir Kazia shows how to connect to a secured Hadoop cluster using Active Directory:

The primary form of strong authentication used on a secure cluster is Kerberos. Kerberos supports credentials delegation where a server process to which a user has authenticated, can perform actions on behalf of the user. This involves the server process accessing databases or other web services as the authenticated user. Historically the form of delegation that was supported by Kerberos is now called “full delegation”. In this type of delegation, the Ticket Granting Ticket (TGT) of the user is made available to the server process and server can then authenticate to any service where the user has been granted authorization. Until recently most Kerberos Key Distribution Center(KDC)s other than Active Directory supported only this form of delegation. Also Java until Java 7 supported only this form of delegation. Starting with Java 8, Java now supports Kerberos constrained delegation (S4U2Proxy), where if the KDC supports it, it is possible to specify which particular services the server process can be delegated access to.

Hadoop within its security framework has implemented impersonation or proxy support that is independent of Kerberos delegation. With Hadoop impersonation support you can assign certain accounts proxy privileges where the proxy accounts can access Hadoop resources or run jobs on behalf of other users. We can restrict proxy privileges granted to a proxy account to act on behalf of only certain users who are members of certain groups and/or only for connections originating from certain hosts. However we can’t restrict the proxy privileges to only certain services within the cluster.

What we are discussing in this article is how to setup Kerberos constrained delegation and access a secure cluster. The example here involves Apache Tomcat, however you can easily extend this to other Java Application Servers.

This is a good article showing specific details on using Kerberos in applications connecting to Hadoop.

Comments closed

One CLR Solution

Published 2017-08-18 by Kevin Feasel

Solomon Rutzky continues his SQL Server 2017 CLR security series:

This new requirement prevents the technique described towards the end of Part 1 from working. That technique uses a SAFE Assembly as an indirect means of creating the Asymmetric Key to create the Login from. That worked perfectly prior to SQL Server 2017, but now even SAFE Assemblies require that the signature-based Login be created first, which now puts us in a whole chicken-egg paradox.

Before proceeding to the solution, it should be noted that yes, Microsoft has, as of RC2 (released on 2017-08-02), provided a kinda/sorta “fix” for this that allows for creating an Assembly without having the signature-based Login. HOWEVER, that “fix” is absolutely horrible, convoluted, and unnecessary. It should not be used by anyone. Ever! In fact, it should be completely removed and forgotten about. In no uncertain terms: it is not an option! To help clarify, I am being intentionally vague about that new feature here (and in Part 1) so as not to distract from these two solutions (this post and Part 3) that do not promote bad practices; it will be covered starting in Part 4.

Solomon outlines one approach to dealing with CLR security changes, though it’s a bit lengthy.

Comments closed

Thoughts On CLR Strict Security

Published 2017-08-16 by Kevin Feasel

Solomon Rutzky has started a series on CLR in SQL Server 2017 and lays down a gauntlet:

What all of that means is that, assuming clr strict security is “1” (i.e. enabled), and TRUSTWORTHY is “OFF” for the Database in which an Assembly is being created, then in order to create any Assembly you first need to:

  1. Sign the Assembly with a strong-name key or a certificate
  2. Create an Asymmetric Key or Certificate in master from whatever you signed the Assembly with
  3. Create a Login based on that Asymmetric Key or Certificate
  4. Grant that Login the UNSAFE ASSEMBLY permission

Is that really so bad? Aren’t many of us (hopefully!) already doing that?

Solomon’s not very happy with the way that CLR security works in 2017, but he does have solutions of his own in mind.

Comments closed

Row-Level Security In Power BI

Published 2017-08-15 by Kevin Feasel

Paul Turley has a video showing how to use row-level security with Power BI:

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend on will depend on your data and requirements.  The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects.

Click through to watch the video.

Comments closed

Indexed Views And Row-Level Security

Published 2017-08-15 by Kevin Feasel

Louis Davidson wants to figure out how indexed views work when you have row-level security enabled:

I was giving my Row Level Security session last weekend for the Richmond User Group (@RichmondSQL http://rva.pass.org/) and a question came up (ok, so I technically asked the question). How does the feature work with indexed views?

Generally speaking, the answer turned out to be obvious. You can apply a RLS Security Policy to a view that has an index on it, but not to a table that is referenced by an indexed view.

Read on for Louis’s demonstration.

Comments closed

Inference Attacks

Published 2017-08-03 by Kevin Feasel

Phil Factor explains that your technique for pseudonymizing data doesn’t necessarily anonymize the data:

It is possible to mine data for hidden gems of information by looking at significant patterns of data. Unfortunately, this sometimes means that published datasets can reveal sensitive data when the publisher didn’t intend it, or even when they tried to prevent it by suppressing any part of the data that could enable individuals to be identified

Using creative querying, linking tables in ways that weren’t originally envisaged, as well as using well-known and documented analytical techniques, it’s often possible to infer the values of ‘suppressed’ data from the values provided in other, non-suppressed data. One man’s data mining is another man’s data inference attack.

Read the whole thing.  One big problem with trying to anonymize data is that you don’t know how much the attacker knows.  Especially with outliers or smaller samples, you might be able to glean interesting information with a series of queries.  Even if the application only returns aggregated results for some N, you can often put together a set of queries where you slice the population different ways until you get hidden details on individual.  Phil covers these types of inference attacks.

Comments closed

Whitelisting SQL CLR Assemblies

Published 2017-07-24 by Kevin Feasel

Niels Berglund walks through the process of whitelisting a CLR assembly in SQL Server 2017:

What Microsoft introduces in SQL Server 2017 RC1, is something I refer to as whitelisting. It is somewhat similar to the TRUSTWORTHY setting, where you indicate that a database is to be trusted. But instead of doing it on the database level, you do it per assembly.

To whitelist in SQL Server 2017 RC1, you use the system stored procedure sys.sp_add_trusted_assembly. As the name implies the procedure adds an assembly to a list of “trusted” assemblies. By marking an assembly as trusted, SQL Server will allow it to be loaded when clr strict security is on (on by default), even if:

  • the assembly is not signed, and

  • the database where you want to deploy it to is not TRUSTWORTHY.

With the elimination of the CAS model finally hitting CLR, this is probably going to be one of the easier ways for DBAs to move forward with CLR in the future.

Comments closed