Re: Using CA issued Certificates for SQL Certificate Encryption



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.



.



Relevant Pages

  • Re: Issues with SSL on Win CE 5.0
    ... creating the .pfx file, the private keys need to be marked as exportable so ... the server certificate you're trying to add is present under ... and tell the web server to use it. ... The old cert was in. ...
    (microsoft.public.windowsce.embedded)
  • Re: Issues with SSL on Win CE 5.0
    ... There is a Certificate in the HKCU under MY. ... This posting is provided "AS IS" with no warranties, and confers no rights. ... creating the .pfx file, the private keys need to be marked as exportable ... the server certificate you're trying to add is present under ...
    (microsoft.public.windowsce.embedded)
  • Re: Issues with SSL on Win CE 5.0
    ... creating the .pfx file, the private keys need to be marked as exportable so ... This posting is provided "AS IS" with no warranties, and confers no rights. ... the server certificate you're trying to add is present under ... and tell the web server to use it. ...
    (microsoft.public.windowsce.embedded)
  • Re: PFXExportCertStoreEx
    ... which contains the actual PFX and write that to the disk. ... methods to export certificate + private key from the IE store. ...
    (microsoft.public.platformsdk.security)
  • Re: ssl renewal questions
    ... Just on the server which I guess explains the clients being able to ... the certificate is invalid ... >> but connections continue to be encrypted and restarts of sql ... >>> appears that I had to stop and restart the sql process to get certs ...
    (microsoft.public.sqlserver.security)