Voice of the DBA

Author: way0utwest

A Basic Encryption Primer for SQL Server
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Encryption is a function call in SQL Server, where we pass in the text to encrypt and a key. That’s really what we are doing with encryption. I pass a key and data into a function and get either encrypted or decrypted data. Here’s a short example:

Basic Encryption Demo

Let’s say I have a simple message, like “Let’s meet at Kunjani Coffee at 8am.”. I want to protect this message.
```
DECLARE
     @data VARCHAR(500) = 'Let''s meet at Kunjani Coffee at 8am.'
   , @encrypteddata VARBINARY(500);

SELECT 'Plain Data', @data

SELECT
     'Encrypted Data'
   , ENCRYPTBYPASSPHRASE ('mysecretk#y', @data) AS EncryptedData

SELECT
     'Decrypted Data'
   , CAST(DECRYPTBYPASSPHRASE ('mysecretk#y', ENCRYPTBYPASSPHRASE ('mysecretk#y', @data)) AS VARCHAR(100)) AS DecryptedData

SELECT
     'Almost Decrypted Data'
   , DECRYPTBYPASSPHRASE ('mysecretk#y', ENCRYPTBYPASSPHRASE ('mysecretk#y', @data)) AS AlmostDecryptedData;
```
In this code, I’m using EncryptByPassPhrase and DecryptByPassPhrase to encrypt data. I pass in a key, which is my passphrase. That is “mysecretk#y” i this case. I then pass in the data and get the result returned. You can see the four results below:

This is a quick example of the functions working together. I use one to hide the data, and another to reveal it.

The Encryption Hierarchy

Encryption is expensive in terms of resources (time, CPU, etc.), which means we want to minimize it. Part of what we do is try to minimize the work done while maximizing protection.

We can double encrypt things, meaning I could take the result of the second query, and use that “encrypted data” string and use that in another encryption function and provide even more protection. The encryption of each string, however, is time and CPU intensive.

Rather than encrypt the data again, we often just encrypt the keys. One key encrypts another and then the resource cost of decrypting a key is much lower than decrypting the data over and over. If you look at the encryption hierarchy on MS Docs, you’ll see that keys are stacked on each other, each one protecting the layer below. Typically we are only encrypting the actual data with a password or a symmetric key, which are essentially the same thing and the quickest way to perform the encryption and decryption.

Let’s lightly look at the parts of this hierarchy.

Asymmetric Keys

The stronger keys are the asymmetric ones. They are called this because the key used for encryption is different than the key used for decryption. Typically these are paired together, and you can give someone 1 key, so they can only perform one operation.

Certificates are asymmetric keys, with other metadata, and have the two keys as the public and private keys. This is how we do a lot of encryption across distances where we need to exchange data on insecure channels, like the Internet.

These are computationally intensive, meaning lots of CPU and time, so we don’t usually want to use these to encrypt lots of data. Instead, we use these to encrypt or symmetric keys.

Note: The strangeness in the SQL hierarchy is that the SMK and DMK are symmetric keys.

Symmetric Keys

The Symmetric keys are used for both encryption and decryption of the data. This doesn’t mean any key works, but the key used to specifically encrypt a set of data is the key used to decrypt it. This means we need to send the key to all parties that do encryption and decryption.

These do require lots of CPU, but much less than asymmetric keys. Typically we use these to actually encrypt the data.

Hash Functions

This isn’t really encryption, but some hash functions are used for things like passwords, where we can have one-way encryption. These are often used to transform the data into a hash, or representation, and then we can compare the hash together.

Using Encryption in Practice

Each of these ideas is just a function call, and that is how we’ve implemented encryption in SQL Server. Whether you use TDE, Always Encrypted (AE), column level encryption, or anything else, you are making function calls in some way.

For TDE and AE, SQL Server handles much of this work for you. It gets keys, does the function call. For the code above, or any of the ENCRYPT/DECRYPT functions, you are writing code and using those in your work.

However, keep in mind that key management is the key to protecting things. This means how you protect them, where they are, how you copy them to other places, and change them over time.

Summary

