SQL Server Troubleshooting: Master Data Services Installation and Configuration

  • Article

When you install and configure Master Data Services (MDS) in SQL Server 2012 and 2014, you might encounter any of the following issues.

 

Fixing an MDS installation in a cluster (SQL Server 2012 only)

If you install a clustered instance of the RTM version of SQL Server 2012 with the Master Data Services checkbox selected, MDS will be installed on a single node, but it will not be available and will not work on additional nodes that you add to the cluster. To resolve this issue, you must install the SQL Server 2012 Cumulative Release 1 (CU1), performing the following steps:

  1. Make sure that there is no existing SQL/MDS installation.
  2. Download SQL Server 2012 CU1 into a local directory.
  3. Install SQL Server 2012 with the MDS feature on the primary cluster node, and then install SQL Server 2012 with the MDS feature on any additional cluster nodes.

For more information about the issues, and information about how to perform the above steps, see KB 2683467.

 

Error when connecting to a remote database server

When you try to create or configure a Master Data Services database on a remote computer, you might encounter the following error message:

  • Unable to connect to server: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

This occurs when you attempt to connect to a remote Database Engine instance from Master Data Services Configuration Manager and the instance is not configured to allow remote connections. To enable remote connections on the database server computer:

 

Database creation fails when user account cannot be resolved

 When you try to create a Master Data Services database, you might encounter one of the following error messages:

  • Could not obtain information about Windows NT group/user 'domain\account', error code 0x5.
  • Unable to look up specified user account.

 

This issue occurs when the service account for the SQL Server service, MSSQLSERVER, has inadequate permissions to query Active Directory and cannot resolve the domain account of the user who is attempting to create the database. Ensure that the service is configured to use a service account that has domain access. For more information about the types of service accounts and access they have to domains, see Setting Up Windows Service Accounts.

 

Database creation fails with system catalog error

 

When you try to create a Master Data Services database, you might encounter the following error message:

  • Ad hoc update to system catalogs is not supported.

    This issue occurs when the allow updates parameter of the sp_configure stored procedure is set to 1. To resolve this issue, set the allow updates parameter to 0 and then attempt to create the database again:

sp_configure ``'allow updates'``, ``'0'

RECONFIGURE ``WITH OVERRIDE

 

For more information, see sp_configure (Transact-SQL) and RECONFIGURE (Transact-SQL).

 

Database creation fails with error “Invalid column name ‘ProductName’. Invalid column name ‘ProductVersion’.”

 

When you try to create a Master Data Services database, you might encounter the following exception:

  • Invalid column name 'ProductName'. Invalid column name 'ProductVersion'.

 

This issue occurs when there is a non-Master Data Services database on the same Database Engine instance that contains a table named tblSystem. As a workaround, temporarily rename the table until the Master Data Services database is created or create the Master Data Services database on a different Database Engine instance.

 

Database creation fails with error “Data is Null. This method or property cannot be called on Null values.”

 

When you try to create a Master Data Services database, you might encounter the following exception:

  • Data is Null. This method or property cannot be called on Null values.

This issue occurs when another database on the same Database Engine instance is either offline or in the process of being restored. When you create the Master Data Services database, make sure that all databases on the same Database Engine instance are online and not being restored.

 

Error when accessing the Web service

 

When you try to access the Master Data Services Web service, you might encounter the following error message:

  • Service 'service_name' has zero application (non-infrastructure) endpoints. This might be because no configuration file was found for your application, or because no service element matching the service name could be found in the configuration file, or because no endpoints were defined in the service element.

This occurs when Web services have not been enabled. For more information, see How to: Enable Web Services (Master Data Services).

 

 

SVC Handler mapping error in MDS Configuration Manager

 

When you configure Master Data Services in the MDS Configuration Manager, you may encounter an error indicating that the required .svc handler mappings are not installed in IIS.

Before creating an MDS web application, you must correct the following errors:

  • Internet Information Services (IIS) is not configured on this server.
  • The required .svc handler mappings are not installed in IIS.

  

This can occur in one of the following instances:

  1. IIS may be missing the necessary .svc Handler Mappings. Visit IIS Manager from the start menu. If you have a default website listed, click the site, and click Handler Mappers icon. In the list, sort by Path column. notice if *.svc is listed or not. If it is already listed, this error may a false positive, but if it is not, you may need to install the *.svc path mappers in Step 2 & 3 below.

  2. Depending on the IIS version, there are different ways to get the Service Mappers installed. For .Net 4.0, the mappers may need to be installed by installing HTTP activation in .Net Framework 4.6 Advanced Services.  From the start menu search or control panel, find the item Turn Windows Features Off or On.  then in the tree of Windows Features find .Net Framework 4.6 > WCF Services > HTTP Activation

  3. On earlier Windows versions where .Net Framework 4.0 does not come preinstalled, when you add or install IIS features after .NET 4 was installed, you may encounter this error. To fix this issue, open a command prompt and go to the .NET directory (for example %windir%\Microsoft.NET\Framework64\v4.0.30319). Run the command: **aspnet_regiis -i. **

  4. You may encounter this error as a false positive or false negative when there is an existing IIS website(s) already existing in your IIS server, but you select a different Web site to host MDS, or when you select a Web site other than the first Web site listed. This can happen in following two ways:

    1. Service handlers are not installed on the first Web site that MDS is not attached to, but are installed on another Web site that MDS is attached to. MDS will post an error because it looks at the first Web site, not the second Web site. This is a false negative, in that the error message indicates that the handler mappings are not made, whereas they actually are. In this case, you can ignore the error and proceed.
    2. Service handlers are installed on the first Web site that MDS is not attached to, but are not installed on another Web site that MDS is attached to. MDS will not post an error because it looks at the first Web site, not the second Web site. This is a false positive, because the lack of handler mappings will cause MDS not to work. You should install the handler mappings for the Web site that you are attaching MDS to.

     

 

