Encryption Performance

Hi

I am trying to encrypt data using a symmetric key which is encrypted by certificate. I do not want grant control on these objects to the users who wants to decrypt this data. Instead I have created a udf with execute context as "dbo" and used DecryptByKeyAutoCert built-in function.

Now this works fine but large data operations this is extremely slow. It takes around 10 minutes to select decrypted data whic in comparision takes 11 seconds when DecryptByKey function is used.

But I am not sure when DecryptByKey is used, whether the symmetric key is decrypted by the private key of the certificate or not. Can somebody give some explanation of this ?

Also, I can not have a UDF with these following steps

1. Open symmetric key

2. Convert secretdata using DecryptByKey

3. Close Symmetric Key.

4. return decrypted value.

Can some one give some insights on this ?

Can you show the way you call the DecryptByKeyAutoCert and DecryptByKey builtins? Also, how much data are you decrypting - what are the number of rows you select and the size of encrypted data per row?

You cannot create a function to decrypt, but you can create a procedure to decrypt. For example, see the procedure from http://blogs.msdn.com/lcris/archive/2006/01/13/512829.aspx.

Thanks
Laurentiu

|||

Number of rows that I am decrypting is 10000. The record size is 516 bytes

DecryptByKey code:

OPEN SYMMETRIC KEY [Cert_Account_Data_Key] DECRYPTION by certificate [cert_Account_Data]

-- Account table has 10000 records

select account_id,

convert( nvarchar(100), decryptbykey(account_number)) as 'Decrypted Account Number',

convert( nvarchar(100), decryptbykey(account_ssn)) as 'Decrypted Account SSN'

from account_t

CLOSE SYMMETRIC KEY [Cert_Account_Data_Key]

DecryptByCert code:

-- 1. create udf

CREATE FUNCTION [dbo].[udf_Decrypt_Account_Data] (@.Secret_Data VARBINARY(256)) returns nvarchar(100)

WITH EXECUTE AS 'DBO'

AS

begin

-- This return decrypted value for the input data using Account Data

return convert( nvarchar(100), decryptbykeyautocert( cert_id( 'cert_Account_Data' ), null, @.Secret_Data))

end

-- selects decrypted data using Account decryption function

select ACCOUNT_ID,

dbo.udf_Decrypt_Account_Data (ACCOUNT_NUMBER) as 'Decrypted Account Number',

dbo.udf_Decrypt_Account_Data (ACCOUNT_SSN) as 'Decrypted Account SSN'

from ACCOUNT_T

thanks

satya

|||

Using decryptbykeyautocert like this will give abysmal performance. The reason for this is that decryptbykeyautocert is efficient if you use it in a query - it will decrypt the key once and it will keep it open for the duration of the query. By putting the builtin call within a function and calling the function from a query, you are basically forcing the builtin to reopen the key each time the function is called - twice per row in your case, and this represents significant overhead.

You don't need to give CONTROL on the encryption key to a user, for him to be able to use it. It is sufficient to grant him VIEW DEFINITION and add another encryption to the key so that the user can access the key through the new encryption. You can add, for example, another certificate encryption using one of the user's certificates. Then the user will be able to just call decryptbykeyautocert directly, instead of this function, and the query will execute much faster.

Thanks
Laurentiu

encryption optimization

Hi,
I have an application that requires the storing of personal data, name,
address, ssn with a requirement that key fields, first name, last name,
address1, city, zip, ssn be encrypted. Currently, there are over 100,000
records, and growing fast. Searches need to be done based on these key
fields (lastname like 'A%' for instance). I am loading the return set into
SqlDataSource and GridView using .net version 2.0.
I'm using a stored procedure to return the data.
When the page first loads (which filters on lastname = 'A%', it loads
slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it times
out.
Is there a best practice to follow in a case like this that would result in
the best performance possible. I realize with all the encryption that it is
very processing intensive, but with a first load at least usable, but the
second load timing out, there may be some things I should do (clear buffers
or something) that I do not know about doing.
Can you help on this?
Thanks.The bottom line is that you can't efficiently search (use indexes) on these
encrypted key columns because you are searching using the decrypted value
and this value is not stored in the database. During the search, not only
must each value be decrypted, a scan of all table rows is required. This is
a very expensive operation.
For an equality search, you can store a hash in clear text, index the hash
value and add the hash search criteria to your search. This will greatly
reduce the number of qualifying rows and usually result in acceptable
performance. See Laurentiu Cristofor's blog
(http://blogs.msdn.com/lcris/archive.../22/506931.aspx) for a more
complete discussion.
Unfortunately, there is no way to perform efficient wildcard/range searches
on encrypted data. If you must have this functionality, you'll need to take
a different approach like encrypting at the file level (EFS) rather than
column level. I don't know if that's an option in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gerhard" <acsla@.community.nospam> wrote in message
news:CA33D347-57EC-451F-A3BB-A3931D8CFF65@.microsoft.com...
> Hi,
> I have an application that requires the storing of personal data, name,
> address, ssn with a requirement that key fields, first name, last name,
> address1, city, zip, ssn be encrypted. Currently, there are over 100,000
> records, and growing fast. Searches need to be done based on these key
> fields (lastname like 'A%' for instance). I am loading the return set
> into
> SqlDataSource and GridView using .net version 2.0.
> I'm using a stored procedure to return the data.
> When the page first loads (which filters on lastname = 'A%', it loads
> slowly, about 20 seconds), if I change the filter (lastname = 'Q%') it
> times
> out.
> Is there a best practice to follow in a case like this that would result
> in
> the best performance possible. I realize with all the encryption that it
> is
> very processing intensive, but with a first load at least usable, but the
> second load timing out, there may be some things I should do (clear
> buffers
> or something) that I do not know about doing.
> Can you help on this?
> Thanks.|||Hi Acsla,
I am interested in this issue. Would you mind letting me know the result of
the suggestions?
I noticed that you bind the query result to a Gridview, so I guess that the
loading time (20s and time out) refers to the whole loading process when
all data has been displayed in your GridView. Does the Gridview have a
paging function? According to my experience, if the query result is also
huge, the render time of the control may also spend a long time.
Basically I agree with Dan's suggestions on SQL. Additionally, I would like
your checking how long it will spend if you separately run the SQL
statement in Query Analyzer.
If you need further assistance, feel free to let me know. I will be more
than happy to be of assistance.
Have a great day!
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Encryption Key Errors

I got error as below:

Failure sending mail: The report server has encountered a configuration error. See the report server log files for more information.

Then i found error in log file as below:

ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., AuthzInitializeContextFromSid: Win32 error: 5; possible reason - service account doesn't have rights to check domain user SIDs.;
Info:

Does that mean sth to do with SQL agent running account or SQl Server Report Service running account ?

Thanks

Nick

http://support.microsoft.com/?kbid=842423|||

Thanks for you information. Teo

when i change the Local account for running SQL Server Report Services to dominon account, then i got http://servername/reports to view report, then i got error below:

An error has occurred during report processing. (rsProcessingAborted)
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)
Bad Data. (Exception from HRESULT: 0x80090005)

What i am understand is that when i create this report it's under local system account , so this report doesn't work when i change the user account. is that right? and how can i handle it ?

Cheers

Nick

|||You will need to re-initialize the server. Assuming RS 2005, use the Reporting Services Configuration (found under the Configuration Tools program group in Microsoft SQL Server 2005 group) to do so. Or, you can drop the decrypted content by running rskeymgmt -d (you will need to reset your data source connection strings after this).|||

when you say re-initialize the server , does that mean i need to delete the existing isntance from reporting service configruation , and create a new one. not just click the initialize button .

is that right understanding?

Cheers

Nick

|||By just clicking on the Initialization button in the Reporting Services Configuration tool.|||

i got erroer when i click initialize button in Report service configuration tool,

"Joining report server ac553f4....... to the web farm of the local instance" The task failed.

Exception Details"

ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.InitializeReportServer(String installationId)

Thanks

Nick

|||it looks like the only option is to drop the encryption content using rskeymgmt -d. You will need to reset your data source credentials after this.|||

Hi all, I have seen that I have a similar problem with Encryption Key working with reports. My problem is that I′m working with VSt2003 and I have some reports made, but yesterday I was just testing some ASP.NET examples with VS2005. This morning when I continued working with reports in VS2003, I recieved this error:

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)

And it wasn′t possible to see the reports.

Do you now the way to fix this problem?

Thanks in Advance

|||I don't know what happened yesterday but somehow you managed to deactivate the server. Re-initializing ASP.NET (aspnet_regiis) will do the trick. Anyway, if you have backed up the encryption key (you did, didn't you?), now is the perfect time to use the Reporting Services Configuration utility and restore it. If you haven't, do rskeymgmt -d.sql

Encryption Key

I am getting the following error while configuring the reporting services on
my local machine. I cannot restore the key as I do not know the file or the
password.
Somebody please help!
ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
(rsFailedToDecryptConfigInformation)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()Hello,
Are you installing a new version of Reporting Services (with clear
ReportServer database) or just reinstalling it with using previous version
of ReportServer database'
It looks like that you've reinstalled Reporting Services without recovering
previous encryption key.
If you don't have any copy of your previous encryption key, the only way to
make Reporting Services content available is to delete all unusable
encrypted data from ReportServer database.
Follow these steps to apply the encryption key to the report server
database:
1.. Run rskeymgmt.exe locally on the computer that hosts the report
server. You must use the -d apply argument. The following example
illustrates the argument you must specify:
rskeymgmt -d
2.. Restart Internet Information Service (IIS).
After the values are removed, you must re-specify the values as follows:
1.. Run rsconfig utility to specify a report server connection. This step
replaces the report server connection information. For more information, see
Configuring a Report Server Connection and rsconfig Utility.
2.. If you are supporting unattended report execution for reports that do
not use credentials, run rsconfig to specify the account used for this
purpose. For more information, see Configuring an Account for Unattended
Report Processing.
3.. For each report and shared data source that uses stored credentials,
you must retype the user name and password. For more information, see
Specifying Credential and Connection Information.
4.. Open and resave each subscription. Subscriptions retain residual
information about the encrypted credentials deleted during the rskeymgmt
delete operation. You can update the subscription by opening and saving it.
You do not need to modify or recreate it.
I hope this infomation will helpful.
Best Regards,
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>I am getting the following error while configuring the reporting services
>on
> my local machine. I cannot restore the key as I do not know the file or
> the
> password.
> Somebody please help!
> ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> value
> for the "LogonCred" configuration setting cannot be decrypted.
> (rsFailedToDecryptConfigInformation)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()|||Hi Rad,
The very first step in trying to delete the key is giving me the same
exception which is ReportServicesConfigUI.WMIProvider.WMIProviderException:
The encrypted value
for the "LogonCred" configuration setting cannot be decrypted.
rskeymgmt -d -i instance
This is completely new installation in 2steps.
1. Installed sql server 2005 with all other services except reporting
services as iis was not installed at that time.
2. Installed reporting services.
"Radoslaw Lebkowski" wrote:
> Hello,
> Are you installing a new version of Reporting Services (with clear
> ReportServer database) or just reinstalling it with using previous version
> of ReportServer database'
> It looks like that you've reinstalled Reporting Services without recovering
> previous encryption key.
> If you don't have any copy of your previous encryption key, the only way to
> make Reporting Services content available is to delete all unusable
> encrypted data from ReportServer database.
> Follow these steps to apply the encryption key to the report server
> database:
> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
> server. You must use the -d apply argument. The following example
> illustrates the argument you must specify:
> rskeymgmt -d
> 2.. Restart Internet Information Service (IIS).
> After the values are removed, you must re-specify the values as follows:
> 1.. Run rsconfig utility to specify a report server connection. This step
> replaces the report server connection information. For more information, see
> Configuring a Report Server Connection and rsconfig Utility.
> 2.. If you are supporting unattended report execution for reports that do
> not use credentials, run rsconfig to specify the account used for this
> purpose. For more information, see Configuring an Account for Unattended
> Report Processing.
> 3.. For each report and shared data source that uses stored credentials,
> you must retype the user name and password. For more information, see
> Specifying Credential and Connection Information.
> 4.. Open and resave each subscription. Subscriptions retain residual
> information about the encrypted credentials deleted during the rskeymgmt
> delete operation. You can update the subscription by opening and saving it.
> You do not need to modify or recreate it.
> I hope this infomation will helpful.
> Best Regards,
> Radoslaw Lebkowski
>
> U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
> >I am getting the following error while configuring the reporting services
> >on
> > my local machine. I cannot restore the key as I do not know the file or
> > the
> > password.
> > Somebody please help!
> >
> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
> > value
> > for the "LogonCred" configuration setting cannot be decrypted.
> > (rsFailedToDecryptConfigInformation)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
> > mo)
> > at
> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>
>|||Hmm, that's a little strange situation.
Under the following link there is a very similar problem with LogonCred
decryption problem.
http://groups.google.pl/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ec7858be1ef25e24/9f72570a671b74e7?lnk=st&q=LogonCred+decrypted&rnum=1&hl=pl#9f72570a671b74e7
Maybe you have problem with authorization a connection to ReportServer
database (bad values contained in RSReportServer.config file used to connect
to the report server database)
Try to use rsconfig utility described below
http://msdn2.microsoft.com/en-us/library/aa179654(SQL.80).aspx
Regards
Radoslaw Lebkowski
U¿ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:7BC3B705-71DF-4B48-93FE-6959E94A51FF@.microsoft.com...
> Hi Rad,
> The very first step in trying to delete the key is giving me the same
> exception which is
> ReportServicesConfigUI.WMIProvider.WMIProviderException:
> The encrypted value
> for the "LogonCred" configuration setting cannot be decrypted.
> rskeymgmt -d -i instance
> This is completely new installation in 2steps.
> 1. Installed sql server 2005 with all other services except reporting
> services as iis was not installed at that time.
> 2. Installed reporting services.
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> Are you installing a new version of Reporting Services (with clear
>> ReportServer database) or just reinstalling it with using previous
>> version
>> of ReportServer database'
>> It looks like that you've reinstalled Reporting Services without
>> recovering
>> previous encryption key.
>> If you don't have any copy of your previous encryption key, the only way
>> to
>> make Reporting Services content available is to delete all unusable
>> encrypted data from ReportServer database.
>> Follow these steps to apply the encryption key to the report server
>> database:
>> 1.. Run rskeymgmt.exe locally on the computer that hosts the report
>> server. You must use the -d apply argument. The following example
>> illustrates the argument you must specify:
>> rskeymgmt -d
>> 2.. Restart Internet Information Service (IIS).
>> After the values are removed, you must re-specify the values as follows:
>> 1.. Run rsconfig utility to specify a report server connection. This
>> step
>> replaces the report server connection information. For more information,
>> see
>> Configuring a Report Server Connection and rsconfig Utility.
>> 2.. If you are supporting unattended report execution for reports that
>> do
>> not use credentials, run rsconfig to specify the account used for this
>> purpose. For more information, see Configuring an Account for Unattended
>> Report Processing.
>> 3.. For each report and shared data source that uses stored
>> credentials,
>> you must retype the user name and password. For more information, see
>> Specifying Credential and Connection Information.
>> 4.. Open and resave each subscription. Subscriptions retain residual
>> information about the encrypted credentials deleted during the rskeymgmt
>> delete operation. You can update the subscription by opening and saving
>> it.
>> You do not need to modify or recreate it.
>> I hope this infomation will helpful.
>> Best Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "RouteC" <RouteC@.discussions.microsoft.com> napisa3 w
>> wiadomo?ci
>> news:D246447F-1622-4230-AC73-71D0F1073C11@.microsoft.com...
>> >I am getting the following error while configuring the reporting
>> >services
>> >on
>> > my local machine. I cannot restore the key as I do not know the file
>> > or
>> > the
>> > password.
>> > Somebody please help!
>> >
>> > ReportServicesConfigUI.WMIProvider.WMIProviderException: The encrypted
>> > value
>> > for the "LogonCred" configuration setting cannot be decrypted.
>> > (rsFailedToDecryptConfigInformation)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
>> > mo)
>> > at
>> > ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()
>>

Encryption Choices / Best Practices for hosted environment (shared server)

I'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.

The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.

My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure? Does one offer a significant performance (dis)advantage?

TIA

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

The encryption / decryption operations with a symmetric key are much faster then the same operations with an asymmetric key or cert.

I'd use AES_128 | AES_192 | AES_256 for the algorithm if the hosted OS supported it.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steven Gott - MS wrote:

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

Steve, thanks for helping.

I think i need to clarify my question. On my development machine at home I am currently doing what you reccomend. Ecrypting the symmetric key with a certificate and using the symmetric key to encrypt column data.

BUT, to deploy my database on a shared server (hosted machine) the DBA at the host has told me I am not allowed to do this. I am only allowed to ecnrypt columns directly with a certificate or a symmetric key. (I need to retrieve the column data so I can't do asymmetric)

Is there a security benefit to using a cert over a symmetric key? (I am assuming i can directly encrypt data with just a cert.)

Basically what are the pro's and cons of encrypting data directly with only a cert and only a symmetric key.

TIA,

josh
|||

The performance of encrypting by certificates will be very bad for large amounts of data. Certificates are better for signing things than encrypting them.

You can also look at Raul's blog for insight into cryptography in SQL Server here is an entry involving indexes and encrypted columns http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

I'd encrypt with a symmetric key.

HTH,

-Steven

SDE/T

SQL Server

|||

Does your dba has any rationale for not letting you use a symmetric key encrypted with a certificate? That is a best practice for encryption, if there ever was one.

You can also encrypt the symmetric key with a password, instead of using a certificate, but then you'll have to pass that password around, whenever you'll need to open the key.

Certificates are much slower at encryption than symmetric keys and they have some additional limitations on how large a piece of data they can encrypt, hence it's not recommended to use them for encrypting data directly. I second Steven's suggestion to look at Raul's blog for additional details on this.

Thanks

Laurentiu

Encryption Choices / Best Practices for hosted environment (shared server)

I'm building a hosted website and I am using SQL 2005.
The DBA for the host has told me that i can not encrypt a symmetric key with a certificate, when using that symmetric key for encryption. As i read that this method provided optimum performance/ security for encrypting columns of data.

The DBA told me i can use a cert or a symmetric key for encryption.
I have searched for comparisons and found a blog entry by Laurentiu Cristofor comparing certs with asymmetric keys. Which leads me to believe that certs and asymm are very different than symmetric keys.

My question is which is the best choice in a hosted environment for column encryption, a cert or symmetric key.
Which is more secure? Does one offer a significant performance (dis)advantage?

TIA

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

The encryption / decryption operations with a symmetric key are much faster then the same operations with an asymmetric key or cert.

I'd use AES_128 | AES_192 | AES_256 for the algorithm if the hosted OS supported it.

HTH,

-Steven Gott

SDE/T

SQL Server

|||

Steven Gott - MS wrote:

I'd encrypt the column data with a symmetric key and protect the symmetric key with an asymmetric key or a cert.

Steve, thanks for helping.

I think i need to clarify my question. On my development machine at home I am currently doing what you reccomend. Ecrypting the symmetric key with a certificate and using the symmetric key to encrypt column data.

BUT, to deploy my database on a shared server (hosted machine) the DBA at the host has told me I am not allowed to do this. I am only allowed to ecnrypt columns directly with a certificate or a symmetric key. (I need to retrieve the column data so I can't do asymmetric)

Is there a security benefit to using a cert over a symmetric key? (I am assuming i can directly encrypt data with just a cert.)

Basically what are the pro's and cons of encrypting data directly with only a cert and only a symmetric key.

TIA,

josh
|||

The performance of encrypting by certificates will be very bad for large amounts of data. Certificates are better for signing things than encrypting them.

You can also look at Raul's blog for insight into cryptography in SQL Server here is an entry involving indexes and encrypted columns http://blogs.msdn.com/raulga/archive/2006/03/11/549754.aspx

I'd encrypt with a symmetric key.

HTH,

-Steven

SDE/T

SQL Server

|||

Does your dba has any rationale for not letting you use a symmetric key encrypted with a certificate? That is a best practice for encryption, if there ever was one.

You can also encrypt the symmetric key with a password, instead of using a certificate, but then you'll have to pass that password around, whenever you'll need to open the key.

Certificates are much slower at encryption than symmetric keys and they have some additional limitations on how large a piece of data they can encrypt, hence it's not recommended to use them for encrypting data directly. I second Steven's suggestion to look at Raul's blog for additional details on this.

Thanks

Laurentiu

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Hi

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Hi

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

sql

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Hi

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and recovery on standby machine

Hi there,

to follow on my question in the other forum, I have a database with triple des encrypted columns

Master key created, certificate and symmetric key using certificate. Values inserted into table (encrypted)

Can select etc, all works fine.

Back up and restore database on another server, decrypting the data results in NULLs being returned. Backed up and restore the master key, cerficate, etc but either it completes successfully or SQL states that the old and the new keys are the same and wont be changed. Master key opens (using password), cert opens (is viewable in sys.openkeys) but then closes automatically after trying to decrypt. Suggests there is something wrong with the certificate or the key, but I have no idea what it is.

Can someone please tell me exactly what is needed to view this on the 2ndary box? And what I might be missing?

Thanks

When you restore the database on the other server, you will need to add the service master key encryption to the database master key. You don't need to individually backup and restore anyhting else (don't worry about individually restoring the master key and the certificate). See this post for additional information on the master keys: http://blogs.msdn.com/lcris/archive/2005/09/30/475822.aspx.

If you opened the master key using a password, things should also work with no change.

But the certificate cannot be "opened" and it never shows in sys.openkeys. What you need to open is the symmetric key and this should be done following the same steps that you did in the original database.

Please send me more details on the steps that you are following, and I'll be able to help you more specifically. Also, please copy-paste the text and number of any error that you encounter.

Thanks
Laurentiu

|||

Hi,

Thanks Laurentiu.

I added the SMK encrypted to the database masterkey, and I was able to succesfully view decrypted information on the secondary node.

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Tomorrow I will run through the procedure again using new databases, and post all the scripts I used for the encryption and restoration on the second server (it wont be pages full, merely one line per activity and someone else might get benefit from it as well)

|||

This ALTER MASTER KEY statement is the only thing you'll need to issue to complete the restoration of the database. Everything else should already be recovered with the database itself. Let us know if you hit any issues.

Thanks
Laurentiu

|||

Hi,

Ok source database -

create master key with password

create certificate

create symmetric key with encryption by certificate created above

open key

insert values into table (using encryptbykey)

create view that that uses decryptbykey, all works fine.

Backup database

Restore on 2nd machine

Open the master key

Alter master key add encryption by service master key

After this I open the symmetric key and try to decrypt but everything gets returned as NULLs.

Will run through this again and see if I missed anything.

|||

The steps you followed seem correct. Let us know if you've also been unable to decrypt the second time.

One simple suggestion: verify that the symmetric key is indeed opened (select * from sys.openkeys).

Thanks
Laurentiu

|||

Hi,

Tried it just now and the values decrypted fine. Havent touched the server since the post was made, and did not run any other scripts on it. Weird.,,

Repeated the restore again with another database, same steps, same result, NULLs when decrypting. The key is open and in sys.openkeys (cant open the key without opening the master key first), try to use the decryptbykey and the are no values returned (key remains open). Restarted SQL, key opens fine but still NULLs get returned but now the key gets closed automatically after the first decryptbykey attempt.

Will leave the server "as is" for now and check it again later to see if it's successfull then.

This made me think of some extra security questions, will post in a seperate thread :)

|||

You should be able to open the symmetric key without opening the master key if you added the service master key encryption to it.

The behavior that you describe is very strange indeed. Could you post the T-SQL that you used to open the key and to decrypt? I will try to repro this behavior, but because of holidays, I may not be able to do this very soon.

Thanks
Laurentiu

|||

This morning, check it out and the restored database is decrypting information... again no changes done, server just left overnight.

This is what I used to check the information/decrypt - you'll recognise it from your blog as I was using snippets of your code as part of this :)

If you need any other info, please let me know.

open symmetric key sk_player decryption by certificate cert_sk_admin;

select * from sys.openkeys

select id,name, CONVERT(varchar(10), decryptbykey(salary, 1, CONVERT(varchar(30), id))) AS salary from t_employees

select * from v_employees

select * from sys.openkeys

This was done before and only once, as it couldnt open the symmetric key without the master key being open first.

open master key decryption by password = 'xxxkickasspasswordxxxx';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

|||Both your steps and the SQL look right. Is this issue still occurring? What version are you using? (SELECT @.@.version).|||

Hi

Problem still occuring.

Version is (on the standby box) - SQL2005 Dev Edition on Windows Server 2000

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

On the main box it is SQL2005 Dev Edition on Windows 2003 Enterprise SP1

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

I got the Enterprise Editions of SQL2005 (which will go on the live systems) from MSDN and will try that as well, maybe it's something with the dev edition?

Thanks

|||

There shouldn't be any difference between Developer and Enterprise regarding encryption. Could you also specify how you created the certificate and the symmetric key?

Thanks
Laurentiu

|||

Hi there,

I create the cert and key using this (using Triple Des since the standby machine is Windows 2000 and doesnt do AES)

create certificate cert_sk_td3 with subject = 'Certificate'

create symmetric key sk_td3 with algorithm = triple_des encryption by certificate cert_sk_td3;

Let me know if you need any more info

|||

We have tried the scenario that you describe, moving a database between a Windows 2000 and a Windows 2003 machine, and we were successful in decrypting data after restores. We were not able to reproduce the problem that you described.

You mentioned that data becomes available after a while. How much time did it took before you could decrypt? Could you post the entire script that you are executing for encrypting the data and then decrypting it after restoring the database?

I'd also suggest to try to encrypt and decrypt some value after the restore to verify if the behavior that you are noticing is also happening for data encrypted after the restore. If the encryption would fail as well, then that would indicate an issue with the key.

In your scenario, which is the machine on which the decryption fails after restore? Is it the 2000 machine or the 2003 one? (I'm not sure what you mean by the standby machine)

Thanks
Laurentiu

|||

Hi Laurentiu,

In the interim I have upgradede both SQL2005s from Dev Edition to Enterprise Edition (from the MSDN Subscription downloads)

What usually happens with the data is that is becomes available the next morning when I am back at the office, after playing with it in the afternoon and not getting any results. I have tried things like restart SQL and reboot the server to see if that would speed up the recovery, but it didnt help anything. Nothing happens in the server during that time, I just get in, open the symm key and it decrypts.

I am going from a Server2003 (SP1 and hotfixes) to a Server2000(SP4 and all hotfixes). It is on the 2000 Server that I cant decrypt (I am using TripleDES to test since AES isnt supported on 2000).

I retried this after the upgrade from Dev to Enterprise, but I still had the problem occuring. I am able to open the key (appears in sys.openkeys) but inserting any values into the 2000 database just insert NULLs into the encrypted column. So the key doesnt seem happy to do encryption or decryption.

I then backed up the database on the 2000 server, and restored it again on the 2003 server (where it came from originally) under a new name. Again I opened the master key and did the service master key change, opened the symmetric key and was able to view the information. So yeah, it seems everything is fine on the database side, just something on the 2000 set up is causing some issues.

This is a bit of a mystery, I have left the machines and will check it tomorrow morning to see if the 2000 machine magically starts to decrypt again. There arent any jobs or policies on them and they are purely standalone.

Is there any trace or debugging that I can enable that might give some more info?

Encryption and Decryption by Key

I am trying to use the encryptionbykey and decryptionbykey from the database to return a key that is used in a web application as a querystring. I can encrpyt fine. But when the value is passed back to a function as as varchar and convert it to a varbinary it does not decrypt. Below is an example:

CREATE SYMMETRIC KEY DummyKey

WITH ALGORITHM = DESX

ENCRYPTION BY PASSWORD = N'd7mmy';

GO

DECLARE @.UnsubscribeURL varchar(300)

DECLARE @.OriginalUserKey varchar(250)

DECLARE @.EncryptedUserKey varchar(250)

-- Get Unsubscribe URL

SELECT @.UnsubscribeURL = 'http://hrowdn01.paychex.com/secure/hronlineApplication/unsubscribe.aspx?UserKey='

-- Create the user key (Company_Id + '_' + Employee_Id)

SELECT @.OriginalUserKey = CONVERT(varchar, 161) + '_' + CONVERT(varchar, 3381)

-- Open Encryption key

OPEN SYMMETRIC KEY DummyKey

DECRYPTION BY PASSWORD = N'd7mmy'

-- Encrypt the user key

SELECT @.EncryptedUserKey = master.dbo.fn_varbintohexstr(EncryptByKey(Key_GUID('DummyKey'),@.OriginalUserKey))

--EXEC master..xp_blowfishencrypt @.OriginalUserKey, @.key, @.EncryptedUserKey OUTPUT

CLOSE SYMMETRIC KEY DummyKey

-- Finish creating the unsubscribe URL

SET @.UnsubscribeURL = @.UnsubscribeURL + @.EncryptedUserKey

SELECT @.UnsubscribeURL

--New function would be here

DECLARE @.decrypted_str VARBINARY(MAX)

DECLARE @.DecryptedUserKey varchar(MAX)

OPEN SYMMETRIC KEY DummyKey

DECRYPTION BY PASSWORD = N'd7mmy'

SET @.decrypted_str = CONVERT(varbinary(max),'0x002da862c3f37f449936e9a3eabc83340100000007f5728f4a1ff60d6a08a3ee30dd7d8626551f0da25d14719f4e81a00147a2d9')

SET @.DecryptedUserKey = DecryptByKey(@.decrypted_str)

-- display decrypted text

SELECT @.DecryptedUserKey AS PlainText;

-- close and drop the key

CLOSE SYMMETRIC KEY DummyKey

DROP SYMMETRIC KEY DummyKey

Thanks,

J

Jason,

I've been playing around with this a little bit.

I don't have an answer.

But from what i can tell, you're going to need a function that reverses the process of

master.dbo.fn_varbintohexstr().

It appears that just recasting that output to varbinary isn't doing what you need.

sql

Encryption & Decryption

Database Security, we are going to use AES 256 Symmetric Encryption. We will be using RSA for Asymmetric Key Encryption, 1024 Bits.

We got the code working for the seond case but for the first, WHEN:

CREATE SYMMETRIC KEY sym_Key WITH ALGORITHM =
AES_256 ENCRYPTION BY ASYMMETRIC KEY asym_Key
GO

THEN:

-- Msg 15314, Level 16, State 1, Line 1
-- Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

What can be the way out to be able to create the AES 256 Symmetric key.

AES is currently supported by SQL Server 2005 only on Windows 2003. If you are using Windows 2000 or Windows XP, you do not have access to AES encryption from SQL Server.

Thanks
Laurentiu

sql

Encryption - Protect from attacks by programmers?

I can encrypt columns in sql 2005 but where do I store the key to decrypt the columns?

I can store the key in the database (or server on which the database resides) but I think that offers little security. I could store the key on another server that the sql server accesses only upon startup (though I don't know exactly how to do that). Or I could store the key on a removable drive that is read (and only needed) when the sql server starts up.

What are your ideas on this matter?

TIA,

barkingdog

Have a look at the encryption hierarchy in SQL Server: http://msdn2.microsoft.com/en-US/library/ms189586.aspx. Also check the other resources mentioned here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286374&SiteID=1.

If you store the key in the database, it is stored encrypted. I am not sure why would you think that this scheme offers "little security", can you elaborate on that statement? Before discussing a protection scheme, it would be helpful to state what you are trying to protect against - against what attacks do you want to protect the key?

Thanks
Laurentiu

|||

Laurentiu,

From what you said about storing the key in the database I obviously have a misconception here. But how then, is one supposed to access the encrypted key to de-crypt the data for later display in the UI? Is there a "proxy" stand-in for the real key once it is encrypted?

My concern is to prevent outsiders from, if they somehow gained access to the database (say a stolen or lost backup tape), from being able to decode sensitive fields such as Social Security number. At the same time, when a SSN is entered via the UI the application needs the key to drive the encryption of sensitive fields.

Barkingdog

|||

Basically, there are two ways to encrypt keys (hence two ways to decrypt them): one is to eventually use a password, so the password needs to be specified when the key needs to be used; the second protection is based on DPAPI, so no password needs to be specified. DPAPI basically uses the credentials of the machine and of the service account to protect the key, so to break it, one would have to know those credentials.

For details on DPAPI, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/seccrypto/security/cryptprotectdata.asp. How DPAPI ties in to the key protection scheme is shown in the key hierarchy diagram from the first link in my previous message - you have a chain of encryptions rooted at the DPAPI encryption of the service master key.

For additional information and examples, see the blogs I referred to you earlier. The example from http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx has the symmetric keys protected so that no password is required for their use (access control for keys is done through permissions). The post from http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx goes over additional considerations related to the use of encryption keys. For a discussion of the protection conferred by encryption, see http://blogs.msdn.com/lcris/archive/2005/12/20/506187.aspx.

For the following explanation, I will assume that you have examined the key hierarchy diagram and that you understand the encryption chain. I will also use SMK and DbMK as shortcuts for the service master key and, respectively, database master key.

In the case of a stolen backup tape that contained a database with encrypted data, the thief cannot decrypt the data without knowledge of the password that protects the DbMK. So the data is secure because removing a database from the server will cut the database from the encryption chain that allows its data to be decrypted.

If you're worried about a stolen laptop scenario, this threat is a little different from the previous one because the thief might be able to figure out how to gain full control of the laptop so that he can connect to the server as usual and decrypt the data. For this scenario you would want to protect the encryption keys by password, and then have the password specified when you're accessing the data (think of it as an additional login operation that grants access to the encrypted data). This would protect against a lost laptop scenario because even with full access to the laptop, knowledge of the password is still required.

Thanks
Laurentiu

|||

Hi,

I have a different type of problem. Lets say I have created a Symetric key (without using a password) and i authorize that key to a user called ASP_NET_My_Appln.

This user is used by my UI(ASP.NET) for querying the DB.

All the programmers who are coding the project WILL know the key name that we are using for encrypting / decrypting the data.

Therefore any programmer who can log into the production server or for that matter the local environment (where we place production server dumps to get the latest data) can decrypt the data by passing a simple SQL like this:

select EncryptXXX(Key,Column) from table

To protect the same I had to resort to use a password phrase. But however there is a problem there too. All the SQLs that we use are stored in SPs. Therefore a sample SP would be:

Sp_GetData @.pwd varchar(10)
AS
OPEN SYMMETRIC...... Password=@.pwd
select EncryptXXX(Key,Column) from table
Close Symmetric...

Anybody who is running the profiler can now read the password as the profiler does not block the same.

What is the best way to overcome this?

Therefore categorising the problems:
1. As far as I see the decyprtion seems to be a very simple select statement. Therefore anyone who has access to the server and knows the correct Key name and table name etc can do the same (Which a programmer WILL know).
2. Profiler is capable of blocking the actual select statements that use encryption commands but NOT the SP that takes the password. How can I overcome that? Should I change my design? Once again the password cannot be hardcoded into an SP as any developer can open it and look into it.

Kindly correct me if I have misqouted anything.

|||

1. To decrypt, you need to have previously opened the key. The access checks on the key and the knowledge of the passwords used to access the key come into place at this time, and it is these checks that restrict the access and use of an encryption key.

2. You're right about not wanting to hardcode the password in a SP. You should treat the key password as a login password and issue a direct OPEN SYMMETRIC KEY statement whenever you want to use the key - the password passed to OPEN will not be traced.

However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Thanks
Laurentiu

|||>>However, I am not sure I understand your scenario very well. Why are your programmers manipulating sensitive data while developing the application? What kind of access to the database and to the sensitive data do they need?

Its like an internal application that deals with the data for the entire organisation (really sensitive data of employees).

So the programmers themselves MIGHT be hackers...

Since I have to give access to my UI user, anybody who gets hold on the connection string can open a connection to the DB and remove the data by a very simple select stmt. So to protect this I wanted to have passwords for the key. But now i am stuck as to how to protect the password from hackers...|||

As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?

Thanks
Laurentiu

|||Hi,

When you mean "As long as the password is not hardcoded in the application, for the programmers to see, they should not be able to get it from just examining the code. What are your concerns if the user is specifying the password to the application?"

Are you talking about the UI? If yes then the problem arises when I have to pass it to the DB (to an SP in the DB).

Where and how exactly do you want me to store the password that protects the key?|||

I am suggesting to have the user specify the password. I am not suggesting for the password to be stored somewhere where it can be programmatically retrieved, given that you are trying to prevent the developers of your application from accessing it. Also, I am not suggesting for the password to be passed around as an argument to stored procedures (which would make it visible in a trace) - it should just be passed to the OPEN SYMMETRIC KEY statement.

Thanks
Laurentiu

|||>>it should just be passed to the OPEN SYMMETRIC KEY statement

Exactly, but my open symmetric statement is inside an SP. There can be more than 100 SPs that have to access this password. In this case there are 2 options for me:

1. Hardcode the password in each SP.
2. Pass it as a parameter to the SP.

I choose the second one therefore the problem.|||

Why do you open the key inside the SP? Why can't you open it as part of the logon process for your application and keep it open for as long as you work with the encrypted data. Once you open a key, it is only available within the current session, so you don't have to worry about other users getting to it. Also, you don't need to open it and close it for each access to encrypted data. You can open it once, use it for many encryptions and decryptions (which can happen in stored procedures that you call - they will have access to the opened key), and then close it when you are done (or you can just disconnect your session and it will be destroyed).

Thanks
Laurentiu

|||Hi,

Before I implement your idea i would like to know more about the defenition of a current session.

I am using EntLib, therefore each call to an SP opens / closes a connection from the pool. How is the session defined in this case?

One more thing, to hide the data from the profiler i will have to pass the OPEN stmt as a direct SQL rather than using an SP right?|||

Hi,

You can use CLR function to open symetric key , The script will be in assembly

and the password is hidden at all.

Thanks,

Tarek Ghazali

SQL Server MVP

web site : www.sqlmvp.com

|||

Hi,

I am totally new to this. Could you possibly giude me to some tutorials on the same?

Encryption - importing keys

Is it possible to load data into MSSQL 2005 that has been encrypted externally with a symmetric key algorithm, such as AES, and then import the key to SQL Server? After browsing through Books Online, I don't see any way to import a symmetric key from an external file, but maybe I'm missing something.

No, at this time, it is not possible to do that. You would have to decrypt the data and then encrypt it again using SQL Server's encryption functions.

Thanks
Laurentiu

|||

If your primary concern is protecting data in transit, you should be able to use SSL to accomplish this:

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

Sung

Encryption

I have tried to encrypt by certificate and by symmetric key. In all cases the decryption comes back as null. Any ideas why?

I have used the code from a learnin tree course and the encryption works OK. I have also added a grant to the certificate to the login

Please post the code you used. Without it we would have to guess every possible problem.

Encrypting with certificate and symmetric key

I posted the following question in the programming section on 3/19 and did
not get any responses. Can anyone here help me out?
I can avoid opening a symmetric key when I decrypt data by using the new
function "decryptbykeyautocert."
But there does not seem to be anything compareable for encrypting.
So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
close the key.
Is this correct? Why isn't there a comparable function for encrypt? What
is the danger of inadvertantly leaving the key open? Will it close on
rollback?
Listed below is some code that provides and example of the issue:
USE master
--DROP DATABASE test
CREATE DATABASE test
USE test
IF object_ID('CreditCards') IS NOT NULL
DROP TABLE creditCards
GO
create table CreditCards (
Id int IDENTITY,
ccno varchar(20),
ccnoe varbinary(2000)
)
GO
INSERT CreditCards (ccno) VALUES ('1234567890')
GO
SELECT * FROM creditcards
GO
--Keys
--create database master key
CREATE master key
ENCRYPTION BY password = 'TestKey(123)'
--create the certificates that protects the data encryption keys
CREATE certificate CCE_Cert
authorization dbo with subject = 'CCE_Cert '
-- View certificates in database
select * from sys.certificates
-- Create symmetric key
CREATE symmetric key CCE_Key
with algorithm = AES_256
ENCRYPTION BY certificate CCE_Cert
select * from sys.symmetric_keys
open symmetric key CCE_Key
decryption by certificate CCE_Cert
--Encryption
--encrypt dat with key
UPDATE creditcards
SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
SELECT * FROM creditcards
--confirm key is open
select * from sys.openkeys
--view as raw data
SELECT * FROM creditcards
--Idccnoccnoe
--112345678900x00506334876D334AB3EA195508AC73E601000000BE92F21E 05800531482DE328AB76E15576D029C289F09F577F09BDEA1F 2A027C
--view as decrypted
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccnoccnoe
--12345678901234567890
close all symmetric keys
--view after key is closed
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccnoccnoe
--1234567890NULL
--use decryptbykeyautocert to avoid opening key
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now encrypt
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
--but this does not work, it is encrypted as NULL
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--open the key then INSERT
open symmetric key CCE_Key
decryption by certificate CCE_Cert
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
ccnoe)) as ccnoe
FROM creditcards
--now it works
--but there is no encryptbykeyautocert, only decrypt
Hi Dave
See inline:
"Dave" wrote:

> I posted the following question in the programming section on 3/19 and did
> not get any responses. Can anyone here help me out?
> --
> I can avoid opening a symmetric key when I decrypt data by using the new
> function "decryptbykeyautocert."
> But there does not seem to be anything compareable for encrypting.
> So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
> an encryption I will need to 1. open the key, 2. perform the mod, and then 3.
> close the key.
> Is this correct? Why isn't there a comparable function for encrypt? What
> is the danger of inadvertantly leaving the key open? Will it close on
> rollback?
> Listed below is some code that provides and example of the issue:
> USE master
> --DROP DATABASE test
> CREATE DATABASE test
> USE test
> IF object_ID('CreditCards') IS NOT NULL
> DROP TABLE creditCards
> GO
> create table CreditCards (
> Id int IDENTITY,
> ccno varchar(20),
> ccnoe varbinary(2000)
> )
> GO
> INSERT CreditCards (ccno) VALUES ('1234567890')
> GO
> SELECT * FROM creditcards
> GO
> --
> --Keys
> --create database master key
> CREATE master key
> ENCRYPTION BY password = 'TestKey(123)'
> --create the certificates that protects the data encryption keys
> CREATE certificate CCE_Cert
> authorization dbo with subject = 'CCE_Cert '
> -- View certificates in database
> select * from sys.certificates
> -- Create symmetric key
> CREATE symmetric key CCE_Key
> with algorithm = AES_256
> ENCRYPTION BY certificate CCE_Cert
> select * from sys.symmetric_keys
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> --
> --Encryption
> --encrypt dat with key
> UPDATE creditcards
> SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
> SELECT * FROM creditcards
> --confirm key is open
> select * from sys.openkeys
> --view as raw data
> SELECT * FROM creditcards
> --Idccnoccnoe
> --112345678900x00506334876D334AB3EA195508AC73E601000000BE92F21E 05800531482DE328AB76E15576D029C289F09F577F09BDEA1F 2A027C
> --view as decrypted
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccnoccnoe
> --12345678901234567890
> close all symmetric keys
> --view after key is closed
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccnoccnoe
> --1234567890NULL
>
> --use decryptbykeyautocert to avoid opening key
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now encrypt
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
------1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A0100000018FAD0F3 E302CA5591F1F4B9AEE8CAE969D538149C1C774DF278DE987A 7990EDC50917BB2E98106C4D8C357C1C2D2FD2
2 1234567890 NULL
(2 row(s) affected)
i.e. the underlying value is NULL and the encryption has not worked.
Therefore decrypting a NULL value does not make sense!

