Author: Nawaz Co-Author: Mamata
Greetings,
I have been using my lab for a long time and I have 1 CAS – 3 Primaries, 3 secondaries, 4 DP’s Hierarchy. Isn’t that too big for Lab 🙂 , Well this is because, with every release, we would like to test features and troubleshoot if any.
One fine morning we realized that we haven’t enabled CMPivot to be running from CAS. We did Follow all steps as suggested in the Microsoft article link: Configuring Kerberos Delegation if you have CAS Remote SQL Server
Once the configuration was done, I thought now its a “voila” moment to run CMPivot from CAS, But no, it didn’t work.
As soon as I executed CMPivot, I got a popup that asks me to check all settings as mention in the Microsoft article Configuring Kerberos Delegation if you have CAS Remote SQL Server
Further drilled down with the SMSPROV.log to check what error it’s giving. As shown below error described login failed for user “NTAUTHORITY\Anonymous”
We did check again, CAS server machine account Delegation settings, where all Primaries instances were selected and in Primaries, SQL database server was selected.
SPN was created on a computer account and no duplicates existed for the same, the error received had no clue to us. Enabled Kerberos Event Logging
Discussed the same issue with Javed, he is the best when it comes to SQL, Thanks to him for explaining out the SPN creating and deletion process.
On Discussion it further with Mamata, author for many blogs on this website, we started troubleshooting again, and there we came Across a Microsoft Tool: “Microsoft® Kerberos Configuration Manager for SQL Server®”
After installation, navigate to C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server
If you are running it locally on the server then just click on Connect
Now comes the turning point. I was excited after seeing the 2 tabs highlighted
This tool gives you relevant information about SPN status, in our case, it was misplaced, i.e. it was misconfigured. Next 2 button allows to Generate Script which will be saved to a location you want and executes it or you see Fix button which will prompt to.
I choose to generate a script to check what was wrong and what is been fixed. There come the exact issue SPN was registered in a different domain. As you can see in the Script there are 2 commands listed one to Delete SPN setspn -d and another one is to setspn -S to create it again.
As per the Microsoft recommendations, always SPN should be registered on the service account. So I want to take this opportunity to registered SPN under a service account instead of a Machine account which is mentioned as a “Local System”
How do you change SPN from Local System to Service Account?
First, we need to delete the existing SPN.
We will be using SetSpn -d command to delete SPN for machines FQDN and FQDN:1433.
Let me run the too to check SPN Status in the tool, now it should be Missing.
Now we need to register SPN, either you do it using the setspn command or go to the service account attribute editor and add values for “servicePrincipalName“.
SQL Server does register it SPN if you don’t create it, and all depends upon SQL services are running using which accounts.
For e.g.
- IF SQL Server (MSSQLSERVER) is running using Local System then SQL will register SPN using Machine Account
- IF SQL Server (MSSQLSERVER) is running using domain account then SQL will register SPN using domain account name.
So we will choose to run (MSSQLSERVER) using a domain Account
You will be prompted to restart the services so that changes can take effect. SPN is not registered yet.
Now you reboot the server, once you reboot the server, go to OU in which the service account is placed.
Right-click the service account –> Attribute editor – servicePrincipalName
The same information of registering the SPN can be captured under SQL server log files, these logs are generated once every time you restart the SQL server
Let us check the Kerberos configuration manager for the SQL Server tool, Voila
Now same needs to be done on all Primary servers so if I summarise steps once again :
- Delete Spn for FQDN
- Delete Spn for FQDN:1433
- Set SQL services to run with a domain account
- Restart SQL Services.
- Reboot Server.
- Check Service account / Domain Account – Attribute Editor – servicePrincipalName to see if SPN.
I did try to connect CMPivot from CAS but it failed again because I have rebooted the SQL server but not the SMS Provider (CAS server).
Rebooted SCCM CAS server. Now I can do CMPivot from CAS.
Conclusion: We summarised how the “Kerberos configuration manager for the SQL Server” tool is life savior when you deal with SPN configuration. Make sure you have a domain account set for SPN. reconfiguration of SPN.
