Voice of the DBA

Category: Blog

Post TDE–Getting Unencrypted Backups

I saw a question posted recently about someone that had disabled TDE and was still having issues restoring a backup. This doesn’t seem like that should be an issue, but it can be. A little testing shows how.

Let’s assume I have an encrypted TDE database. If I run a query, I can see the status as 3, which is encrypted.

If I take a backup at this point, the backup will be encrypted, and to restore this on another instance, I’d need to first restore the certificate. I don’t want to do that, so let’s remove encryption. This is a simple command

ALTER DATABASE TDE_Primer

SET ENCRYPTION OFF;

This runs quickly.

If I now query for encryption, I see this.

A one means that this is an unencrypted database, but a DEK (Database Encryption Key) exists. If I were to detach and examine this database file with a hex editor, the pages would be decrypted.

I’ll now take a backup and move that to another instance. Once I’ve copied that over, I’ll try to restore the backup. In T-SQL, I’ll see this:

Why is this? The database was decrypted, as was the backup. In fact, if I open my backup file in a hex editor, I can see row data.

The Problem

When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

The issue here is the DEK still exists in the source database.

The Solution

Let’s fix this. I’ll return to my first instance and the original database that was TDE encrypted and now is not. I can issue this:

DROP DATABASE ENCRYPTION KEY

Once I do this, it completes quickly. This is a standard DDL command, but one that’s not often used.

Once I do this, I’ll take another backup and return to the second instance. Now when I try the restore, I see this:

If you’re having issues restoring a database that used to be TDE encrypted, try removing the DEK and then backing it up.

December 28, 2016
Back After the Break

Refreshed and back to work today. After a week at Redgate Software for SQL in the City, I took a week to travel in Ireland with my family. In that time, I really ignored almost all email and didn’t manage SQLServerCentral at all.

Well, I did fix one article bug and replied to a couple comments, but mostly I ignored things. Even over Christmas and yesterday, back in the US, I tried to really ignore most work. I spent about an hour doing some SPAM cleanup, but that was early morning because my body clock was still on GMT time.

However, I’m back at it today. Up at almost a normal time (no kids to get to school, so slightly late), I wandered to the office, made coffee, and got back to editor duties.

I feel nice and refreshed. Being disconnected in Ireland for most of a week, I really had a nice chance to relax and not think or worry about computers and technology. I even avoided most Twitter/news on my phone, just getting away.

Now it’s time to get back on track, and I’m a little overwhelmed as I’ve been out of touch for almost two weeks. Feels like I have a lot of editing to do and plenty of scheduling and certainly need to get back to learning more and improving myself.

Hope everyone else had a great holiday break.

December 27, 2016
New Formatting in #SQLPrompt
I love SQL Prompt. This might be my favorite tool from Redgate Software and just when I thought it couldn’t be any better, a new formatting engine has been released.

Reading through existing source code and understanding the intent is an important part of any software developer’s life. Often, we find ourselves reading code written by other people, which can be difficult to comprehend when the structure and layout is unfamiliar. This is one reason why many companies have had coding standards that not only enforce items such as naming, but also can prescribe whether tabs or spaces are used, indentation prescriptions, and even line breaks. While our industry has some conventions, they are not as widespread as we might hope.

One of the features I find most useful about SQL Prompt is the ability to quickly reformat code in an easy to read fashion. For example, I’ll get code like this from a forum post or a co worker:
```
SELECT  * FROM
  ( SELECT 'Month' = DATEPART(mm, NewsDate), 'Count' = COUNT(*)     FROM News GROUP BY DATEPART(mm, newsdate)  ) a
```
With a quick CTRL+K, CTRL+Y, and then I see this:
```
SELECT  *
 FROM
    (
        SELECT  'Month' = DATEPART(mm, NewsDate)
              , 'Count' = COUNT(*)
         FROM   dbo.News
         GROUP BY
            DATEPART(mm, NewsDate)
    ) a
;
```
That’s much easier for me to read, and thus easier to understand, debug, and improve. Over the years I’ve become used to indentations in certain places, and different types of alignment that can help me understand how a query is structured quicker. I also find certain layout, such as commas first, easier when I try to alter and debug code.

While SQL Prompts formatting engine has worked well, it’s lacked some features that I’ve wanted. For example, I’ve written a lot of code with the Window functions in T-SQL and prior to SQL Prompt v7.3, the formatting would place then entire OVER() clause on a single line. The same thing used to happen with various DDL features as well, meaning that I’d be able to clean up some code, but I’d then go back and add various line breaks to help the code fit on one line and be easier to read.

The Way0utwest Style

That’s changed. I’ve been beta testing a new formatting engine for a few months as a part of the Experimental Features in SQL Prompt, and I love it. In fact, I’ve even added my own style, as you can see below:

