SSAS Tabular Connector without Active Directory Sync?
There should be a way to use the SSAS Tabular Connector without AD Sync, e.g. by using CustomData on the connection string.
Thanks for the suggestion, Teo! Is anyone else in a position where they can’t use the SSAS Connector because they aren’t using AD Synchronization? If so, please add your vote here!
Ali Malekshahi commented
Any update on this? Can't wait for this feature to be available.
This works for smaller customers too. we have large customers who are trying to avoid IT costs to implement a solution. They already have access to the models & SQL Server but the AD requirement means they cannot progress with the tool. Automated updates are just expected by our clients and this would remove massive road blocks to entry
Luis Higueros commented
I think it's important to understand the use case scenarios. We currently host about 400+ customers in our own hosted environment. Each of these can have from 100's to 10 of thousands users. There is no domain trust or federated identity in place between the customers' domain and the hosted domain. Our customers are heavy OLAP and Tabular users - the cube security is not role based - it is dynamic security based on data grants that are driven down as the cubes are processed. Identity is generated thru use of CustomData leveraging a Proxy that intercepts calls to the HTTPS MSDMPUMP. With this approach, there is no need for management of cube roles, etc. We don't set any local accounts or set up roles on the cubes as it would be prohibitive to do - for the same reason we don't need to use Azure DirSync. Please let me know if you want more details -
You really don't need dirsync for this. Check if the result on whoami\upn works with SSMS. Try connecting to SSMS using that result as the effective user name. If that works, the Connector will work. If the SSMS test fails, the connector unfortunately as of now will not work!
We are looking into ways to provide custom mapping so that we do not have to rely on effective user name.
Eric E. commented
I am not sure I understand your scenario completely. It is not clear to me whether you have already installed the AS Connector to connect your tabular server to Power BI.
Take a look at this blog post - http://blogs.msdn.com/b/powerbi/archive/2015/03/11/power-bi-analysis-services-connector-deep-dive.aspx
If you have installed & configured the AS Connector but cannot use it with Power BI, let us know & we can help you out.
Bill Schmidt commented
I do not know for sure whether my problem is the same but I suspect so. I have done the following:
1. Set up a demo SSAS Tabular database in an Azure VM that is standalone (not connected with my company domain).
2. Made the demo SSAS Tabular database available externally by making its port static, opening up that port for inbound traffic, and creating an Azure endpoint.
3. Tested the connectivity from SQL Management Studio (which has to be opened using a "run as" command, e.g. runas /netonly /user:<<VM name>>\<<username>> “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”)
4. In Power BI, tried to create a SSAS Tabular data connection.
On (4), I ran into a blank screen. There appears to be no way to do, from Power BI, what I was able to do from SQL Management Studio - that is, to connect to this Azure-VM-hosted SSAS database.
We had thought the combination of an Azure VM (not domain connected) and some Power BI dashboards would be a perfect, easy and safe way to demonstrate our product to external users, without exposing our home network to hacking, etc. But this looks like it is going nowhere at the moment.
Can you comment on whether my problem is the same as what others here are seeing? And can anything be done about it?
John Young commented
See my prior comments. I wonder if Teo would be willing to alter this idea to not just be about working without Active Directory Sync, but to also address the scenario where your AAD UPN does not match your on-prem UPN thereby causing the connector to be unsuccessful when connecting to the on-prem SSAS server instance.
John Young commented
I am running in to a problem due to the fact that even though our company has turned on DirSync and we have associated our Office 365 tenant with our AAD account, our AAD account and our on-prem account are still two *different* accounts. Our AAD account uses a suffix that is publicly routable, but our on-prem account uses a non-routable account. Therefore our UPNs are different and the EffectiveUserName that is passed to SSAS on-prem appears as an invalid UPN/account to our on-prem server. Granted, I would like our organization to clean up the mismatch and make our on-prem UPN match the AAD UPN, but that is a massive undertaking. So I'm interested in alternative options that help us easily work around this. Maybe an alternative AAD attribute that would contain our on-prem UPN string could be something we configure in the connector setup. I'm not looking for the ability to put a single, trusted account into the string. I really want individual users to be authenticated. I just need the proper on-prem UPN to be passed through.
Ali Malekshahi commented
Can't wait for this feature.
Tableau currently offers connection to SSAS using hard coded Windows user name and password. It is sad to see that two of Microsoft products (Power BI or Power Pivot and SSAS) can't talk to each other!
Todd Chittenden commented
Yes, being able to specify different domain in the connection is key for us.
Pinak kakadiya commented
Even I am also getting error while using connector without ADsync. It would be great if we can connect on premise SSAS tabular models without AD sync on azure.
John White commented
Absolutely - and the need is greater than that. A proxy account may be required by designed, so passing user credentials may not help. Consider the scenario where I have an SSAS model that has no per-user security requirements - it's homogeneous to the entire organization. Managing users on that model could be cumbersome, and an unnecessary step. Or I may be an ISV running a reference model...... I may want to make it available to everyone - much like the way the demo dashboard is done in the current Power BI Preview.
Donald Parish commented
Trying with SQL Server 2014 Dev on same machine. Getting 400 error after entering friendly name.