Exporting Masked Data with Dynamic Data Masking

The SQL Herald | Databases et al…

Dynamic Data Masking is a presentation layer that got added to Azure SQL DB and SQL Server 2016. In a nutshell it prevents end users from seeing sensitive data, and lets administrators show some data (e.g. the last 4 digits of social security number) for verification purposes. I’m not going to focus too much on the specifics of data masking in this post—that’s a different topic. This is how once you have a masking strategy you can protect your sensitive data going to other environments.

Well at PASS Summit, both in our booth and during my presentation on security in Azure DB, another idea came up—exporting data from production to development, while not releasing any sensitive data. This is a very common scenario—many DBAs have to export sensitive data from prod to dev, and frequently it is done in an insecure fashion.

Doing this requires a little bit of trickery, as dynamic data masking does not work for administrative users. So you will need a second user.

First step—let’s create a database and a masked table.

CREATE DATABASE DDM_Demo
GO

USE DDM_Demo
GO
CREATE TABLE Membership
(MemberID int IDENTITY PRIMARY KEY,
FirstName varchar(100) MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’) NULL,
LastName varchar(100) NOT NULL,
Phone# varchar(12) MASKED WITH (FUNCTION = ‘default()’) NULL,
Email varchar(100) MASKED WITH (FUNCTION = ’email()’) NULL);

