AHHH I need a Blog Topic!!!

One of the hardest things you can do as a blogger is to come up with a post topic. Do you make it simple for newbies, technical, or something personal?  After figuring out a topic, now you have to write.  However, there is a difference in what you say and what will people actually want to read.

Blogging is not easy, but without it, all of our google searches to help solve problems would be much less fruitful.   It’s important to put your experience into written words to help others, and let’s be honest lots of us use out blog posts to remind ourselves of how we did the first time. So, I figured I’d take a minute and let you know what I do to come up with a topic. Maybe it could help others break into the blogosphere or fix their writer’s block.

First, I look at what I have done recently in my job.

Did I fix something?

Come across an error?

Did I find something I didn’t like?

Did I find something I really liked?

Did I just do something really interesting that I got excited about?

If those don’t help I move on to.

Is there something I wish I knew when I was starting out in SQL Server?

A tip?

An option I didn’t know?

A how to?

Nothing coming to mind to write about still? Then I move on to complete RANDOMNESS.  Yes, you read that correctly.   I will go into SQL Management Studio and randomly pick a check box or option and research, test and then proceed write about it.

Lastly, if I am still at a loss I’ll write something like this post.   Something I think might be useful to others that is just a stream of consciousness. It may not be ground breaking, but it might get someone else thinking or motivated. My point of this, is that not all blogs you create have to be ground breaking, technical, or even long in length. Just blog, say what you want to say.

It makes a difference in more ways then you know.

How to Get Started with Always Encrypted for Beginners Part 2

In this post we will pick up where we left off in Part 1, if you haven’t read that please go back and do so.

Now that we have encrypted our columns, it’s time to take a look at how we decrypt them inside SQL Server Management Studio or through our applications. You’ll be surprised to see how easy it is.

Verify Your Setup

First, let’s verify that the table is still encrypted, and nothing changed after you ran through the Part 1 examples. To confirm, simply query sys.columns, script out the table, or query the data to check that the Birthdate column is still encrypted.

You can also just SELECT and look at the data. Here you see the encrypted values for the data in the birthdate column.

Check system tables

Decrypt with SQL Server Management Studio

Viewing decrypted data within SQL Server Management Studio (SSMS) is very easy. SSMS uses .NET 4.6 and the modern SQL Server client, so you can pass in the necessary encryption options. SSMS uses the connection string to access the Master Key and return the data in its decrypted format.

First create a new SQL Connection and Click Options to expand the window.

Then go to the Additional Connections Parameters Tab of the login window and simply type column encryption setting = enabled. Then choose Connect.

Now try SELECT From your columns.

If you did it correctly you will see the decrypted BirthDate column.

Now the reason this works is both the Column Key and Master Key are stored in the Windows Certificate Store of this SQL Server. The Master Key was setup in Part 1 in the Windows Certificate Store.

Decrypt with an Application

According to MSDN for the application to decrypt data the account that connects to the database must have the VIEW ANY COLUMN MASTER KEY DEFINITION and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION database permissions. These permissions are required to access the metadata about Always Encrypted keys in the database.

Once those permissions are established all you must do is change your application connection string to include Column Encryption Setting=enabled. Below is an example using SQL Server integrated security.

Example

string connectionString = “Data Source=server63; Initial Catalog=Clinic; Integrated Security=true; Column Encryption Setting=enabled”; SqlConnection connection = new SqlConnection(connectionString);

 Summary

Decrypting the data when you have the Master Key stored on your Database Server makes it easy, but it also gives access to the encrypted data to the DBA. Make sure when you are planning to use Always Encrypted you consider who you want to have access to the data and where you want to store the keys.  There are many more layers of security you can add to this by defining those items. The example I gave in both Part 1 and Part 2 are the least complex and therefore not the most secure, but it gives you a beginner’s overview of how implement it. You need to examine your application to understand if it fits with in the current supported features of always encrypted.