These prerequisites are not installed: 64-bit version of Microsoft Excel 2010

I had all the prerequisites installed (.NET framework 4.6.2 and the Visual Studio 2010 Tools for Office Runtime) and the correct bitness. A quick search led me to this KB article:

FIX: Can’t install Master Data Services Add-in for Excel on a computer that has Microsoft Office 2013 installed

The article says you can get rid of the error message by using the 2012SP1 installer. Which I was using. Dead end. I also found this post at Katie&Emil: MDS Excel 2013 and 2010 Add-in Installation. Emil had the same issue and succeeded in installing the add-in by running the installer as admin through the command line. However, I still got the same error. That add-in must really hate Office 2016.

So I installed the SQL Server 2016 add-in which did install successfully, but apparently isn’t able to connect to SQL Server 2012. No backwards compatibility. I turned to Twitter and someone suggested me to install the 2014 add-in. Luckily, this one does install and fortunately has backwards compatibility with SQL Server 2012.

To wrap everything up:

• SQL Server 2012 with Office 2010 – MDS add-in for SQL Server 2012
• SQL Server 2012 with Office 2013 – MDS add-in for SQL Server 2012 SP1
• SQL Server 2012 with Office 2016 – MDS add-in for SQL Server 2014
• SQL Server 2014 – MDS add-in for SQL Server 2014
• SQL Server 2016 – MDS add-in for SQL Server 2016 (no backwards compatibility)
• SQL Server 2017 – MDS add-in for SQL Server 2017

I recently had to install SQL Server 2016 again on my Windows 10 machine. This also meant that I had to install and configure MDS as well. Last time it didn’t went so smoothly: Master Data Services error – Cannot read configuration file due to insufficient permissions. However, this time I had already applied Service Pack 1, so I had another error when I tried to launch the MDS website.

The error this time: “The ‘tempDirectory’ attribute must be set to a valid absolute path”. If you can’t see the error, it’s possible you have to enable them in the web.config file of MDS. Typically you can find this configuration file in the folder “C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication”. The customErrors attribute should be changed to the following:

<customErrors mode="RemoteOnly" />

Anyway, apparently a temp folder is missing. Cody Konior already warned for this bug. The error message at the website is kind enough to provide you with the exact location of the error in the web.config file. Funny that the comment above explains that the Configuration Manager is responsible for this temp folder. Someone has not done its job properly.

The resolution is simple: create a temp folder with the name specified in the web.config file at the specified location. Now you are greeted with the following error:

Of course the MDS Application Pool account doesn’t have write access to the newly created temp folder. This is also easy to fix:

Note that I assigned permissions to the MDS_ServiceAccounts group. I manually added the MDS Application Pool account to this group, because MDS also forgets to do this. Now you can finally browse to the MDS website!

I was preparing a demo for a session about the new features of Master Data Services in SQL Server 2016. In that demo, I created a business rule to test out the extension with custom scripts. When the business rule was finished, I tried to publish it. But, I was greeted with the following error: “300 – A database error has occurred. Contact your system administrator”.

Not the most helpful error message, especially when you are the admin yourself. The MDS team promised that in SQL Server 2016, troubleshooting would be easier. So I went and checked out their blog post What’s New in Master Data Services – SQL2016 CTP2 (May) Release, where they have a section Improved Troubleshooting and Logging.  There it is mentioned the web application logs to the following folder: C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication. In the log file over there, I could find the following – actual – error message:

VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’

Apparently during the publishing of a business rule, MDS wants to run a stored procedure called udpValidationIsRunning. This procedure wants to check the system view sys.dm_broker_activated_tasks and apparently the necessary permissions are lacking.

The fix is luckily quite easy: just run the following statement to grant the permission to the MDSAppPool security group:

USE MASTER;
GRANT VIEW SERVER STATE TO [yourmachine\MDSAppPool];

Now the business rule can be published without an issue.

UPDATE:

it’s possible to logs are located at the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\MDSTempDir. This might have been changed in a later CU.

In preparation of my upcoming webinar on the new features of Master Data Services 2016, I installed MDS on my system. Otherwise it would be quite hard to give demos, of course. The installation itself went quite smooth, no remarks there. I also enabled Internet Information Services (IIS) on my system, as well as a number of required prerequisites. Of course I forgot a few, but to be honest, the MDS Configuration Manager has become quite good at telling you which ones are missing.

