Home data management mds sql server troubleshooting

Troubleshooting: SQL Server MDS Validation Command Fails - Causes and Solutions

Updated: • 0 • min read
Table of Contents

Troubleshooting SQL Server MDS Validation Command Failures

This article addresses a common issue encountered in SQL Server 2012 and SQL Server 2014 Master Data Services (MDS), where the Validate Version command unexpectedly fails, resulting in a server error. This problem can disrupt data quality management processes and hinder the smooth operation of MDS environments. Understanding the underlying causes and applying the appropriate solutions are crucial for maintaining data integrity and system stability. This guide provides a comprehensive overview of the symptoms, root cause, and effective workarounds to resolve this validation command failure.

Symptoms

Consider a scenario where you are managing a Master Data Services environment within SQL Server 2012 or SQL Server 2014. After successfully installing and deploying the MDS website, utilizing a dedicated application pool account for enhanced security and resource management, you might encounter an error when attempting to validate a version within MDS.

The steps to reproduce this issue are typically as follows:

  1. An administrator has installed Microsoft SQL Server 2012 or SQL Server 2014 MDS components.
  2. The MDS website has been deployed, configured to run under a newly created application pool account. This is a common practice to isolate the application and enhance security.
  3. Navigating to the MDS website through a web browser, you proceed to manage data versions.
  4. Within the MDS web application, you access the Manage Versions page, intending to perform data validation.
  5. You initiate the validation process by clicking the Validate Version command located on the toolbar, typically at the top of the page.
  6. In the validation dialog, you select the Validate checkbox specifically for the Model you wish to validate. This indicates you are validating the entire data model within the chosen version.
  7. Upon confirming the validation action by acknowledging the Are you sure that you want to validate this version? prompt and clicking OK, the process begins.

Instead of a successful validation, the web browser displays an error message, interrupting the workflow. This error message is typically presented as a “Server Error in ‘/’ Application.” The detailed error description within the message provides further insight into the nature of the problem. It indicates “An error occurred while processing message request type ‘ValidationGetRequest’,” suggesting an issue during the communication between the web application and the MDS service when requesting validation status.

The error message further elaborates with “Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.” This is a standard web application error message, pointing towards an unexpected problem during the execution of the web request.

The technical details continue with “Exception Details: Microsoft.MasterDataServices.WebUI.ServiceAdapterException: An error occurred while processing message request type ‘ValidationGetRequest’. See exception details for more information.” This clarifies that the error originates within the MDS web user interface (WebUI) component, specifically during the interaction with the MDS service adapter.

The “Stack Trace” section within the error message provides a detailed call sequence of the functions that were executed leading up to the error. Analyzing the stack trace, we can see the error originates in Microsoft.MasterDataServices.WebUI.ServiceAdapter.InspectResponseForErrors, suggesting an issue with handling the response from the MDS service. The call sequence progresses through ExecuteRequest, GetValidationStatus (called multiple times with different parameters), and eventually to LoadGrid and EvaluateSelectedVersion within the Microsoft.MasterDataServices.WebUI.Audit.Dimensions namespace, indicating the error is related to loading or displaying validation status within the dimensions audit functionality of the MDS web application. Finally, OnLoad and ProcessRequestMain show the standard ASP.NET page lifecycle events where the error bubbles up.

This detailed error message and stack trace are crucial for diagnosing the problem. The core issue, as hinted in the error message, lies within the permissions of the application pool account used to run the MDS website.

Cause

The root cause of this “Server Error” during the MDS validation command execution is often attributed to insufficient permissions granted to the newly created application pool account. Specifically, the account lacks the crucial VIEW SERVER STATE permission on the SQL Server instance hosting the MDS database.

When setting up an MDS website using the Master Data Services Configuration Manager, the utility prompts for the application pool user account credentials. Upon providing these credentials and selecting the MDS server and database, the configuration manager automatically grants a set of essential permissions to this account. These permissions are primarily focused on the MDS database itself and the local server environment.

The application pool account is granted various permissions within the specified MDS database, ensuring it can access and manipulate MDS data. Furthermore, the account is added to the MDS_ServiceAccounts local users group on the server, which is often required for general MDS service operations. It is also added to the mds_exec database role within the MDS database catalog, granting it execution rights for MDS stored procedures.

However, a critical permission that is often overlooked is the VIEW SERVER STATE permission. This server-level permission is not automatically granted by the MDS Configuration Manager to the application pool account during website creation. While some Windows accounts might inherently possess this permission in SQL Server due to broader administrative roles or group memberships, newly created, dedicated application pool accounts typically do not have it by default.

