Automatically Install SQL Server and Enable SSL Encryption with Domain CA on Server Core

24 Shares

Windows Server Core is the perfect candidate for Microsoft SQL Server. While I rather spend 15 hours automating something, making SQL Server secure on Core is quit a hard / impossible task without PowerShell.

SQL Server Management Studio 2017

So the first task will be to install the latest version of Management Studio. I’ve started to upgrade all my Automation Framework PowerShell scripts to automatically download the media if it doesn’t exist in the $Version folder.

That way I just need to provide $Version and $URL when a new version is released. The rest is handled automatically. The easiest way to find the URL is just using Google Chrome Downloads and copy the link.

SQL Server 2016

For this installation I’m using the good old batch script. I’ll hopefully publish a post soon using PowerShell with Microsoft SQL Server 2017.

After the Automated installation of Microsoft SQL Server 2016 you’ll want to create a Domain Certificate to Force Encryption.

cd

So again we’re using a script created by Martin Therkelsen to do so automatically. Create the following two files in a Temp folder and run the script. To make it simpler and secure, run it on any server that has IIS with Management Tools.

SSL.ini

New-Certificate.ps1

The final part is to import the certificate, bind it to the SQL instance and set permissions on the private key. The SQL Service Account is running the Network Service so without permissions to the private key the SQL Server simply won’t start. Luckily I stumbled open this post which solved that for me.

Make sure to run this script on the SQL Server itself.

To verify you can run the following query

The above query will show encryption even if you just set Force Encryption on the instance. In that case it will use a self sign certificate!

The only way to check is the SQL Server Error log. Simply search for the Certificate Thumbprint. Click here to learn how.

24 Shares

Leave a reply