Side note: Matt Smith has a PowerShell script in Github that will install all of the prerequisites for you!

@Ko_Ver https://t.co/tCrXeZlgAN

— Matt Smith (@matt40k) October 23, 2016

Next I had to install and configure the MDS database, which was quite painless, as usual. But then comes the tricky part: adding the MDS website. First I created a new website using the MDS Configuration Manager, but that did not work. At all. For some reason, IIS searched for the MDS website at …\inetpub\wwwroot\MDS, instead of at the MDS installation folder in Program Files. So I deleted the website and assigned MDS to the default website. That also didn’t work, but this time I got a different error:

The error seemed quite clear: Cannot read configuration file due to insufficient permissions. Just to be sure, I added the user MDSAppPool – created in the MDS Configuration Manager for the MDS Application Pool – to the Administrators group on the machine. A brute-force solution, but since it’s on my own machine for demo purposes, I didn’t really care. Of course it didn’t work. Then I assigned full control permissions for the MDSAppPool user on the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services. Didn’t work. Used the browser in Administrator modus. Also didn’t work. Checked IIS settings and discovered that Windows Authentication was not enabled. So I enable it, but the error persists. This is the point where it all starts to get frustrating. Adding MDSAppPool to the IIS_IUSRS group doesn’t work. Giving that group full control on the MDS directory either.

As a last attempt, I went directly to the web.config file and assigned MDSAppPool full control directly on that file, for which I had to pass numerous UAC verifications. But lo and behold, the MDS website worked!

Conclusion: if you encounter a permission issue on the web.config file, directly assign permissions to it for the MDS application pool user. Adding the user to the admin group does not work, for whatever reason.

I’ll be delivering two webinars for MSSQLTips.com in the coming months. The first one is about the – amazing – new features in Reporting Services 2016.

SQL Server Reporting Services is Microsoft’s corporate reporting tool. The SQL Server 2016 release marks one of the biggest evolutions of the product since it’s initial release. In this webcast we’ll give an overview of all those new changes. A couple of highlights:

• New chart types
• The parameter grid
• The brand new report portal
• The integration of Datazen, which means SSRS now supports mobile reports
• and more…

Join us in this session to discover why SSRS 2016 will blow your mind away!

This webinar will be hosted on Wednesday, October 26, 2016 at 6PM UTC (be careful with daylight savings time during that period
You can register here. If you can’t catch my session at the SQL Server Days next week, this webcast is the perfect opportunity to still learn about this subject.

The other webinar is about another tool of the SQL Server stack which finally has gotten some love: Master Data Services.

SQL Server Master Data Services is Microsoft’s master data management solution. The SQL Server 2016 release brings MDS to a higher level: usability and performance have been improved, security is more flexible and there are tons of new features. Some highlights of this webcast:

• How to sync entities
• Slowly Changing Type 2 support
• The new security and performance features
• How to use change sets
• and much more…

Join us in this demo-packed session to find out how MDS 2016 will improve your master data

The webcast will be held at Wednesday, November 09, 2016 at 8PM UTC. You can register over here.

Here is an overview of the articles I published in the second quarter of 2016, which were all focused on the new changes for Master Data Services in SQL Server 2016:

• Approval Workflow in SQL Server Master Data Services 2016 using Change Sets – Part 1
• Approval Workflow in Master Data Services 2016 using Change Sets – Part 2
• How to Merge a Conflict in SQL Server 2016 Master Data Services
• Member Revision History in Master Data Services 2016 – Part 1
• Member Revision History in Master Data Services 2016 – Part 2
• Using Data Compression in Master Data Services 2016
• Add a Custom Index in Master Data Services 2016

Very happy that MDS has gotten quite some love in the 2016 release.

In the pipeline: What’s new in SSIS 2016 and some articles about the new sample database Wide World Importers.

I recently came across the following error message when I tried to look at the batches in the Integration Management section of MDS:

515: A database error has occurred. Please contact your system administrator

Too bad I am the system administrator on that machine…

Anyway, after some searching it came out the MDS stored procedure udpEntityStagingAllBatchesByModelGet throws a bit of a temper tantrum if one of the batch tags used in the staging process of a leaf member is NULL. You can check this in the table mdm.tblStgBatch.

Simple delete the offending batches and you’re good to go!

DELETE FROM mdm.tblStgBatch
WHERE BatchTag IS NULL;

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…