Voice of the DBA

Tag: security

  • Enable Transparent Data Encryption

    This is one of the things in my Encryption Primer presentation that I don’t demo. It’s really easy to do, and it’s rather mechanical, so I just show the image that has the steps from MSDN and leave it at that.

    However there are a few things I wanted to change, and test, so I thought I’d show my procedure on a local database. I roughly follow the MSDN article, but a few slight items.

    First, use master and create your keys and certificates.

    CREATE DATABASE TDETest
;
GO
USE master
;
GO
CREATE MASTER KEY
 ENCRYPTION BY PASSWORD = 'AReallyStr0ngP@ssword'
;
go
CREATE CERTIFICATE SteveCert
 WITH SUBJECT = 'My DEK Certificate'
;
go
USE TDETest
;
GO
CREATE DATABASE ENCRYPTION KEY
 WITH ALGORITHM = AES_128
 ENCRYPTION BY SERVER CERTIFICATE SteveCert
;
GO

    I created a test database here for another process, and this is roughly the setup. However before I enable the encryption, here’s what I recommend you do:

    USE master
;
go
BACKUP CERTIFICATE SteveCert
TO FILE = 'c:\SQLBackup\SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
go

    Encryption is serious stuff. If you lose this certificate from a server crash, you are definitely not going to be able to open your database or recover your data. Gone is gone, and data loss means data loss here.

    Back up your certificate.

    Quick question: do you know where your backup of the certificate is?

    Once this is done, you can continue on:

    USE TDETest
;
go
ALTER DATABASE TDETest
SET ENCRYPTION ON;
GO

    The encryption is quick on this new, small database.

    Now let’s see if this worked. We’ll add data and make a backup.

    CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE TDETest
 TO DISK='tdetest.bak'
;

    If I go to my backup location and look for this backup, I can open it in an editor.

    encrypt2

    It’s random gibberish. If I run a search for data in my table:

    encrypt1

    I get no results

    encrypt3

    Don’t think this is valid? Run this below and re-search this backup for the string. You’ll find it. This is one thing encryption protects you from.

    CREATE DATABASE NoTDE
;
GO
USE NoTDE
;
GO
CREATE TABLE MyTable( LogData VARCHAR(MAX))
;
INSERT MyTable SELECT 'This is an encrypted database'
;
GO
BACKUP DATABASE NoTDE
 TO DISK='notde.bak'
;

    The database is encrypted, but anything I do with the database doesn’t require code changes, hence the “transparent” nomenclature.

    The value of this is debatable, but I think it’s not a bad feature to implement if you have Enterprise Edition and you need this protection for PCI, HIPAA, or some other regulation.

  • Make a Backup First

    Mat Honan
    Are you protecting your digital life?

    I was reading in detail about the Apple / Amazon hack that targeted a Gizmodo writer.  A hacker used a few techniques to get from his Amazon account to his Apple account, his GMail, Twitter, and more. His iPhone, iPad, and Macbook were remotely wiped, and he ended up losing quite a bit of data that wasn’t backed up. That’s a horrible situation, and I know every time I’ve lost a picture or document at home, I regret not making another backup.

    The situation caused some discussion and comments on Twitter about the various things you should do to prevent this type of issue. Updating your security, choosing better passwords, and other ideas are great, but the number one thing you need to do is make a backup of your data. No matter what happens to your security or even hardware, if you have a second copy of your data somewhere else, you can recover from the situation.

    While listening to a Brent Ozar, PLF webinar recently, I heard this interview question: what is the first thing you do on a new server you’ve never worked with? The answer is ensure it’s being backed up. Not run a backup, since you can cause problems, but make sure there is a backup plan in place, or get one started as soon as possible. Why? Because if you have a backup, you can recover.

    I think this is the number one priority for any data professional, and perhaps for anyone using a computer. I know I have backups of my home machines run to a Windows Home Server. I take a backup of my Macbook regularly, and leave a copy at home when I’m traveling. I also carry backups of my VMs on separate disks, in a separate suitcase when I travel. I know that if I have one of those copies of my data, I can recover from almost anything.

    What’s the saddest part of this story? There wasn’t any intent (allegedly) to target the writer’s work or his data. The hacked just wanted to use his cool Twitter account, @mat.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Where’s My Certificate Backup?

    If you’re like me, you take advantage of the default backup paths in SQL Server. It makes my code cleaner, and if I need to move the instance somewhere else, all my code works. No pathing issues.

    A certificate backup might look like this for me:

    USE master
;
go
BACKUP CERTIFICATE SteveCert
TO FILE = 'SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
GO

    If I run this, and immediately go to my backup folder, sorting by the last modified date for files, I see this:

    backupcert1

    No certificate backup file. What happened?

    The answer is actually documented, and you should be aware of this. In the BACKUP CERTIFICATE page, it says this: “The default is the path of the SQL Server DATA folder. “

    That’s interesting, and it makes sense to me. This folder is more likely to be secured than the backup folder, where developers and who knows who else may have access to the folder. By limiting it in the data folder, you provide a little obfuscation, perhaps more protection, and you force the administrator, the DBA, to get the files.

    However the files are also ACL protected. If I go to my data folder, I see the files.

    backupcert2

    If I select the certificate and CTRL+C (copy) it, and then go to the backup folder and try a CTRL+V (paste), I get this:

    backupcert3

    The service account has permissions to this file, not administrators by default. This action invokes the UAC command to require me to make a conscious decision to make this copy.

    Of course, I can just provide a path to make sure I can find the file.

    BACKUP CERTIFICATE SteveCert
TO FILE = 'c:\SQLBackup\SteveCert'
WITH PRIVATE KEY 
(
    FILE = 'c:\SQLBackup\SteveCertPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'R@ndomP3ssW0rd'
);
go

    Whatever you do, make sure you backup your certificate files and keep them safe. If they go, you do lose data.

  • Separate Accounts

    Separated Strawberry
    Is separation that much of a hassle? I don’t think so.

    Many years ago I worked in a small company that only had about 5 or 6 servers. We had one system administrator whose job it was to manage all the servers. One day our sysadmin was on vacation when there was a problem with the Exchange server. One of the other developers worked on the system and ended up fixing it, but changed the service account password while doing so. The next day I walked into the office to find a group of people stymied as to what was wrong with the development server and version control system. Everyone claimed they hadn’t changed anything on that server, and they were right. However our admin used the same domain account for all servers, including my SQL Servers. I changed the SQL service account that day.

    One of the recommendations that I learned a long time ago, and one that I make regularly, is that every SQL Server instance should have a separate security account. In that case, I had separate accounts created for each database instance, and for each SQL Agent instance. We used long, random passwords that were never stored, and if we needed to access a password, we just changed it. That kind of flexibility and separation prevented any crosstalk issues between services, and it allowed us to easily alter permissions or passwords for one service without affecting any others.

    The other day I saw someone recommending a single service account for all SQL Servers. Someone else recommended a single account for each version of SQL Server, using separate accounts where it’s really needed. That’s a better recommendation, but I still prefer completely separate accounts. I know that some security groups don’t like that, but is it that big a problem? This Friday I wanted to ask you about your experiences.

    Do you find separate accounts for each instance (or Agent) to be a security or administrative issue?

    I’m not sure why this is unwieldy. Service accounts rarely change, and you could easily script changes to a group of accounts with PowerShell or some other tool. Once I set a service account, the only thing I might ever do later is alter the permissions to add access to a folder. When that happens, I definitely want to have separate accounts for each instance.

    Let us know this Friday how you feel and what works for you.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.