Configuring accounts for work with SQL Server (SQL Server authentication)

April 17, 2023

ID 240594

Prerequisites

Before you assign rights to the accounts, perform the following actions:

  1. Make sure that you log in to the system under the local administrator account.
  2. Install an environment for working with SQL Server.
  3. Make sure that you have a Windows account under which you will install Administration Server.
  4. Make sure that you have a Windows account under which you will start the Administration Server service.
  5. On SQL Server, enable the SQL Server authentication mode.

    If you use SQL Server Management Studio, in the SQL Server Properties window, on the Security page, select the SQL Server and Windows Authentication mode option.

  6. On SQL Server, create a login with a password. The Administration Server installer (hereinafter also referred to as the installer) and the Administration Server service will use this SQL Server account to access SQL Server.

    If you use SQL Server Management Studio, on the General page of the login properties window, select the SQL Server authentication option.

If you want to install Administration Server and SQL Server on devices that are located in separate Windows domains, note that these domains must have two-way trust relationships to ensure the correct operation of Administration Server, including running tasks and applying policies. For information about the required accounts for work with various DBMSs and accounts' rights, see Accounts for work with the DBMS.

Configuring the accounts to install Administration Server (automatic creation of the Administration Server database)

To configure the accounts for the Administration Server installation:

  1. On SQL Server, map the SQL Server account to the default master database. The master database is a template for the Administration Server database (hereinafter also referred to as a Server database). The master database is used for mapping until the installer creates a Server database. Grant the following rights and permissions to the SQL Server account:
    • Server-level role: public
    • Database role membership for the master database: db_owner
    • Default schema for the master database: dbo
    • Permissions:
      • CONNECT ANY DATABASE
      • CONNECT SQL
      • CREATE ANY DATABASE
      • VIEW ANY DATABASE
  2. Log in to the system under the Windows account used to run the installer.
  3. Run the installer.

    The Administration Server Setup wizard starts. Follow the instructions of the wizard.

  4. Select the custom installation of Administration Server option.
  5. Select the Microsoft SQL Server as a DBMS that stores the Administration Server database.
  6. Specify the Administration Server database name.
  7. Select the SQL Server Authentication mode to establish a connection between Administration Server and SQL Server through the created SQL Server account. Then, specify the SQL Server account credentials.
  8. Specify the Windows account used to start the Administration Server service.

    You can select an existing Windows user account or create a new Windows account in the KL-AK-* format by using the installer. Regardless of the account choice, the installer assigns the required system rights to the Administration Server service account.

After the installation finishes, the Server database is created and all the required system rights are assigned to the Administration Server service account. Administration Server is ready to use.

You can cancel the mapping to the master database, because the installer created a Server database and configured the mapping to this database during the Administration Server installation.

Since the automatic database creation requires more permissions than normal work with Administration Server, you can revoke some permissions. On SQL Server, select the SQL Server account, and then grant the following rights for work with Administration Server:

  • Server-level role: public
  • Database role membership for the Server database: db_owner
  • Default schema for the Server database: dbo
  • Permissions:
    • CONNECT SQL
    • VIEW ANY DATABASE

Configuring the accounts to install Administration Server (manual creation of the Administration Server database)

To configure the accounts for the Administration Server installation:

  1. On SQL Server, create an empty database. This database will be used as an Administration Server database.
  2. On SQL Server, grant the following rights and permissions to the SQL Server account:
    • Server-level role: public.
    • Database role membership for the created database: db_owner.
    • Default schema for the created database: dbo.
    • Permissions:
      • CONNECT SQL
      • VIEW ANY DATABASE
  3. Log in to the system under the Windows account used to run the installer.
  4. Run the installer.

    The Administration Server Setup wizard starts. Follow the instructions of the wizard.

  5. Select the custom installation of Administration Server option.
  6. Select the Microsoft SQL Server as a DBMS that stores the Administration Server database.
  7. Specify the name of the created database as the Administration Server database name.
  8. Select the SQL Server Authentication mode to establish a connection between Administration Server and SQL Server through the created SQL Server account. Then, specify the SQL Server account credentials.
  9. Specify the Windows account used to start the Administration Server service.

    You can select an existing Windows user account or create a new Windows account in the KL-AK-* format by using the installer. Regardless of the account choice, the installer assigns the required system rights to the Administration Server service account.

After the installation finishes, the Administration Server will use the created database to store the Administration Server data. All the required system rights are assigned to the Administration Server service account. Administration Server is ready to use.

Did you find this article helpful?
What can we do better?
Thank you for your feedback! You're helping us improve.
Thank you for your feedback! You're helping us improve.