Voice of the DBA

Tag: Data Masker

Powershell and Data Masking with SQL Provision
Just a quick post here after the PASS Marathon Webinar during which I talked about the GDPR effects around the world. In the talk, I demo’d SQL Provision, which is SQL Clone + Data Masker for SQL Server. Someone asked for the PoSh, so here it is.
```
# Connect to SQL Clone Server

$mycredential = Get-Credential

#$password = Get-Content .\socratescredential.txt | ConvertTo-SecureString

#$mycredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "home\sjones", $password

Connect-SqlClone -ServerUrl 'http://SOCRATES:14145' -Credential $mycredential

# Set variables for Image and Clone Location

$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName 'PLATO' -InstanceName 'SQL2016'

$SqlServerDevInstance = Get-SqlCloneSqlServerInstance -MachineName 'PLATO' -InstanceName 'SQL2016_qa'

$ImageDestination = Get-SqlCloneImageLocation -Path 'E:\SQLCloneImages'

$ImageScript = 'e:\Documents\Data Masker(SqlServer)\Masking Sets\datamaskdemo.DMSMaskSet'

# connect and create new image

$NewImage = New-SqlCloneImage -Name 'GDPRImage2' -SqlServerInstance $SqlServerInstance -DatabaseName DataMaskerDemo -Destination $ImageDestination -Modifications @(New-SqlCloneMask -Path $ImageScript) | Wait-SqlCloneOperation

#Demo pause

Start-Sleep -Seconds 2

$DevImage = Get-SqlCloneImage -Name 'GDPRImage2'

# Create New Masked Image from Clone

$DevImage | New-SqlClone -Name "GDPR2" -Location $SqlServerDevInstance | Wait-SqlCloneOperation
```
This uses SQL Clone, and I’ve coded in my server here, just for simplicity. I assume you could use your own variable or parameter, but if not, you need to learn a bit more PoSh.

This script works as follows: First, I get credentials for my server. I have a lab domain, but my primary desktop isn’t on the domain for various reasons. As a result, I need to authenticate. The Connect-SqlClone cmdlet is used to do this.

Once I connect, I need to get the SQL Server instance used for cloning. In this case, I need two. One ($SqlServerInstance) is production and one is development ($SqlServerDevInstance).

I set the location for the image, which is a folder here, but this should be a share in your environment as you typically have images used for multiple developers. If you watched the webinar, that was the warning that popped up since I used a local path and not a share.

The data masking is done with Data Masker for SQL Server. I’ve written a few pieces on this, but essentially the GUI creates a file that describes the masking rules. In this case, I set a variable to the file.

The creation of the image is with the New-SqlCloneImage cmdlet. This needs an instance to use for copying the data and then the parameters for the db and the masking script. The key here is the script needs to be a New-SqlCloneMask object. Hence the @(). If you use a T-SQL script, and you can, you need to use the New-SqlCloneSqlScript object.

From there I use New-SqlClone to cerate the actual database for the developers. I could automate this and create multiple ones if I wanted.

One last note, Wait-SqlCloneOperation is good in scripts as a few things take time to complete. I added the Start-Sleep for demos since things take longer at times, and in a demo seconds matter. I need the consistency. In an overnight script, I’d likely leave this out.

I have other articles on Data Masker if you’re interested.
March 13, 2018
Table Internal Sync Rule–Substituting the Sync Column

I’ve been playing with Data Masker for SQL Server v6 and it’s an interesting product. I like the way it works, but I do find it a little challenging sometimes to figure out how to mask values. I’ve written a set of posts for different scenarios.

In a previous post I showed how I could change column values across rows in a table. This is useful in that it moves my data from this:

to this:

That’s good, but since I haven’t changed the myid column, I really haven’t masked things well. In fact, it would be trivial to derive the initial mychar values from the set if I knew the original values.

Let’s fix that.

My masking set looks like this for now:

I want to add a new substitution rule that will mix up my myid values. I add the rule and configure the correct column. In this case I limit the boundaries of my random numbers to 1-100, but I could choose any value.

I save this and run just this rule. Now my data looks like this:

