Re: Certificate Requirements for SQL Data Encryption

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".










""Charles Wang [MSFT]"" <changliw@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:lITVmgWxIHA.2252@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi Jediah,
I recommend that you directly give Microsoft feedback at the bottom of
this
article:
CREATE CERTIFICATE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187798.aspx

Appreciate your understanding that the documents are huge and it is very
hard to cover every aspect when we initially release them. Your feedback
will help us enrich our contents and have them become more helpful.

Regarding your non-working certificate, based on my current test, I could
not reproduce your issue at my side. I used a certificate from my
Certificate Authority, however I noticed that you said "So far I have
tried
issuing from the user template and the computer template. For the user
and
computer templates I have tried various different options: including and
not including Client Authentication, and Server Authentication in the
Application Policy extension, I have tried changing the purpose from
encryption to signature to signature and encryption, I have set the key
usage to various values (Digital Signature, Key exchange, etc)." , it
seemed not from CA, could you please elaborate this phase and your process
to load a certificate in SQL Server 2005 so that I can perform further
checking?


Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
=========================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================






.

Relevant Pages

  • RE: Certsrv and Autoenrollment problem
    ... Thank you for posting to the SBS Newsgroup. ... so it will not be instantiated on the template ... Certificate Authority snap-in will show the templates in the Certificate ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Problems requesting computer certificates on an issuing CA
    ... The exact permissions on my template are: ... I tried to manually enroll for a computer certificate based on ... CA allows the computers to request certificates. ...
    (microsoft.public.windows.server.security)
  • Re: Error enrolling machine certs
    ... failing to enroll using Domain Controller template. ... certificate templates and to the certificate services - everything that can ... > computer as a local admin to request a computer certificate either through ...
    (microsoft.public.windows.server.security)
  • Re: Win2003 server: certificate templates
    ... The best way will be to enroll from the web page. ... request a machine certificate from a user account. ... > I created a "serverCert" template by modifying the "computer" template, ... > but I cannot access "serverCert" through the mmc panel. ...
    (microsoft.public.win2000.security)
  • Re: Modify Default User Template in Certificate Server
    ... Modify Default User Template in Certificate Server ...
    (microsoft.public.security)