PowerProtect Data Manager SQL: How to configure required privileges/permissions for service and system accounts
Summary: The following article explains the required privileges and permissions to be granted to service accounts, and system accounts, for successful PPDM SQL backup/restore operations.
This article applies to
This article does not apply to
This article is not tied to any specific product.
Not all product versions are identified in this article.
Instructions
When trying to protect SQL servers using PPQDM SQL agent, the following privileges are required to be granted:
Required Windows user permissions
Create a local or domain Windows user account and assign the following roles:
● A domain user added to the local Administrators user group.
● For a stand-alone server only:
○ For table-level backup and recovery, assign the administrative privileges.
○ For database-level backup and recovery, assign the following permissions:
￭Add the user to the "Create global objects" Windows policy.
￭For all Microsoft SQL Server instances on the host that will be protected, assign the following permissions to the data and log folder of the database:
- Read
Assign the permissions for all paths where databases are stored, including the default data and log folder of the Microsoft SQL Server installation. The default data and log folder may be under the Microsoft SQL Server installation folder. For example, for Microsoft SQL Server 2012, the default folder is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\.
● For an Always On availability group only:
○ A local user account added to the Administrators user group on each node in the cluster. The username and password must be the same on each node.
NOTE: For an Always On availability group, Failover Cluster Instance, or Always On Failover Cluster Instance, if you use an account that you created (an account that is not the built-in Windows Administrator), you must launch the tool where you perform the backup or recovery with elevated permissions (run as administrator).
Required Microsoft SQL Server roles
● sysadmin
● public
Setting the sysadmin privilege for Microsoft SQL Server hosts
To enable the integration with PowerProtect Data Manager on each Microsoft SQL Server host, ensure that the database OS account or NT AUTHORITY\SYSTEM account on each host has the required sysadmin privilege.
Before you register any Microsoft SQL Server host with PowerProtect Data Manager, set the required sysadmin privilege by completing one of the following two procedures on each Microsoft SQL Server host.
Setting the sysadmin privilege for database OS account
By default, the Microsoft application agent uses the SYSTEM account for asset discovery. However, this requires granting the SYSTEM account the Microsoft SQL Server sysadmin role, which might not comply with corporate security requirements. As an alternative, you can complete the following procedure to use a domain or local user account instead for asset discovery. When you select the option Use the credentials for both asset discovery and backup as follows, the configuration for using the domain or local user account is centrally managed from PowerProtect Data Manager.
NOTE: This procedure is only supported for a host with application agent release 19.11 or later installed.
Complete the following steps to ensure that the database OS account on each host has the sysadmin privilege.
1. Log in to each Microsoft SQL Server instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.
2. In the Object Explorer, expand Security and then expand Logins.
3. Right-click the database OS account name, and then select Properties.
4. In the Login properties window, select Server Roles.
5. Select sysadmin under Server roles.
6. Click OK.
The Login properties window closes.
7. In the PowerProtect Data Manager UI, select Infrastructure > Asset Sources, and then select the SQL tab.
8. Select the required hostname in the list, and then click Edit Credentials.
9. On the Set Credential page, specify the database OS credentials and select the option Use the credentials for both asset discovery and backup.
NOTE: On the Set Credential page, the option Use the credentials for both asset discovery and backup does not appear when the database OS credentials setting is None or the application agent release is earlier than 19.11.
10. Click Save.
Setting the sysadmin privilege for NT AUTHORITY\SYSTEM account
Complete the following steps to ensure that the NT AUTHORITY\SYSTEM account on each host has the sysadmin privilege.
1. Log in to each Microsoft SQL Server instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.
2. In the Object Explorer, expand Security and then expand Logins.
3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.
4. In the Login properties window, select Server Roles.
5. Select sysadmin and public under Server roles.
6. Click OK.
The Login properties window closes.
Required privileges for T-SQL backups
To enable the Application Direct backups using Transact-SQL (T-SQL) scripts with the Microsoft application agent, specific access permissions are required. The SQL Agent Service login user must have read and write access permissions in the Microsoft application agent installation directory, which is C:\Program Files\DPSAPPS\MSAPPAGENT by default.
Required Windows user permissions
Create a local or domain Windows user account and assign the following roles:
● The built-in Windows Administrator.
● A domain user added to the local Administrators user group.
● For a stand-alone server only:
○ For table-level backup and recovery, assign the administrative privileges.
○ For database-level backup and recovery, assign the following permissions:
￭Add the user to the "Create global objects" Windows policy.
￭For all Microsoft SQL Server instances on the host that will be protected, assign the following permissions to the data and log folder of the database:
- Read
- Write
- List folder contents
Assign the permissions for all paths where databases are stored, including the default data and log folder of the Microsoft SQL Server installation. The default data and log folder may be under the Microsoft SQL Server installation folder. For example, for Microsoft SQL Server 2012, the default folder is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\.
● For an Always On availability group only:
○ A local user account added to the Administrators user group on each node in the cluster. The username and password must be the same on each node.
NOTE: For an Always On availability group, Failover Cluster Instance, or Always On Failover Cluster Instance, if you use an account that you created (an account that is not the built-in Windows Administrator), you must launch the tool where you perform the backup or recovery with elevated permissions (run as administrator).
Required Microsoft SQL Server roles
Assign the user the following Microsoft SQL Server roles:
● sysadmin
● public
Setting the sysadmin privilege for Microsoft SQL Server hosts
To enable the integration with PowerProtect Data Manager on each Microsoft SQL Server host, ensure that the database OS account or NT AUTHORITY\SYSTEM account on each host has the required sysadmin privilege.
Before you register any Microsoft SQL Server host with PowerProtect Data Manager, set the required sysadmin privilege by completing one of the following two procedures on each Microsoft SQL Server host.
Setting the sysadmin privilege for database OS account
By default, the Microsoft application agent uses the SYSTEM account for asset discovery. However, this requires granting the SYSTEM account the Microsoft SQL Server sysadmin role, which might not comply with corporate security requirements. As an alternative, you can complete the following procedure to use a domain or local user account instead for asset discovery. When you select the option Use the credentials for both asset discovery and backup as follows, the configuration for using the domain or local user account is centrally managed from PowerProtect Data Manager.
NOTE: This procedure is only supported for a host with application agent release 19.11 or later installed.
Complete the following steps to ensure that the database OS account on each host has the sysadmin privilege.
1. Log in to each Microsoft SQL Server instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.
2. In the Object Explorer, expand Security and then expand Logins.
3. Right-click the database OS account name, and then select Properties.
4. In the Login properties window, select Server Roles.
5. Select sysadmin under Server roles.
6. Click OK.
The Login properties window closes.
7. In the PowerProtect Data Manager UI, select Infrastructure > Asset Sources, and then select the SQL tab.
8. Select the required hostname in the list, and then click Edit Credentials.
9. On the Set Credential page, specify the database OS credentials and select the option Use the credentials for both asset discovery and backup.
NOTE: On the Set Credential page, the option Use the credentials for both asset discovery and backup does not appear when the database OS credentials setting is None or the application agent release is earlier than 19.11.
10. Click Save.
Setting the sysadmin privilege for NT AUTHORITY\SYSTEM account
Complete the following steps to ensure that the NT AUTHORITY\SYSTEM account on each host has the sysadmin privilege.
1. Log in to each Microsoft SQL Server instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.
2. In the Object Explorer, expand Security and then expand Logins.
3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.
4. In the Login properties window, select Server Roles.
5. Select sysadmin and public under Server roles.
6. Click OK.
The Login properties window closes.
Required privileges for T-SQL backups
To enable the Application Direct backups using Transact-SQL (T-SQL) scripts with the Microsoft application agent, specific access permissions are required. The SQL Agent Service login user must have read and write access permissions in the Microsoft application agent installation directory, which is C:\Program Files\DPSAPPS\MSAPPAGENT by default.
Additional Information
Products
Data Protection Suite For Applications, PowerProtect App DirectArticle Properties
Article Number: 000217603
Article Type: How To
Last Modified: 29 Nov 2023
Version: 3
Find answers to your questions from other Dell users
Support Services
Check if your device is covered by Support Services.