I have created a certificate authority on my Linux server using OpenSSL. I have added that certificate authority to the Windows Trusted Root Certificate Authorities, which it now recognises.
What do I need to do, from this point, to create a valid SSL certificate that SQL Server will recognise and be able to use? Assuming that my CA key is named root.key, its certificate named root.pem, and its equivalent certificate signing request named root.csr.
No guide / tutorial has been able to tell me this. Every guide seems to be concerned with using the database server to create a certificate authority and generate keys, or something or other – I do not want this. All I want is for my Linux server to generate the SSL certificates, and for those certificates to be used by my client database machines (possibly without going through Microsoft Management Console and other unnecessarily complicated bullsh*t), such that I can connect to them via TCP/IP from my Linux machine (it can connect fine when using no encryption). The computer running the MSSQL server is not part of a domain, and its computer name / hostname is WORKSTATION (if this matters at all).
Best Answer
I'm assuming that the certificate has been created for Server Authentication using your linux server. What you are essentially describing is the same concept that public CAs use to provision their certificates. The CA, or Certificate Authority, is the service or application used to attest to the identity of users, computers, and organizations as well as generate, manage and renew certificates. I would recommend removing the CA service from your SQL Server. You won't need it and if you do use it to create a certificate for the SQL Server, you will be creating a self-signed certificate on your SQL Server, which will leave you open to the risk of a man in the middle attack. It is also possible to encrypt all sessions by simply setting Force Encryption to true, but this will cause the SQL Server to internally generate a self-signed certificate, which is not advised for the same reason.
As long as your SQL Server, linux system and client system can communicate with each other and you use a certificate from the CA on the linux system, what should happen is that the client initiates a session with the SQL Server, which then responds with the request for a secure session and presents a certificate generated from the linux server to the client for validation. Then the client should validate the certificate in two ways, by verifying with the linux system that it is a valid certificate and by using the digital signature to verify that it has not been modified. The client system will also need a way to verify that the certificate can be trusted, which will mean adding it or a root certificate from the linux CA to the trust store of the client.
The basics of enabling SSL on your SQL Server are to import the certificate and private key into the certificate store of the service account running the database engine and then set the Force Encryption property to yes in the properties of the server protocols using SQL Server Configuration Manager. In your case, you may need to convert your pem and key files into a format that Windows Server supports. The question Convert a CERT/PEM certificate to a PFX certificate specifically addresses using OpenSSL to accomplish this.