Want to encrypt your msSQL traffic? Here is how to do it.
- Request/Install a certificate in the Windows Certificate store
- If you are on a domain with a certificate authority, you can do this by clicking Start->run->mmc
- Click File->Add/Remove Snap-in
- Select Certificates, click the Add button, select Computer account, click OK, click Finish the wizard.
- Expand Certificates (Local Computer) and navigate to Personal->Certificates
- Right click All Tasks -> Import… or Request New Certificate (depending on what you want to do)
- Once you have finished installing the certificate, click Start->All Programs->Microsoft SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager (Launch SQL Server Configuration Manager)
- Expand SQL Server Network Configuration
- Right click on “Protocols for MSSQLSERVER” (or whatever your instance name is on the left side) and click Properties
- On the Flags tab, you can optionally set “Force Encyrption” to Yes, which will make your msSQL server only allow connections that are secure. You may skip this step if you don’t want to do this.
- Click on the Certificate tab.
- Select your certificate that you installed in Step 1 in the Certificate dropdown box.
- Click OK
- Click on SQL Server Services
- Right click on the SQL Server (MSSQLSERVER) service and click Restart (MSSQLSERVER==your instance name)
That’s all that’s to it. Note, if you receive an error that the service cannot run (I forgot what the original error was), try disabling the VIA Client Protocol underneath SQL Native Client 10.0 Configuration inside of SQL Server Configuration Manager.
As a reference, you can find the official Microsoft KB article on how to do this here.