...

Crypto

I wasn't really sure if the how safe this SQL Server traffic was plain text or not, and after running Wireshark it turned out that is wasn't...

Because we are part of the TERENA Certificate Service, we have access to 'free' SSL certificates from SURFnet.

, but running Wireshark revealed plain text SQL queries on the wire . I think it's good practice to encrypt traffic containing personal data, even though it's inside our own network. SQL Server 2008 R2 supports SSL encryption, and because we get "free" SSL certificates through SURFnet (which is part of the TERENA Certificate Service), we should be good to go!

I created the cryptographic materials Note that I did this on one of our Ubuntu system systems because those already have OpenSSL . The same things are installed. All of this is also perfectly possible on Windows as well, but you do need some extra bits such as Microsoft Visual C++ 2008. I just didn't want to pollute my Windows system with extra software that is already installed elsewhere in our network.

openssl req -new -keyout server.key -out server.csr -subj /CN=hayek.terena.org/

I submitted the signing request to the SURFnet web site, and after a few hours, and jumping through the Domain Control Validation hoops, I got a signed certificate (cert-11988-hayek.terena.org.pem) back, and the chain, which consists of 3 certificates concatenated into in one file (chain-11988-hayek.terena.org.pem).

Then I combined all certificates into one file, and created a PFX from it:

cp cert-11988-hayek.terena.orgcp server.pem all.pem
cat chain-11988-hayek.terena.org.pem >> all.pem
openssl pkcs12 -export -inkey server.key -in all.pem -out server.pfx

After copying the PFX file to the Windows server, I ran mmc and added the Certificates snap-in. When it asked for who to manage certificates, I selected the "Local System" account, because I was running the snap-in as Administrator.

Then expandExpand: Console Root -> Certificates (Local Computer) -> Personal.

Right-click -> All Tasks -> Import. Navigated to the pfx file and imported it. Include , including all extended properties.

I kept Mark this key as exportable unchecked, as I already have the key material in PEM format materials in a different place. Since we don't need this, I figured that since it is not needed on this machine, we might as well disable it, so any future malicious export attempts will be impossible (or at least more difficult this way).

Configuring MS SQL Server to use the certificate

...