This section discusses the SQL Server permissions required to:
Configure the Active Roles Administration Service (Configuration permissions).
Run the Active Roles Administration Service (Operation permissions).
Configure replication in Active Roles (Replication configuration permissions).
Run Active Roles replication (Replication Agent permissions).
IMPORTANT: Starting from version 8.2, Active Roles supports (and its installer is shipped with) Microsoft OLE DB Driver 19.x for SQL Server. However, Active Roles still supports earlier OLE DB Driver versions as well (18.4 or newer).
If you perform a clean installation of Active Roles 8.2 and want to use Microsoft OLE DB Driver 19.x (bundled with the Active Roles installer) due to security concerns, then verify that your SQL Server has SSL configured and the necessary trusted certificate set. Otherwise, Active Roles cannot communicate with the SQL Server and the Active Roles Administration Service might not start.
To use SSL with your SQL Server, configure a valid certificate. For more information on installing or viewing certificates for SQL Server via SQL Server Configuration Manager, see Certificate management in the Microsoft SQL Server documentation.
For general information about the encryption and certificate requirements of Microsoft OLE DB Driver 19.x, see Encryption and certificate validation in OLE DB and Certificate requirements for SQL Server in the Microsoft SQL Server documentation.
When configuring the SSL connection, consider the following:
Microsoft OLE DB Driver 19.x for SQL Server requires a certificate from a Certificate Authority and no longer accepts self-signed certificates. For more information on how to access a Certificate Authority, see Certification Authority Guidance in the Microsoft Windows Server documentation.
The Service Account running the SQL Server service must have permission to view the private key from the server certificate. For more information, see Configure SQL Server Database Engine for encrypting connections in the Microsoft SQL Server documentation.
Microsoft OLE DB Driver 19.x for SQL Server requires specifying the Service Principal Names (SPNs). For more information, see the following Microsoft SQL Server documentation resources:
You might need to change your SQL connection string to match the certificate and the SPN. For more information, see Using Connection String Keywords with OLE DB Driver for SQL Server in the Microsoft SQL Server documentation.
If you perform a clean installation of Active Roles 8.2 but you want to use an earlier supported version of Microsoft OLE DB Driver (18.4 or newer) instead of version 19.x that is bundled with the Active Roles installer, you must perform additional configuration steps in your environment. For more information,
To assign a valid and trusted certificate to SQL Server, in the SQL Server Configuration Manager, navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER.
The account that you use when configuring the Administration Service must have sufficient rights on SQL Server to perform the configuration tasks.
Which account is used to access SQL Server during configuration of the Administration Service depends upon the SQL Server connection option you select in the wizard for configuring the Administration Service. If you select the option to use Windows authentication, the wizard accesses SQL Server with the Windows user account under which the wizard is running. If you select the option to use SQL Server authentication, then the wizard accesses SQL Server with the SQL login and password that you specify in the wizard.
NOTE: Windows authentication is not applicable for configuring Active Roles on Azure database server.
The required rights of the account that is used to access SQL Server during configuration vary depending on your configuration scenario:
The wizard can create a new database for the Administration Service only if the account is a member of the dbcreator fixed server role.
For Azure SQL database variants, Azure SQL database and Azure SQL on elastic pool dbmanager role must be provided to the Server Admin to create databases.
However, for variant Azure SQL Managed instance, the dbcreator fixed server role should be provided.
If you want the wizard to import data from the Active Roles database of an earlier version, then the account must be a member of the db_datareader fixed database role in the source database.
If you want the wizard to configure the Administration Service to use an existing database of the current version, then the account must be a member of the db_owner fixed database role and have the default schema of dbo in that database.
If you want the wizard to use an existing blank database for the Administration Service, then the account must be a member of the db_owner fixed database role and have the default schema of dbo in that database.
The Administration Service accesses its database with the account specified during configuration:
If you select the option for Windows authentication to configure the Administration Service, then the Administration Service uses its service account to access the database.
If you select the option for SQL Server authentication, then the Administration Service accesses the database with the SQL login and password supplied in the configuration wizard.
In either case, the account must have sufficient rights on SQL Server to retrieve data from, and make changes to, the database. The required rights vary depending on the role of the Administration Service’s database server in the Active Roles replication environment.
NOTE:Active Roles does not support replication on Azure SQL databases.
When initially installed, the Administration Service database is configured not to participate in Active Roles replication. This configuration is known as "standalone Administration Service". The account that the standalone Administration Service uses to access the database must be, at minimum, the member of the db_owner fixed database role and must have the default schema of dbo in that database.
If the Administration Service’s database server holds the role of the Publisher in Active Roles replication, then the account the Administration Service uses to access the database must at a minimum be a member of the db_owner fixed database role and have the default schema of dbo in that database. Additional rights are required if you want to see the replication status information and error messages in the Active Roles console. These additional rights are as follows:
Default schema of dbo in the msdb system database.
SELECT permission on the sysjobs, sysjobsteps and MSagent_parameters system tables in the msdb system database.
SELECT permission on the sysservers system view in the master system database.
EXECUTE permission on the xp_sqlagent_enum_jobs system extended stored procedure in the master system database.
SELECT permission on the MSmerge_agents, MSmerge_history, MSmerge_sessions, MSsnapshot_agents and MSsnapshot_history system tables in the distribution database (AelitaDistributionDB database by default).
If the Administration Service’s database server holds the role of a Subscriber in Active Roles replication, then the account that the Administration Service uses to access the database requires the same rights as in standalone mode: The account must at a minimum be a member of the db_owner fixed database role and have the default schema of dbo in that database.
After you install and configure two or more Administration Service instances, each with its own database, you can deploy replication, if necessary, to synchronize the databases so that all your Administration Service instances have the same configuration and management history. Replication deployment begins when you configure the Publisher. Once the Publisher has been configured, the next step is to configure Subscribers. The task of configuring the Publisher or a Subscriber requires more rights on SQL Server than the Administration Service needs for normal operation. To elevate the rights of the Administration Service, Active Roles prompts for an alternative account. The following topics elaborate on the permissions needed to create the Publisher or add a Subscriber.
To create the Publisher, the Administration Service needs sysadmin rights on SQL Server. If the Administration Service’s account for database access does not belong to the sysadmin role, then Active Roles prompts you to supply an alternative account. The alternative account must be a member of the sysadmin fixed server role on the database server you are going to make the Publisher.
Active Roles does not store the login name and password of this account. It only uses the login name and password of this account to configure the Publisher.
The same permissions are required for removing (demoting) the Publisher.
To add a Subscriber, the Administration Service’s database server must hold the Publisher role. When adding a Subscriber, the Administration Service makes changes on the Publisher database server and on the database server being configured as a Subscriber (Subscriber database server). Therefore, the Administration Service needs sufficient rights on both database servers.
On the Publisher database server, the Administration Service needs sysadmin rights. If the Administration Service’s account for database access does not belong to the sysadmin role, then Active Roles prompts you to supply an alternative account for connection to the Publisher database server. The alternative account must be a member of the sysadmin fixed server role on the Publisher database server.
Active Roles does not store the login name and password of this account. It only uses the login name and password of this account to configure the Subscriber.
On the database server you are going to make a Subscriber, the Administration Service needs db_owner rights in the Active Roles database. If the Administration Service’s account for database access does not have sufficient rights on the Subscriber database server, then Active Roles prompts you to supply an alternative account for connection to the Subscriber database server. The alternative account must:
Be a member of the db_owner fixed database role in the Active Roles database on the database server you are going to make a Subscriber.
Have the default schema of dbo in that database.
Active Roles does not store the login name and password of this account. It only uses the login name and password of this account to configure the Subscriber.
The same permissions are required for removing a Subscriber.
© 2024 One Identity LLC. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
