Voice of the DBA

Category: Blog

Some Numbers in 2019
It’s the end of 2019, and I thought I’d look back at some data from this year. It’s been a busy, but wonderful year for me. I like numbers, so here are some stats from this year. They’re somewhat random, but interesting to me.

Work
- 181 podcasts
- 61 flights on United
- 139,000 miles flown
- 12 International business class upgrades (makes a big difference going to work)
- 5 countries visited (outside the US, 3 new ones for me)
- 68 nights in Hilton hotels, 23 in AirBnBs, 3 in another hotel
- 600+ pieces of content (articles, editorials, questions of the day, and blogs) published on SSC
- 12 webinars
- 8 SQL Saturdays attended (Memphis, Melbourne, Brisbane, Christchurch, Slovenia new ones for me)
- 9 SQL in the City Summits (a high and none missed), along with 2 Streamed events
Personal
- 2 days snowboarding (a low in the last decade)
- 1 Horse shelter built, 1 generator shed built
- 1 year head coaching volleyball
- Final 8 tournaments attended for my daughter’s club volleyball career.
- 2 camping trips with horses (rode on one)
- Read 125 books (all time high)
- 4 comedy shows attended (finally, no kids!)
- 230 workout days this year, a little low
There are probably lots more things I could track, but these are the big ones for me, and certainly these help me think about next year. I want to ski more, build more things, and workout more. Not sure I’ll read more, but I spent the year re-reading some series and some of those went quickly.

All in all, 2019 was great for me. Looking forward to seeing where 2020 takes me.
December 30, 2019
Installing PowerShell for SQL Server – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

PowerShell is the hot new scripting language for working with processes outside of an application. It’s cross platform, and it has a lot of capabilities for working with SQL Server. The way that this gets installed has changed, so this is a quick post to ensure others know how to do this.

Running a Command

I know there is an Invoke-SqlCmd cmdlet in PowerShell. On a new laptop, I tried to run it and had issues.

SQLPS is the old module, and SqlServer is the new one. The error message says to try importing the module, so let’s do that.

That doesn’t work either. Hmmm. I think I need to install this. To do that, I need an elevated command prompt. I’m using ConEmu, and I can restart this session as an admin. Or you can use the start menu to find cmd and start this as an admin:

Once you have an elevated prompt, start PowerShell and then run this:
```
Install-Module SqlServer
```
This will install the module if you answer the prompts correctly. I had to use the -AllowClobber parameter as I had some conflicting things installed from dbatools. I’ll likely update those after this with their own AllowClobber.

Once this is done, you should be able to use the cmdlets. First you import the module with this:
```
Import-Module SqlServer
```
Then you can run code:

SQLNewBlogger

After getting a new laptop, I needed to set a few things up. PowerShell was one of those. As I started to do this, I grabbed screenshots to document the process for my blog. I then built this post in about 10 minutes.

You could do this as well, to round out the knowledge you gain as you do something similar, and show you’re familiar with these concepts.
December 27, 2019
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