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…
I was asked to do a review of the book Microsoft Power BI Performance Best…
This is a quick blog post, mainly so I have the code available if I…
Praise whatever deity you believe in, because it's finally here, a tenant switcher for Microsoft…
This book was making its rounds on social media, and the concept seems interesting enough…
I'm hosting a free webinar at MSSQLTips.com at the 19th of December 2024, 6PM UTC.…
The slides and scripts for my session "Tackling the Gaps & Islands Problem with T-SQL…
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?
My company has MDS in an Azure Virtual Machine, so I suppose it works :)