Awhile back, if you are on Twitter, you can probably recall my ranting about the 949 indexes I was reviewing. The process was to determine duplicate indexes and consolidate them or discard the unneeded ones. My ranting was not about the duplicates per se it was about the index names. It only takes a second to name an object with some name that tells what the thing is. Below I will show you some examples and give you an easy script that will help you generate your index names. Taking a little time to name things appropriately can go a long way, it can not only be time saving but can help to reduce redundancy.
As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew it was not a Clustered Index. The second index did note it is a “Covering Index” which gave some indication that many columns could be included I also know it was created with the Data Tuning Advisor due to the dta prefix. The third index was also created with dta but it was left with the default dta naming convention. Like the first one I know the date it was created but instead of the word Cover2, I know there are 16 key columns are noted by the “K#” and regular numbers tell me those are included columns. However, I still have no idea exactly what these numbers denote without looking deeper into the index. The last index is closer to what I like however the name only tells me one column name when in fact it encompasses five key columns and two included columns.
Above we see a few good examples with varying naming conventions, but each tell me a much more than what we saw in the “Donts” list. The first one I know right away is a non-clustered index with two fields. The second is a clustered index with one field. The third is an index that has 9 fields, probably a covering index of some sort, which tells me that it is probably important to a specific query or procedure. Index four uses the name of the table and the field, which does give me more information but given the name of indexes are limited to 128 characters I prefer to leave that out. The last one closer to one of my favorites, because it does give more information. The name lets us know that it has an included column of Birthdate.
Here is the script I use when creating indexes. It will go thru and identify a missing index and create Index statement using a standard name convention.
NOTE: This modified version of what we use at DCAC is for just showing you how I include and create a standard statement in my code, this is not to be used to identify missing indexes, as it is not the purpose of my post. I have removed pieces of that from this script.
SELECT DB_NAME(c.database_id) as DatabaseName,
OBJECT_NAME(c.object_id, c.database_id) as TableName ,
c.equality_columns as EqualityColumns ,
c.inequality_columns as InequalityColumns ,
c.included_columns as IncludedColumns ,
'USE [' + DB_NAME(c.database_id) + '];
CREATE INDEX IDX_'
+ REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(equality_columns, '')
+ ISNULL(c.inequality_columns, ''),
', ', '_'), '[', ''), ']', ''), ' ',
'') + ' ON [' + SCHEMA_NAME(d.schema_id) + '].['
+ OBJECT_NAME(c.object_id, c.database_id) + ']
(' + ISNULL(equality_columns, '')
+ CASE WHEN c.equality_columns IS NOT NULL
AND c.inequality_columns IS NOT NULL THEN ', '
END + ISNULL(c.inequality_columns, '') + ')
' + CASE WHEN included_columns IS NOT NULL
THEN 'INCLUDE (' + included_columns + ')'
END + '
WITH (FILLFACTOR=90, ONLINE=ON)' as CreateIndexStmt
FROM sys.dm_db_missing_index_group_stats a
JOIN sys.dm_db_missing_index_groups b ON a.group_handle = b.index_group_handle
JOIN sys.dm_db_missing_index_details c ON b.index_handle = c.index_handle
JOIN sys.objects d ON c.object_id = d.object_id
WHERE c.database_id = DB_ID()
ORDER BY DB_NAME(c.database_id) ,
OBJECT_NAME(c.object_id, c.database_id) ,
ISNULL(equality_columns, '') + ISNULL(c.inequality_columns, '') ;
Create Statement Output
USE [My_Reporting]; CREATE INDEX IDX_ID_StartTime_EndTime ON [dbo].[Shift]
([ID], [StartTime], [EndTime]) INCLUDE ([Notes], [EmployeeID]) WITH (FILLFACTOR=90, ONLINE=ON)
This statement gives the proper database context and create statement syntax, it adds all the needed key columns within the () and separated by commas. In addition, it adds the word INCLUDE and encompasses the included columns also in () and comma separated. Note the index name only includes the Key columns, which is just my preference.
Now everyone has their own naming conventions. You do you, however should stay consistent and give some meaning to it. When others look at the objects we should be able to know what it’s doing or be given a good clue as to what it’s for. This not only helps to quickly identity its definition but also keep you from creating duplicates. By looking at names you can tell with columns you need are already included in other indexes. Naturally you can’t just trust the name you have to dig deeper while examining your indexes but it at least will give you a realistic starting point.
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?
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.
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.
CREATE TABLE [HumanResources].[Employee_AE](
[BusinessEntityID] [int] NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[OrganizationNode] [hierarchyid] NULL,
[OrganizationLevel] [smallint] NULL,
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto7], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
[SalariedFlag] [dbo].[Flag] NOT NULL,
[VacationHours] [smallint] NOT NULL,
[SickLeaveHours] [smallint] NOT NULL,
[CurrentFlag] [dbo].[Flag] NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
You can also just SELECT and look at the data. Here you see the encrypted values for the data in the birthdate column.
SELECT * FROM [AdventureWorks2016CTP3].[HumanResources].[Employee_AE]
Check system tables
SELECT DB_NAME() as [database] , t.name as [table], c.name as [column], encryption_algorithm_name, encryption_type,encryption_type_desc from sys.columns c JOIN sys.tables t on t.object_id= c.object_id WHERE c.Name='Birthdate' and t.name = 'Employee_AE'
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.
SELECT * FROM [AdventureWorks2016CTP3].[HumanResources].[Employee_AE]
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.
string connectionString = “Data Source=server63; Initial Catalog=Clinic; Integrated Security=true; Column Encryption Setting=enabled”; SqlConnection connection = new SqlConnection(connectionString);
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.
Encryption has always been intriguing to me but seemed like it could be a very complex process to set up. However, SQL Server has made it very simple when they introduced Always Encrypted (AE) into SQL Server 2016 and Azure SQL Database. Unlike Transparent Data Encryption (TDE) which only encrypts data files and backups at rest, AE is configured on a column level and not database level. Additionally, Always Encrypted is available in Standard (and Express) Edition, starting with SQL Server 2016 SP1. You can easily encrypt a social security number (SSN) which is considered very sensitive within the United States or Salary column in a table with just a few clicks. In past versions of SQL Server, you could use cell-level encryption (CLE) perform this, but it required code changes and the keys were stored in the database, and the data was sent to the application unencrypted. Which brings us to the other benefit of AE, which is that DBAs can no longer see the unencrypted values of the data, as they could with CLE, because the column encryption key is stored outside of SQL Server.
Let’s see how you do it and walk through what each of these options means.
Using Adventure Work 2016 CTP3 HumanResources.Employee Table we are going to encrypt the Birthdate column.
Start by Right Clicking on the Table > Choose Encrypt Columns
It brings up a Wizard, one of the two recommend ways to configure AE. The other option is to use PowerShell.
Click Next on the Intro Screen
You will note in the example below, that it lists the columns and then shows the encryption STATE, which indicates if the column is eligible for encryption. There are several unsupported column characteristics that may make it so a column cannot be encrypted. This link to MSDN describes this in further detail. The items on this list are unsupported because they have a default constraint or a check constraint defined:
ALTER TABLE [HumanResources].[Employee] ADD CONSTRAINT [DF_Employee_VacationHours] DEFAULT ((0)) FOR [VacationHours]
ALTER TABLE [HumanResources].[Employee] WITH CHECK ADD CONSTRAINT [CK_Employee_BirthDate] CHECK (([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate())))
This is just an example of one of the road blocks you may encounter. So, let’s take a step back and setup an example we can easily use.
Run the below to create a copy of the Employee table. We are doing this to make a table without any constraints.
SELECT * INTO [HumanResources].[Employee_AE]
Now again, Right Click on the Table > Choose Encrypt Columns
In this case, the column we want is BirthDate, so I place a check next to it. To continue I need to Choose a Type of Encryption.
There are two possibilities Deterministic and Randomized.
MSDN defines Deterministic encryption as always generates the same encrypted value for any given plain text value. Which means that if you have a birthdate of 01/03/1958 it will always be encrypted with the same value each time such as ABCACBACB. This allows you to index it, use it in WHERE clauses, GROUP BY and JOINS.
Randomized encryption per MSDN- uses a method that encrypts data in a less predictable manner. This makes Randomized encryption more secure, because using the example above each encrypted value of 01/03/1958 will be different. It could be ABCACBACB, BBBCCAA, or CCCAAABBB. All three encrypted values are subsequently decrypted to the same value. Since the encrypted value is random you cannot perform search operations etc. as you can with Deterministic.
In most cases, you will want to use deterministic encryption. The places where random encryption makes sense is where you have a low range of distinct values. An attacker might be able to determine what the encrypted value was by brute force attacking using a variety of parameters. Some examples of this data include birth date, blood type, or credit card verification numbers (CVV).
So, going back to our example, select deterministic from the drop down.
The next step is to choose an Encryption Key. Let’s choose CEKAUTO (NEW). This stands for Column Encryption Key. You can use the same Key for every column or choose a new one for each.
Then click NEXT
Every Encryption Key must have a MASTER KEY. This is the value that is used to protect the other column keys. In the below we are going to just go with the defaults. If you have already generated a master key in you SQL Server instance, you can choose to reuse it for any new column you add.
One of the most complex parts of encryption is determining where to store these keys and who will have access to it. You can store these keys on a client machine using a Windows Certificate store or in Azure Key store.
The next screen has a great feature and kudos to Microsoft for this add-in. You can choose to generate a PowerShell Script, so you can rerun this again, or store in your source control.
After clicking NEXT, you’re done. The wizard will create all the keys, and encrypt the selected columns.
Now if you SELECT from the table you will see the values in Birthdate are now encrypted.
SELECT * FROM [AdventureWorks2016CTP3].[HumanResources].[Employee_AE]
Key Management in Windows Certificate Store
If you would like to see where the keys are stored within Windows, you can do so by doing the below. Go to Microsoft Management Console (type MMC your run bar Win+R). Then go to File, then Add/Remove Snap In. Certificates will be the third one down, click Add.
If you scroll back up you will note the when we created our Master Key it did so under CURRENT USER so choose My user account.
Expand Personal and Click Certificates (Key)
So, there you have it. Encryption made easy. This is only the tip of the iceberg. You need to understand how your environment will access and decrypt the data, encrypting is only part of the puzzle. I will cover how to get SSMS to decrypt the data in Part 2, in the meantime play around with it.