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 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…

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. It looks…

SSIS Job fails when it calls Excel via the SQL Agent but succeeds from SSDT

If you have an SSIS package which fails when run on a schedule but succeeds when executed interactively in Visual Studio/BIDS/SSDT, it may produce an error like this:
Executed as user: DOMAIN\user. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  10:52:25  Error: 2017-02-06 10:52:26.26     Code: 0x00000001     Source: Open Spreadsheet and Run Macro      Description: Exception has been thrown by the target of an invocation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:52:25  Finished: 10:52:26  Elapsed:  0.485 seconds.  The package execution failed.  The step failed.
The issue is a type of permissions error. Excel needs to have its permissions changed via the DCOM applet in Control Panel. By default it is on “The launching user.” This needs to be changed to a user with more permissions, in this case we have used the service account used by SQL A…