Configuring SSL for SQL Server using Microsoft Certificate Authority Server
Refer attached document for detailed steps
1. Install IIS Server from ADD/Remove Windows Components (if it is not installed already)
2. Install Certificate Server ADD/Remove Windows Components (if it is not installed already)
3. OPEN Certsrv browser console by either of below mentioned ways,
A. IIS Manager and browse to Machine Name — Web sites — CertSrv
B. IE open
C. From IE open
4. To Install CA (Root) Certificate
A. Click on ‘Download a CA Certificate, Certificate Chain, or CRL’
B. Click on Install this CA certificate chain
C. Click YES
D. CA chain (Root Certificate) installed successfully
5. Create a SERVER Side Authentication Certificate
A. Go to Certsrv site and click on ‘Request a certificate’
B. Click on ‘Advanced Certificate request’
C.Cick on ‘Create and submit a request to this CA’
D. Enter the certificate information
- 1. Type the FQDN (Fully Qualified Domain Name) for the name
- 2. Select ‘Server Authentication Certificate’ for Type of Certificate Needed.
- 3. Check the ‘Mark Keys as exportable’ option
- 4. Click on Submit
E. Click on YES to complete
F. We need to make a note of the ‘Request Id’ from the below screen.
6. Issue the certificate.
A. In MMC add ‘Certificates’&’Certificate Authority’ using ‘Add/Remove Snap-in’ options.
B. Click on ‘Pending requests’ in ‘Certificate Authority’.
(We would see certificate with Request ID which we generated in STEP 5.i.e., 7 here)
C. Right click on the certificate –> All Tasks –> ISSUE
D. Now we should see the certificate under ‘Issued Certificates’
7. Install the certificate
A. Click on ‘View the status of a pending certificate request’
B. Click on the certificate.
C. Click on ‘Install this certificate’
D. Click on YES
E. We will see the successfully installed screen.
8. Assign the certificate to the SQL Server instance.
A. Open SQL Server Configuration Manager
B. Right click on ‘Protocol on <instance name>’
(for the instance which we need, here it is STANDARD)
C. In the certificate tab and select the certificate we created earlier.
D. Click on Apply and restart the SQL Server instance to get this change applied.
9.After the successful deployment of the certificate (Server side) we should see the below message in our SQL Error Log file during the server startup.
The certificate was successfully loaded for encryption.
Technical Lead, Microsoft SQL Server Support