Sql 2005 - how to allow users to decrypt table data using a database certificate ??

[frostbb]

Ok, I've spent the last day or so reading up on Sql Server 2005 data
encryption methods. i.e. Encryption newbie.

We have a couple of columns in a couple of tables that need to be encrypted
for privacy purposes.

We're using Win2003 Enterprise & Sql Server 2005

I think I understand the basic Sql Server 2005 encryption hierachy i.e.

(01) Win Data Protection API =>
(02) Searvice Master Key (backed up) =>
(03) Database Master Key (created for db's w/ sensitive data & backed up)
=>
(04) Certificates (using Database Master Key) => etc.

Ok ... so I've got a certificate in place and as 'SA' I can encrypt data to
a table column and decrypt data from a table column. So far so good.

Now I need to allow a certain set of Win Domain users to encrypt and decypt
the data to and from the secure table columns using the EncryptByCert and
DecryptByCert.

THE QUESTION: How do I as the 'dba' assign the ability to use a Certificate
to encrypt and decript data to an Sql Server 2005 database >> roll << that
represents a set of Win Domain users ??

The documents I've found on the web do a pretty good job of explaining how
to setup the encryption tools but I've yet to find a good explanation of how
to allow my users to access the encrypted data. Data security does us no
good if the people that NEED to access the data can't.

For instance ...

I have a set of Win Domain Users that belong to the Win Domain Group
"License Mgrs"

The Sql Server 2005 roll "License Managers" maps to the Win Domain
Group "License Mgrs" and has R/W permissions to the Database Tables with the
encrypted columns. The members of the "License Manager" roll can Read and
Write to the non-encrypted data within the target database tables with no
problems. Works fine.

So the question boils down to "What permissions do I grant to the "License
Managers" roll that will allow the members to use the EncryptByCert and
DecryptByCert statements" ???

Thanks in advance !!

Barry
in Oregon

P.S. Insert, Update & Delete access to the tables is via stored procs, the
domain users are granted access to the appropriate stored procs via database
roll.

Select privledges are granted directly to the target database tables again
via "read-only" rolls.

[privatenews]

Hello,

Thank you for using Microsoft MSDN newsgroup. Great to meet you again. :-)

From your description, I understand that you'd like to know what permission
you need to grant to user group so that they could use EncryptByCert and
DecryptByCert to read/write tables when necessary. If I'm off-base, please
let me know.

If the users have permission of the table, they could read the data
properly by using the decryptByCert function by refererence the certificate
ID. For example:

SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardType)) AS CardType,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardNumber)) AS CardNumber,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),Notes)) AS Notes
FROM Customer


Also, users need to have the proper permission on the certificate if they
want to use the certificate in a SQL statement. We shall grant the
permission to the user or group by using sth like:

grant control on certificate::certficatename to testuser

Please check the following link for details:

http://msdn2.microsoft.com/en-us/ms186278.aspx

Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.asp
x#S5


Also, you could refer to "Key access control" topic in the following
article to use a SP to use a broker to do this job:

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx


More relate inforamtion

http://blogs.msdn.com/yukondoit/articles/480854.aspx

http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx

918346 The user is not assigned to a default schema when you use Windows
authentication to connect the user to SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918346

If anything is unclear, please feel free to let us know. Have a great day.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

[frostbb]

Peter,

Again thanks for your quick response. As always, it's very much
appreciated!

You understand the issue correctly. Thanks for the references. I found
many of them yesterday when I was researching encryption and I've looked
thru the others that you provided. Surprisingly, most of the articles I've
read do a great job describing how to CREATE Certificates, Aysymmetric and
Symmetric keys. Unfortunately, none of the articles I've seen actually
seem to discuss (or better yet - provide examples of) how and what
PERMISSIONS need to be GRANTED to these objects to allow them to be used by
the target users.

Your suggestion to grant CONTROL permission on the CERTIFICATE to
"License_Managers" NT Domain Group login >> worked <<.