INSERT Membership (FirstName, LastName, Phone#, Email) VALUES
(‘Roberto’, ‘Tamburello’, ‘555.123.4567’, ‘RTamburello@contoso.com’),
(‘Janice’, ‘Galvin’, ‘555.123.4568’, ‘JGalvin@contoso.com.co’),
(‘Zheng’, ‘Mu’, ‘555.123.4569’, ‘ZMu@contoso.net’);

CREATE LOGIN TestUser WITH PASSWORD ‘P@ssw0rd!’
CREATE USER TestUser FROM LOGIN TestUser

GRANT VIEW DEFINITION TO demoexport;
USE msdb
GO

ALTER ROLE db_datareader ADD MEMBER demoexport;
GO
ALTER ROLE db_datawriter ADD MEMBER demoexport;
GO

Next I’ll login as this user and select from the membership table.

image_thumb.png

From here, I’m going to (as the TestUser) take an export of the database. You can do this by selecting the Export Data Tier Application option from the tasks menu in Management Studio.

image.png

I won’t bore you with clicking through the process, but this will give you an export of your database, with the data masked. Your next step is to import the .bacpac file you created. In this case I’m going to the same instance, so I changed the database name.

Right click on “Databases” in SSMS and select “Import Data-tier application”. Import the file you created in the previous step.

image.png

 
Now try selecting as your admin user.

imageimage_thumb.pngimage.png

Boom, you’ve exported and imported masked data in your lower environments.

SQL Server 2016 Database Mail Not Working

One of the nice things about SQL Server 2016 is that .NET 3.5 is no longer required for installation. The .NET requirement wouldn’t have been problematic, but in order to add the .NET feature access to the Windows media was required (this was particularly painful in Azure VMs). Fortunately the product team eliminated the requirement as part of the release. Unfortunately, a few things broke–namely database mail, native log shipping, and distributed replay. You can identify this problem, by a couple of symptoms:

  • Your database mail log has no entries
  • You have messages with a status of “unsent’ in sysmail_allitems in MSDB

Screen Shot 2016-07-27 at 2.41.59 PM

  • If you try to execute the file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe you get a .NET 3.5 error

This should be fixed in CU2, but there are reports below of it failing in SP1. The XML to put in the .config files is:

<?xml version=1.0 encoding=utf-8 ?>

<configuration>

  <startup useLegacyV2RuntimeActivationPolicy=true>

    <supportedRuntime version=v4.0/>

    <supportedRuntime version=v2.0.50727/>

  startup>

configuration>

SQL Server 2016—The Licensing Info

SQL Server 2016 launched last week to great reviews and with a ton of great new features. I have been working with this version for well over a year now and extremely happy to see it hit RTM and be broadly adopted. So as DBAs it always sucks when you get excited about new features, only to find out the price changed, or vendor “O” made that feature a cost option. So what’s new with SQL Server 2016 licensing? (you won’t this as a session title at any upcoming SQL Server events).  Well first the good news—SQL Server 2016 is the same price and 2012 and 2014 (roughly $6800 core for Enterprise Edition). That’s definitely good news—Microsoft gave us a bunch of new functionality and didn’t raise the price. Additionally, if you see my below post on what is in Standard Edition, they added a lot of functionality there, too.

But we know finance and marketing employees have jobs to do as well, and there is no way they were letting a major version release happen without some changes. So let’s take a look at the one’s Denny Cherry (b|t) and I could glean out of the licensing guide. Please download and read for yourself.

Licensing Changes

Additional licenses are required when:
 A single hardware thread is supporting multiple virtual cores. (A core license is required for each v-core.)
 Multiple hardware threads are supporting a single virtual core simultaneously. (A core license allows a
single v-core to be supported by a single hardware thread.)

What does this mean? It means you can’t over provision CPUs on your VMs (which you shouldn’t be doing for DB servers anyway). There may be something that applies to hyperthreading here, but if you are licensing individual VMs, you probably shouldn’t be using hyperthreads.

“Beginning with SQL Server 2016, deploying and running SQL Server PDW is done through SQL Enterprise
Edition Per Core licensing with SA coverage. The number of SQL Server Enterprise Edition core licenses for an
APS appliance will depend on the total number of physical cores in the SQL Server PDW compute servers
configured within the appliance.”

What does this mean? It means you can actually know what an APS costs, at least from a licensing perspective. Hardware costs will need to be gathered from a reseller. This is a good change as it makes SQL Server’s pricing consistent across platforms (pro-tip: use SQLDW)

“For each server licensed with SQL Server 2016 and covered by active SA, customers can run up to the same
number of passive failover instances in a separate, OSE to support failover events. A passive SQL Server
instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The
passive failover instances can run on a separate server. These may only be used to synchronize with the primary
server and otherwise maintain the passive database instance in a warm standby state in order to minimize
downtime due to hardware or software failure.

 The secondary server used for failover support does not need to be separately licensed for SQL Server as
long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as
reports to clients running active SQL Server workloads, or performing any “work”, such as additional
backups being made from secondary servers, then it must be licensed for SQL Server.”

What does this mean? I had to consult Microsoft on this one, as it was a change in my understanding of the “free” secondary licensing benefit. Basically, if you are going to dedicated hardware (that you own or lease) your secondary license (if you have SA) is still included in your primary license. However, if you go to Azure from on-prem for your HA model, you will need to license the secondary. This does get murky because if both of those workloads are in Azure, you only license the primary.

“All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as
needed; however, they can only be reassigned to another server in another server farm, or to a non-private
cloud, once every 90 days.
A server farm may consist of up to two data centers located in time zones that are within four hours of
one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
A given data center may only be part of one server farm.”

What Does this Mean? Basically you can’t cross an ocean for HA or DR and not pay for it.

SQL Server Developer Edition
SQL Server 2016 Developer Edition is a fully featured version of SQL Server software—including all of the
features and capabilities of Enterprise Edition—licensed for development, test and demonstration purposes
only. SQL Server Developer Edition may not be used in a production environment or with product data. Any
test data that was used for design, development or test purposes must be removed prior to deploying the
software for production use.
Customers may install and run the SQL Server Developer Edition software on any number of devices. This is
significant, because it allows customers to run the software on multiple devices (for testing purposes, for
example) without having to license each non-production server system.”

What does this mean? It basically means only your production environments need to be licensed as long as you are following Microsoft’s rules for not using production data. This is a huge benefit, note that you can’t just restore prod to dev, you need to create some testing data, which is best practice anyway.

“Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016
edition.”

What does this mean? You need to have 2012 licenses (at least) to upgrade to SQL 2016. Basically Microsoft wants to make sure you went through the core conversion in 2012.

Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016
edition.”

What does this mean? If you had BI edition, your licenses will get converted to Enterprise Edition. If you read further into page 29, you will see that BI edition customers will be treated quite favorably. Any time there is a change like this, it’s usually a good time to negotiate with your Microsoft sales professional about getting a better deal.

Summary

There are no earth shattering changes in SQL Server 2016 licensing. The developer edition changes are probably the most significant, and can reduce your overall costs a great deal.

SQL Server 2016—Standard Edition Doesn’t Suck!

Yesterday Microsoft announced SQL Server 2016 was launching on June 1st. I can say this is the most production ready version of SQL Server I have ever worked on. I have had a customer on production since last August. We have been very happy and stable, and performance has been fantastic, we are using columnstore, availability groups, and lately R integration. All of these features are tested first in Azure, then deployed to the on-premises product which allows you to have a fully tested enterprise class RDBMS on day 1 of GA.

But What About Standard Edition

In my circles, there are number of people who are complaining about the lack of features in standard edition. While I do agree that Always Encrypted should be in every version, as lack of strong data encryption is a problem that continues to confound IT. Putting Always Encrypted in all editions would be a good start to having wide ISV adoption of the Always Encrypted feature.

However, even without Always Encrypted, Microsoft added a LOT of new features to Standard Edition. Let’s list them (no specific order here):

  • Temporal Tables
  • Query Store
  • Basic Availability Groups
  • Row Level Security
  • Dynamic Data Masking
  • Basic R Integration
  • Tabular Mode of Analysis Services
  • JSON Support
    I saw a complaint about the Tabular support only being 16 GB, which equates to (with typical compression) 100-150 GB which a very reasonable size model. I’ve also seen complaints about Standard Edition only addressing 128 GB of RAM. Microsoft is not a charity, their end goal is to make their shareholders money. There are a bunch of smart finance people, who make these calculations. If Microsoft increased the memory limit to standard edition to say 512 GB, this might mean 25% fewer customers (note—I made these numbers up) buy Enterprise Edition. Here is the Microsoft description of the aim of standard edition.

Standard
SQL Server Standard provides core data management and business intelligence capabilities for non-critical workloads with minimal IT resources.

If you are running mission critical workloads, and need high levels of uptime, and the tools that come with Enterprise  Edition, you need to pay the big bucks (and it’s still way the hell cheaper than Oracle, no matter how your calculate it) or there’s another option.

The Other Option—Azure SQL DB

You may have noticed Microsoft is making a push towards cloud computing (sarcasm). Cloud computing is going to be the defining trend of the next decade and beyond. Microsoft has been careful to avoid feature limitations in Azure SQL Database (there are a couple, columnstore and in-memory are only available in Premium, but that’s just because of limited hardware resources). Want features like partitioning, data compression, and online index rebuilds? Use SQL DB and all of those features are available to you. Always Encrypted is available at all service tiers, and has been since last summer. You can even have scale out readable secondaries now, even in Basic edition. Microsoft, through these actions, has incentivized moving your databases into Azure (and with elastic pools, it’s easier than ever).  While there while there will always be some on-premises systems,the cloud is changing the way products are developed and sold, and the way customers deploy. Don’t get left behind!

%d bloggers like this: