Appendix I. Configuring Sentinet with no requirement for sysadmin permissions

Repository Configuration Wizard application and automated deployment scripts can be executed with no requirement for sysadmin account privileges for the account that runs the Wizard application or the deployment scripts. If you use SQL Server authentication option in the System Administrator account section of the Wizard, you may also use SQL account with no sysadmin account privileges. The account that will be used in these cases must have db_owner privileges, and certain manual steps described in this chapter must be executed before (and in some cases after) running Repository Configuration Wizard application or automated deployment scripts.

Single Repository Database scenario

In this scenario Repository Database must be created and prepared before running Repository Configuration Wizard application or automated deployment scripts.

Step 1 – create database and database accounts

  1. Make sure the following SQL Server Logins are created or exist:

    1. NT AUTHORITY\SYSTEM
    2. Account for Sentinet Web Application (e.g. IIS APPPOOL\SentinetAppPool)

    3. System Administrator account to be used by the Repository Configuration Wizard. This can be a Window account or SQL Authentication account.

      Examples of SQL statements to create accounts:

      CREATE LOGIN [IIS APPPOOL\SentinetAppPool] FROM WINDOWS;

      or

      CREATE LOGIN [admin] WITH PASSWORD = 'P@ssword!';

  2. Make sure Repository Database is created or exists.

    Examples of SQL statements to create database:

    CREATE DATABASE [Sentinet];

    ALTER DATABASE [Sentinet] SET RECOVERY SIMPLE;

    ALTER DATABASE [Sentinet] SET ANSI_NULL_DEFAULT ON;

    ALTER DATABASE [Sentinet] SET ANSI_NULLS ON;

    ALTER DATABASE [Sentinet] SET ANSI_PADDING ON;

    ALTER DATABASE [Sentinet] SET ANSI_WARNINGS ON;

    ALTER DATABASE [Sentinet] SET ARITHABORT ON;

    ALTER DATABASE [Sentinet] SET CONCAT_NULL_YIELDS_NULL ON;

    ALTER DATABASE [Sentinet] SET NUMERIC_ROUNDABORT OFF;

    ALTER DATABASE [Sentinet] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

  3. Make sure db_executor role is created or exists in the Sentinet Repository Database.

    Examples of SQL statements to create db_executor role:

    CREATE ROLE db_executor;

    GRANT EXECUTE TO db_executor;

  4. Create the following SQL Users in the Sentinet Repository Database:

    1. NT AUTHORITY\SYSTEM, which must be member of the following database roles: db_datareader, db_datawriter, db_executor.

    2. System Administrator account to be used by the Repository Configuration Wizard, which must be member of the following database roles: db_owner

      Examples of SQL statements to create accounts:

      CREATE USER [IIS APPPOOL\SentinetAppPool] FOR LOGIN [IIS APPPOOL\SentinetAppPool];

      EXEC sp_addrolemember 'db_datawriter', 'IIS APPPOOL\SentinetAppPool';

      EXEC sp_addrolemember 'db_datareader', 'IIS APPPOOL\SentinetAppPool';

      EXEC sp_addrolemember 'db_executor', 'IIS APPPOOL\SentinetAppPool';

      Or

      CREATE USER [admin] FOR LOGIN [admin];

      EXEC sp_addrolemember 'db_owner', 'admin';

    3. Sentinet Web Application’s IIS Application Pool account, which must be member of the following database roles: db_datareader, db_datawriter, db_executor.

Step 2 – run Repository Configuration Wizard

  1. Launch Repository Configuration Wizard and select Use existing Repository database option.

  2. In the System Administrator account section select db_owner account created in Step 1–4-(b) above.

  3. In the Repository account section select account created in Step 1–4-(c) above.

  4. Leave all checkboxes unchecked in the Monitoring Database page. Using stand-alone Monitoring Database scenario is described later in this chapter.

  5. Continue running Repository Configuration Wizard using the same settings you would have used during normal configuration process from step 4.

Repository Database with Stand-alone Monitoring Database scenario

In this scenario Repository Database and stand-alone Monitoring Database must be created and prepared before running Repository Configuration Wizard application or automated deployment scripts, while SQL replication must be setup after running Repository Configuration Wizard application or automated deployment scripts. Before configuring Sentinet in this scenario make sure all required prerequisites are executed.

Step 1 – create databases and database accounts

  1. Make sure the following SQL Server Logins are created or exist:

    1. NT AUTHORITY\SYSTEM
    2. Account for Sentinet Web Application (e.g. IIS APPPOOL\SentinetAppPool)

    3. System Administrator account to be used by the Repository Configuration Wizard. This can be a Window account or SQL Authentication account.

    4. The account that SQL Server Agent is running under (e.g. NT AUTHORITY\SQLSERVERAGENT).

      Examples of SQL statements to create accounts:

      CREATE LOGIN [IIS APPPOOL\SentinetAppPool] FROM WINDOWS;

      or

      CREATE LOGIN [admin] WITH PASSWORD = 'P@ssword!';

  2. Make sure Repository Database is created or exists.

    Examples of SQL statements to create database:

    CREATE DATABASE [Sentinet];

    ALTER DATABASE [Sentinet] SET RECOVERY SIMPLE;

    ALTER DATABASE [Sentinet] SET ANSI_NULL_DEFAULT ON;

    ALTER DATABASE [Sentinet] SET ANSI_NULLS ON;

    ALTER DATABASE [Sentinet] SET ANSI_PADDING ON;

    ALTER DATABASE [Sentinet] SET ANSI_WARNINGS ON;

    ALTER DATABASE [Sentinet] SET ARITHABORT ON;

    ALTER DATABASE [Sentinet] SET CONCAT_NULL_YIELDS_NULL ON;

    ALTER DATABASE [Sentinet] SET NUMERIC_ROUNDABORT OFF;

    ALTER DATABASE [Sentinet] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

  3. Make sure db_executor role is created or exists in the Sentinet Repository Database.

    Examples of SQL statements to create db_executor role:

    CREATE ROLE db_executor;

    GRANT EXECUTE TO db_executor;

  4. Create the following SQL Users in the Sentinet Repository Database:

    1. NT AUTHORITY\SYSTEM, which must be member of the following database roles: db_datareader, db_datawriter, db_executor.

    2. System Administrator account to be used by the Repository Configuration Wizard, which must be member of the following database roles: db_owner

      Examples of SQL statements to create accounts:

      CREATE USER [IIS APPPOOL\SentinetAppPool] FOR LOGIN [IIS APPPOOL\SentinetAppPool];

      EXEC sp_addrolemember 'db_datawriter', 'IIS APPPOOL\SentinetAppPool';

      EXEC sp_addrolemember 'db_datareader', 'IIS APPPOOL\SentinetAppPool';

      EXEC sp_addrolemember 'db_executor', 'IIS APPPOOL\SentinetAppPool';

      Or

      CREATE USER [admin] FOR LOGIN [admin];

      EXEC sp_addrolemember 'db_owner', 'admin';

    3. Sentinet Web Application’s IIS Application Pool account, which must be member of the following database roles: db_datareader, db_datawriter, db_executor.
    4. SQL Agent Server’s account (e.g. NT AUTHORITY\SQLSERVERAGENT), which must be member of the following database roles: db_datareader, db_datawriter, db_executor, db_owner.

  5. Repeat the same steps described above but for the Monitoring Database.

Step 2 – run Repository Configuration Wizard

  1. Lunch Repository Configuration Wizard and select Use existing Repository database option.

  2. In the System Administrator account section select db_owner account created in Step 1–4-(b) above.

  3. In the Repository account section select account created in Step 1–4-(c) above.

  4. On the Monitoring Database page check the Enable stand-alone Monitoring database checkbox.

  5. Select Use existing Monitoring database option.

  6. In the System Administrator account section select db_owner account created as part of executing Step 1-5 above.

  7. In the Repository account section select account created as part of executing Step 1-5 above.

Note

A warning message will be displayed, which notifies that the specified Repository or Monitoring database’s System Administrator account is not a member of sysadmin server role. It is expected to agree and continue in this scenario.

Continue running Repository Configuration Wizard using the same settings you would have used during normal configuration process from step 4.

Step 3 – replication setup

After running Repository Configuration Wizard, setup SQL Server replications using SQL scripts provided in the default installation folder, C:\Program Files\Nevatech\Sentinet\SQL (see Using stand-alone scripts chapter in this document for more details on this scenario and its SQL scripts’ description).

  1. Execute Create Replication Repository.sql against Repository Database.

  2. Execute Create Replication Monitoring.sql against Monitoring Database.

  3. Execute Create Table Partitioning Monitoring.sql against Monitoring Database. Execute this script if Monitoring tables partitioning is needed (optional).