I was granting REFERENCES (obvously I'm still unsure of what the Sql Server
'flavor' permissions actually 'mean'.

However, isn't granting CONTROL permission on a CERTIFICATE kind of HUGE
permissions functionality to the "License_Managers" group when I simply what
to allow them to USE the certificate to encrypt and decrypt data ??? It's
my understanding that anyone with CONTOL permissions on a database object
essentially has complete permissions to do ANYTHING to the object. I would
like to restrict the "License_Managers" group members to simply encryption
and decryption functionality USING the CERTIFICATE. I'm not exactly
excited about giving them the ability to alter, drop, etc the certificate.
Granting CONTROL permission seems to destroy, to a great extent, the
security that the CERTIFICATE is intended to provide.

For instance, as a member of "License_Managers" I successfully granted
CONTROL of the CERTIFICATE to the NT Domain Group "My Friends".

Doesn't seem very secure to me ... IS THERE SOME LESSER PERMISSION than
CONTROL that I could grant to the "License_Managers" Login group that still
allow group members to encrypt and decrypt data while preventing them from
having essentially sa permissions on the certifcate object ??

The permissions avaiable for CERTIFICATES seem to be CONTROL, TAKE
OWNERSHIP, ALTER, REFERENCES & VIEW DEFINITION.

Is there some place that actully describes the REFERENCES permisson
functionality for CERTIFICATES ?? BOL isn't very clear at all about what
the REFERENCES permissions actually does ...


I can also recommend the following articles when researching Sql Server 2005
encryption.

http://www.databasejournal.com/features/mssql/article.php/3483931
http://www.sqlservercentral.com/columnists/bkelley/sqlserver2005logins.asp
http://www.code-magazine.com/article.aspx?quickid=0509021&page=1
http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx

Thanks in advance. REALLY appreciate your help on this.

Barry
in Oregon

===== The steps I've taken to create the certificate and attempt to grant
permissions to the "License Managers" NT Domain Login. =====
use [testdb]
GO

CREATE CERTIFICATE testCertificate
WITH SUBJECT = 'testCertificate for data encryption',
EXPIRY_DATE = '08/01/2006';
GO

GRANT REFERENCES ON CERTIFICATE::[testCertificate] TO
[NtDomain\License_Managers]
GO

changed to CONTROL at your suggestion allowed the "License_Mangers" login to
decode the data using the testCertificate.

GRANT CONTROL ON CERTIFICATE::[testCertificate] TO
[NtDomain\License_Managers]
GO

===== Results of the test script below
==========================================

As an SA level user I was able to use the following script to encrypt and
decrypt test_data successfullly i.e. the results of the

@test_data = 'abcdefgh'
@encrypted_test_data = '-?Þ\ª-¢®àõN"Pê>»ÕL*Y;. ... snip ...
Qþ­Pw5èQ¹-W$õTúŠ~Íÿ.v'
@unencrypted_test_data = 'abcdefgh'

As a non-sa level Win Nt domain user belonging to the "License_Managers"
(with REFERENCES permissions) Sql Server login the results are

@test_data = 'abcdefgh'
@encrypted_test_data = '-?Þ\ª-¢®àõN"Pê>»ÕL*Y;. ... snip ...
Qþ­Pw5èQ¹-W$õTúŠ~Íÿ.v'
@unencrypted_test_data = NULL

As a non-sa level Win Nt domain user belonging to the "License_Managers"
(with CONTROL permissions) Sql Server login the results are

@test_data = 'abcdefgh'
@encrypted_test_data = '-?Þ\ª-¢®àõN"Pê>»ÕL*Y;. ... snip ...
Qþ­Pw5èQ¹-W$õTúŠ~Íÿ.v'
@unencrypted_test_data = 'abcdefgh'

SUCCESS SCORE !!!!!!

Barry
in Oregon

===== Encryption - Decryption Test Script ================================

DECLARE @test_data varchar(9)
DECLARE @encrypted_test_data varchar(150)
DECLARE @unencrypted_test_data varchar(150)

SET @test_data = 'abcdefgh'
SET @encrypted_test_data = NULL
SET @unencrypted_test_data = NULL

SET @encrypted_test_data = EncryptByCert(Cert_ID('testCertificate'),
@test_data)
SET @unencrypted_test_data =
CAST(DecryptByCert(Cert_ID('testCertificate'),@encrypted_test_data) AS
varchar(100))

PRINT 'test_data = ' + RTRIM(ISNULL(@test_data,'NULL'))
PRINT 'encrypted_test_data = ' + RTRIM(ISNULL(@encrypted_test_data,'NULL'))
PRINT 'unencrypted_test_data = ' +
RTRIM(ISNULL(@unencrypted_test_data,'NULL'))

================================================================

Post by privatenews
Hello,
Thank you for using Microsoft MSDN newsgroup. Great to meet you again. :-)
From your description, I understand that you'd like to know what permission
you need to grant to user group so that they could use EncryptByCert and
DecryptByCert to read/write tables when necessary. If I'm off-base, please
let me know.
If the users have permission of the table, they could read the data
properly by using the decryptByCert function by refererence the certificate
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardType)) AS CardType,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardNumber)) AS CardNumber,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),Notes)) AS Notes
FROM Customer
Also, users need to have the proper permission on the certificate if they
want to use the certificate in a SQL statement. We shall grant the
grant control on certificate::certficatename to testuser
http://msdn2.microsoft.com/en-us/ms186278.aspx
Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.asp
x#S5
Also, you could refer to "Key access control" topic in the following
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
More relate inforamtion
http://blogs.msdn.com/yukondoit/articles/480854.aspx
http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx
918346 The user is not assigned to a default schema when you use Windows
authentication to connect the user to SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918346
If anything is unclear, please feel free to let us know. Have a great day.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[Laurentiu Cristofor [MSFT]]