> --but this does not work, it is encrypted as NULL
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --open the key then INSERT
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'1234567890'))
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert'), NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now it works
That is because the key has been re-opened!

> --but there is no encryptbykeyautocert, only decrypt
>
I am not sure why there is not one, but I can see that if you have a one
key/one certificate mapping then this may be something hat would be useful,
but if you have (say) 10 keys encrypted by the one certificate do you encrypt
with all the keys, the first key or one key at random? The first option would
be very expensive, the second option would not have great value and would
potentially be very dangerous if it was used without understanding what was
happening, and the third option depends on the meaning and implementation of
random! I would probably not advise the use of decryptbykeyautocert either if
you want quicker decryptions!
You may want to put in a request at
https://connect.microsoft.com/SQLServer/Feedback
John
sql

Encrypting with certificate and symmetric key

I posted the following question in the programming section on 3/19 and did
not get any responses. Can anyone here help me out?
--
I can avoid opening a symmetric key when I decrypt data by using the new
function "decryptbykeyautocert."
But there does not seem to be anything compareable for encrypting.
So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
an encryption I will need to 1. open the key, 2. perform the mod, and then 3
.
close the key.
Is this correct? Why isn't there a comparable function for encrypt? What
is the danger of inadvertantly leaving the key open? Will it close on
rollback?
Listed below is some code that provides and example of the issue:
USE master
--DROP DATABASE test
CREATE DATABASE test
USE test
IF object_ID('CreditCards') IS NOT NULL
DROP TABLE creditCards
GO
create table CreditCards (
Id int IDENTITY,
ccno varchar(20),
ccnoe varbinary(2000)
)
GO
INSERT CreditCards (ccno) VALUES ('1234567890')
GO
SELECT * FROM creditcards
GO
--Keys
--create database master key
CREATE master key
ENCRYPTION BY password = 'TestKey(123)'
--create the certificates that protects the data encryption keys
CREATE certificate CCE_Cert
authorization dbo with subject = 'CCE_Cert '
-- View certificates in database
select * from sys.certificates
-- Create symmetric key
CREATE symmetric key CCE_Key
with algorithm = AES_256
ENCRYPTION BY certificate CCE_Cert
select * from sys.symmetric_keys
open symmetric key CCE_Key
decryption by certificate CCE_Cert
--Encryption
--encrypt dat with key
UPDATE creditcards
SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
SELECT * FROM creditcards
--confirm key is open
select * from sys.openkeys
--view as raw data
SELECT * FROM creditcards
--Id ccno ccnoe
-- 1 1234567890 0x00506334876D334AB3EA19550
8AC73E601000000BE92F21E05800531482
DE328AB76E15576D029C289F09F577F09BDEA1F2
A027C
--view as decrypted
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 1234567890
close all symmetric keys
--view after key is closed
SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
FROM creditcards
--ccno ccnoe
--1234567890 NULL
--use decryptbykeyautocert to avoid opening key
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
ccnoe)) as ccnoe
FROM creditcards
--now encrypt
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
--but this does not work, it is encrypted as NULL
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
ccnoe)) as ccnoe
FROM creditcards
--open the key then INSERT
open symmetric key CCE_Key
decryption by certificate CCE_Cert
INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
ccnoe)) as ccnoe
FROM creditcards
--now it works
--but there is no encryptbykeyautocert, only decryptHi Dave
See inline:
"Dave" wrote:

