Performing a clean installation of SQL Server 2012 SP1 on the (new) production servers seems to have resolved a number of issues relating to MDS.
Here’s a summary of what I have learned:
- It may be safer if you have a DB engine instance on the server where you install MDS. I suppose you could remove it afterward if you don’t need it.
- It may be safer to create the MDS web application within the IIS default site, rather than assigning it to its own site. This may, however, not be important — see next items
- If you create an instance of the MDS web application using the MDS configuration tool and then remove it using IIS Manager, something seems to be “left over” that interferes if you try to create the web app again in the same place. I didn’t have time to figure it out, so I removed all instances of the app and then uninstalled MDS itself, cleaning out the remaining temp folder in the installation directory afterward. I then reinstalled MDS, re-created the web app, and everything worked.
- When you create an instance of the MDS web application, the “Apply” button should become active after you have specified the IIS location for the app and the database connection. If it does not, you will not be able to complete automatic configuration of the web app, and you may be in for a lot of fiddling to make it work, unless you uninstall and reinstall MDS as I did above. When you click “Apply” the config tool completes its work and then offers to take you to the site. If you don’t see this final prompt, the web app is probably not going to work right, if at all!
- If permissions become corrupted for an MDS user, it should be possible to fix it using the MDS web app. What I finally had to do was completely remove the user, which meant deleting all privileges for that user, deleting the user itself, and deleting all active directory groups (from MDS) that the user belonged to. At that point all the user could see on the MDS website was “Access denied,” and attempting to access the website did not re-create the MDS user. When you reach that point, the database is clean with respect to that user and you can hopefully then re-create it without further problems.
- Use IIS 7.5/ Windows Server 2008 R2 or up for the MDS web server if possible. Getting Silverlight to work in the MDS web UI with earlier versions can be tricky (although again, this could have been due to the configuration tool not completing its work as described above). When I installed clean on Windows Server 2008 R2, all of the UI features started to work the way they were supposed to. Note that you can create several instances of the web app and point them to different database servers. Now that I have a good web server for doing this, I am going to host the MDS page for my dev server there as well as production.
- It is somehow possible for MDS group permissions to override individual ones, even though they should be additive. That seems to be the real reason why my working user identity kept losing its special MDS privileges. Apart from its own permissions, it inherits permissions from a global group that grants read-only permissions. None of my other users had a problem because they were not members of the global group. This was never an issue before SP1, and appears at this point that it might be a bug introduced with SP1.
This may not apply across all MDS permissions. The specific issue that keeps showing up is losing access to the “Import” page, which uses Silverlight. My user seems to have retained update privileges on the model, this time, anyway. I haven’t done any further investigating, because I have to this thing up and running. I will deal with the loss of the global group later.