Voice of the DBA

Tag: encryption

  • Who’s Got Your Data?

    enigmaThe headlines lately have been filled with the plight of Edward Snowden, along with his disclosures on how the US National Security Administration (NSA) has been gathering, cataloging, and examining all sorts of data about people in the world. It’s not just terrorists, criminals, or anyone suspected of illicit activities, but also many ordinary people that may not feel they should have been subjected to this level of surveillance. Whether you think this was a proper way to disclose this information or not, there’s a separate issue here.

    We are producing a tremendous amount of data about ourselves all the time. There are so many ways in which companies can gather data points about us, often with logging activity that we might not find intrusive. Our houses produce logs of electrical activity along with the various types of services we might use (water, trash, etc.). We use cell phones, whose locations and usage are recorded, and we often use on line services for mail, research, entertainment, and more that all produce logs of our activities. Purchases on line are stored, and purchases off line might be stored if you use any type of loyalty card. I can imagine it not being long before any card purchase can be linked to the actual items themselves, regardless of whether you want this to occur.

    We can add in any services we use that store data in the cloud, from physical activity to medical information to even your location from status updates. While much of this data is stored separately, and not necessarily aggregated, that might not be the case in the future. The government could potentially request this data, which is unnerving to me, but what is more disconcerting is the idea that businesses might engage in complex deals to share much, or all, of your data without you knowing about it. This could be under the guise of providing better services, which makes sense, but that’s not what concerns me.

    What concerns me is the lack of care that so many companies take with our data. It’s lots constantly, and the more data that might be shared between companies, and need to be transformed and loaded into new data warehouses, the more people that will touch this data. And the more developers that will have copies of it on their laptops as they build new applications. It’s scary to think about the lack of control we have, and now many mistakes will be made in the future.

    I wish I had a good suggestion on how to improve the situation, but I don’t. However I do think disk encryption, for all machines that touch data, is a good place to start.

    Steve Jones

  • Don’t Use MD5

    “The hashing alone being MD5 tells me that they really don’t care about their passwords too much, so it’s probably some pre-generated site.”

    That was from this article on an Anatomy of a Hack. It’s an interesting quote, and it shows a few things.

    First, we have a history issue with our frameworks and the lack of updates as we learn more about a technology, or circumstances change. This could be that frameworks are not being updated. It could be that developers are not updating their frameworks. It could be that they are downloading the wrong versions.

    The bottom line is that older technologies, those that have vulnerabilities, are still being used. If you use encryption for passwords, don’t use MD5, and I’d say that SHA1 is a bad idea. If you are on a version of SQL Server prior to 2012, SHA2 is not available, but with the SQL CLR and SHA2 in .NET, you can write your own.

  • Does TDE really work on MDF files?

    Yes, it does. However, let’s prove it. First let’s create a database, a table, and enter some data:

    -- create a database
CREATE DATABASE TDE_Primer
;
GO
-- create and populate a table
USE TDE_Primer
go
CREATE TABLE MyTable
( myid INT
, myname VARCHAR(20)
, mychar VARCHAR(200)  
)
;
go
DECLARE @i INT = 65;
WHILE @i < 92
 begin
  INSERT mytable SELECT @i, 'Steve Jones', REPLICATE(CHAR(@i), 200);
  SELECT @i = @i + 1;
 END
;
GO
SELECT * FROM Mytable;
go

    If I look at the table, I see my name with lots of data:

    Capture_030

    Now let’s detach the database and examine the results with a hex editor:

    -- detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TDE_Primer'
;

GO

    I use XVI32 as an editor. It’s free, and you can download it. If I open up my MDF in this utility, here’s what I see:

    Capture_031

    If I search for my name:

    Capture_032

    I find it:

    Capture_033

    This is what I expect, and you should as well. Even without SQL Server, your data files are readable, which is why you must protect them.

    Now let’s attach the file and enable TDE.

    USE [master]
