Re: Using CA issued Certificates for SQL Certificate Encryption
- From: "Jediah L" <rife@xxxxxxxxxxxxx>
- Date: Wed, 21 May 2008 12:33:46 -0400
Peter,
I was able to successfully create a PVK from a PFX last night and install it
into SQL and use it for encryption - but I did have to use two open source
utilities to do so (which I'm not very excited about). In similar steps to
you, I downloaded and compiled openssl, and also utilized the PVK.exe that I
found on the internet. I wonder why, if Microsoft uses PFX as it's standard
for private key encryption, it built SQL Server to utilize PVK....
I used the following steps:
1. Extracted PEM from PFX using openssl
2. Converted PEM to PVK using pvk.exe
3. Used CER from certificate export for public key
4. Used PVK from steps 1 & 2 for private key
In regards to the use of an external certificate - true SQL server does
support the creation of certificates, but we want to use the management
features of our Enterprise Certificate Authority to remain consistent in our
environment and also to provide additional recoverability as part of our
encryption process.
Thanks for your response!
Jediah L.
""Peter Yang[MSFT]"" <petery@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:pCy3QvwuIHA.1784@xxxxxxxxxxxxxxxxxxxxxxxxx
Hello Jediah,
Usually in SQL 2005 it's not necessary to use a certificate from outside
source. You could create certificate directly and it's managed by SQL
itself and protected by service master key and database key.
--USE master;
--CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password>';
GO
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate',
START_DATE = '05/10/2006'
GO
You could back up certificate as:
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOST_A_cert.cer';
On a different server, you could restore the certificate via:
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\HOST_A_cert.cer'
GO
As for PVK format certificate, you could refer to the following article
for
some related information:
915852 How to use certificates for SQL Server 2005 Service Broker remote
security on multiple instances of SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;915852
I tried to do the following test and I encountered similar issue as you:
1. Use pvk2pfx in .net sdk tool to convert pvk/cer to pfx format.
pvk2pfx -pvk HOST_A_cert.pvk -pi fnf -pfx HOST_A_cert.pfx -po fnf -spc
HOST_A_cert.cer
2. Try to use openssl tool to convert it to pem format
openssl pkcs12 -in HOST_A_cert.pfx -out HOST_A_cert.pem -nodes
3. Use pvk to convert pem to pvk
pvk.exe -in HOST_A_cert.pem -topvk -strong -out HOST_A_cert-o.pvk
I compared the HOST_A_cert-o.pvk and HOST_A_cert.pvk and they are same
size.
4. convert pem to SPC file using openssl
openssl.exe crl2pkcs7 -nocrl -certfile HOST_A_cert.pem -outform DER -out
HOST_A_cert.spc
It seems HOST_A_cert.spc has different size from HOST_A_cert.cer
5. Try to use spc/pvk to create a certificte and I encountered the
following error:
CREATE CERTIFICATE HOST_A_cert_o
FROM FILE = 'C:\sql\security\HOST_A_cert.spc'
WITH PRIVATE KEY (FILE = 'C:\sql\security\HOST_A_cert-o.pvk',
DECRYPTION BY PASSWORD = 'Password!');
GO
Msg 15468, Level 16, State 1, Line 1
An error occurred during the generation of the certificate.
I was not able to find other tool or method to convert pfx to cer
correctly. I will try to do some more research on this.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
.
- Follow-Ups:
- Re: Using CA issued Certificates for SQL Certificate Encryption
- From: "Peter Yang[MSFT]"
- Re: Using CA issued Certificates for SQL Certificate Encryption
- References:
- Using CA issued Certificates for SQL Certificate Encryption
- From: Jediah L
- RE: Using CA issued Certificates for SQL Certificate Encryption
- From: "Peter Yang[MSFT]"
- Using CA issued Certificates for SQL Certificate Encryption
- Prev by Date: RE: Msg 15404: Could not obtain information about user
- Next by Date: Re: Using CA issued Certificates for SQL Certificate Encryption
- Previous by thread: RE: Using CA issued Certificates for SQL Certificate Encryption
- Next by thread: Re: Using CA issued Certificates for SQL Certificate Encryption
- Index(es):
Relevant Pages
|
