Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

"No exact match was found" when adding users in MDS

Discussion in 'Products running on SQL Server' started by Peter Schmitz, Apr 21, 2017 at 8:42 AM.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    We recently ran into an issue with Master Data Services (MDS) at a customer when we attempted to add our user accounts in order to access MDS. The error thrown was "No exact match was found". Initially, we assumed this could have been due to the fact our user accounts were relatively new at the time (albeit a few hours old). We then attempted to add an older account but were met with the exact same error.

    I will describe what we did to resolve the issue, though most likely the final step would have done the trick. We just wanted to ensure we would have every way to resolve any potential issues popping up.

    Changing the MDS System Administrator account
    As it turns out, the specific instance of MDS had been installed several years before, by a contractor whose account had since been disabled. However, this contractor's account was still the MDS administrator (which you can figure out by querying the MDS database using the following query):

    Code (sql):
    SELECT * FROM mdm.tblUser WHERE ID = 1
    Note that there are two types of administrators in MDS; model administrators (who can create, change, or delete models) and a single System Administrator (which is the one we are after for this. There is exactly one (1) system administrator, and their account will have ID 1 in the tblUser table). More info about MDS Administrators can be found on Technet.

    Query the MDS table for the account of the person you wish to make the new administrator:

    Code (sql):
    SELECT * FROM mdm.tblUser
    Note down their full username, as well as their SID, as you will need them i the next step, where you assign that account to be the new sysadmin. This can be achieved by issuing the following query:

    Code (sql):

    EXEC [mdm].[udpSecuritySetAdministrator] @UserName='DOMAIN\user_name', @SID = 'SID', @PromoteNonAdmin = 1
    Replace 'DOMAIN\user_name' and 'SID' with the values you noted down before.

    However, this did not resolve the issue for us, as we were still unable to add new users to MDS.

    Ensure the user running the MDS application pool is a valid user
    The next step we took was ensuring that the account running the MDS Application pool in IIS on the server was a valid account. It took us a bit to find someone in the organization able to grant us access to IISAdmin, but once that was in place, we popped open IISAdmin, expanded 'Connections', and and then selected 'Application Pools'.

    We determined that MDS was running under the default Application pool (this might be different in your setup), selected that, and in the 'Action' panel on the right-hand side, selected 'Advanced Settings'.

    We then looked for the 'Identity' property, and clicked the Elipsis (...) button to pop open the 'Application Pool Identity' window, where we changed the user credentials. Once done, we started the MDS application in IIS.

    This finally allowed us to add new users.

    I hope this helps someone facing the same issue :)

Share This Page