HTTP 401.2-Unauthorized

 

When you try to access Master Data Manager, you might encounter an HTTP 401.2-Unauthorized error. This can occur for the following reasons:

  • Windows Authentication is not configured on the Web server computer. For more information, see Web Application Considerations (Master Data Services).
  • Windows Authentication is not enabled for the Master Data Manager Web application in Internet Information Services (IIS).

For more information, see Error message when you try to visit a Web page that is hosted on IIS 7.0: "HTTP Error 401.2 - Unauthorized" on the Microsoft Support Web site. For more information on enabling Windows Authentication, see http://social.msdn.microsoft.com/Forums/en/sqlmds/thread/56bcb386-cbe4-4ff1-bc8a-25f0820952f7.

 

HTTP 404.3 - Not Found

 

When you try to access the master data web service - http://.../Service/Service.svc url you may get the error

  • HTTP Error 404.3 - Not Found

    The page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map.

You may also see the following error in MDS Configuration Manager:

  • The required svc handler mappings are not installed in IIS.

 

To fix this issue, check the Handler Mappings in IIS Manager and if the *.svc handler is missing, the resolution is to follow http://msdn.microsoft.com/en-us/library/ms752252(VS.90).aspx

If you installed IIS after Windows Communication Foundation (WCF) was installed, you can run the following command to correct the missing WCF handler mappings.

"%WINDIR%\Microsoft.Net\Framework\v3.0\Windows Communication Foundation\ServiceModelReg.exe

For more information, see SVC Handler mapping error in MDS Configuration Manager.

If using Windows 8, you should instead make sure that the .Net 4.5 WCF services features are installed, and HTTP Activation feature is installed, as shown highlighted here:

 

HTTP 500.19-Internal Server Error

 

When you try to access Master Data Manager, you might encounter the following error:

  • HTTP 500.19-Internal Server Error: The requested page cannot be accessed because the related configuration data for the page is invalid.

 

This occurs when a required role, role service, or feature is missing from the Web server computer. Ensure that the computer is configured properly for the Master Data Manager Web application. For more information, see Web Application Requirements (Master Data Services).

 

HTTP 500.21-Internal Server Error

 

When you try to access Master Data Manager, you might encounter the following error:

  • HTTP 500.21-Internal Server Error: Handler "PageHandlerFactory-Integrated" has a bad module "ManagedPipelineHandler" in its module list

 

This occurs when .NET was installed before IIS. To fix this issue, open a command prompt and go to the .NET directory (for example %windir%\Microsoft.NET\Framework64\v4.0.30319). Run the command: aspnet_regiis -i.

 

HTTP 503-Service Unavailable

 

When you try to access Master Data Manager, you might encounter an HTTP 503-Service Unavailable error. This can occur when there is a problem with the application pool that the Master Data Manager Web application runs in. In IIS, ensure that the application pool is running, and verify that the application pool identity is configured properly. For example, if the account used as the identity recently required a password change, update the application pool credentials accordingly. For more information, see Managing Application Pools in IIS 7 on Microsoft TechNet.

 

Upon Upgrade, MDS May Not be Displayed in Selected Features (SQL Server 2012 only)

 

When you upgrade the RTM version of SQL Server 2012 Master Data Services to the CU1 version, MDS will not be displayed on the Selected Features page in setup, even though MDS will be patched as expected. You cannot easily determine whether MDS CU1 is installed. When this occurs, you can see the MDS CU1 is installed as follows. 1. Upgrade the MDS database in the MDS Configuration Manager. 2. Open up SQL Server Management Studio, and open up the mdm.tblSystem table in the MDS database. 3. Check the SchemaVersion value. For the CU1 release, the schema version should be “11.0.3.0”.

 

Extended Protection settings can cause problems with MDS Explorer

 

When you launch the MDS Web site, and click on Explorer, you can get an error condition in which the data is not displayed, and you get an error message indicating that the extended protection settings configured on IIS do not match the settings configured on the transport.

