SQL Server – Fun with certificates

So, you have a PKI or access to certificates and want to encrypt SQL connections. Easy!

NOTE: We are not discussing encrypting the database or anything like that. Just the connection from the application (in this case the application is SQL Server Management Studio (SSMS) to the SQL server.

In my lab environment I have an Enterprise PKI consisting of an offline root and a subordinate issuing CA.


Creating the SQL Server certificate


In my lab my subordinate CA (or issuing CA) is on my DC. DON’T do that in production! First we need to open the Certificate Authority. This can be found in Administrative Tools.


Once the Certificate Authority is open, right click Certificate Templates and select Manage


That will open the Certificate Template Console. Navigate to the Web Server certificate template, right click and select Duplicate Template


Provide a Template Display Name. You might also want to change the validity period and renewal period, but that is completely up to you and your organizational policies.

In this example I am naming my template SQL Server – TEE-SQL-1. You will not want to use the same cert for all your SQL servers. Thus, you can see I have a naming convention of “SQL Server – ” and then the SQL Server name, which in my case is TEE-SQL-1.


The only other step is to go to the Security tab and remove Enroll rights from whatever groups you want. Then add the machine name of the SQL server and give it Read and Enroll rights.


All set. Click OK to close and save the template

Close the Certificate Template Console

In the Certificate Authority, right click Certificate Templates and select New > Certificate Template to Issue. NOTE: You will need to wait for Active Directory to replicate the template in order to issue it.


Find the template you just created and select OK



The SQL Server certificate has now been created and issued.



Install the certificate on the SQL server


On the SQL server that the certificate is to be used open an MMC. Select File > Add Remove Snap-In


Select Certificates and click Add


Select Computer Account and click next


Select Local Computer and Finish


Click OK to close Add Remove Sanp-ins

Expand Certificates (Local Computer) > Personal

Right click Personal and select All Tasks > Request New Certificate


Select Next and select Active Directory Enrollment Policy.

Find the certificate that was created previously and click on More information is required to enroll……


In the Subject Name select Common Name


Enter the FQDN of the SQL Server and select Add


In the Alternative name select DNS

Enter both the FQDN and the Netbios name of the SQL Server. NOTE: If you have availability groups you will want to enter those names too.


Select the General tab and enter a friendly name. NOTE: without a friendly name certificates can be hard to find!

Select OK and Enroll

You should now see the certificate under Personal > Certificates


But wait…..there’s more! Security…..

If you were to try and configure SQL Server to use the cert at this point, it wouldn’t show up. Unless you are running SQL as local system, in which case you can close this article. You have bigger things to worry about!!

We need to give the SQL Server service account rights to read the certificate.

To find out what account SQL server is running under open SQL Server Configuration Manger and select SQL Server Services

In my case I am running SQL Server under an AD Group Managed Service Account named gmsaSQLServer$

Return to the Certificates MMC and right click the certificate and select properties.


Add the account that is running the SQL services and provide it read permissions.


You have now enrolled the SQL Server certificate and given the SQL Server service account rights to read it.


Using PowerShell to install and configure the certificate


That last section had a lot of steps. There must be an easier way!

The following PowerShell script will do the same as above.





“CN=TEE-SQL-1.EmptyGarden.btls” `

Cert:\LocalMachine\My `

“TEE-SQL-1.EmptyGarden.btls”, “TEE-SQL-1”


$userName, $permission,




[io.path]::combine($root, $keyname)

if ([io.file]::exists($p))







$cert.Certificate.FriendlyName =
“SQL Server”


Configure SQL Server to use the certificate


Open SQL Server Configuration Manager and select SQL Server Network Configuration > Protocols for MSSQLServer.

NOTE: On my SQL Server I have two instances. One is the Default (MSSQLServer) and the other is a Named Instance (CONFIGMGRTP)

Right click Protocols for MSSQLServer and select Properties


Select the Certificate tab and find the friendly name of your certificate


Select OK. You will be prompted to restart the SQL services. The services need to be restarted for SQL Server to bind to the cert.


Once the SQL services are back to running open SSMS

Connect to the NETBIOS name of the SQL server, select Options and select Encrypt connection. Click connect

Run the following query:

AS instance, encrypt_option


WHERE session_id =



You have now enabled the ability for encrypted connections to SQL Server

PowerShell Template–Because we all want some sort of consistency!

Does your company have a lot of PowerShell scripts? Do they all follow the same template? No? You are not alone.

In my experience, troubleshooting scripts is much easier if all the scripts have the same look.

This script not only uses logging for errors, but can also be run in verbose mode so that the errors display in the PS session. Continue reading “PowerShell Template–Because we all want some sort of consistency!”

SCCM Training part 4

Topic:  SCCM HTTPS, IBCM, CloudDP, and Cloud Management Gateway






Questions: do you have to reinstall client after IBCM.  Answer:  Yes

SCCM Training–Part 3

Followed this guide and built an Azure Dev Test Lab – https://www.verboon.info/2017/02/deploying-configmgr-current-branch-in-azure-dev-test-lab/

Another great link for lab environments: https://technet.microsoft.com/en-us/windows/mt604890.aspx

Good link from Brian Mason on OCSP! Do it!!


Save this link for the rest of your life!!! Perfect step by step on setting up a two tier PKI environment and the OSSP.

I would provide more notes, but honestly the link has it all.


The one thing I would change is the CAPolicy.inf files the guide references.

The Offline RootCA should look like this:

Signature=”$Windows NT$”






The other stuff they have in the guide is meant more for internet based PKI, which you are not doing.

I would install the Offline RootCA as follows:

On the offline root machine install ADCS – Certificate Authority

Standalone CA

Root CA

Configure new Private Key

RSA@Microsoft Software Key Storage Provider


Key length 4096

Name of CA: azEmptyGardenRootCA

Validity period- 20 Years

Database location- default

RootCA Post Installation

Certutil -setreg CA\DSConfigDN “CN=Configuration,DC=azEmptyGarden,DC=BTLS”

Certutil -setreg CA\CRLPeriodUnits 6

Certutil -setreg CA\CRLPeriod “Months”

Certutil -setreg CA\CRLDeltaPeriodUnits 0

Certutil -setreg CA\CRLOverlapPeriodUnits 12

Certutil -setreg CA\CRLOverlapPeriod “Hours”

Certutil -setreg CA\ValidityPeriodUnits 20

Certutil -setreg CA\ValidityPeriod “Years”

NOTE: The CRLPeriod of 6 months means that the Offline RootCA needs to be booted up once every six months to copy the latest CRL to the SubordinateCA

Subordinate CA – CAPolicy.inf file


Signature=”$Windows NT$”







Subordinate CA – CAPolicy.inf file


Signature=”$Windows NT$”







Subordinate CA install

Install ADCS – This will be the Subordinate CA

Certification Authority only

Configure the Certification Authority

Enterprise CA

Subordinate CA

Create a new private key

Key length: 4096


IIS Log File Cleaning

The other day I noticed that my SCCM Site Server’s C drive was almost full.  After some searching, I found that the directory C:\Inetpub\Logs\Logfiles was consuming all of the drive space.

Now, I don’t recall ever being told to clean IIS Log Files, but all of my peers seem to know about it!!  Thus, that makes me…uh…..well….late to the game at the very least!!

Here is a great document on IIS Log File Cleaning.  Don’t be like Matthew.  Clean your log files!


ConfigMgr Training–Session 2

Supported configurations for SCCM – https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/supported-configurations

SQL version comparison – https://www.microsoft.com/en-us/sql-server/sql-server-2016-editions

Remember, format your SQL Data, Log, and TempDB drives to 64kb. To see the NFTS block size:  fsutil fsinfo ntfsinfo [your drive]

SQL Server Mangaement Tools – https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

SQL Server Data Tools – https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

Random note: Am I the only one who had their drive fill up with IIS logs? Yes? Wow!! Learn something every day. (Though, it seems I should have learned this years ago!!)

IIS Logs Maintenance – https://docs.microsoft.com/en-us/iis/manage/provisioning-and-managing-iis/managing-iis-log-file-storage

Use Managed Service Accounts (MSA) for SQL Services.


Add-KdsRootKey –EffectiveTime ((get-date).addhours(-10));

New-ADServiceAccount -name MSA_SQLServer -DNSHostName MSA_SQLServer.TEmptyGarden.btls -PrincipalsAllowedToRetrieveManagedPassword TTEE-CM1$

Enter-PSSession -ComputerName tTEE-CM1

Install-ADServiceAccount MSA_SQLServer

setspn -l tEmptyGarden\MSA_SQLServer$

setspn -s MSSQLSvc/ttee-cm1.tEmptyGarden.btls tEmptyGarden\MSA_SQLServer$

SCCM Sizing Guide:   http://blog.coretech.dk/kea/system-center-2012-configuration-manager-sql-recommendations/

OLA:  https://ola.hallengren.com/

Optimizing SQL:  https://stevethompsonmvp.wordpress.com/2016/11/29/optimizing-sccm-databases-revisited/

Windows ADK:  https://docs.microsoft.com/en-us/windows-hardware/get-started/adk-install

ADK fix:  https://blogs.technet.microsoft.com/configurationmgr/2017/04/14/known-issue-with-the-windows-adk-for-windows-10-version-1703/

SCCM Requirements step by step:  https://www.systemcenterdudes.com/sccm-2012-r2-installation-prerequisites/

Windows 10 Versions:  https://support.microsoft.com/en-us/help/4018124

Next class:  Go through the Administration node in the SCCM console, PKI, CloudDP, Cloud Management Point, SCCM certificate management point

Future meeting topics:

Endpoint Protection

Application deployment and the App Deployment Toolkit


The Compliance Setting Node

SCCM Fast Ring–Good or Bad (hint….bad!)

Last week I noticed that a colleague of mine had upgraded their SCCM environment to 1706.  This person manages well over 100,000 endpoints, so I figured 1706 was good to go.  Nope!

First, a bit of information regarding fast ring and regular ring.  Fast Ring for ConfigMgr is technically beta.  This means that even though you can put it in your production environment, you might want to wait.  Just like Windows 10 servicing, the Fast Ring will go production four months after the release.  In between……well, therein lies the problem.  You will most definitely get hotfixes every now and then while on Fast Ring, but you may also run into this:


“This update is available in the Updates and Servicing node of the Configuration Manager console for environments that were installed by using first wave (Fast Ring) builds of version 1706 downloaded between August 8 and August 11, 2017.”  According to the product team, only about 50 SCCM infrastructures were affected.  Guess who was part of the 50!

I installed SCCM 1706 on Friday August 11th.  Thus, I fell into one of the 50 that needed the hotfix.  Until today we were not able to install applications from the Software Center or OSD.  That, as you can imaging, was bad!

Lesson learned:  Perhaps don’t install SCCM Fast Ring unless you really really really need it and even then, be prepared for some issues from time to time until the release is full production.

Reading Task Sequence Variables in WinPE

Now and then you may have a need to read a task sequence variable during the OSD process. Today was one of those days. I had just modified a step in my task sequence and used the variable %OSDisk%. Well, the task failed. So……I put a pause in the task sequence and used PowerShell to find the variable. Turns out, there isn’t an OSDisk variable once the OS is dropped.

To see the variables and their values:

· Your WinPE boot image needs to have PowerShell on it

· In WinPE hit F8 to open a command prompt

· Type PowerShell to….well, you know, open PowerShell!!

$tsenv = New-Object -COMObject Microsoft.SMS.TSEnvironment

Foreach ($i in $TSEnv.GetVariables()) {“$i” + ‘ = ‘ + $TSEnv.Value(“$i”) | Out-File FilePath X:\Windows\Temp\SMSTSlog\TSVar.log –append}

At this point you can use CMTrace to open and view the file.

If you just want to see the value of one TS Variable: $TSEnv.Value(“OSDTargetSystemDrive”) for example.

Notes: The log file created has a ton of duplicate variables and values. Not sure why, and really don’t care either!! All I wanted to do was find the value for where the Operating System is. I have no idea if I will ever need to query TS Variables again, but good to know that I can!

ConfigMgr Training–Session 1

ConfigMgr training has started!  It will happen from 5:00 Central to 7:00 Central every other Tuesday beginning August 1st.

How to build your own lab environment:

Option 1: Have someone else do it for you! This is a great option that many of my peers use. Big thanks to Johan for giving this to the community.


Option 2: Use Azure: https://www.verboon.info/2017/02/deploying-configmgr-current-branch-in-azure-dev-test-lab/

Option 3: Build it yourself. This is the option I like because everything is mine! I get to customize the environment a bit more than the hydration kit.

This training session will focus on Option 3:

General notes:

Create a Server 2016 gold image. Install all updates. Then run: sysprep.exe /oobe /generalize /shutdown /mode:vm

Copy the gold image and rename it to whatever you want

Create a DC using the copied file of your gold image

2048 GB RAM

Two network cards. One private internal. The other internet connected.

Create a CM / SQL server. Two NICs, dedicated IP, 4096GB

4096 GB RAM

Two network cards. One private internal. The other internet connected.

SCCM Prereq tool: https://gallery.technet.microsoft.com/ConfigMgr-2012-R2-e52919cd

SCCM sizing and capacity planning: https://docs.microsoft.com/en-us/sccm/core/plan-design/configs/size-and-scale-numbers

Next Training: Tuesday August 15th 5:00 – 7:00

Install and configure SQL and SCCM