Document toolboxDocument toolbox

(12.04.120) Microsoft SQL Server/Cluster with Native SQL Authentication

Owned by Harald Saller
Last updated: Aug 26, 2024 by Emese Pogany
4 min read

This article describes the setup of a UMS database using a Microsoft SQL server, the configuration of the database login, and the connection of the IGEL Universal Management Suite (UMS) to the database using native SQL authentication.

Creating the UMS Database

It is recommended to create a separate database with a specific schema for the UMS.

Configuration Hints

The UMS Server application runs several services in parallel to provide the required functionality. These services establish separate connections to the database. The database must therefore allow a certain number of connections. The expected maximum number of connections is 128 * [number of UMS Servers].

Please make sure that your database can handle these connections.

Using the SQL Management Console

→ In the SQL Management Console, select New Query and enter the script below; replace the placeholders accordingly.

Do NOT use the schema dbo for the UMS database tables!

  • <database_name>: The name for the UMS database

  • <schema_name>: The name of the schema for the UMS database

    USE [master] GO CREATE DATABASE [<database_name>]; GO USE [<database_name>]; GO CREATE SCHEMA [<schema_name>]; GO

     

Using the GUI

  1. In SQL Server Management Studio, right-click Databases and select New Database.

  2. Under General, give the database a name.

  3. Optionally set additional parameters according to your company requirements.



Configuring the UMS User, Schema, and Database Permissions

Using the SQL Management Console

→ In the SQL Management Console, select New Query and enter the script below; please note the following.

  • <ums_user>: The local alias in the database <database_name> of the real user <sql_user>

  • According to the Microsoft SQL Server documentation, the <ums_user> must be db_owner to create and alter tables.

USE [<database_name>] GO CREATE USER [<ums_user>] FOR LOGIN [<sql_user]; GO ALTER ROLE [db_owner] ADD MEMBER [<ums_user>]; GO ALTER USER [<ums_user>] WITH DEFAULT_SCHEMA = [<schema_name>]; GO ALTER AUTHORIZATION ON SCHEMA::[<schema_name>] TO [<ums_user>] GO

Using the GUI

  1. In SQL Server Management Studio, open the database that was created in Creating the UMS Database.

  2. Under Security > Users, right-click New User.

  3. Under General, search for your login name (<sql_user>) and give the user a name.

     

  4. In the Membership area, give the user the db_owner role.

     

  5. Go to Security > Schemas and right-click on New Schema.

  6. Search for the <ums_user> as the Schema owner and provide a Schema name.

     

  7. Under Security > Users in your UMS database, double-click on the <ums_user>.

  8. Under General, set the default schema to <schema_name>.

     

  9. Under Security > Logins > Users, double-click on the <sql_user>.

  10. In the User Mapping area, check the mapping of the UMS database, the user, and the default schema.

     

  11. Depending on whether you are using a single server or a cluster for your Microsoft SQL database, continue with Connecting the UMS to the Database (Single Server Instance) or Connecting the UMS to the Database (Cluster), 

Connecting the UMS to the Database (Single Server Instance)

  1. In the UMS Administrator, set up a new SQL Server type data source.

     

     

  2. Edit the data as follows:

    • Host: The hostname or IP address of the Microsoft SQL server; if you deploy MS SQL Server Always On Availability Groups, enter the domain name of the Always On Availability Group listener.

    • Port: The port on which the Microsoft SQL Server listens for requests. (Default: 1433)

    • User: The login name for connecting to the database 

    • Schema: The database schema

    • Database / SID: The database name 

    • JDBC Parameter (double-click): 

      • sendStringParametersAsUnicode: false

      • trustServerCertificate: true

         

         

  3. Select your database configuration and click Activate.

     

  4. Enter the username and the password for the connection.

Connecting the UMS to the Database (Cluster)

  1. In the UMS Administrator, set up a new SQL Server type data source.

     



  2. Edit the data as follows:

    • Host: The hostname or IP address of the Microsoft SQL server; if you deploy MS SQL Server Always On Availability Groups, enter the domain name of the Always On Availability Group listener.

    • Port: The port on which the Microsoft SQL Server listens for requests. (Default: 1433)

    • User: The login name for connecting to the database

    • Schema: The database schema

    • Database / SID: The database name 

    • Instance: The instance for your Microsoft SQL Server Cluster

    • JDBC Parameter (double-click): 

      • sendStringParametersAsUnicode: false

      • trustServerCertificate: true

         

         

  3. Select your database configuration and click Activate.

     

  4. Enter the username and the password for the connection.