The VIEW SERVER STATE permission is essential for querying system-level dynamic management views (DMVs) that provide insights into the internal state of the SQL Server instance. In the context of MDS validation, this permission becomes crucial because the MDS web application internally relies on querying the service broker using the sys.dm_broker_activated_tasks DMV. This DMV is used to monitor the progress of background tasks, specifically the MDS validation process which is queued as a service broker task.

To check the validation progress, the MDS web application executes the stored procedure mdm.udpValidationIsRunning. This stored procedure, as part of its logic, attempts to query sys.dm_broker_activated_tasks to determine if the validation process is still running. However, if the application pool account executing this stored procedure lacks the VIEW SERVER STATE permission, the query against the DMV fails.

The error encountered within the stored procedure is “The user does not have permission to perform this action.” This error, although occurring within a stored procedure on the database, manifests as a “Server Error” on the web application because the web application is unable to retrieve the validation status due to this permission issue.

The specific line of code within the mdm.udpValidationIsRunning stored procedure that triggers the permission error is:

IF EXISTS (SELECT 1 FROM sys.dm_broker_activated_tasks
    WHERE procedure_name = N'[mdm].[udpValidationQueueActivate]')

This query attempts to check if a validation queue activation task is currently running. Without VIEW SERVER STATE permission, the application pool account cannot access sys.dm_broker_activated_tasks, leading to the failure and consequently, the inability to render the validation status webpage, resulting in the “Server Error” displayed in the browser.

Workaround

To effectively resolve this issue and enable the MDS validation command to function correctly, you need to manually grant the VIEW SERVER STATE permission to the application pool account designated to run the MDS application pool. This workaround requires using an account that possesses system administrator privileges within SQL Server, as granting server-level permissions is a privileged operation.

The process involves executing a SQL command against the master database, which is the system database responsible for server-level configurations and permissions.

Here are the steps to grant the necessary permission:

  1. Connect to SQL Server: Open SQL Server Management Studio (SSMS) or any other SQL client tool and connect to the SQL Server instance that hosts your MDS database. Ensure you are connecting using an account with the sysadmin server role or equivalent permissions capable of granting server-level permissions.

  2. Open a New Query Window: Create a new query window in SSMS and ensure the connection context is set to the master database. You can verify this in the dropdown menu in the toolbar or by using the USE master; command at the beginning of your query.

  3. Execute the GRANT Statement: Execute the following SQL command in the query window:

    USE Master;
GO
GRANT VIEW SERVER STATE TO <domain\MdsWebAppAccount>;

    Important: Replace <domain\MdsWebAppAccount> with the actual domain and account name of the application pool account that is running your MDS web application. For example, if your domain is “CONTOSO” and the application pool account is named “MDSWebAppSvc”, the command would be:

    USE Master;
GO
GRANT VIEW SERVER STATE TO CONTOSO\MDSWebAppSvc;

    Ensure you use the correct domain and account name to grant the permission to the intended account.

  4. Verify Permission Grant: After executing the command, you can verify that the permission has been granted. You can do this by querying the server principals or by checking the effective permissions of the account using SSMS.

    For example, to verify using T-SQL, you can run:

    SELECT
    sp.name AS principal_name,
    CASE WHEN sp.type_desc = 'WINDOWS_LOGIN' THEN 'Windows Login'
         WHEN sp.type_desc = 'WINDOWS_GROUP' THEN 'Windows Group'
         WHEN sp.type_desc = 'SQL_LOGIN' THEN 'SQL Login'
         ELSE sp.type_desc END AS principal_type,
    CASE WHEN HAS_SERVER_PERMISSION(sp.name, 'VIEW SERVER STATE') = 1 THEN 'Yes' ELSE 'No' END AS has_view_server_state_permission
FROM sys.server_principals AS sp
WHERE sp.name = '<domain\MdsWebAppAccount>'; -- Replace with your account

    This query will return a row indicating whether the specified account has the VIEW SERVER STATE permission. The has_view_server_state_permission column should show ‘Yes’ if the permission has been successfully granted.

After granting the VIEW SERVER STATE permission, recycle the application pool for the MDS website in IIS Manager to ensure the changes take effect. Then, attempt to execute the Validate Version command again within the MDS web application. The “Server Error” should be resolved, and the validation process should proceed as expected.

More Information

To confirm if the encountered issue is indeed related to the missing VIEW SERVER STATE permission, you can utilize SQL Profiler to capture and analyze the SQL statements executed when you trigger the validation command in the MDS web application.

