Redgate – Page 90 – Voice of the DBA

The SQL Privacy Summit

This May 18th, Redgate is putting on a SQL Privacy Summit for people that are looking for solutions to help them comply with the GDPR regulations. I’ll be heading over to participate, and I’m looking forward to hearing from customers and attendees about the challenges they’re facing.

The Details

Friday May 18th 2018

The Grange Tower Bridge Hotel, 45 Prescot Street, London E1 8GP

8:15am – 5:30pm (GMT – convert)

The schedule is out and I’ll be doing a variation of a talk I’ve delivered before on how the GDPR is really asking for solid data practices that we’d all like to implement. There are some panel sessions and lots of networking time built in.

Registration

This is an all day conference, though early bird rates continue through this week. You can purchase a ticket for the event from the event announcement. If you’re a customer, contact sales, and you may be able to get a set of discounted tickets.

Hopefully I’ll see you there and we’ll get the chance to talk about how we can all do a better job securing and protecting our sensitive data.

March 14, 2018

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

Catch up on SQL in the City 2018–GDPR Edition

The videos for our SQL in the City 2018 broadcast from last month are up on the Redgate channel in a playlist. If you missed anything or would like to rewatch something, you can do it.

If you’ve got questions or comments, we’d love to hear from you.

March 8, 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.