You should not encrypt your data directly using certificates. You should
encrypt data using symmetric keys instead. Asymmetric key
encryption/decryption is considerably slower that symmetric key encryption
and is not necessarily stronger or more secure.

If you really, really want to use certificates to protect your data, then
you can wrap the decryptbycert, encryptbycert calls in signed stored
procedures and control access to those operations by granting EXECUTE
permissions on those procedures.

Thanks
--
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

Post by frostbb
Peter,
Again thanks for your quick response. As always, it's very much
appreciated!
You understand the issue correctly. Thanks for the references. I found
many of them yesterday when I was researching encryption and I've looked
thru the others that you provided. Surprisingly, most of the articles I've
read do a great job describing how to CREATE Certificates, Aysymmetric and
Symmetric keys. Unfortunately, none of the articles I've seen actually
seem to discuss (or better yet - provide examples of) how and what
PERMISSIONS need to be GRANTED to these objects to allow them to be used
by the target users.
Your suggestion to grant CONTROL permission on the CERTIFICATE to
"License_Managers" NT Domain Group login >> worked <<.
I was granting REFERENCES (obvously I'm still unsure of what the Sql
Server 'flavor' permissions actually 'mean'.
However, isn't granting CONTROL permission on a CERTIFICATE kind of HUGE
permissions functionality to the "License_Managers" group when I simply
what to allow them to USE the certificate to encrypt and decrypt data ???
It's my understanding that anyone with CONTOL permissions on a database
object essentially has complete permissions to do ANYTHING to the object.
I would like to restrict the "License_Managers" group members to simply
encryption and decryption functionality USING the CERTIFICATE. I'm not
exactly excited about giving them the ability to alter, drop, etc the
certificate. Granting CONTROL permission seems to destroy, to a great
extent, the security that the CERTIFICATE is intended to provide.
For instance, as a member of "License_Managers" I successfully granted
CONTROL of the CERTIFICATE to the NT Domain Group "My Friends".
Doesn't seem very secure to me ... IS THERE SOME LESSER PERMISSION than
CONTROL that I could grant to the "License_Managers" Login group that
still allow group members to encrypt and decrypt data while preventing
them from having essentially sa permissions on the certifcate object ??
The permissions avaiable for CERTIFICATES seem to be CONTROL, TAKE
OWNERSHIP, ALTER, REFERENCES & VIEW DEFINITION.
Is there some place that actully describes the REFERENCES permisson
functionality for CERTIFICATES ?? BOL isn't very clear at all about what
the REFERENCES permissions actually does ...
I can also recommend the following articles when researching Sql Server
2005 encryption.
http://www.databasejournal.com/features/mssql/article.php/3483931
http://www.sqlservercentral.com/columnists/bkelley/sqlserver2005logins.asp
http://www.code-magazine.com/article.aspx?quickid=0509021&page=1
http://blogs.msdn.com/yukondoit/archive/2005/11/24/496521.aspx
Thanks in advance. REALLY appreciate your help on this.
Barry
in Oregon
===== The steps I've taken to create the certificate and attempt to grant
permissions to the "License Managers" NT Domain Login. =====
use [testdb]
GO
CREATE CERTIFICATE testCertificate
WITH SUBJECT = 'testCertificate for data encryption',
EXPIRY_DATE = '08/01/2006';
GO
GRANT REFERENCES ON CERTIFICATE::[testCertificate] TO
[NtDomain\License_Managers]
GO
changed to CONTROL at your suggestion allowed the "License_Mangers" login
to decode the data using the testCertificate.
GRANT CONTROL ON CERTIFICATE::[testCertificate] TO
[NtDomain\License_Managers]
GO
===== Results of the test script below
==========================================
As an SA level user I was able to use the following script to encrypt and
decrypt test_data successfullly i.e. the results of the
@test_data = 'abcdefgh'
@encrypted_test_data = '-?Þ\ª-¢®àõN"Pê>»ÕL*Y;. ... snip ...
Qþ­Pw5èQ¹-W$õTúŠ~Íÿ.v'
@unencrypted_test_data = 'abcdefgh'
As a non-sa level Win Nt domain user belonging to the "License_Managers"
(with REFERENCES permissions) Sql Server login the results are
@test_data = 'abcdefgh'
@encrypted_test_data = '-?Þ\ª-¢®àõN"Pê>»ÕL*Y;. ... snip ...
Qþ­Pw5èQ¹-W$õTúŠ~Íÿ.v'
@unencrypted_test_data = NULL
As a non-sa level Win Nt domain user belonging to the "License_Managers"
(with CONTROL permissions) Sql Server login the results are
@test_data = 'abcdefgh'
@encrypted_test_data = '-?Þ\ª-¢®àõN"Pê>»ÕL*Y;. ... snip ...
Qþ­Pw5èQ¹-W$õTúŠ~Íÿ.v'
@unencrypted_test_data = 'abcdefgh'
SUCCESS SCORE !!!!!!
Barry
in Oregon
===== Encryption - Decryption Test Script
================================
@test_data)
varchar(100))
PRINT 'encrypted_test_data = ' +
PRINT 'unencrypted_test_data = ' +
================================================================

