Charles,

So after trying another 25 or so certificates and various configurations, I
have found that I can't even load the public key of any of these
certificates that I have issued from our internal CA, while I can load the
public certificates from an external CA.  Both internal and external (as I'm
referring to them) are certificates that are issued from Microsoft
Enterprise Certificate Authorities and are fully functional otherwise.

The documentation also seems to suggest that I can load certificates from
the certificate store - but there is also no documentation that shows how to
do that...

Could I send you the public cert that is working, and the one that isn't
working, and you take a look and possibly come up with a reason as to why
one is working and the other not?  The errors are very non-descript just
saying that the certificate can't be loaded...  both are valid certificates,
and I've tried matching the certificates as exactly as I could, but still no
go.

Thanks!

Charles,

Here is what I am doing:

1. Create a Certificate Template for the SQL Encryption
1a. Duplicate the User Template call new template SQL Encryption
1b. Under the General Tab set the Validity Period to 2 years
1c. Check Publish in Active Directory
1d. Under Request Handling set the Purpose to Encryption
1e. Allow private key to be exported, minimum key size 1024 bit, enroll
subject without user input
1f. Under Subject Name choose supply in the request
1g. Under Extensions
1h. Remove all application policies (I've tried various different settings
for this, none seem to matter)
1i. Under Key Usage Extension only set Allow Key exchange only with Key
encryption (I've tried various different settings for this, non seem to
matter).

2. Create INF file for certificate request (can do this by hand or use the
RequestEditor.exe that came with the Microsoft Press PKI book).
2a. Here is my INF file

[Version]
Signature = $Windows NT$

[NewRequest]
KeyLength = 1024
Exportable = True
MachineKeySet = True
SMIME = False
Subject="CN=SQL Encryption"

[Note: I've tried various Subjects, they don't seem to matter]

2b. Create certificate request file using INF file

certreq -new -attrib "CertificateTemplate:SQLEncryption" input.inf
output.req

2c. Submit request to Enterprise CA

certreq -submit output.req publiccert.cer certchain.p7b

2d. Accept certificate request

certreq -accept certchain.p7b

3. Export PFX file for previously created certificate
3a. Run through private key export wizard and create PFX file.

4. Convert PFX file to PVK file (Microsoft requires PVK file format)
4a. Download and install or compile OpenSSL
4b. Run the following command to create PEM file from PFX file

openssl pkcs12 -in [pfxinputfile] -nocerts -nodes -out [pemoutputfile]

4c. download pvk.exe utility from web
4d. Run the following command to create the PVK file from the PEM file

pvk -in [pemfile] -topvk -out [pvkoutputfile]

5. Install certificate into SQL Server
5a. Run the create certificate command

create certificate cert2 from file = 'c:\temp\public.cer'
with private key (file='c:\temp\created.pvk',
decryption by password = 'password_used_in_pvk_command')

Everything works file, except the very last command for the create with
throw the following error with or without the WITH PRIVATE KEY option:  "An
Error occrred during the generation of the certificate".