WinDev


Hi All,

I'm trying to ensure that the access to my symmetric key is validated every use via the use of (X.509 Certifcate) Online Certificate Status Protocol (OCSP).

Question: When a Certificate encrypted Symmetric Key is opened in SQL Server 2005, does SQL Server support OCSP revocation checking

If it does support OCSP: Can the Certificate Revocation List Distribution Point of the certificate only contain an OCSP distribution point (instead of also including HTTP, LDAP, etc)

This is really important as I do not want any cached CRL to authorise the access to this highly sensitive data...

Platform:

- SQL Server 2005 on Windows Server 2003 R2

- Certificate Services on Windows Server 2003 R2

Refer to:

USE AdventureWorks;
OPEN SYMMETRIC KEY SymKeyMarketing3
DECRYPTION BY CERTIFICATE MarketingCert9;
GO

http://msdn2.microsoft.com/en-us/library/ms190499.aspx

Regards

Andrew R.




Re: OCSP support in SQL Server 2005?

Steven Gott - MS


I don't believe we check for revocation or expiration for certificates used to encrypt/decrypt symmetric keys in SQL Server 2005.

You will get a warning message if the certificate you are creating from file is expired but the operation is not blocked.

HTH,

-Steven Gott

SDE/T

SQL Server







Re: OCSP support in SQL Server 2005?

WinDev

Thanks for the reply Steven - this is a bit disappointing but I can understand the position - introducing an off-server lookup during a transaction could have some disasterous performance implications...

This raises some questions in this absence:

Question #1: Given the lack of CRL/Expiry checking, what makes (external) Certificates any stronger/better than the SQL Server generated Asymmetric Keys

Question #2: If the server to stolen physically or via backup tapes (and booted without access to Active Directory), do Cached Credentials allow the SQL Server to be booted and accessed (say by a rouge administrator)

Question #3: If there a recommended MS design and associated backup strategy for highly sensitive SQL Servers

