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

Fun and games with the Management Data Warehouse (MDW and Data Collectors)

The SQL Server Management Data Warehouse (when you first come across it) seems to promise so much if the verbiage from Microsoft and some other websites is to to believed. But when you install it you may find that it is not as useful as it could be. This is a shame but we are currently only on v2 of the product with SQL 2012 so one hopes it will improve in subsequent versions. However, it probably is worth playing with if you have never used it before - at least you can show your boss some reports on general server health when he asks for it and you have nothing else in place. There is one big problem with it though if you decide that you don't want to use it any more, uninstalling it is not supported! Mad, I know. But as usual some very helpful people in the community have worked out, what seems to me, a pretty safe way of doing it. I had a problem with my MDW. The data collector jobs were causing a lot of deadlocking on some production servers and impacting performance. I...

SAN performance testing using SQLIO

Introduction This document describes how to use Microsoft’s SQLIO to test disk/SAN performance. It is biased towards SQL Server – which uses primarily 64KB and 8KB data pages so I am running the tests using those cluster sizes, however, other sizes can be specified.  Download SQLIO from https://www.microsoft.com/en-gb/download/details.aspx?id=20163   SQLIO is a command line tool with no GUI so you need to open a command prompt at  C:\Program Files (x86)\SQLIO  after you have installed it. Configuration First of all edit param.txt so that you create the test file we will be using. The file needs to be bigger than the combined RAID and on-board disk caches. In this case we are using a 50GB file. The “ 2”  refers to the number of threads to use when testing, you don’t need to change this now. The “ 0x0”  value indicates that all CPUs should be used, which you probably don’t want to change either, “ #”  is a comment. The o...