SQL Server 2005: A look at the master keys - part 2

re: SQL Server 2005: A look at the master keys - part 2

SteveJC — Sat, 08 Apr 2006 12:50:35 GMT

Hi Laurentiu

If we need to use the FORCE option when regenerating the SMK it mentions data loss.

If after using the FORCE we alter the DbMK and drop and then add the encryption by the SMK, will this prevent the data loss?

Cheers

re: SQL Server 2005: A look at the master keys - part 2

lcris — Mon, 10 Apr 2006 21:51:25 GMT

I just wrote a new post on this topic:

http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx

In the case of the DbMK, a loss could only occur if you don't remember the DbMK password. Take a look at the above link and let me know if it doesn't answer your questions.

Thanks

re: SQL Server 2005: A look at the master keys - part 2

MelS — Tue, 25 Apr 2006 15:20:34 GMT

Can you clarify the scenario in a cluster failover? Are you saying that if the Service Master Key's machine encryption is no longer valid because of a failover, any encryption that incorporates the SMK component in the DMK will fail? Decrypt and encrypt no longer valid in that database while it is failed over? Say it isn't so...

re: SQL Server 2005: A look at the master keys - part 2

lcris — Tue, 25 Apr 2006 20:38:41 GMT

It isn't so. That's why there are two encryptions of the SMK. If the machine key encryption is no longer valid, then the service account encryption will be used to access the SMK. Also, the machine key encryption will be restored if it was found to be invalid. So, it's a self-healing mechanism. This is explained in the second paragraph of my original post.

re: SQL Server 2005: A look at the master keys - part 2

si.rapier — Fri, 07 Jul 2006 17:13:18 GMT

I've got a couple of questions, hope they aren't too simplistic.

1. I understand that sql login passwords are hashed with SHA-1. Are they then encrypted via the service master key before being physically stored in the database file?

2. If I'm running the sql service under the Network Service account and I restore my master database to another server do I need to restore the service master key if I run the sql service under the Network Service account on the new server? In other words, are the Network Service account credentials the same on different machines?

I guess I'm most concerned about linked server passwords and other credentials rather than encrypted user database data, although cleary that would be affected in the same way.

re: SQL Server 2005: A look at the master keys - part 2

lcris — Sat, 08 Jul 2006 02:54:07 GMT

1. For SQL logins, SQL Server only stores the SHA1 hashes, so no encryption is necessary. For linked server SQL logins, we need the actual passwords, so they are encrypted using the SMK. No SHA1 hashes are maintained for linked logins - there is no need because we have the passwords themselves.

2. Yes, if the service account is Network Service, you should backup the SMK before moving the master database, and then you should restore it on the new server after copying the database. The only case when you don't need to do this backup-restore of the SMK is if the service account on the source and destination server is the same domain account.

re: SQL Server 2005: A look at the master keys - part 2

Catherine — Tue, 15 Aug 2006 08:44:46 GMT

We setup DR machine using disk Mirroring (e.g. EMC mirroring) from production to DR machine for the full databases disks. i.e. including the master, msdb & application databases
If we use the SAME 'DOMAIN SQL Server Services' account in both Production and DR machine, when we fail over to the DR machine, the encrypted data should be readable. This means we do NOT need to explicity restoring the SMK backup from production to DR machine?
If so, it will streamline the failover.

re: SQL Server 2005: A look at the master keys - part 2

lcris — Tue, 15 Aug 2006 21:57:27 GMT

What does DR stand for? If the DR machine and the production machine have the same set of databases and if SQL Server runs under the same domain account, then you should not need to do anything for the SMK in the event of a failover - the DPAPI service account encryption of the SMK will be used to decrypt it. If you can't make this work, please start a thread on http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1 and we'll look into it.

Thanks

re: SQL Server 2005: A look at the master keys - part 2

tdubya — Wed, 16 Aug 2006 00:10:22 GMT

Please let me know if this seems feasible or if it's a totally screwy idea. I have a SQL 2000 cluster with two nodes on which I need to be able to encrypt some sensitive data in one table. We're not quite ready to upgrade the whole system to SQL 2005. I was thinking of installing SQL 2005 on the same cluster and letting it run in parallel with SQL 2000 and then moving just the one table that needs to be encrypted into the 2005 instance. Would it be possible to do this and access the table in the 2005 instance using a DBLink and encrypt and decrypt the data in the process?

re: SQL Server 2005: A look at the master keys - part 2

Catherine — Wed, 16 Aug 2006 04:36:36 GMT

I have to appologize, the "DR" stands for "Disaster Recovery" machine.

re: SQL Server 2005: A look at the master keys - part 2

lcris — Thu, 17 Aug 2006 03:14:27 GMT

Thanks for the explanation Catherine.

re: SQL Server 2005: A look at the master keys - part 2

lcris — Thu, 17 Aug 2006 03:15:06 GMT

Re: tdubya's post

If I understand well, you want to move some data into SQL Server 2005, encrypt it there, and then access it from the SQL Server 2000 instance. The encryption should be no problem, you could automate it using triggers to do the encryption and a view for the decryption. But there may be issues if you have constraints that you need to maintain across the servers, for example. Basically, the encryption would not be a problem, but other aspects of accessing data on another server may be.

re: SQL Server 2005: A look at the master keys - part 2

bstichter — Thu, 19 Jun 2008 21:23:03 GMT

lcris, A thought occured to me as I read this post and I wanted your thoughts. It seems to me that in this era of virtualization the dual encryption of the SMK introduces a new problem. No longer do sysadmins "just" backup the data (which they probably do as well), but now they backup the entire machine by taking a snapshot of the virtual. Wouldn't that mean that anyone who successfully obtains a backup of a virtual now have the machine credentials necessary to obtain the SMK? In the pre-virtual world backups were safe with this sceme, but it seems they could now be compromised. Am I right?

re: SQL Server 2005: A look at the master keys - part 2

lcris — Mon, 23 Jun 2008 22:11:17 GMT

I don't think the problem is introduced by the dual encryption - it is introduced by the ability to take the VM snapshot, which, depending on the extent of information that it captures, might expose sensitive information that is normally protected by the OS/applications. Loosing a snapshot would be similar (if not equivalent) to having the entire machine stolen.

To decrypt the SMK using the machine credentials, you just need access to the machine and to the registry. You can probably get the registry contents from the snapshot and if you can restore the snapshot and logon to the system, then you can eventually decrypt the SMK (same path you would need to follow with a stolen machine).

If you want to protect against a VM snapshot loss, the best solution is to cut the dependency on the SMK by having the root of your encryption key chain be protected by a password - even then you're still running a risk if the snapshot could capture that password in memory (it should only be kept around while it is used for decryption, but if that's when the snapshot occurs...).

Note that if your data depends on the security of the SMK, then to protect it against the case of machine theft, you should also use a feature like Vista's BitLocker, which will protect the DPAPI keys protecting the SMK. If we keep the comparison between machine loss and VM snapshot loss, then the equivalent of BitLocker in the latter case would be a snapshot encryption feature.

Hope this helps. Also see http://blogs.msdn.com/lcris/archive/2005/12/20/about-security-and-encryption-with-references-to-sql-server-2005.aspx.