I believe beginning SQL 2016 self-signed certificates are being issued using SHA-2 but older SQL server are issued SHA-1. I have to replace the self-signed certificate with CA issued certificate.
- Make sure SQL server has installed server authentication certificate installed . If your server does not have one, then request one from your CA. Go to MMC->add Snap-in->Certificates->Add->Computer Account->Local Computer->OK.
On the MMC console->Certificate->Personal->Right click on the Certificates folder->All Tasks->Request New Certificate->Active Directory Enrollment Policy->select Computer under Active Directory Enrollment Policy-> Choose Enroll.
Very important, we have to make sure that the installed/received certificate has SQL server service account permissions to read this certificate. To provide SQL server service account proper permission, right click the certificate we just installed ->All Tasks->Manage Private Keys->Properties->Security-> Add the SQL Server service account and provide the read permission.
2. We need to tell SQL server to start using the new certificate. Lauch SQL Server Configuration Manager -> Network Configuration->Protocols for MSSQLServer->Properties->Certificate Tab-> Select the new certificate and click OK. You need to restart SQL Service to take effect. We can test if the new certificate by connecting to SQL Management Studio. You need to choose Encrypt connection and add TrustServerCertificate=True under Additional Connection Parameters under SQL Connection dialog box otherwise you will get an error saying that it doe snot trust the cert.
