Why you would want to do this is a longer discussion, but suffice it to say that if your environment allows for self-signed certificates, you have a couple options for creating these in SQL Server and Windows. I’ll show you how easy this can be using these two methods:
- CREATE CERTIFICATE
Please be careful if you plan on creating your own certificates. The value of a certificate and asymmetric keys comes in the hierarchy of trust for these certificates and if you do not have a strong hierarchy, you could potentially be making your security worse, rather than better.
I downloaded the SDK, extracted it, and then fired up a command prompt, running this:
makecert -sv "c:\EncryptionPrimer\MyHRCert.pvk" -pe -a sha1 -b "01/01/2012" -e "12/31/2012" -len 2048 -r -n CN="HR Protection Certificate" c:\EncryptionPrimer\MyHRCert.cer
This code creates a private key file (MyHRCert.pvk) and a public key certificate (MyHRCert.cer)
You can click the link and read the parameters, but it’s really that simple. When you create this certificate, you can use the FROM FILE options for CREATE CERTIFICATE to load this certificate into your SQL Server.
I guess technically you are using the CREATE CERTIFICATE in either case here, but this section looks at the actual creation of the certificate by SQL Server.
CREATE CERTIFICATE is standard DDL, like so many other commands in SQL Server. The parameters are similar to those for makecert. Here’s a statement that matches up with the one above.
create certificate MySalaryCert ENCRYPTION BY PASSWORD = N'R3allyToughP@ssword4You' WITH SUBJECT = 'HR Protection Certificate', START_DATE = '20120101', EXPIRY_DATE = '20121231';
Note that you don’t need to specify the algorithm or other parameters. SQL Server handles that for your. You also don’t need to specify the two files here. The database engine stores these keys inside the database. You should make a backup of them, and you can use the BACKUP CERTIFICATE command to do this.