SQL Server DB_SYNC Internal Error when Status Indicates "VIEW SERVER STATE permission was denied on object 'server', database 'master'" (KBA5101)
KBA
KBA# 5101
Issue
After an upgrade from 5.3.0.3 to 5.3.6.0, all SQL Server dSources on the engine failed validated sync and manual DB_SYNC with:
DB_SYNC job for "SQLServer_dSource_Group_Name/dSource_Name" failed due to an internal error during execution. STATUS Validated sync for dSource "dSource_Name" failed with the error: PreparedStatementCallback; uncategorized SQLException for SQL [USE master; SET NOCOUNT ON; SELECT COUNT(*) FROM sys.dm_hadr_database_replica_states rs JOIN sys.databases db ON rs.database_id = db.database_id JOIN sys.dm_hadr_name_id_map map ON map.ag_id = rs.group_id WHERE rs.is_local = 1 AND db.name = ? AND map.ag_name = ?]; SQL state [S0001]; error code [300]; VIEW SERVER STATE permission was denied on object 'server', database 'master'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: VIEW SERVER STATE permission was denied on object 'server', database 'master'
Applicable Delphix Versions
This article applies to the following versions of the Delphix Engine:
|
Major Release |
All Sub Releases |
| 5.3 | 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0 |
Resolution
The message indicates the issue - We require the SQL Server database user have the VIEW SERVER STATE and VIEW ANY DEFINITION permissions in order for the DB_SYNC (manual and validated sync) to query for the Source database's backupset history. We have added an additional query to check that the database is a member of the Availability Group. In rare situations, a database on one of the nodes might be removed from the AG cluster and prior to 5.3.3.0 this database was considered as part of the AG and the backupset history query could fail and prevent the dSource from being properly synchronized.
Explicit Commands
To add the permissions, a SQL Server database administrator can grant the permissions:
USE master GO GRANT VIEW ANY DEFINITION TO Delphix_Database_User USE master GO GRANT VIEW SERVER STATE TO Delphix_Database_User
SSMS (SQL Server Management Studio)
Alternatively, one can also set the permissions via SSMS:
Select the Delphix Database user from Security | Logins. Go to Securables and scroll down the explicit list to the two permissions "VIEW ANY DEFINITION" and "VIEW SERVER STATE" and check the Grant boxes. If this causes issues use the explicit commands described above.
After these changes are implemented, monitor the status of the dSource to verify Validated Sync has resumed. If there is an issue where files are missing, you will need to run a Manual Full Backup Sync to resume Validated Sync.
Troubleshooting
The SQL statement derived from the fault error message can be executed manually to verify the permission issue. Also, the sql can be copied from manual DB_SYNC (snapshot) error message. The SQL is displayed as a SQLPreparedStatement, using "?" placeholders for the actual values of the database and AG group name.
As an example, if your database is AGwin2016clusterSQL2014 and AG group name is silurian-ag then run this query:
USE master; SET NOCOUNT ON; SELECT COUNT(*) FROM sys.dm_hadr_database_replica_states rs JOIN sys.databases db ON rs.database_id = db.database_id JOIN sys.dm_hadr_name_id_map map ON map.ag_id = rs.group_id WHERE rs.is_local = 1 AND db.name = 'AGwin2016clusterSQL2014' AND map.ag_name = 'silurian-ag'
If the permissions are not granted for the SQL Server database user this is the expected message:
Msg 300, Level 14, State 1, Line 2 VIEW SERVER STATE permission was denied on object 'server', database 'master'. Msg 297, Level 16, State 1, Line 2 The user does not have permission to perform this action.
After the permissions are granted the expected result if the database is part of the AG group is "1" and Delphix will proceed to query the database for its backupset history. If "0" is returned it means the database is not a replica (not clustered) in this AG group and Delphix will not query this database for the backupset history.