Categories: MDS

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:

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.

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”.

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

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:

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…


------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂
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.

View Comments

  • 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?

Share
Published by
Koen Verbeeck

Recent Posts

Free webinar – Tackling the Gaps and Islands Problem with T-SQL Window Functions

I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…

5 days ago

dataMinds Connect 2024 – Session Materials

The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…

4 weeks ago

Connect to Power BI as a Guest User in another Tenant

Sometimes your Microsoft Entra ID account (formerly known as Azure Active Directory) is added as…

2 months ago

How to use a Script Activity in ADF as a Lookup

In Azure Data Factory (ADF, but also Synapse Pipelines and Fabric Pipelines), you have a…

4 months ago

Database Build Error – Incorrect syntax near DISTINCT

I wrote a piece of SQL that had some new T-SQL syntax in it: IS…

4 months ago

Speaking at dataMinds Connect 2024

I'm very excited to announce I've been selected as a speaker for dataMinds Connect 2024,…

5 months ago