For those of you that need to setup Kerberos constrained delegation for Group Managed Service Accounts, you will likely get more familiar with msDS-AllowedToDelegateTo. This attribute stores a list of SPNs that are used to configure a service so that it can obtain tickets used for constrained delegation. In my previous post on setting up Kerberos constrained delegation, I walked you through how we used it to overcome the authenticaiton double hop issue. In that example, we needed to define which SQL Servers the PowerBI Report Server was able to delegate permisisons to. The command below demonstrates how we did...
Continue reading...Setup Kerberos Constrained Delegation for Group Managed Service Accounts
Kerberos delegation is not a new concept in Active Directory; however, setting it up for Group Managed Service Accounts (gMSA) can be a bit confusing. Unlike normal domain accounts, gMSAs do not have a GUI for configuring delegation. Those configuraitons will need to be handled through PowerShell. A common use for Kerberos delegation is to solve the authentication double hop. For example, a user accesses a report which is configured to use a remote data source. That’s one hop from the user’s workstation to the webserver and then a second hop from the webserver to the data source. Our environment...
Continue reading...Managed Service Account Naming Convention
A while back I had to help a customer update their naming standard for their Managed Service Accounts, so I thought I would share some of those details. The DBAs and other administrators had a difficult time determining which service account belonged to which server and to which service. They had a corporate requirement to have separate accounts for each service; for example, one for the engine and one for the agent. Their current naming convention was just to have an incrementing number at the end. SVCSQL1$ = DB engine service SVCSQL2$ = Agent service SVCSQL3$ = Full Text service...
Continue reading...Get Certificate Details for SQL Encryption In-Transit Using PowerShell
There is an updated version of this script here. In my opinion, all customers should be encrypting connections to their SQL Servers to secure data in-transit, but that’s a discussion for another day. Today, I wanted to discuss how you can use PowerShell to lookup details on the certificate being used for that encryption. For this example, we have three SQL Server instances each configured slightly different for encrypted communication. Using the SQL Server Configuration Manager, we can see the first instance is configured to use a certificate named “deathstar.govlab.corp (SHA256)”. The FORCE ENCRYPTION option is set to YES to...
Continue reading...Finding the NTFS Allocation Unit Size for Mount Points
The 64Kb Allocation Unit size is one of the easiest SQL Server best practice items to complete during your new builds, but from time to time, I still see this one overlooked. And I know this topic has been beat to death over the years, so we are not going to cover the why you should format volumes using 64Kb Allocation Size. We are just going to cover how to check the current value. In our setup, we have a server with 4 disks attached; two are mounted with drive letters (M: and S:) and two are mounted as folders...
Continue reading...
