encryption with certificate

I am trying to create a encrypted row in my database
Everything here worked except that when i run the final query to decrypt the data
It just comes up with null for each row. Even if i do a query to show me the rows that are not null
It's like it is saying yeah there is data here but I am only going to show you null instead of what I am supposed to decrypt.
Here is what I tried from start to finish

Create Certificate TestCertEncryptionBy Password ='Password'With Subject ='SQLCert',Expiry_Date ='12/01/2050';declare @.Testnvarchar(50)set @.Test='123456789'insert into testenc (testencry)Values (encryptbyCert(Cert_ID('TestCert'),@.Test ))selectconvert (Nvarchar(50),DecryptByCert(Cert_ID('TestCert'),testencry,N'Password'))As Testfrom testenc

I am using sql 2005 by the way|||Nevermind Just realized I need to use VarBinary instead of NvarChar to store the data!

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 not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enable
d
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Encryption not enabled on Server

Trying to get5 SSL work between SQL server and Query Analyzer. I have enabled
encryption on the Query Analyzer client and installed a certificate on SQL
server,
When I connect from QA, i get a message saying "Encrytion not supported on
SQL server".
I enabled "Force Protocol Encryption" on Server and disable encryption on
client.
Now SQL server lwon't start and log says "Encryption requested but no valid
certificate was found. SQL Server terminating."
I used MMC to install certificate on SQL server, on Windows 2000 Pro.
What is the correct way to install a valid cert on SQL 2000 server?
Follow the steps here;
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have Active Directory then
316898 How to enable SSL encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

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 the client

I have a basic understanding of the encryption using T-SQL, but is all of this at the T-SQL level? Like can a client have a certificate, be sent the encrypted value (that was encrypted on SQL Server), and using the same cert that is on the server decrypt the data?

Is there a good article/book that covered encryption in detail that you can suggest? Or is this something that you would build in .NET?

If your concern is to protect data in transit, I would strongly recommend you to use SSL to protect the data between your server and the client.

If your goal is to protect data at rest, but in such a way that the protected data cannot be decrypted by the server (i.e. the decryption key is never stored/used in the server hosting SQL Server) you can use .Net to protect the data directly, but all the key management should be on your client application.

Typically, for normal data I don’t recommend using a model where you use public keys to protect data directly on SQL Server, and use the private key on the client to decrypt it (or vice versa).

The main reasons are that your application will still have to do most of the key management (private/public keys), the performance of asymmetric key encryption is orders of magnitude slower than symmetric key encryption and that in SQL Server the limit for encrypting data using an asymmetric key is 1 block (based on the private key modulus, for the self signed certificates SQL Server generates this means you can only encrypt up to ~117 bytes of plaintext).

After saying that, there may be some cases where this mechanism may actually work better for your particular needs. You can find one sample on how to use the .Net framework to encrypt data and decrypt it on SQL Server using asymmetric keys: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=384472&SiteID=1

I hope this information can help you.

-Raul Garcia

SDE/T

SQL Server Engine

|||

Actually my question was largely academic in nature, as I was trying to get my head straight on a few topics with SQL Server for a client. I had a few preconcieved notions about how things worked with encryption.

Thanks again (you have helped me before :)!

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

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

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 ccno
--1 1234567890 0x00506334876D334AB3EA195508AC73E601000000BE92F21E05800531482DE328AB76E15576D029C289F09F577F09BDEA1F2A027C
--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'),'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 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 0x00506334876D334AB3EA195508AC73E601000000BE92F21E05800531482DE328AB76E15576D029C289F09F577F09BDEA1F2A027C
> --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'),'1234567890'))
>
If you did a select * from CreditCards you would get
Id ccno ccnoe
-- --
------1
1234567890
0x00599BE28153D949881C25E2DFCDCB3A0100000018FAD0F3E302CA5591F1F4B9AEE8CAE969D538149C1C774DF278DE987A7990EDC50917BB2E98106C4D8C357C1C2D2FD2
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

Encrypting mdf files

Hi,

We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.

Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?

You can access 'service account's cert store through mmc -

Quote:

Originally Posted by

>Certificates Snap-in -Service account

Thanks,
rsm
---rsm (prakandapandit@.yahoo.com) writes:

Quote:

Originally Posted by

We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.
>
Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?

No.

If you are using SQL 2005, there are encryption routines builtin,
so that you encrypt some columns. Keep in mind that encrypting key
columns will have a very serious impact on performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 16 Feb, 04:16, "rsm" <prakandapan...@.yahoo.comwrote:

Quote:

Originally Posted by

