gMSA and SSL in SQL, a true journey


Lately when I set up a Microsoft SQL server I’ll always implement SSL and a gMSA account to run SQL from.
Why? Convince yourself reading this blog post.

Because this post focuses on a specific issue, I’ll not explain implementing SSL as such. Here’s the Microsoft documentation I follow for my implementations:
This is initially made for SQL 2000, but all buttons and clicks are still the same.

However, the first time I’ve tried combining a gMSA for SQL and configure SSL, SQL wouldn’t start anymore.

The problem

Windows and SQL were friendly enough to provide me with some error messages:

The error number ‘0x80009030d’ suggested (according to a search on Google) an issue with the ‘common name’ used in my certificate.

In my troubleshooting process I’ve tried the following:

  • Remove the SSL configuration from SQL: SQL started
  • Tried a whole bunch of certificates since the suggestion the ‘common name’ in the certificate was wrong.
  • Grant the gMSA account the Windows Administrator role: SQL started with SSL configuration.

After this last step I’ve concluded that the issue must be rights related. Which was actually a good thing. Rights can be added, I knew where to look.
A few troubleshooting hours later I came up with the solution.


Here’s what you need to do:
Give the gMSA account read acces on the private key of your certificate stored in the certificate store of the computer account.

Here’s how you do it:
1. Open the certificate store for the local computer (when logged in to the SQL server):

2. Browse to the certificate you’ve imported to use for SSL encryption on SQL

3. Open the ‘Manage Private Keys’ menu by right-clicking the certificate

4. Click Add and make sure you select ‘Service Accounts’ in the ‘Object Types’ box

5. Add your gMSA account and give it the read rights


After that you are good to go and SQL should launch and load the certificate:


Enjoy your secure connections and all nights without worrying that accompany it…


If you like some extra reading material on how to use gMSA accounts:

Instances instances instances or SQL server consolidation

This post is about the (lack) of usage of Microsoft SQL Instances or SQL instance consolidation.

In the wild you see a lot networks where there are several (small) SQL servers. And if you want to save costsĀ our keyword should beĀ consolidation.

When should you think about consolidation?

  • You have more then 2 SQL Servers in your environment? Oh yes, those nasty SQL Express editions delivered with several software packages. I mean you too.

Continue reading

Shrink MS SQL Temp database using T-SQL

Problem:Temp database on SQL has grown to full size of the disk. Usually because of a bug/error in a scheduled task. There is no need to increase the disk size or replace with another disk. However other files (temp db log, …) are not able to perform well when the disk where they reside is full.

Shrinking our temp db is the only solution. Best practices tell us to always do this with a restart of the server. Sometimes, this just isn’t an option.


Got this script from a colleague.

Continue reading