NB Updating SPNs in AD is not for the faint hearted plus I got inconsistent results from different servers. Do so at your own risk!
If you need the SSAS account on a SQL Server to use a domain account rather than the local “virtual” account “NT Service\MSSQLServerOLAPService”. You may think you just give the account login permissions to the server, perhaps give it sysadmin SQL permissions too. However, if you try and connect to SSAS remotely you may get this error:
Authentication failed. (Microsoft.AnalysisService.AdomdClient) The target principal name is incorrect (Microsoft.AnalysisService.AdomdClient)
From Microsoft:
“A Service Principle Name (SPN) uniquely identifies a service instance in an Active Directory domain when Kerberos is used to mutually authenticate client and service identities. An SPN is associated with the logon account under which the service instance runs.
For client applications connecting to Analysis Services via Kerberos authentication, the Analysis Services client libraries construct an SPN using the host name from the connection string and other well-known variables, such as the service class, that are fixed in any given release of Analysis Services.
For mutual authentication to occur, the SPNs constructed by the client must match a corresponding SPN object on an Active Directory Domain Controller (DC). This means that you might need to register multiple SPNs for a single Analysis Services instance to cover all of the ways in which a user might specify the host name on a connection string. For example, you probably need two SPNs to handle both the fully-qualified domain name (FQDN) of a server, as well as the short computer name. Correctly registering the Analysis Services SPN is essential for a successful connection. If the SPN is non-existent, malformed, or duplicated, the connection will fail.
SPN registration is a manual task performed by the Analysis Services administrator. Unlike the SQL Server database engine, Analysis Services never auto-registers its SPN at service startup. Manual registration is required when Analysis Services runs under the default virtual account, a domain user account, or a built-in account, including a per-service SID.”
This is because you need to create a SPN in AD for the account used by the service. Unfortunately, the documentation from Microsoft that describes what to do has a typo and there are some other issues, https://msdn.microsoft.com/en-us/library/dn194200.aspx#Anchor_3.
In the "SPN registration for a domain account" section the first slash should be a forward slash not a back slash or you will get a syntax error. So, instead of:
Setspn –s msolapsvc.3\AW-SRV01.Adventureworks.com AdventureWorks\SSAS-Service
It should read:
Setspn –s msolapsvc.3/AW-SRV01.Adventureworks.com AdventureWorks\SSAS-Service
To see the SPN mappings as they are, type:
Setspn -L <domain account> or
Setspn -L <server name>
-either one or both may or may not have entries listed so you need to execute both to understand your config.
The lines beginning MSOLAPSvc may or may not exist. In one case they existed but I had to delete and recreate them to be able to log in to SSAS remotely by SSMS.
To delete them you need to type NOT copy and paste the following, at least that is what worked for me.
Replace the redacted part with the FQDN of your server plus the domain account the service will run under. E.g. MSOLAPSvc.3/dbserver1.company.local company\sqlagentact
And you should see this:
Do the same for the non-FQDN entry.
Now both entries have been deleted type the same thing or press up arrow and change the -D to -S to create the entries. You could also use -A but -S checks for dupes and is safer.
Do the same for the non-FQDN entry.
Restart the SSAS service and you should be able to connect to it remotely using SSAS.
Rollback is easy, just change the service to use [NT Service\MSSQLServerOLAPService]. You will have to type this because it you can browse for it. However, once you have started to use the local account you need to ensure that the MSOLAPSvc.3 property is associated with the server name not the domain account you are no longer using, or you will get the same error again that you see at the top of the page.
IMPORTANT
NB If you need to delete an SPN using setspn -D MSOLAPSvc.3/dbserver1.company.local company\sqlagentact while the Windows service is running (this applies to MSSQLSvc as well) it may appear that you have deleted it but in fact it hasn't. When you restart the service the SPN will appear back in AD.
The Windows service, whether it is SQL or OLAP needs to be stopped when the SPN is deleted to ensure that it actually gets removed from AD.
Appendix
Useful links
Comments
Post a Comment