We’re partway there, but I don’t have grouping. Elric has two different values for myid, which is wrong. Let’s fix that. Just as in the previous post, we’ll now add a Table Internal Sync rule. We configure this in reverse of the previous article. Now we use the new, changed names in mychar as the group column and myid as the column to sync across rows.

If I execute this, then I get the following:

Tada.

Now I just need to ensure these rules run in order (all four) with dependencies and when I run the entire set, I’ll get my table synced. Here’s the final rule set. Notice that I’ve created dependencies.

I’ll save the set, and then re-run it. Now I get these results:

Data masking is something that’s never been this easy for me. I’ve built lots of large scripts that made perfect sense for me, but were difficult to turn over to anyone else for usage and maintenance.

I’d urge you to give Data Masker a try if you’re looking to ensure compliant, safe data sets for your non-production environments.

I have other articles on Data Masker if you’re interested.

March 6, 2018
Data Masker for SQL Server–Syncing Values Across Rows
I’ve been playing with Data Masker for SQL Server v6 and it’s an interesting product. I like the way it works, but I do find it a little challenging sometimes to figure out how to mask values. I’ve written a set of posts for different scenarios.

We had a customer ask about how to mask data across rows. The customer had some data in a table that was a standalone table, and contained data in a column that matched across rows. They wanted this changed, but the matching between rows kept.

In other words, here’s a small mocked set of the original data:
```
myid        Mychar     myint       mytinyint
----------- ---------- ----------- ---------
1           Steve      12345       1
1           Steve      12345       2
2           Andy       12345       3
2           Andy       12345       4
3           Brian      12345       5
3           Brian      12345       6
3           Brian      12345       7
```
Here are the results they want:
```
myid        Mychar     myint       mytinyint
----------- ---------- ----------- ---------
1           aaa      12345       1
1           aaa      12345       2
2           bbb       12345       3
2           bbbb       12345       4
3           ccc      12345       5
3           ccc      12345       6
3           ccc      12345       7
```
I thought this was an interesting scenario, so how do we mask this? It’s not that hard, so let me show you this.

First, let’s create a new masking set. I won’t walk through that here, but once you have a set connected to your database, here’s what we do.

First, we need to substitute data out. In this case, I’ll substitute the name only. In a real world, we’d probably need to substitute the myid and myint columns as well, but I’ll leave those again.

I add a new Substitution rule first.

This is a standard rule. I’ll add my column and pick a dataset. In this case, I’ll just pick make first names (Names, First, Male) and use that. This will result in a random set of names. I’ve chosen unique values. This is important as across a large number of rows, I could end up with random values that match, but with different MyID values. That would be bad.

If I save and run this rule, I’ll see something like this.

Not quite what I need, but it’s a start. The important thing is that the first myid=1 is different from the first myid = 2, which is different from myid=3.

Next we’ll add a Table Internal Sync rule. This is the rule that fixes values across rows inside a table. Here’s the basic config. Note that I choose a table and then I choose the columns that need syncing, in this case just the mychar column.

I need a way to determine which sets of rows should match. In this case, the myid column is used for that. If you examine the initial set, I have the same values for each name. This is what groups things together, so I’ll use this.

One Note: The red “I” to the right means this isn’t an indexed column. If I wanted better performance, I can add an index for this column, either permanently or just for the masking process.

Now I execute this rule, and I see these results:

I have my groups back.

I could expand this to include other columns as well, substituting the myint column in my first rule and including it in the second.

Setup Scripts

Here is the code to set this up:
```
CREATE TABLE MyTestMask
( myid INT
, Mychar VARCHAR(10)
, myint INT
, mytinyint TINYINT PRIMARY KEY
)
GO
INSERT dbo.MyTestMask ( myid,
    Mychar,
    myint,
    mytinyint
)
VALUES
  ( 1, 'Steve', 12345, 1)
, ( 1, 'Steve', 12345, 2)
, ( 2, 'Andy', 12345, 3)
, ( 2, 'Andy', 12345, 4)
, ( 3, 'Brian', 12345, 5)
, ( 3, 'Brian', 12345, 6)
, ( 3, 'Brian', 12345, 7)
GO
```
I’d urge you to give Data Masker a try if you’re looking to ensure compliant, safe data sets for your non-production environments.

I have other articles on Data Masker if you’re interested.
March 2, 2018