> I posted the following question in the programming section on 3/19 and did
> not get any responses. Can anyone here help me out?
> --
> I can avoid opening a symmetric key when I decrypt data by using the new
> function "decryptbykeyautocert."
> But there does not seem to be anything compareable for encrypting.
> So I guess that in each of my data mod procs (INSERT/UPDATE) that performs
> an encryption I will need to 1. open the key, 2. perform the mod, and then
3.
> close the key.
> Is this correct? Why isn't there a comparable function for encrypt? What
> is the danger of inadvertantly leaving the key open? Will it close on
> rollback?
> Listed below is some code that provides and example of the issue:
> USE master
> --DROP DATABASE test
> CREATE DATABASE test
> USE test
> IF object_ID('CreditCards') IS NOT NULL
> DROP TABLE creditCards
> GO
> create table CreditCards (
> Id int IDENTITY,
> ccno varchar(20),
> ccnoe varbinary(2000)
> )
> GO
> INSERT CreditCards (ccno) VALUES ('1234567890')
> GO
> SELECT * FROM creditcards
> GO
> --
> --Keys
> --create database master key
> CREATE master key
> ENCRYPTION BY password = 'TestKey(123)'
> --create the certificates that protects the data encryption keys
> CREATE certificate CCE_Cert
> authorization dbo with subject = 'CCE_Cert '
> -- View certificates in database
> select * from sys.certificates
> -- Create symmetric key
> CREATE symmetric key CCE_Key
> with algorithm = AES_256
> ENCRYPTION BY certificate CCE_Cert
> select * from sys.symmetric_keys
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> --
> --Encryption
> --encrypt dat with key
> UPDATE creditcards
> SET ccnoe=encryptByKey(Key_GUID('CCE_Key'), ccno)
> SELECT * FROM creditcards
> --confirm key is open
> select * from sys.openkeys
> --view as raw data
> SELECT * FROM creditcards
> --Id ccno ccnoe
> -- 1 1234567890 0x00506334876D334AB3EA19550
8AC73E601000000BE92F21E058005314
82DE328AB76E15576D029C289F09F577F09BDEA1
F2A027C
> --view as decrypted
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 1234567890
> close all symmetric keys
> --view after key is closed
> SELECT ccno, convert (varchar, decryptbykey(ccnoe)) as ccnoe
> FROM creditcards
> --ccno ccnoe
> --1234567890 NULL
>
> --use decryptbykeyautocert to avoid opening key
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now encrypt
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
----
----1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A010000
0018FAD0F3E302CA5591F1F4B9AEE8CAE969
D538149C1C774DF278DE987A7990EDC50917BB2E
98106C4D8C357C1C2D2FD2
2 1234567890 NULL
(2 row(s) affected)
i.e. the underlying value is NULL and the encryption has not worked.
Therefore decrypting a NULL value does not make sense!

> --but this does not work, it is encrypted as NULL
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --open the key then INSERT
> open symmetric key CCE_Key
> decryption by certificate CCE_Cert
> INSERT CreditCards (ccno, ccnoe) VALUES ('1234567890',
> encryptByKey(Key_GUID('CCE_Key'),'123456
7890'))
> SELECT convert (varchar, decryptbykeyautocert(cert_id('CCE_Cert')
, NULL,
> ccnoe)) as ccnoe
> FROM creditcards
> --now it works
That is because the key has been re-opened!

> --but there is no encryptbykeyautocert, only decrypt
>
I am not sure why there is not one, but I can see that if you have a one
key/one certificate mapping then this may be something hat would be useful,
but if you have (say) 10 keys encrypted by the one certificate do you encryp
t
with all the keys, the first key or one key at random? The first option woul
d
be very expensive, the second option would not have great value and would
potentially be very dangerous if it was used without understanding what was
happening, and the third option depends on the meaning and implementation of
random! I would probably not advise the use of decryptbykeyautocert either i
f
you want quicker decryptions!
You may want to put in a request at
https://connect.microsoft.com/SQLServer/Feedback
John