Post by privatenews
Hello,
Thank you for using Microsoft MSDN newsgroup. Great to meet you again. :-)
From your description, I understand that you'd like to know what permission
you need to grant to user group so that they could use EncryptByCert and
DecryptByCert to read/write tables when necessary. If I'm off-base, please
let me know.
If the users have permission of the table, they could read the data
properly by using the decryptByCert function by refererence the certificate
SELECT CustID, Name, City,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardType)) AS CardType,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),
CreditCardNumber)) AS CardNumber,
CONVERT(VARCHAR,
DecryptByCert(Cert_ID('User1Certificate'),Notes)) AS Notes
FROM Customer
Also, users need to have the proper permission on the certificate if they
want to use the certificate in a SQL statement. We shall grant the
grant control on certificate::certficatename to testuser
http://msdn2.microsoft.com/en-us/ms186278.aspx
Keep Bad Guys at Bay with the Advanced Security Features in SQL Server 2005
http://msdn.microsoft.com/msdnmag/issues/05/06/SQLServerSecurity/default.asp
x#S5
Also, you could refer to "Key access control" topic in the following
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
More relate inforamtion
http://blogs.msdn.com/yukondoit/articles/480854.aspx
http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx
918346 The user is not assigned to a default schema when you use Windows
authentication to connect the user to SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918346
If anything is unclear, please feel free to let us know. Have a great day.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[privatenews]

