Windows 7 Excel 2007 and connecting to SQL Server Analysis Services
The problem
A customer of mine has recently had a problem on one of the first Windows 7 machines he installed; the problem was that when trying to connect to a SSAS from Excel it kept asking for credentials.
So what was the problem with this machine besides the different OS, when all the other machines worked correctly?
Investigating the problem
I knew that the name the client was using to connect to SSAS wasn't the name of the physical machine where the SSAS service was running, but the name he wanted to use was correctly pointing to the server, and the proof were the several hundreds of other machines that were able to connect to the server, using that name. If I pinged the name the correct IP address was resolved. Another weird thing was that, if I tried the physical name of the machine it worked without problems. So I went to the DNS to check that name, and what I found was something like the following entry that was pointing to the correct address of the server:
At this point I knew that I had to get the big guns, so I went for Microsoft Network Monitor to try to figure out what was happening under the hood. I stated capturing traffic and tried to connect to the server, so it would ask me for the credentials again. Then, as I was experiencing an authentication problem I filtered the traffic by using the filter Authentication Filter that is one of the Standard filters in netmon, like the following:
// Returns any frame with authentication type traffic. KerberosV5 OR KerberosV5_Struct OR NLMP OR NLMP_Struct OR GssAPI OR SpnegoNegotiationToken OR GssapiKrb5 |
Looking at the following traffic we can see a Kerberos error “Kerberos: KRB_ERROR - KDC_ERR_S_PRINCIPAL_UNKNOWN (7)”. We also can see in the previous line “Kerberos: TGS Request Realm: … Sname: MSOLAPSvc.3/…”, this means that this is our problem, somehow we are not able to connect to the MSOlap SSAS Service using Kerberos authentication.
Analyzing our specific Kerberos error “KDC_ERR_S_PRINCIPAL_UNKNOWN”, the constant suggested an unknown principle name, so I went to find out what it really means, and by doing so I came across the following technet article https://technet.microsoft.com/en-us/library/cc772897(WS.10).aspx, that refers:
"0x7 - KDC_ERR_S_PRINCIPAL_UNKNOWN: Server not found in Kerberos database"
The next step was to check what were the SPN´s registered to the account running the SSAS service using the command setspn.exe in the command prompt:
C:\>setspn –L DOMAIN\USERNAME Registered ServicePrincipalNames for CN=UserName,OU=xxx,OU=xxx,DC=xxx,DC=xxx,DC=xxx: MSOLAPSvc.3/SSASSERVER_MACHINE_NAME MSOLAPSvc.3/ SSASSERVER_MACHINE_NAME.domain.com MSOLAPSvc/ SSASSERVER_MACHINE_NAME.mch.moc.sgps MSOLAPSvc/ SSASSERVER_MACHINE_NAME |
and then I really found the cause. As the message in the technet article referred there wasn´t a name registered in the Kerberos database for the host entry we have seen in the DNS.
The other XP machines that i referred earlier were able to connect to SSAS but for that they were using NTLM, because the windows client automatically tries to fail back to NTLM authentication for the user account. In this case for some reason the client was not able to do the same and as a consequence wasn’t able to authenticate.
Just out of curiosity this article describes how to enable Kerberos event logging https://support.microsoft.com/kb/262177/en-us, so you can find any Kerberos-related events in the system log. This could also have helped to troubleshoot this problem, because if the fail back to NTLM would fail, we should see an Kerberos event in the event viewer.
Resolution
The hard part was finding the problem, and it was done. To solve it we only have to register the host name we are trying to connect in the Kerberos database. To do so we have to use setspn.exe again, but this time to add the missing entries, like the following:
C:\>setspn -A MSOLAPSvc.3/SSASSERVER DOMAIN\USERNAME C:\>setspn -A MSOLAPSvc.3/SSASSERVER.domain.com DOMAIN\USERNAME C:\>setspn -A MSOLAPSvc/SSASSERVER DOMAIN\USERNAME C:\>setspn -A MSOLAPSvc/SSASSERVER.domain.com DOMAIN\USERNAME |
And there you are, problem solved.
Hope you enjoy.
See you next time!