Voice of the DBA

Tag: syndicated

Backwards Compatible Symmetric Keys in SQL Server 2017+
I discovered recently that there was a change made in SQL Server 2017 to the way that symmetric key passphrases are hashed. There’s a KB article that notes the fix, but basically the passphrases used to be encrypted with SHA1. That’s cryptographically insecure, so the algorithm was updated to SHA2.

This is a problem, and can cause some issues. I’ll show the issue and then how to get around it.

No More Decryptions

Let’s say I have a SQL Server 2016 instance and database. I run this code:
```
CREATE SYMMETRIC KEY SalaryKey

WITH ALGORITHM = AES_256

, IDENTITY_VALUE = 'Salary Protection'

, KEY_SOURCE = 'Protect this from hackers'

ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';

GO

OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD  = 'SomeReallyStr0ngP@ssword'
UPDATE dbo.Employees
  SET EncryptedSalary = ENCRYPTBYKEY(KEY_GUID('SalaryKey'), CAST(Salary AS VARCHAR(50)))

GO
```
I can easily decrypt this data:

Let’s now say I move this data to SQL Server 2017. It could be a restore, some ETL, replication, etc. In any case, I have the data there.

Now, if I drop the symmetric key, or it doesn’t exist, I need to recreate it. These are supposed to be deterministic, which means I can run the code above and get the same key. I’ve done this on SQL 2014 and SQL 2016 databases, and I can decrypt data encrypted in another database if I use the same code to create the key. Let’s try this. I’ll run this code:
```
CREATE SYMMETRIC KEY SalaryKey

WITH ALGORITHM = AES_256

, IDENTITY_VALUE = 'Salary Protection'

, KEY_SOURCE = 'Protect this from hackers'

ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';

GO

OPEN SYMMETRIC KEY SalaryKey DECRYPTION BY PASSWORD  = 'SomeReallyStr0ngP@ssword'

SELECT top 10
  e.EmpID

, e.EmpSSN

, e.Salary

, CAST(DECRYPTBYKEY(e.EncryptedSalary) AS VARCHAR(50)) AS DecryptedSalary

, e.EncryptedSalary
  FROM dbo.Employees AS e

GO
```
I get this:

Why do I get NULL? SQL Server can’t decrypt this data, so it returns a NULL This isn’t supposed to happen, but the hash change caused this.

Let’s fix this.

A Trace Flag

The KB article linked above mentions that trace flag 4631 will fix this. Let’s try it. I’ll run this code:
```
DROP SYMMETRIC KEY SalaryKey

DBCC TRACEON( 4631)

GO

CREATE SYMMETRIC KEY SalaryKey

WITH ALGORITHM = AES_256

, IDENTITY_VALUE = 'Salary Protection'

, KEY_SOURCE = 'Protect this from hackers'

ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';

GO
```
Now, let’s open the key and requery:

Hmm, this doesn’t seem right. With a little experimentation, I discovered the trace flag needs to be global, or it can be enabled instance wide. Let’s do that.
```
DROP SYMMETRIC KEY SalaryKey

DBCC TRACEOFF( 4631)

DBCC TRACEON( 4631, -1)

GO

CREATE SYMMETRIC KEY SalaryKey

WITH ALGORITHM = AES_256

, IDENTITY_VALUE = 'Salary Protection'

, KEY_SOURCE = 'Protect this from hackers'

ENCRYPTION BY PASSWORD = 'SomeReallyStr0ngP@ssword';

GO
```
Now we query, and this works.

Most people don’t deal with column encryption, but if you do, be aware of this.
December 23, 2019
Using Parameters in #SQLPrompt
I am a big fan of snippets in SQL Prompt, often using them in demos to quickly get code written. However, I’ve liked the idea of snippets and templates for a long time. These are great time savers, and they can dramatically improve productivity and code quality.

How? If you have certain constructs in your environment that developers struggle to remember or implement, make a snippet. This makes things very easy and consistent. It’s a great way to help younger developers learn as well.

Here’s an example.

Create Primary Keys

I have worked with no shortage of developers that build tables like this:
```
CREATE TABLE Shipper

( ShipperKey INT NOT NULL

, ShipperName VARCHAR(100)

, ShipperAddress VARCHAR(100)

, ShipperCity VARCHAR(100)

, ShipperRegion VARCHAR(20)

, CountryCode CHAR(3)

)

GO
```
This isn’t a great design, but more importantly, deploying this results in a heap. Perhaps another issue is that there are no indexes, which isn’t usually a good idea.

A better idea might be a table like this:
```
CREATE TABLE dbo.Shipper

( ShipperKey INT NOT NULL CONSTRAINT ShipperPK PRIMARY KEY

, ShipperName VARCHAR(100)

, ShipperAddress VARCHAR(100)

, ShipperCity VARCHAR(100)

, ShipperRegion VARCHAR(20)

, CountryCode CHAR(3)

)

GO

CREATE INDEX Shipper_Region ON dbo.Shipper (ShipperRegion)
```
Now we can’t template all of this, but we can do a few things. I’ll show you how Prompt facilitates this.

A Customized Snippet

Let’s start with the basic code. I know I need a table name, I’ll want a PK, and I want to help someone add at least one index. With that in mind, I’ll build this snippet code. Note that I’ve replaced the table name with a parameter. I did this with a search and replace in the script.
```
CREATE TABLE dbo.$TableName$

( $TableName$Key INT NOT NULL CONSTRAINT $TableName$PK PRIMARY KEY
   $CURSOR$

)

GO

CREATE INDEX $TableName$_ ON dbo.$TableName$ ()
```
You can see this in the SQL Prompt Snippet Manager. Note that the parameter (or placeholder) has been inserted in the bottom by Prompt.

One other thing I might do is add a schema placeholder like this:

