June 20, 2024 at 4:39 pm
Hi there, I have a sql server on government AWS and we're trying to satisfy a requirement to force encryption on the server. I have tried creating self signed certs and we have obtained official certs from DoD, but the SQL configuration manager will not accept the certificates. I have been able to get it to recognize a self signed certificate, but then once I force encryption the sql server service will not start up or will not let me connect to the database anymore.
Anyone have experience with this and what I might be doing wrong?
June 20, 2024 at 5:41 pm
What type of SQL Server on AWS? EC2? RDS? You need to be clear on the setup in the cloud.
June 20, 2024 at 5:50 pm
It is an EC2 in AWS. SQL Server 2022 Standard Edition.
Could it be the file type of the certificate? In configuration manager it expects the files to be PFX, PEM or Private Key if you do the import from there. But the certificate we have is a PKCS #12 ... might that make a difference?
June 20, 2024 at 6:21 pm
In EC2, it's the same as if you were doing this on-premises with any SQL Server instance in a VM.
There are requirements for the cert, and the config is something that can be tricky. When I've done this, I've followed the process and then usually have to work through the things that are poorly documented. Your question really provides no information, so it's hard to give any sort of answer.
June 20, 2024 at 6:33 pm
Ok I apologize I seem to be having issues communicating on the forums.
My first question is why SQL Server won't recognize the DoD certificate that I'm trying to import... I was asking if the file type of the certificate was a problem as PKCS doesn't seem to be a default file type that configuration manager was accepting.
I am taking a look at the requirements link you provided however... I'm not sure if they are just not creating the certificate properly. When you have done this, did you do it with a self signed certificate that you created yourself?
June 20, 2024 at 7:30 pm
Now I got a PFX certificate file and when I go into SQL Configuration Manager and try to import it, it spins for a bit and then crashes the program.... sigh.
I also found something that said this:
Grant read permission to the private keys to the SQL Server service logon user.
But in the MMC, there is no option to "manage private keys" like the web is telling me to do. How can I grant permission to the key if it won't give me the option to do it?
June 20, 2024 at 8:39 pm
Here are my notes about forcing encryption on a local instance. I presume EC2 will be much the same.
1. Check the DoD is a valid CA on the EC2 instance - may need help from your Infrastructure Team.
2. The certificate requirements are:
a) The Subject property CN field must be the host name or FQDN. (or Virtual Network Name for failover clusters.)
b) The Subject Alternative Name property must contain both the host name and FQDN.
c) The keySpec option should be AT_KEYEXCHANGE.
d) The Intended Purpose should be Server Authentication.
The certificate must be valid when added to SQL Server.
3) Import the certificate into the local computer store of SQL Server
Your Infrastructure Team may be able to automate 2 and 3 so the certificate is replaced on a regular basis.
4. Give the SQL Server Service Account (which defaults to NT Service\MSSQLSERVER) read only permissions to the certificate's private key.
a) Load certlm.msc
b) Personal | Certificates
c) Select the certificate and then Right Click
d) All Tasks | Manage Private Keys
e) Add the SQL Server Service Account and give it Read Permissions.
Exit certlm
5. Load SQL Server Configuration Manager (or MMC with snapin)
a) SQL Server Network Configuration | Protocols for ...
b) Properties
c) Flags - Force Encrytion = Yes
d) Certificate - Select the certificate which you have just give read permissions to the SQL Server Service Account.
e) Press the APPLY button
6. Restart SQL Server
June 20, 2024 at 8:41 pm
Above, and cert type has to be right. I have been able to use a self-signed, but that isn't secure unless you drop it on every machine. What you really need is a cert with a chain of authentication.
June 20, 2024 at 9:01 pm
Our infrastructure team now has a CA server which is recognised as such by every machine on our network. All I have to do is ask for a certificate with the above properties and it appears in the local certificate store of the SQL Server. I then just give the read permissions on the private key to the SQL Server service account and then force encrytion. (Only TLS 1.2 and TLS 1.3 are enabled on our servers.) The CA server also rotates the certificates every quarter. It seems to run smoothly, no need for clients to trust server certificates etc.
ie We now only buy certificates for external use. As SQL server is never directly exposed externally I can always use the internal CA certificates.
June 28, 2024 at 3:07 pm
Good morning,
We are still having issues with this... we created a self signed certificate and when we go to import it into configuration manager, the application just closes and exits... anybody have an idea why the configuration manager is shutting down without error or anything? Or is there a way around that?
Also, we did create a self signed certificate... sql does automatically see that one. If I select it and then turn on force encryption, the sql services will not start. Why does this cause the service to not start?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply