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…