Among the cool new features in SQL Server 2019, one stands out that I remember being a pain in the butt with earlier versions: Certificate Management in SQL Server Configuration Manager.
Making sure that your SQL Server environment is secured can be cumbersome and tricky. In SQL Server 2012 (and later), Microsoft introduced a tool called, “SQL Vulnerability Assessment“, which helps in finding potential database vulnerabilities. It’s a great tool to help proactively improve your SQL Server database security.
Here is a quick way that I use to create a server-level certificate which allows me to use SSL encryption option in SQL Server Configuration Manager. If you have any questions throughout this process, feel free to contact me here. I will be more than happy to help you out.
Copy the below (in between the ==) into a blank textfile. To find your Fully Qualified Domain Name (or FQDN), open the Command Prompt (Run –> Type cmd then press ENTER), at the prompt type:
Your FQDN will be in the following format:
“Host Name.Primary Dns Suffix”
Take that FQDN and replace the MY.FQDN.COM with your FQDN below
[Version] Signature = $Windows NT$
Exportable = TRUE
KeyLength = 2048
KeySpec = 1 ; AT_KEYEXCHANGE
KeyUsage = 0xA0 ; Digital Signature, Key Encipherment
MachineKeySet = True ; The key belongs to the local computer account
ProviderName = “Microsoft RSA SChannel Cryptographic Provider”
ProviderType = 12
SMIME = FALSE
RequestType = PKCS10
Then save it as a .inf file. For example, MyServer.inf
*** Make sure you change the “Save as type” option to “All Files.” The default is “.txt” and if you don’t change it to “All Files” you will end up with a file like “MyServer.inf.txt” which will not work.
Next, open the Command Prompt and type the following:
certreq -new C:\certificate\MyServer.inf (see screenshot below)
Once you hit [Enter], a pop-up will open and prompt you to pick a location to save the hash file. Just choose the same folder that your MyServer.inf file is in to lessen confusion.
Next, if you have a person who acts as the CA (Certificate Authority) or is the single point of contact for all certificate requests then all you have to do is rename the .inf file to a .csr file and send it to them as an attachment in an encrypted/secured email.
They will take that hash output, fill out a form, and submit it to the CA. After a few days (or weeks) they will receive an email with the complete certificate hash and they forward that to you.
I have worked in environments where I had to email the single point of contact as well as fill out the form myself. If you are required to go to https://ca-27.csd.disa.mil/ca/ and submit the request for a certificate yourself, and need help…check out instructions on how to do that below:
I have worked in many government facilities throughout my career and most recently I was in charge of securing a couple SQL Server database servers. One of the items on the “checklist” to secure was installing a server-level DoD SSL certificate. By installing this certificate, it would allow me to enable “Force Encryption” in SQL Server Configuration Manager.
Work environments differ. Some require you to do everything. Some require you to do a certain point before passing it to a point of contact within the organization. If you are in charge of installing a DoD SSL Certificate and have a CAC card, then hopefully this blog will help you save time and headache! (feel free to contact me if you have any issues)
You will first have to submit your hash at the following link to get a “request ID”, which you will need to fill out the form.
**UPDATE: New DoD PKI SHA-256 CAs have been released. Enrollment pages for these CAs are available at: https://ee-id-sw-ca-37.csd.disa.mil
1. You click on the link above, and then choose “New 2048-bit SSL Enrollment form” option (see screenshot below)
2. Then, choose PKCS#10 for Certificate Request Type.
3. Cut and paste your hash in the “Certificate Request” textbox.
4. Type out the FQDN of your server in the “General Name Value” textbox.
5. Enter in your Name, Email, Phone in the “Requestor Information”
6. Click Submit.
The following screen will display a “Request ID.” Copy that Request ID and paste it in the Certificate Request form. For more details on how to install the certificate, check out my blog post here:
Most recently I had to create a SQL Logon trigger that set a limit to sessions a user could open in SQL Server.
For example, I log into SSMS and cannot open more than 10 sessions. This logon trigger will enforce that I don’t exceed 10 concurrent sessions. Continue reading “How to Create LOGON Trigger in SQL Server”
I have enabled TDE (Transparent Data Encryption) on almost all of my production SQL Server database servers. Yesterday I was approached by the IA (Information Assurance) team and they wanted to know what was the encryption level (key length and algorithm) of one of the database servers. Continue reading “How to Check SQL Server Database Encryption Algorithm”
If you work for the government and have to create a SQL Server Trace that monitors all the trace event IDs that come in the DISA STIGs then you have come to right place! (I feel like I’m hosting an infomercial) Continue reading “How to Create a DISA STIG SQL Server Trace”