Skip to main content

How to configure the SSAS service to use a Domain Account

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

Popular posts from this blog

How to create a custom Windows Event Log view and email trigger

The filtering on Windows event logs can be slow, clunky and although you can do it on fields like event ID, it seems that many event IDs are shared amongst many different errors – the event ID may match but the body of the error (therefore the actual error) may be completely unrelated. Fortunately, it is possible to filter on the contents of the body of the error message but it requires creating a custom XML query. Also, it would be handy to send out a notification email when this event gets logged. Read on to find out how to work this magic…. This example is looking for a  Warning  event  1309  for  ASP.NET 4.0.30319.0  on a web server. If you were to just filter the log on the criteria above today it would return 435 results because it is a fairly general error ID. If I filter it using XML for SqlException (what I’m really interested in) only 5 results are returned. So the first step is go to the Application Log and choose  Create Custom View…  Select the  XML  tab, check  Edit

How to import a large xml file into SQL Server

(Or how to import the StackOverflow database into SQL Server) Introduction NB  This process can be generalised to import any large (>2G) xml file into SQL Server. Some SQL Server training you can find online including that by Brent Ozar uses the StackOverflow database for practice. The tables from it are available online for download in xml format. In the past it was possible to use the scripts found here, https://www.toadworld.com/platforms/sql-server/w/wiki/9466.how-to-import-the-stackoverflow-xml-into-sql-server , to import them but as each xml file is now over 2GB you will get an error like this when you try to execute them: Brent Ozar, has a link to SODDI.exe, https://github.com/BrentOzarULTD/soddi , which can import the files (I haven’t tried it) but it means downloading and importing eight tables: Badges, Comments, PostHistory, PostLinks, Posts, Tags, Users, and Votes tables which amounts to >30GB of compressed xml increasing to ~200GB when decompre