Master Data Services Configuration Woes

Installing and configuring SQL Server Master Data Services (MDS) is like a box of chocolates: you never know what you’re gonna get. Recently I ran into some troubles (or woes) when configuring MDS at a client. The installation went smoothly, as well as the installation of other prerequisites (like IIS, PowerShell and so on). The MDS Server Configuration tool didn’t even display any error or warning, which was a first for me:

mdsconfig_01

After deleting the default website from IIS (MDS really hates it when its not the first website in the list) and creating the MDS website with its application pool, we were all set to go. At least that’s what I thought. When surfing to the MDS website, I was greeted with the simple message: Access is denied. A bit strange, since I was using the service account specified during MDS set-up (which has all the admin access). After a bit of searching, I disabled Anonymous Authentication and enabled Windows Authentication on the MDS website. Lo and behold, we could log into the MDS website.

mdsconfig_02

After a short moment of celebration, I started the configuration of the MDS Excel add-in. However, I was greeted with the following error message: “The requested service, …, could not be activated”.

mdsconfig_03

Also, when using the Explorer or Integration Management in the MDS website, there were strange errors from time to time:

mdsconfig_04

Or something useful as “The remote server returned an error: NotFound“. After some more digging, it turned out that the webservice and the Excel add-in need Anonymous Authentication. But we disabled it to make the site work. You can however enable it on the service level only:

mdsconfig_05

Now all the errors were gone and the Excel add-in could connect to the MDS service. And all was right again in the world…

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He’s also a speaker at various conferences.

2 thoughts to “Master Data Services Configuration Woes”

  1. I feel your pain … I attended a SQLSaturday where free online training was offered for MDS by Profisee. I started the install and the documentation mentioned requiring Windows Auth – unfortunately access to this setting appears to ONLY be available on Pro versions of the OS. All the machines I have access to have standard version of Windows (7 / 8 and 10), trying to see if Azure will support MDS now – any thoughts / comments?

Leave a Reply

Your email address will not be published. Required fields are marked *