SQL Server 2005: A note about the use of certificates

re: SQL Server 2005: A note about the use of certificates

lcris — Fri, 05 Oct 2007 12:21:27 GMT

With the risk of repeating myself, let me try to explain again why certificate revocation is not essential to the SQL Server use:

In SQL Server, certificates are not used for signing code that is distributed to clients or other servers. They are used to either encrypt keys or to grant permissions to code in a database. If a certificate is compromised, you can generate another and re-encrypt your keys in the database or re-sign your objects - you do not need to revoke the certificate for other servers to know it's not trustworthy, because other servers should not care about your certificate anyway - the trust in a certificate is limited to the server to which it is deployed, because the permissions assigned to it are database or server scoped. In a way, you can look at code signing in SQL Server as a way of assigning roles to T-SQL code. Same way as you can assign a bag of permissions to a role, you can assign a bag of permissions to a certificate and then sign code with that certificate like you would make users members of a role. The equivalent of dropping a role member in this signing paradigm would be removing the signature off a procedure (or any signable module).

re: SQL Server 2005: A note about the use of certificates

aloukian — Thu, 12 Nov 2009 02:57:38 GMT

Hi, Thanks for this very good blog covering SQL server cryptography.

I have a question.

I am planing to use SQL encryption in my project, but I encountered a problem. I have two users that should be able to access encrypted information. One user is mapped to Windows user, the second user is mapped to Windows security group. I was planning to encrypt symmetric key (which is used for encryption) using two certificates created for each of these users. The problem is that user mapped to Windows security group cannot own a certificate, therefore this user cannot open symmetric key. I cannot use password to protect the encryption key, because both users are actually programs and I will face the problem of storing the password securely.

I read your article on TSQL code signing - can it be used to solve my problem? Is there any other solution?

re: SQL Server 2005: A note about the use of certificates

lcris — Wed, 25 Nov 2009 19:55:42 GMT

Sorry for the delay in responding - I haven't got a comment in a while, so I don't check for new comments that often these days.

I suggest posting your question on the SQL Server securirty forum and then posting a link to that thread here. Please make sure to explain what exactly you are trying to accomplish - often times, people ask how I can do X and it turns out that it's not necessary at all to do X and a simpler solution exists.