Skip to main content

Analysis Services Connector will not work in a One Way Trust – Possible workaround

Headshot of article author Adam Saxton

NOTE: Information good as of 6/16/2015 and is subject to change.

We had a scenario come up when using the Analysis Services Connector.  In the scenario, the customer was restricted by a One Way Trust.  Their domain (Domain A) trusted users in Domain B, but Domain B didn’t trust users from Domain A. 

This worked fine for most of their scenarios.  They deployed the AS Connector on a Tabular instance which resided in Domain A.  When trying to use this from Power BI for a user in Domain B, they would see something similar to the following.

SNAGHTMLa7a0896

Power BI can’t connect to the On-Premises Analysis Services Server <server name>. 

SQL Server Analysis Services Connector is unable to connect to the Analysis Services instance.  Please make sure the Analysis Server instance is up and running.

For this walkthrough, I will use the following domains:

battlestar.local – Domain A

guyinacube.com – Domain B

So, people using Power BI are coming from guyinacube.com (Domain B) and want to use the Analysis Services Server on battlestar.local (Domain A).

Looking into the Data Management Gateway Event Log, we do see an error, but it isn’t much more helpful from the generic error we see above.

Log Name:      Data Management GatewaySource:        Data Management Gateway Host ServiceDate:          6/5/2015 10:15:27 AMEvent ID:      10100Task Category: NoneLevel:         ErrorKeywords:      ClassicUser:          N/AComputer:      BSGalactica.battlestar.localDescription:Open MSOLAP connection failed. The connection string is Provider=MSOLAP.5;Data Source=bsgalactica;transport compression=None;protocol format=XML;effectiveusername=asaxton@guyinacube.com;sspropinitappname=PowerBI. Activity ID: e1e8146d-9683-4bb0-af61-e02b6833029b

The AS Connector makes use of the connection string property called EffectiveUserName to work on behalf of the user from Power BI. We can try taking the AS Connector out of the picture and verify if this even works trying to connect with Management Studio.

SNAGHTMLa821b4c

The security database on the server does not have a computer account for this workstation trust relationship.

So, this is an issue with how we implement EffectiveUserName.  It doesn’t play nice with a One Way Trust. This means that we can’t use the AS Connector in a One Way Trust configuration.  This is just a limitation. 

I started looking to see if we could possibly work around this somehow.  I stumbled across a few articles discussing selective authentication. Most of these were pretty old.  Referencing Windows 2003.

Enable selective authentication over a forest trusthttps://technet.microsoft.com/en-us/library/cc758152(v=ws.10).aspx

From what I could tell, I could end up setting up a Two Way Forest Trust using Selective Authentication and this would not allow the accounts in Domain A to have access to Domain B by default, and vice versa.  Instead you have to be intentional about what you want to be allowed access to resources.  To test this, I removed the One Way trust I had setup and re-added a Two Way Forest Trust. 

NOTE:  This was done on Domain A.  Be sure you are doing this from the right side of the equation otherwise Incoming and Outgoing swap and it can be confusing.

The first option to select was with regards to Domain A – Local Forest.  In this select, we are saying that the users in Domain B can be authenticated to use all of the resources in the local forest (Domain A).  This is really what we wanted to accomplish with the One Way Trust.  So, we select Forest-wide Authentication here.

SNAGHTMLa8c5305

The next step is what happens on Domain B (specified forest).  Users in the local forest (Domain A) can be authenticate to use resources in the specified forest (Domain B).  For this one we can choose Selective authentication.  This means that users from Domain A would need to be added specifically to resources in order to use them.  This is done by way of the Allowed to Authenticate right.  I’ll talk about that later on.

SNAGHTMLa8d0926

Now that this was done, I was then able to use the AS Connector from Power BI.  The Models showed up correctly and also Management Studio was able to connect with using EffectiveUserName.

SNAGHTMLa943fa5

What if I wanted to go the other direction, or use Selective Authentication on both sides of the Trust?

To actually configure accounts properly to use Selective Authentication properly, we need to make use of the Allowed to Authenticate right on accounts. This took a little trial and error as documentation on how to do this was either very slim to none, or not very descriptive to understand how to do this.

In this case, I reset my Two Way Trust to use Selective Authentication for both ends.  When I do that, and I try to connect with Management Studio using EffectiveUserName, I get the following error.

SNAGHTMLa983f05

The computer you are signing into is protected by an authentication firewall.  The specified account is not allowed to authenticate to the computer.

This is a very specific error and the firewall term in this case is the Selective Authentication selection.  From what I could find, this is the only time you would see this kind of verbiage in an error.  This means that we have Selective Authentication selected, but have not granted access to the resource for the user in question.  Which in this case is asaxton@guyinacube.com by way of the EffectiveUserName property.  Selective Authentication is the Authentication Firewall.

Allowing an Account access to a Resource

So, we want to allow asaxton@guyinacube.com access to our Analysis Services Tabular Instance so we can use it within Power BI. To do this, we need to give asaxton@guyinacube the Allowed to Authenticate right on that resource.

Everything I could find on this kept saying to do this on the Computer Account.  I tried that, but that didn’t work.  I still got the same error.  I did it on the Computer Account that the Analysis Services instance resided on within Domain A. 

I then found an obscure reference to say it needed to be on the account that hosted the service.  Very similar to how Kerberos SPN’s work.  So, I went ahead and added the asaxton@guyinacube account the Allowed to Authenticate on the service account which was BATTLESTAR\OLAPService within Domain A.

SNAGHTMLa9f0594

Once I did that, I was able to connect in Management Studio using EffectiveUserName.  Power BI also worked when this was done.

SNAGHTMLaa17c70

Wrapping up…

Hopefully this helps you with regards to the inability to using a One Way Trust when trying to use the Analysis Services Connector or even just EffectiveUserName.  It may not fit for your overall security scheme, but it does appear to be a possible valid workaround for you to get this up and running.

 

Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services@GuyInACube | YouTube | Facebook.com\guyinacube