GO
CREATE DATABASE [TDE_Primer] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TDE_Primer.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TDE_Primer_log.ldf' )
 FOR ATTACH
GO

USE TDE_Primer
go
SELECT * FROM mytable
;
go

-- begin encryption setup
-- from http://msdn.microsoft.com/en-us/library/bb934049.aspx
USE master;
GO
-- create master key for master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AlwaysU$eaStr0ngP@ssword4This'
;
go

-- create certificate to secure TDE
CREATE CERTIFICATE TDEPRimer_CertSecurity WITH SUBJECT = 'TDE_Primer DEK Certificate';
go

USE TDE_Primer;
GO
-- Create DEK
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDEPRimer_CertSecurity;
GO

-- backup TDE cert
USE master
;
go
BACKUP CERTIFICATE TDEPRimer_CertSecurity
 TO FILE = 'tdeprimer_cert'
  WITH PRIVATE KEY (
               FILE = 'tdeprimer_cert.pvk',
               ENCRYPTION BY PASSWORD = 'AStr0ngB@ckUpP@ssw0rd4TDEcERT%')
;
go

-- check encryption status
SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO

-- enable encryption
USE TDE_Primer
;
GO
ALTER DATABASE TDE_Primer
  SET ENCRYPTION ON;
GO
-- check encryption status
SELECT
    db.name,
    db.is_encrypted,
    dm.encryption_state,
    dm.percent_complete,
    dm.key_algorithm,
    dm.key_length
FROM
    sys.databases db
    LEFT OUTER JOIN sys.dm_database_encryption_keys dm
        ON db.database_id = dm.database_id;
GO
-- TDE_PRimer and tempdb encrypted

-- detach database again.
-- detach database
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'TDE_Primer'
;

GO

    I won’t go into all the code, but this encrypts the database, backs up the certificate and then detaches it again. There are a few other things, but I cover them in another post.

    Now let’s open up the file in the hex editor again.

    Capture_034

    It looks the same. It’s not in the image, but just below this you can see the database name. There is a header, which is not encrypted. However when I search for my name, it fails.

    Capture_035

    If you scroll further around, you’ll see that most of the file is now encrypted.

    Capture_036

    Play with this and prove to yourself that TDE does really encrypt things.

  • A Good Security Response

    I'm glad Evernote acted quickly and decisively with this incident.
    I’m glad Evernote acted quickly and decisively with this incident.

    Security will become more and more important in the future, at least in my mind. As we become more interconnected and dependent on digital services, if the level of fraud and security failures do not fall, many people will hesitate to use these services. I think certificates will be the future of digital security, but until we get better support for managing them built into all our OSes, I’m not sure we’ll move beyond passwords. I do think we need to move past passwords, but we’re stuck with them for now.

    Recently Evernote had a security breach and they forced all users to reset their passwords. It was slightly annoying, but it was a comforting response for me. Two week after the incident I had to change the password on my iPad, which I rarely use. It was ironic since I was working on this particular piece when I reset my password.

    I wasn’t the only one that thought this was a good response. In this article from Enterprise Security, a number of security professionals praised the way Evernote handled this incident. They note that Evernote had implemented good security practices (from what we know) and notified people immediately. I certainly appreciate Evernote moving quickly on this and am glad I had to deal with the annoying password change. I don’t use the same password on other sites, and this was a good reminder to me that I shouldn’t. It also served as a reminder to tell my family to do the same thing.

    I’m not sure any company I’ve worked for would handle things this way. I haven’t had many security incidents at my previous employers, but I know in one case we were told not to disclose anything and fix issues. I’d like to think that most companies would disclose this, and I do think they should, but most wouldn’t. These things happen, just like break-ins happen in physical buildings. Companies should accept that, diagnose the issues, repair them, and move on. Customers will understand the problem and remediation steps. What customers don’t understand, or accept, is a company failing to inform them. Or failing to improve security when they know there are issues.

    Steve Jones

    The Voice of the DBA Podcasts

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