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,
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\ AdventureWorks\SSAS-Service
It should read:
Setspn –s msolapsvc.3/ 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/ 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.


NB If you need to delete an SPN using setspn -D MSOLAPSvc.3/ 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.


Useful links


Popular posts from this blog

How to move the Microsoft Assessment and Planning Toolkit (MAP) database to a different drive

The Microsoft Assessment and Planning Toolkit (MAP) is a very useful tool for scanning your network to find instances of SQL Server plus all manner of detailed information about the installed product, OS and hardware it sits on.

<Click image to enbiggen>
There is an issue with it the database it uses to store the data it collects, however. Assuming you don't have an instance called MAPS on your server, the product will install using LocalDB (a cut down version of SQL Server Express) and puts the databases on your C: drive. If you then scan a large network you could easily expand the database to 10GB which may cause issues on a server when that drive is often one of the smallest. However, there is a simple solution: connect to LocalDB using Management Studio, detach the databases, move to a different drive, set permissions on the new location if required and reattach the database. How do you connect to LocalDB? Here you go:

Connect to (localdb)\MAPTOOLKIT

The databases I move…

The Purge SQL Agent Job for MDW takes a long time to complete

I use the dbWarden alerts to inform me if a SQL job is taking longer to complete than normal and I got one this morning:
I noticed by looking at the history this purge job was gradually taking longer and longer to complete each day since I installed it again (see mylast post on this):
RunDate RunTime Duration ExecutionStatus JobName 04/10/2013 02:00:01 00:00:45 Succeded mdw_purge_data_[MDW] 05/10/2013 02:00:00 00:13:27 Succeded mdw_purge_data_[MDW] 06/10/2013 02:00:00 00:17:03 Succeded mdw_purge_data_[MDW] 07/10/2013 02:00:01

Generate scripts to attach multiple databases

There is a handy little "by product", if you like, when running queries which means you can quickly generate scripts to do different things. Below is an example of generating multiple "attach" commands that you can copy from the results pane into the main SSMS window for execution. I have found this very handy in the past:

SELECT 'CREATE DATABASE ['+name+'] ON ( FILENAME = N''F:\MSSQL\Data\'+name+'.mdf'' ), ( FILENAME = N''E:\MSSQL\Log\'+name+'_log.ldf'' )  FOR ATTACH GO ' FROMmaster.dbo.sysdatabases WHEREnamenotin('master','msdb','model','tempdb')