This is my custom style, which I’m still working on. On a regular basis, I’ll edit the style, changing some of settings as I find new code that doesn’t quite look right to me. I’m amazed by the incredible array of options, and I find myself impressed with the SQL Prompt team once again.

This hasn’t been an effort for me. I don’t usually go poking too far into the settings or make a concerted effort to get things right the first time. Like many things with Prompt, I just want it to work in the background, correcting code. When it doesn’t format in an easy to read way, I’ll go alter a setting. For example, the first time I got some CTE code, the default style will format things like this:
```
WITH myTally (n)
AS (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM sys.sysobjects
   )
SELECT *
FROM myTally;
```
However, that isn’t quite what I want. I quickly opened the SQL Prompt menu, and I see my formatting options at the bottom:

I have also been able to access this from the Experimental Features menu (you’ll want to keep an eye on this area in the future).

Once in the style editor, I can click the colon after my style name and then edit.

There are a number of options, but CTE is one of these.

Inside this area, I can choose to do a few things. I can choose when to break to new lines, when to add spaces, and where to indent. This is truly a flexible and highly configurable formatting engine.

As I make changes, I can see the new format below with a sample query, or I can check a box to see how my current query will be reformatted. Once I’m happy, I can save the changes and move on to what I need to do: write code.

There are far too many options to work with, and it’s one reason that I don’t bother trying to set all the various settings. In some cases, I’m not even sure how I want the code formatted until I realize that there’s an issue understanding a complex layout. It’s at that point that I’ll go ahead and change some settings to adapt SQL Prompt to my current query. I can even create a new style that inherits the settings from a previous style if I’m experimenting, a topic for another blog.

Formatting for Friends

In my format list there’s a second style I’ve labeled “Grant”. This is a separate style that formats differently based on Grant’s preferences. I asked Grant for his .sqlpromptstylev2 file and added to my system. The default path is %USER_PROFILE%\AppData\Local\Red Gate\SQL Prompt 7\Stylesv2. Once I have his style, I can reformat code the way he wants it if I send it over.

Why? Certainly Grant can reformat code the way he wants it with his own SQL Prompt. It’s a nice touch fo rme to be able to work with code the way that’s easier for me and then reformat it in a way that’s easy to understand if I send a short segment over email, Slack, or some other communication means.

I’ve also worked in situations where code needed to be formatted a certain way. Various clients and employers have dictated that code is strictly laid out in a certain style for code reviews and committed in a VCS in a particular way. By having the option to quickly switch styles in Prompt, I can reformat code for others without too much effort.

SQL Prompt has been one of the more useful and helpful tools I’ve worked with in SQL Server, and just when I thought it might be mature, I get this fantastic formatting engine. It’s been fun to play with across the last few months and now it’s live as a default feature in SQL Prompt 7.3. You can now start playing with your own formatting style when you upgrade.

Or download a copy of my style and see how I prefer to read code: Way0utwest’s Style.sqlpromptstylev2

If you’re not a SQL Prompt customer, you don’t know what you’re missing. This is a vast improvement over the native intellisense and I’d say you should download an evaluation and give SQL Prompt a try today.
December 27, 2016
Administrative Native and ConEmu Command Prompts–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I would think many people know how to do this, but I’ve run into a few, so here you go.

There are times you need to run elevated commands on a machine as an administrator. I’ve had to run various apps, as well as command utilities in this way, so I’ve learned how. I’ll give you two ways to get admin command prompts running.

Cmd.exe

This is the command prompt most people on Windows use. You can hit the Windows key and type cmd, and you’ll see this:

On Win 7 or earlier, you’d get something along these lines.

In either case, if you hit Enter, you’ll get this:

A command line where you can type DOS style commands. Many of you might not use this much, but you should. The more you know about doing things from the command line, the more efficient and effective you’ll be. Plus you’re well on your way to being comfortable in DevOps type environments.

This isn’t a privileged command prompt, and certain things won’t work here. To get an administrative level prompt, do this. First, after typing “cmd” don’t hit enter. Instead, right click the icon. You’ll see this:

Or this:

In both of these, there is a “Run as administrator” option. You want that. Once you do, you’ll need to accept the UAC prompt to allow an administrative level command prompt to open.

ConEmu

I have switched to ConEmu. I think it’s a much better console, and it’s handy. I get it with Chocolatey, and then it’s just a hot key away. What’s more, I can easily open multiple command windows. For example, I’ve got two open, but I can open a third, administrative level one, but right clicking allows me to restart my prompt.

When I do that, I need to acknowledge the UAC prompt, but then I’ll have an admin level prompt.

SQLNewBlogger

These quick, handy tips showcase your continuous learning and improvement. As well as help you remember cool things like this.

December 26, 2016