All images are made by myself using Windows and Microsoft-tools.
All statements and information on this page are given by my best knowledge.
I give no Support for ANY Problems that might appear following this tutorial.
1. Get a Certificate and install it to your Server. It must have at least 2048 bit long encryption-Key.
I recommend to choose at least 2048. For most People 2048 or 4096 should be the way to go.
!Note: The bigger the value, the more work is needed to de- and encrypt the data, but the safer it is!
!Note: The Hostname the Certificate is made for must be the Hostname of your server.
If your Server is named MYMSSQLSERVER and your Domain is MYDOMAIN.XYZ the Certificate must be made for
MYMSSQLSERVER.MYDOMAIN.XYZ
Let me quote technet.microsoft.com:
The Subject property of the certificate must indicate that the common name (CN) is the same as the
host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster,
the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on
all nodes in the failover cluster.
How to create a self-signed Certificate
How to order a trusted Certificate
How to change Server-Domain

2. Open the SQL-Server-Manager. Sometimes some needed Options will not show possibilities (bug)
3. Expand 'SQL Server Network Configuration'
4. Right click on your instance and open Properties.

5. Set the Flag 'Force Encryption' to Yes.
6. Go to Certificates, choose your certificate and finish by clicking on Apply.
Note: If your cerificate is not shown, you have to set the correct Suffix
7. You are done.

Allowing MSSQL to read the Certificate
1. Run MMC (Windows-Key + R, enter 'MMC', press Enter)
2. Click on File -> 'Add/Remove Snap-in...' or press Ctrl + M

3. Select 'Certificates' and click 'Add >'

4. In the window 'Certificates snap-in' select Computer, click 'Next' and then 'Finish'

5. You should now have 'Certificates' in the right box. Close the window by clicking 'OK'.

6. Navigate to 'Certificates'->'Personal'->'Certificates' and choose your Certificate on the right.
7. Right-click the certificate and chose 'All Tasks'->'Manage Private Keys...'

8. In the 'Permission for ...'-window, click on 'Add...'.

9. Enter the name of the user MSSQL is using and click 'OK'.
Default for MSSQL2008 and newer: 'NT Service\MSSQL$InstanceName'
(enter 'NT Service\MSSQL$' and click 'Check Names', mostly this will autocomplete the user)

9. (Optional) The user doesn't need 'Full control', you can uncheck it.

10. Click on 'Apply' and 'OK'. The MSSQL-Instance should now be able to read the Certificate.

Restarting MSSQL
1. Open 'SQL Server Configuration Manager'.
2. Navigate to 'SQL Server Services'
3. Right-Click the Instance you want to restart and click on 'Restart'
back to the beginning


Creating a self-signed Certificate:
1. Open IIS-Management
2. Select your server
3. Doubleclick on 'Server Certificates'
4. Click on 'Create Self-Signed Certificate'
5. Set the store to 'Personal'
6. Give it a Name and finish by clicking OK.
7. Doubleclick your certificate to open the Info.
8. Navigate to Details and click 'Copy to File'
9. Click next, choose 'No, dont export private key', then click next, next, choose a Savepath
and finish by clicking on Save.
10. You have to set the clients to trust Server-Certificates OR install the Certificate you just
exported on every Client which will have to Connect via SQL to the server.
back to the beginning


Getting a trusted Certificate:
1. Open IIS-Management
2. Select your server
3. Doubleclick on 'Server Certificates'
4. Click on 'Create Certificate Request'
5. Fill out the required information and click on next.
6. Choose RSA and a Key-Length. 2048 is nice. !Dont use 1024!. Click on next.
7. Save the Certificate Request.
8. Go to your Favorite certificate-reseller and buy your certificate giving the Certificate Request.
9. Save the Certificate on your server.
10. Navigate to the 'Server Certificates' in IIS.
11. Click on 'Complete Certificate Request'.
12. Choose the Certificate-File, give the Certificate a visible Name, and finish by clicking OK.
13. Done.
back to the beginning


How to change Server-Domain:
Note: I show you how to change the suffix, not how to join a domain.
1. Open your System-Window. (e.g. Using Windows-Key + Print)
2. In the System-Window click on 'Change settings'

3. The System-Properties window opens. Click on 'Change'

4. You now see a window called 'Computer Name/Domain Changes'. Click on 'More...'

5. Enter your Domain-Information, then click on 'OK'

6. It now shows you the change. Click on 'OK', an information-popup appears -> Click on 'OK'

7. We are left with the 'System Properties' window. It shows you the changes and a note at the bottom. Click on 'Close'

8. Another popup appears and asks you for a reboot. Your decision, the changes take effect after reboot.
back to the beginning