Here’s how to use SQL Profiler to diagnose this issue:

  1. Launch SQL Profiler: Open SQL Server Profiler. It is typically found in the SQL Server Management Studio tools menu or by searching for “SQL Profiler” in the Windows Start Menu.

  2. Connect to the SQL Server Instance: In SQL Profiler, connect to the SQL Server instance hosting your MDS database. Use an account with sufficient permissions to run Profiler traces.

  3. Create a New Trace: Create a new trace. You can use a blank template or a predefined template if available.

  4. Select Events: In the “Events Selection” tab of the Trace Properties dialog, select the events you want to capture. For diagnosing permission issues, the following events are particularly useful:

    • Errors and Warnings: Errorlog, Exception, Security Audit Event (specifically Audit Failed Database Operation Event, Audit Failed Server Operation Event).
    • Stored Procedures: SP:StmtCompleted, SP:Starting.
    • T-SQL: SQL:StmtStarting, SQL:StmtCompleted.

  5. Filter by Database (Optional): If you want to narrow down the trace to just the MDS database, you can set a filter on the “DatabaseName” column in the “Column Filters” tab to include only your MDS database.

  6. Run the Trace: Start the SQL Profiler trace.

  7. Reproduce the Error: In your web browser, navigate to the MDS website and reproduce the steps to trigger the Validate Version command that results in the “Server Error”.

  8. Analyze the Trace Output: After reproducing the error, stop the SQL Profiler trace and analyze the captured events. Look for the following indicators:

    • Error Events: Examine the “Errorlog” or “Exception” events for any messages related to permissions.
    • “The user does not have permission to perform this action.” Error: Specifically search for events containing the error message “The user does not have permission to perform this action.” This message, when associated with stored procedure execution or DMV access, strongly suggests a permission problem.
    • Stored Procedure Execution: Look for events related to the execution of the mdm.udpValidationIsRunning stored procedure. Check if any errors occur during or immediately after the execution of this procedure.

If you find events containing “The user does not have permission to perform this action” in the trace, especially in the context of stored procedure execution or DMV access related to sys.dm_broker_activated_tasks, it strongly indicates that the VIEW SERVER STATE permission is indeed missing and is the cause of the “Server Error”.

Checking Effective Permissions via SSMS

Another way to verify permissions is to directly check the effective permissions of the application pool account using SQL Server Management Studio (SSMS):

  1. Connect to SQL Server in SSMS: Open SQL Server Management Studio and connect to the SQL Server database engine hosting the MDS catalog.

  2. Navigate to Security: In the Object Explorer pane, expand the Security folder.

  3. Locate the Account: Find the account used to run the IIS MDS application pool within the Logins section.

  4. Open Account Properties: Right-click on the account and select Properties.

  5. Go to Securables Page: In the Login Properties dialog, navigate to the Securables page.

  6. Check Effective Permissions: In the bottom pane of the Securables page, click on the Effective tab. This tab displays the cumulative permissions that the account effectively possesses, considering both explicit grants and permissions inherited through role memberships.

  7. Search for VIEW SERVER STATE: Scroll through the list of effective permissions and look for VIEW SERVER STATE.

    • If VIEW SERVER STATE is listed: If you see VIEW SERVER STATE in the list of effective permissions, then the missing permission is likely not the cause of the problem. You will need to investigate other potential causes for the validation failure.

    • If VIEW SERVER STATE is NOT listed: If VIEW SERVER STATE is not present in the effective permissions list, it confirms that the account lacks this permission. In this case, proceed to grant the permission as described in the Workaround section.

  8. Grant Permission (if necessary): If VIEW SERVER STATE is missing, switch back to the Explicit tab in the Login Properties dialog. In the “Server permissions” section, locate View Server State Permissions and check the Grant checkbox to explicitly grant the permission to the account. Click OK to save the changes.

By using SQL Profiler and checking effective permissions in SSMS, you can definitively diagnose whether the missing VIEW SERVER STATE permission is the root cause of the MDS validation command failure and take the appropriate corrective action.

References

For further information on data validation within Master Data Manager web application, consult the official Microsoft documentation:

[Link to Microsoft Documentation on Master Data Services Data Validation - Placeholder, please insert actual link here]

[Link to Microsoft Documentation on SQL Server Permissions - Placeholder, please insert actual link here]

[Link to Microsoft Documentation on sys.dm_broker_activated_tasks DMV - Placeholder, please insert actual link here]

These resources provide in-depth explanations and best practices for managing and troubleshooting Master Data Services environments.

If you found this article helpful or have further questions regarding SQL Server MDS validation issues, please feel free to leave a comment below! Your feedback and experiences are valuable to the community.

Post a Comment