[Maybe backup the OS (on local drives) via a local tape library, and backup the database (on SAN drives) via a SAN tape library; and then store them seperately -- This means access to the two backup tape sets would be required to restore the server so that Question #2 is possible...]

Regards,

Andrew R.






Re: OCSP support in SQL Server 2005?

Steven Gott - MS

Question #1: Given the lack of CRL/Expiry checking, what makes (external) Certificates any stronger/better than the SQL Server generated Asymmetric Keys

For SQL Server we will not know the difference.

Question #2: If the server to stolen physically or via backup tapes (and booted without access to Active Directory), do Cached Credentials allow the SQL Server to be booted and accessed (say by a rouge administrator)

If the server is stolen and restarted in isolation sql server should start but I haven't tested that recently so don't quote me.

If the backup tapes are stolen then they could be restored on a new machine and your enencrypted data would be compromised.

In SQL2008 some of these issues are addressed but it is a tricky area because you need to be bale to support disaster recovery scenarios where all the customer has is the backup tapes or the machine from the AD forest.

Question #3: If there a recommended MS design and associated backup strategy for highly sensitive SQL Servers

I'll ask around. I don't know of any off the top of my head but that doesn't mean the work hasn't been done.

HTH,

-Steven Gott

SDE/T

SQL Server






Re: OCSP support in SQL Server 2005?

Laurentiu Cristofor

For additional information related to question 1, see this post I just made: http://blogs.msdn.com/lcris/archive/2007/10/04/sql-server-2005-a-note-about-the-use-of-certificates.aspx.

Thanks

Laurentiu






Re: OCSP support in SQL Server 2005?

WinDev

Hi guys,

Thanks Stephen for your post; if you chould find a encryption/backup guideline/whitepaper, that would be great.

Thanks Laurentiu for your blog (this one and your entire site in general); it have been using it to confirm alot of my own design decisions.

So where from here The SQL Server 2008 mentions better data encryption... How about if I rephrase some of my questions in a SQL Server 2008 guise ; what changes

Original Question #1: When a Certificate encrypted Symmetric Key is opened in SQL Server 2008, does SQL Server support OCSP revocation checking

Original Question #2: Can the Certificate Revocation List Distribution Point of the certificate only contain an OCSP distribution point (instead of also including HTTP, LDAP, etc)

Followup Question #2: If the server to stolen physically or via backup tapes (and booted without access to Active Directory), do Cached Credentials allow the SQL Server 2008 to be booted and accessed (say by a rouge administrator)

New Question: If SQL Server 2008 is hosted on Windows 2008, does the extra OCSP support make any difference

References: SQL Server 2008 Datasheet

http://download.microsoft.com/download/B/F/2/BF24C54E-5635-4C79-AFB4-0C3F840E79F4/SQLServer2008_Datasheet_Final.pdf

"Protect Your Data"

"Dynamically encrypt your valuable data within an entire database, data files, or log files, without the need for application changes. Simplify increasing regulatory compliance by using the advanced data auditing abilities of SQL Server 2008."

Regards,

Andrew R





Re: OCSP support in SQL Server 2005?

Laurentiu Cristofor

As I mentioned in my post, implementing a PKI is up to you, so SQL Server does not enforce any certificate revocation policy.

Backups of databases (all server databases or a single database) can be protected via encryption since the release of SQL Server 2005. A backup severs the encryption chain at the DbMK or SMK level if it wasn't already severed by use of passwords. So a thief cannot decrypt the data because he is missing the encryption keys - he would have to break either DPAPI encryption or the 2TDES encryption used by passwords.

If you want to protect against the loss of the entire machine, SQL Server encryption can help again and you have two choices:

(1) keep your keys encrypted by password only - in this case the drawback is that you will have to deal with password management.

(2) use automatic key protection via DbMK->SMK->DPAPI, but then you should use this in conjunction with Vista's BitLocker encryption, which protects the OS in a lost machine scenario, which means protection for the DPAPI keys managed by the OS.

All of this applies to SQL Server 2005 - there is no need to shift the discussion to the next version. The new encryption features in the next version provide more flexibility and more choices in how you can implement this security, but these questions are already addressed by existing features.

Thanks

Laurentiu






Re: OCSP support in SQL Server 2005?

Laurentiu Cristofor

I added another comment to my recent post, for another take on why revocation is not really relevant to the SQL Server use of certificates.

I can definitely understand that you may have in mind a different application of certificates, but in that case you will need to deal with revocation enforcement in your own code.

I am actually a bit curious why you would need revocation enforcement at all - who would be the one to revoke the certificate and why would they need to go through revocation instead of immediately removing the certificate from the database and replacing its use with that of a newly created certificate

Thanks

Laurentiu






Re: OCSP support in SQL Server 2005?

Michael Hotek

Yes, if someone steals the entire physical server and boots it up off the network, the SQL Server will start with the cached credentials for the service account. But, if you have a SQL Server where this is one of your concerns, then you first need to address the physical security. No one should be able to steal a machine unless your physical security fails.

Yes, stolen backup tapes can be restored. However, unless you have backed up your Database Master Key and stored it with the backups, you can NOT get into the encrypted data. If someone were to attempt to regenerate a Database Master Key, it would not be able to decrypt and of the certificates, asymmetric keys, or symmetric keys which were encrypted by your original Database Master Key. Therefore, the encryption keys could not be opened to allow decryption of the data stored. So, obviously, you need to keep the backup of your Database Master Key separate from the backups of the database that it is used with.

SQL Server 2005 and prior does not have a capability to encrypt the backup itself. So, the backup strategy for highly sensitive servers follows the backup strategy for any sensitive server. You encrypt the files at an OS level and ensure that the ecnryption keys you are using are NOT stored with the backups, otherwise it is worthless to even encrypt it in the first place. If you have data encrypted within the database, you keep the backup of your database master key, separate from the SQL Server backups. So, yes, can accomplish an extremely secure server in SQL Server 2005 and you then have to apply the same standards to the backup media as you apply to any other sensitive data in your organization.






Re: OCSP support in SQL Server 2005?

Laurentiu Cristofor

It is a very good suggestion to keep the backup of the DbMK and that of the database separate. But note that even if you would keep them together, the DbMK backup can only be made with a password protection, which uses the same encryption strength as the DbMK itself. So, unless your password choice is poor or you stored the password with the backups, you would be covered even if the DbMK backup and the database backup are both stolen.

Thanks

Laurentiu






Re: OCSP support in SQL Server 2005?

Laurentiu Cristofor

One more addition: having backups of the DbMK is a good idea, but not that important, because the DbMK is stored within the database anyway, so regular database backups will backup the key too. If you change the DbMK more often than you do backups, then you can make backups of the new DbMK after each change, but you don't need to back it up each time you also make a database backup - so these backups don't need to be made or stored together.

For the SMK, it is more essential to have backups available because this key is protected by the OS via DPAPI and you cannot backup the DPAPI keys, so if your system goes down and you want to port the entire server databases (from backups) to another machine, you will need to recover the SMK from a backup, otherwise anything encrypted by it will be lost.

Thanks

Laurentiu