Note that I’ve added a default for schema, as this is usually dbo. I’ll also click the up arrow to the right to ensure schema is entered first.

Now, let’s use this. I’ll save this and close the options. Then in a new query window, I’ll type my snippet beginning as “crt”. I see this:

My snippet is listed. I can select it and I’ll then see this code. See how Prompt has inserted my snippet, but already highlighted the schemaname parameter and given me the intellisense of the schemas in my database.

I’ll type dbo and Enter. Prompt moves to the next placeholder parameter. Here I see TableName highlighted all over.

If I type “Shipper”, I see this.

Now I’ll hit Enter again. This time Prompt puts the cursor where I need it to start entering other columns.

I can easily enter my columns, and now my table has a PK. What’s more, if I enter a few columns and run this, I’ll see an error.

The table was created, but the index statement isn’t correct. While this doesn’t necessarily ensure developers follow naming standards or create an index, at least this will get them to think about it. They can correct the statement by adding a column to the index statement between parenthesis, and hopefully change the name.

If you haven’t seen how Prompt can really improve your coding, download an eval today and give it a try. If you have it, take advantage of snippets.
December 20, 2019
Using a PoSh variable in a string- #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is something I haven’t quite understood or used often, but I’ve been aware of it and wanted to learn more.

A member at SQLServerCentral wanted to embed a value in a string, and was having issues. In this case, they had this code:
```
$dt = get-date -format "_yyyyMMMdd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017" |
Export-Csv -Path E:\Documents\sql\$dt.csv -NoTypeInformation
```
In this case, there was an error with the Export-Csv cmdlet, with a syntax issue near the period. I suspected this was some variable expansion that didn’t work.

I found this post that helped me understand a bit more and decided to experiment a bit. Let’s try some things. First, I used to do this type of code:
```
$dt = Get-Date –format “yyyyMMdd”
write-host(“Today is “ + $dt)
```
I then see this:

However, I can use this code:
```
 write-host("Today is $dt")
```
That gives me the same result. Apparently, I can include the variable in the string and it gets expanded. This works with just a string, as shown here:
```
PS C:\Users\Steve> write-host("Today is $dt.csv")
Today is 20191202.csv
PS C:\Users\Steve>
```
Not the error I expected, but this makes more sense with a value that’s needed in a parameter. The blog helps explain this with the following code:
```
PS C:\Users\Steve> $directory = Get-Item 'c:\windows'
PS C:\Users\Steve> $message = "Time: $directory.CreationTime"
PS C:\Users\Steve> $message
Time: C:\windows.CreationTime
PS C:\Users\Steve>
```
An issue. However, if I use the expression evaluation of $() inside, I get this:
```
PS C:\Users\Steve> $message = "Time: $($directory.CreationTime)"
PS C:\Users\Steve> $message
Time: 09/15/2018 00:09:26
PS C:\Users\Steve>
```
That’s the trick I needed for Export-Csv. I used this code in the last line:
```
Export-Csv -Path E:\Documents\sql\$($dt).csv –NoTypeInformation
```
And the code worked as expected.

There’s likely more I should know, but I will start to use varaiables inside strings when I just need the value of the variable as a string. If I need this to better work with some property, method, or parameter value, I’ll use $() around the variable.

SQLNewBlogger

This post was about 20 minutes of me experimenting with a few things and slowly working out how some variables worked. I somewhat wrote this as I was experimenting, adding in the code that ran.

A good example of writing while learning. You could do this on your blog as you learn to work through some code or a feature.
December 18, 2019
Unprepared for Travel
I’ve had a month off from travel, which has been nice. It’s been an interesting time catching up on things at home, and a nice break from the disruption to my schedule that travel entails. However, all good things come to an end, and last week I headed out on Thursday morning for another trip, this time to Slovenia and the UK.

I found myself woefully unprepared. A list of things I’ve forgotten:
1. two pairs of bluetooth headphones
2. phone charger cable
3. usb adapter for phone cable
4. two pairs of wired headphones
5. external mouse
6. HDMI wireless adapter
7. gloves
8. belt
9. naproxen
At least I remembered to pack my laptop charger, wallet, and passport, something I haven’t always done. I also did get sneakers packed for the gym.

In the last month, I did spend a few nights in the mountains, and had some long day trips around Denver, during which I’d slowly moved a few things from my laptop bag, or roller bag, to use for a few hours. In the past I’ve been good about putting those things back in bags right away, so I’d be ready to travel. I usually keep my main luggage and laptop bag ready to go, since I travel so much. Often I can just throw a few changes of clothes in the bag and leave.

With a busy week last week, I was slightly worried I might have forgotten something, but I was in a hurry Thursday morning as I packed and didn’t double check myself. I did look for the wallet and passport, since with those I can likely replace anything I need.

When I got to the airport, I realized that I’d left the bluetooth headphones charging on my desk. I’d used one pair for a meeting while cooking, and grabbed another for the gym last week and didn’t put them back. Worse, I’d taken some of the wired headphones from a jacket and bag and used them at different times, getting lazy about putting them back. Same for the phone charger cable. I used that while cooking, and it’s sitting in the kitchen now.

These are minor issues, and I can certainly survive. Fortunately I’ve kept a spare pair of wired headphones and charging cable in my luggage for emergencies and pulled them out. I survived Slovenia without gloves, though it wasn’t that cold. I did have to buy a belt and some pain meds for an injured wrist, and I can live without the mouse.

This is the same type of thing I’ve seen in an office at work, where myself or someone deviates from a routine, gets lazy and then starts taking more shortcuts to get around the other shortcuts I’ve taken. I need to stick to a routine, and certainly adhere to any expectations I’ve set for myself and others. Hopefully I’ll remember to do this in the new year, with quite a bit of time off.
December 17, 2019