Tag: Security

Something Went Wrong When Connecting Power BI to SSAS

Published February 2, 2015

One of the more powerful features in the new Power BI preview is the ability to connect your Power BI dashboard to an on-premises instance of SQL Server Analysis Services. This is done by installing a connector on-premises that connects to bot the SSAS server(s) and to the Power BI service. Installation is straightforward, but quite often the first attempt to connect to the data results in the “oh-so-helpful” error message, “something went wrong”.

The message isn’t very helpful, unless you’re speaking with a support representative.

I have found that the problem in most cases is that the user connecting to the data does not have sufficient rights on the SSAS server, or the server does not understand who the user is. This may very well be the same user that was used to connect from the SSAS Connector back into the service, but that doesn’t matter. The problem is that SSAS does not know about that identity.

To explain, first, we need to consider how the connector is registered.

When the connector is registered, two sets of credentials are provided. One credential is used to connect the connector to the SSAS server. This is an Windows credential (typically in the form of DOMAIN\username, and it must be an Administrator on the SSAS server. The reason for this requirement is that it will be used to funnel all Connector queries to the SSAS server, and it uses the EFFECTIVEUSERNAME feature in SSAS. EFFECTIVEUSERNAME requires admin level access.

The second credential is used to connect the connector to the Power BI service. This one is used to register the connector with the service so that it can be used by dashboard authors, and isn’t extensively used afterward. This credential will be an Organizational Account (i.e. an Office 365 identity/Azure Active Directory) and needs to have enough rights to register a data source with Power BI.

Once registered, it works as follows.

When a dashboard user interacts with the dashboard, or accesses the data source, a request is sent to the connector with the credentials of the user making the request. The connector then establishes a connection with the SSAS server, using the admin credentials registered with the connector, and issues the query using the EFFECTIVEUSERNAME parameter, which basically means “run this query using the provided user’s credentials”. The user provided is the one making the request. This allows for per-user level security for Power BI, but unfortunately, it is what typically causes the error above.

The issue is that SSAS only understands Windows (NTLM and Kerberos) credentials. Without doing anything else, it has no idea what an Organizational ID is. So how can it work at all? There are two ways.

The first, is that your domain can be federated with Azure Active directory, specifically with the Azure Active directory that your Power BI (Office 365) tenant is using. Once federation is complete, your AD domain (domainxxx.com) will be registered and trusted with your internal NTLM/Kerberos domain and your users will be mapped to their Azure ID identities. SSAS will then understand who they are, and if granted permission, they will be able to access SSAS data via the Power BI dashboard. This is the only supported method, and is what should be used in a production environment. There is however another way.

If AD federation is not an option in the short term, or you simply need to get a development or demonstration environment spun up, it is possible to “hack” your active directory to allow SSAS to understand the organizational IDs. First, the AD domain is registered directly with Active directory, then the Active Directory users can be set to use that directory. The key part is that the user name in the internal AD (ie DOMAIN\xxx) must match the user portion of the Azure AD account (ie xxx@domainxxx.com). The entire procedure is outline very well by Greg Galloway in this article, and I won’t repeat it here.

Going back to the original error, it would be nice if it could be a little more descriptive. I’d be happy with “Access Denied”. These are early days, and the product is still in preview – I expect this will change. Security also may not be the only cause of this error, but it’s the only one that I’ve seen thus far.

2 Comments

Enable LastPass and other Add-Ins in Windows 8.1

Published September 10, 2013

With Windows 8.1 hitting MSDN yesterday, I of course jumped on it and steamed right ahead. The upgrade was pretty smooth, but one of the things that I noticed was that LastPass wasn’t working in Internet Explorer anymore, the add-in was disabled. As it turns out, most add-ins get disabled by the new “Enhanced Protected Mode” in Windows 8.1.

I always hated protected mode on servers, and now I get to hate it on the desktop. The good news is that I can make it go away. It’s pretty easy. You need to get to Internet Options, which in IE is found in Tools-Internet Options, or by selecting the little gear icon in the upper right.

Then select the Advanced tab, and scroll down to the Security section. Finally, clear the checkbox next to “Enable Enhanced Protected Mode”.

Internet Properties window

Restart your browser, and your plugins will start working again. I’d be fine without the rest of them, but I need my LastPass.

With the new settings synchronization in Windows 8.1, at least you won’t have to do this on all of your devices, right? Wrong. This setting doesn’t synchronize across devices. Apart from this little hiccup, I love Windows 8.1.

4 Comments

Strong Passwords Are A Bad Idea

Published August 10, 2011

I’ve always been bothered by ridiculous password policies in many organizations. Frankly, they strike me the same way that many aspects of airport security strike me, which is to say that they are there to give you the semblance of security, but are in fact ineffective, and get in the way of doing something productive.

Mike Fitzmaurice, a friend of mine posted the following cartoon on his Facebook wall this morning, which I think really puts this into perspective.

This is taken from the humour site, xhcd.

The problem comes from policies that are developed without a true understanding of the underlying technology. This gets compounded with policies that require users to change their passwords periodically. So what happens when you are confronted with a situation like this? You take this password that is difficult to remember (and in some cases difficult to type) and you write it down on a piece of paper. No amount of instruction will prevent users from doing this – they’re really being forced to do this.

Some time ago I attended a security seminar where a representative from CSIS stated that 90% of all passwords are stored on paper within 5 feet of the computer that they access. The problem isn’t the strength of the password, it’s the strength of the user’s memory.

To me, these Byzantine security policies only serve to defeat their own purpose. Here’s my policy – choose a good password that you can remember, don’t write it down, don’t tell it to anyone, and unless compromised, never change it. I would urge many IT organizations to adopt their policies accordingly. Let’s not forget that the purpose of information technology is not to impose policies, but to support productivity.

What Domain Accounts to Use For SharePoint 2010

Published August 6, 2010

When setting up a SharePoint farm, whether 2007 or 2010, you have the option of providing various service identities throughout the process. Indeed, every application can run with its own identity. Far too often, administrators pick a single account, and use it for everything. While this is certainly the easiest approach, it is far from the most secure, and it can be very limiting down the road if you need to get granular with your permissions. The trouble is that there are a lot of intricacies as to what account does what, and getting it right requires a pretty comprehensive understanding of the product.

We now have enough work under our belt with SharePoint 2010 that I feel comfortable sharing some of our best practices around account creation for SharePoint 2010. The product itself has gotten more complex, and so therefore have the configuration options. There is no “one size fits all” approach for all scenarios, but the list that I am providing below should work as a good starting point. There is often a trade-off between the ease of manageability and providing good security, and the approach below,I feel,find a good balance.

The chart below describes the account, its purpose, what rights it needs to the local machines in the farm (including the SQL server machine(s), the rights it needs for SQL Server directly, and the rights it needs to the Active Directory domain.

Base Set of SharePoint 2010 Service Accounts

Account	Purpose	Local Rights	SQL Rights	Domain Rights
spSetup	Used to login to the farm servers Used to install bits on the farm servers	Administrator Remote Desktop Login	DB Creator Security Admin	Member
spFarm	Identity for all Windows Services Identity for all SQL Services (optional) Identity for Profile Synchronization Service Identity for all code running with elevated permissions (web parts)	None ⁽¹⁾⁽³⁾	DB Creator Security Admin	Member
spApps	Identity for all SP Application App Pools ⁽⁴⁾	None	None	Member
spServices	Identity for all SP Service Applications⁽⁴⁾	None	None	Member
spUPS	Identity for the User Profile Service	None	None	Member Replicating Directory Changes⁽²⁾
spCrawl	Used by the Indexer to crawl content	None	None	Member⁽⁵⁾
spBI	Trusted account for Reporting Services and PerformancePoint when not using Kerberos	None	DB Access as appropriate	Member
spSuperUser	Used for Object Caching	None	None	Member
spSuperReader	Used for Object Caching	None	None	Member

(1) Needs to be a part of the Local Administrators group while the User profile service is being created. See my previous post for more details. Once created, this account can be removed.

(2) AD Permission required by the User Profile service

(3) Required for a specific AD container when using the incoming email service. See this post for details on how.

(4) There may be a large number of these, one per entity

(5) Appropriate rights will need to be granted to this account for any EXTERNAL content being crawled (file system, shared folder, Lotus Notes, etc)

Hopefully this will help a few of you get started with a little less head scratching.

Opening PDF Files in SharePoint 2010

Published May 14, 2010

If you’ve installed SharePoint 2010, you may have noticed a change in behaviour of any PDF files that you may have stored. Previously, they would open directly in the browser, but now the user is prompted to save the file to the disk. This is due to a new security feature in IE8 that SharePoint 2010 respects. In order to allow the old behaviour, you must set the browser file handling options to “permissive” as opposed to “strict”.

This change is done at the application level. First, navigate to Manage Web Application within Central Admin, select the application in question, and choose General Settings on the ribbon.

Once in the settings screen, scroll down and find the section for Browser File Handling, and change it to permissive.

It goes without saying that this is less secure, but if you trust your PDF files, you should be good to go. Of course,as always,no warranties, express or implied…

Thanks to Mike Hacker’s Blog where I originally came across this.

UPDATE – SEPT 7 2010

I just ran across a case where this does not fix the problem. PDFs and all file types including HTML were prompting the user for download. This was not consistent, as it was happening in some libraries and not others. As it turns out, it’s a bit of a bug, and document libraries will not always inherit the browser handling attribute.

You can test this by running the following Powershell:

$site = Get-SPSite(“https://mysitecollection")
$web = $site.OpenWeb("/MyWeb")
$list = $web.GetList("https://myweburl/LibraryName")
$list.browserfilehandling

If it returns “Strict”, then you have a problem. The good news is, you can set it:

$list.browserfilehandling = “Permissive” ;
$list.update();

You should probably loop through your entire site collection and set this value to be safe. The powershell to do this can be found on Nerdtastics Tips, which is where I found the fix in the first place.

UPDATE – SEPT 10 2010

As opposed to hunting through your sites to find the problems, I wrote the below PowerShell script that take the URL for the site collection as an argument, and sets the permissive flag on any lists set to strict.

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$siteURL = $args[0]
$webname = $args[1]
$site = Get-SPSite($siteURL)

foreach ($web in $site.AllWebs) {
    Write-Host "Inspecting " $web.Title
    foreach ($list in $web.Lists) {
        if($list.browserfilehandling -eq "Strict") {
            Write-Host "Changing " $list.Title
            $list.browserfilehandling = "Permissive";
            $list.update();
            $site.url,
            $list.title,
            $list.browserfilehandling
        }
    }
}

UPDATE – OCT 5 2010

I’ve run into more situations where this doesn’t solve the problem. I created a new post describing them here.

18 Comments