This occurs when Extended Protection is turned on, and its value is set to Always. To turn Extended Protection on or off, proceed as follows:

  1. Open the IIS Manager.
  2. Select the Web application where MDS is deployed from the tree on the left.
  3. Right-click on Authentication in the center pane, and select Open Feature.
  4. Make sure Windows Authentication is enabled and selected.
  5. Click Advanced Settings in the Actions pane on the right.
  6. Set the Extended Protection (Off by default) to either Accept or Required.

 

Error while trying to enable integration with Data Quality Services when running on a Domain Controller

In MDS Configuration Manager, when you click the button [Enable integration with Data Quality Services] , it may fail with an error when the server is a Windows Domain Controller (DC).  The error is:

 "Error while trying to enable integration with Data Quality Services. SQL Server returned the following error: Windows NT user or group 'ComputerName\MDS_ServiceAccounts'  not found. Check the name again.

The cause of the problem is that MDS has created a global domain group called MDS_ServiceAccounts, and is trying to put the group into the DQS security role. Instead it should use the domainname\MDS_ServiceAccounts. SQL only allows windows user names (not group names), and MDS is confusing it about group vs. user.  This is not fixed in the SQL Server, so use an easy workaround.

Manual Workaround:

1. Manually add DOMAIN\MDS_ServiceAccounts as a Windows User login into your SQL Server security.

Query to run in Management Studio. Replace the placeholder DOMAIN with your own domain name. 

use [DQS_MAIN]

GO

IF NOT EXISTS (SELECT * FROM SYS.SYSUSERS WHERE NAME = 'MDS_ServiceAccounts')

CREATE USER [MDS_ServiceAccounts] FOR LOGIN [DOMAIN\MDS_ServiceAccounts]

 

2. Map the login to access DQS_MAIN role dqs_administrator which in effect is the same thing that the button does.

Query to run in Management Studio:

USE DQS_MAIN

GO

EXEC sp_addrolemember @rolename=N'dqs_administrator',@membername=N'MDS_ServiceAccounts'

// It will call - ALTER ROLE [dqs_administrator] ADD MEMBER [MDS_ServiceAccounts] within the DQS_MAIN database.

Now the MDS_ServiceAccounts are part of the dqs_administrator role, and MDS can interface with DQS on the same SQL Server instance.

 

Error while trying to enable integration with Data Quality Services when running in a distributed configuration

In MDS Configuration Manager, when you click the button [Enable integration with Data Quality Services], it may fail with an error when the IIS server is running on a separate machine from the SQL Server.   The error is:

"Error while trying to enable integration with Data Quality Services. SQL Server returned the following error: Windows NT user or group 'ComputerName\MDS_ServiceAccounts'  not found. Check the name again.

The cause of the problem is that MDS has created a local security group called MDS_ServiceAccounts on the IIS computer, but configuration manager is trying to put the group into the DQS security role on the SQL Server, where it does not exist. This is not yet fixed, so use a workaround.

Manual Workaround:

1. On the SQL Server computer, using Windows Computer Manager, Local Users and Groups, create a new local group called MDS_ServiceAccounts.

2. Using IIS Manager, locate the domain account identity used to run the MDS Application Pool. 

In Computer manager, add the domain account into the MDS_ServiceAccounts group on the SQL Server machine.

3. Manually add the new group SQLServerComputer\MDS_ServiceAccounts as a Windows User login into your SQL Server security. In this query, replace the placeholder SQLServerComputer with your own computer name. 

use [DQS_MAIN]

GO

IF NOT EXISTS (SELECT * FROM SYS.SYSUSERS WHERE NAME = 'MDS_ServiceAccounts')

CREATE USER [MDS_ServiceAccounts] FOR LOGIN [SQLServerComputer\MDS_ServiceAccounts] 

 

4. Map the login to access DQS_MAIN role dqs_administrator which in effect is the same thing that the button does.

Query to run in Management Studio:

USE DQS_MAIN

GO

EXEC sp_addrolemember @rolename=N'dqs_administrator',@membername=N'MDS_ServiceAccounts'

5. Now the button should work from Master Data Services Configuration Manager by clicking [Enable integration with Data Quality Services].

"Successfully integrated with Data Quality Services"

 

Service Broker error while upgrading the Master Data Services schema

When you try to upgrade the MDS database schema using MDS Configuration Manager after restoring the MDS database on a computer from a backup, you might receive the following error:

"Cannot be enabled because there is already an enabled Service Broker with the same ID."

This happens if the computer on which you restored the MDS database already has another copy of the database restored. When you restore the most recent copy of the MDS database from another computer to the current computer, SQL Server disabled Service Broker, which is an expected behavior. Subsequently, when you try to upgrade database schema the newly restored database, the MDS Schema upgrade process could not use the same Service Broker GUID as it was used by other copy of database restored from the same computer earlier, and resulted in an error. 

To resolve this issue, you must generate a new GUID for the newly restored database by executing following command:

USE [master]

GO

ALTER DATABASE MDS_DB SET NEW_BROKER

GO

Next, upgrade your MDS database schema.

See Also

 

Other Languages

This article is also available in the following languages.

Brazilian Portuguese (pt-BR)