Hi,
>
We want to encrypt MS Sql Server data files - .mdf and .ldf with
logged in user certificate and make sure that MS Sql Server service
(running as Local System Account) can decrypt it.
>
Is it possible to encrypt data files with a certificate that resides
in logged in user's
cert store and also MS SQL Server Service 'service account's cert
store?
>

No. Assuming you are using SQL Server 2005 you should read the
encryption topics in Books Online.

It is in principle possible to encrypt every bit of user data in a
database, but I can't think of any good reasons for wanting to do that
- and there are many good reasons why NOT to do it. Could you explain
a bit more about your requirements.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||We are using SQL Server 2000.

We are trying to provide an encryption solution for SQL Server
database. ldf and mdf files are some thing we want to encrypt.

Problem is that if we encrypt using user cert, we need to run SQL
Server service as that user which works fine until user changes his
password. In this case, we have to some how automatically change SQL
Server service 'run as' user password. I was wondering if there is a
way to install user cert as service cert so SQL Server can decrypt the
ldf files on its own.|||"rsm" <prakandapandit@.yahoo.comwrote in message
news:1172172849.993451.142190@.t69g2000cwt.googlegr oups.com...

Quote:

Originally Posted by

We are using SQL Server 2000.
>
We are trying to provide an encryption solution for SQL Server
database. ldf and mdf files are some thing we want to encrypt.
>
Problem is that if we encrypt using user cert, we need to run SQL
Server service as that user which works fine until user changes his
password. In this case, we have to some how automatically change SQL
Server service 'run as' user password. I was wondering if there is a
way to install user cert as service cert so SQL Server can decrypt the
ldf files on its own.
>

There is no built-in encryption in SQL 2000, so I'm 99% sure the answer is
no.

Simple answer; the user SQL Server runs under shouldn't be changing its
password often and when it does, should go through a normal change
procedure.

--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Encrypt Connection with Certificate

I have been trying to create a certificate for use with SQL2005. I found openSSL to create a cert but I am not sure how to use it.

When I go into SQL Config Manager / Protocol Properties / Certificate Tab... I do not see any certificates. The list is empty. Where are these certs pulled from and how can I create one on my own?

Here are the Reqs:

Certificate Requirements

For SQL Server 2005 to load a SSL certificate, the certificate must meet the following conditions:

The certificate must be in either the local computer certificate store or the current user certificate store.

The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.

The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).

The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE. Usually, the certificate's key usage property (KEY_USAGE) will also include key encipherment (CERT_KEY_ENCIPHERMENT_KEY_USAGE).

The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.http://msdn2.microsoft.com/en-us/library/ms187798.aspx|||Is this the cert that SQL is looking for? I thought it was a local computer cert generated through CA.|||Data Source=server.domain.com;Initial Catalog=master;User ID=SQLuser;Encrypt=True;TrustServerCertificate=Tru e

this works but I want to create my own cert.

Enabling SSL and FQDN

To enable SSL on my SQL server, I need a certificate with the same name as
the FQDN of the SQL server computer name. Does this FQDN need to be the
primary DNS name used for this server or can it be one of the aliases?
ThanksYou can use ping <servername> -a or run ipconfig /all on the
server to get the FQDN.
You may want to refer to the following for steps on enabling
SSL:
HOW TO: Enable SSL Encryption for SQL Server 2000 with
Certificate Server
http://support.microsoft.com/?id=276553
-Sue
On Fri, 18 Jun 2004 12:40:14 -0400, "Chuck"
<no.address@.no.where> wrote:

>To enable SSL on my SQL server, I need a certificate with the same name as
>the FQDN of the SQL server computer name. Does this FQDN need to be the
>primary DNS name used for this server or can it be one of the aliases?
>Thanks
>

Enable SSL

I have read the MSDN articles about setting up SSL on the SQL server. I am
running Microsoft Certificate services on my network as well. The article t
alks about the following
Enter the fully qualified domain name of your computer in the Name: text box
. Ping your computer to get the fully qualified domain name if you are not s
ure what it is.
In the Intended Purpose section, change the selection to Server Authenticati
on Certificate by using the drop-down list box from the Client Authenticatio
n Certificate.
I cannot seem to find these options anywhere...How do I change the FQDN if I
have no name field...I don't even have an intended purpose dropdown list.
I can issue a computer certificate from the MMC on the SQL Server but it doe
s not use the FQDN of the c
luster I am trying to setup. How can I get this to work if I cannot see the
options that these articles are telling me? I am logging in with Domain Ad
min rights as well.
Ignore my last post...I forgot my email address...That article is if you make a http request to a Standalone Certificate
Cerver.
If you have a standalone CA you have to use http request to request the
cert. Follow this article:
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you installed the Enterprise CA as part of Active Directory, then you
can use MMC. Follow this article.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.