Hello Barry,

Thank you for your reply. As Laurentiu metnioned, it is not suggested that
you use encrypt data directly uisng certficates because of performance
consideration, and this is documented in BOL.

Though it is feasible to use grant permssion to certificate to user/groups,
this has security risk as you have noticed. You may consider the method
Laurentiu suggested if you still want to use certficiates to
decrypt/encrypt data directly.

As per your question on "references" permission, I did not find exact
documents on this but I think it shall similar to references permssion for
Table/Type etc. It shall the permissions to reference the properties of
certificates. Apparently references permission is not enough to use
decrypt/encrupt method.

If you have further concerns on this, please let's know. Have a great day!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.

[frostbb]

Peter,

Thanks to you and Mr Cristofor for your responses.

Being new to encryption, I was unaware and kind of surprised that Sql Server
2005 Certificates shouldn't be used directly to encrypt/decrypt data. Most
of the documents mentioned that asymmetric encryption is more expensive, CPU
cycle wise, but it's also supposed to be more secure. If you've got a 2000
row table with 1 or 2 columns that need to be encrypted. Using Certificate
/ asymmetric encriptions shouldn't much of an issue. Most of us out here in
the trenches are NOT Nike or Boeing.

BOL and most of the other documents I've read seem to leave it up to me
(i.e. the user) as to whether to use Certificates, Asymmetric or Symmetric
encryption.

I'm finding the whole subject more that a little confusing. Its also
something that I feel like I need to 'get right' at the outset. After all,
if I blow it, I could lose us a column's worth of sensitive data. A MS
WHITE PAPER with >> multiple << simple, moderate and complex "best practice"
Sql Server 2005 encryption >> EXAMPLES << and recommendations would be VERY
MUCH APPRECIATED at this point.

Again, thanks for the prompt responses.

Barry
in Oregon

Post by privatenews
Hello Barry,
Thank you for your reply. As Laurentiu metnioned, it is not suggested that
you use encrypt data directly uisng certficates because of performance
consideration, and this is documented in BOL.
Though it is feasible to use grant permssion to certificate to
user/groups,
this has security risk as you have noticed. You may consider the method
Laurentiu suggested if you still want to use certficiates to
decrypt/encrypt data directly.
As per your question on "references" permission, I did not find exact
documents on this but I think it shall similar to references permssion for
Table/Type etc. It shall the permissions to reference the properties of
certificates. Apparently references permission is not enough to use
decrypt/encrupt method.
If you have further concerns on this, please let's know. Have a great day!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

[privatenews]

Hello Barry,

Thank you for your feedback on our document/samples on SQL 2005 encryption
and I agree with you it will surely benefit customers. Currently all the
security related documents are listed under

http://www.microsoft.com/technet/prodtechnol/sql/2005/library/security.mspx

Please rest assured that your feedback on this is routed to the proper
channel this is a great idea for a future product/document enhancement

Based on my scope, it is usually suggested to use Symmetric keys to encrypt
key and the symmetric key can be encrypted using a certificate's public
key, so the certificate's private key is necessary for decryption. The
certificate's private key in turn can be protected either by a password or
by using a database master key.

http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx

Also, I recommend that you refer to some useful articles written by
Laurentiu

http://blogs.msdn.com/lcris/archive/category/10357.aspx

If you have further questions, please let me know. Have a great day!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

[privatenews]

Hello Barry,

Just want to check in to see if you have further questions or comments on
this issue. Please feel free to post back if you have any feedback.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================