Encryption is just a series of function calls. We have different types of functions and different parameters, but that’s really the core of what is happening.

We can use these functions with the outputs of one as the inputs of another, or more often, the keys used as inputs instead of text, allowing us to encrypt and protect the keys themselves.

There is a lot more to learn, but this gives a basic look at encryption in SQL Server.
May 26, 2021
Daily Coping 26 May 2021

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to set a goal to do something important to you in the next year.

This week is goal week, looking forward a bit. Yesterday I had a small project to tackle this summer. Now I need a goal for something that matters to me.

I want to travel, but that’s not something I can count on. Rather than focus on life things, I’m going to pick another small goal for this year.

I’m going to build Christmas presents. I like working with wood, and every year I think about it, but wait too long and don’t get many, or any, built. This year I’m going to start this summer and tackle a few things, customizing them for different family members, and then get them ready to send out in December.

That’s my goal.

Hopefully I’ll get there.

May 26, 2021
Daily Coping 25 May 2021

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to write down one thing you plan to do in the next year.

I don’t make a lot of plans. While I do have a life (bucket) list, it doesn’t drive me on a regular basis. I have enough commitments that I prefer to not add other items if I don’t need to do so.

For the next year, as I think about something that I might want to do, there are a lot of items outside of my control. I hate to plan something based on what others might not do, so I’m thinking about what can I control and get done.

I think the item for me isn’t big, but it is something I need to do. I built a generator shed last year, but it’s too small, and doesn’t have proper ventilation. I can control my actions to work on that, so I’m going to spend time fixing that this summer and get it ready for winter.

A small project, but it will make me feel good to get it done.

May 25, 2021
2020 Advent of Code Day 6
This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 6. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

This is another weird string grouping issue. The data load is a mess, meaning that there are groups of data I need to consider, and the groups are separated by blank lines. However, each group has multiple lines.

Ugh.

Easier in Python, where I can load the data line by line and break things. I do that with this code:
```
for answers in open("2020\day6\day6_data.txt").read().split("\n\n"):
```
In SQL, it’s harder. I bulk load into a table, the cursor through the data.
```
DECLARE pcurs CURSOR FOR SELECT lineval FROM Day6 ORDER BY linekey;

DECLARE
     @val VARCHAR(1000) = ''
   , @groups VARCHAR(1000);

OPEN pcurs;

FETCH NEXT FROM pcurs

INTO @val;

SET @groups = '';

WHILE @@FETCH_STATUS = 0

BEGIN
     IF @val > ''
         SELECT @groups += ' ' + @val;
     ELSE
     BEGIN
         INSERT dbo.Day6_Groups (groupanswers) VALUES (@groups);
         SET @groups = '';
     END;
     FETCH NEXT FROM pcurs
     INTO @val;

END;
     INSERT dbo.Day6_Groups(groupanswers) VALUES (@groups);

DEALLOCATE pcurs;

GO
```
Once that is done, I again have to do things differently. Python is easy, where I count the values and add them up.
```
answers = set(answers.replace("\n",""))
part1 += len(answers)
```
In SQL, I need to distinctly find the values, for which I need a function of some sort.
```
UPDATE dbo.Day6_Groups
  SET deduppedanswers =  DBO.REMOVE_DUPLICATE_INSTR(1,groupanswers)
```
Once that’s done the answer is the sum of lengths.

Part 2

More complex here. Now I need to match up the common answers among the groups. In Python, this isn’t bad. I use the intersection method to find out what matches between the groups.
```
for answers in open("2020\day6\day6_data.txt").read().split("\n\n"):
    matches = set.intersection(*[set(answer) for answer in answers.split()])
    part2 += len(matches)
print("Part 2: ", part2)
```
Fairly simple here, with the grouping of the answers in a set.

SQL is hard.I reloaded the data, and separated each group by a comma. This gave me data I could split up, keeping each group in a group.

From here, I had a CTE for this, another to put these into groups of 2 strings by groupID. I then did a comparison for common characters across each group of 2 strings. This gave me partial matches, and I then compared all these in a group, taking the minimum number of matches. From here, I summed up the count of all the matches.
May 24, 2021