(12.03-de) Microsoft SQL Server / Microsoft SQL Server Cluster

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 UMS database.

From a top-level perspective, the procedure is as follows:

1. Create the UMS database
2. Configure the database login and the connection of the UMS to the database. Select the method that suits your requirements:
   • Native SQL Authentication
     The UMS uses credentials to connect to the Microsoft SQL Server.
   • Active Directory (AD) Native Authentication (Windows only)
     The UMS does not know the database credentials; instead, the credentials are taken from the underlying system user. A Windows API is used to connect to the database. This mode is only available if both the UMS Server and the UMS Administrator are running in a Windows domain. Also, the domain user account under which the UMS Server and the UMS Administrator are running must have access to the database.
   • Active Directory (AD) Authentication with Kerberos
     The credentials of the database user must be entered into the UMS. The database connection is handled by the Kerberos protocol. The underlying system must provide the access data to connect to the domain controller for Kerberos. The UMS Administrator and the UMS Server can run with normal users.

Creating the UMS Database

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

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

• <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.
   

Setting up SQL Server Authentication for the UMS Database

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, click Change Password and enter the password required by your Microsoft SQL Server.
   
   
   
   
2. In the UMS Administrator, set up a new SQL Server type data source.
   
   
   
   
3. 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
       
       
4. Select your database configuration and click Activate.
   
   
   
5. Enter the username and the password for the connection.
   

Connecting the UMS to the Database (Cluster)

1. In the UMS Administrator, click Change Password and enter the password required by your Microsoft SQL Server.
   
   
   
   
2. In the UMS Administrator, set up a new SQL Server type data source.
   
   
   
   
3. 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
       
       
       
       
4. Select your database configuration and click Activate.
   
   
   
5. Enter the username and the password for the connection.
   

Setting up Microsoft Active Directory (AD) Native Authentication for the UMS Database (UMS Server Must Run on Windows)

Prerequisites

For connecting the UMS Server to your UMS database with Microsoft Active Directory (AD) native authentication, the following components must be available:

• A Windows domain server
• The Microsoft SQL server on which the UMS database is running is located in the Windows domain
• The UMS Server and the UMS Administrator are located in the Windows domain
• The SQL service account has local administration rights to the UMS Server

Adding Users and a Group to the Windows Domain

Make sure that your Windows domain contains users who have the following permissions:

• Log in to the database server
• Log in to the database that is connected to the UMS
• Log in to the server with the UMS components
• Run the UMS Server as a Windows service 

Adding the User or Group to Microsoft SQL Server

Using the SQL Management Console

1. In SQL Server Management Studio, select New Query.
   
   

2. Use the following script to create the database login; replace <ad_user> with the AD user you want to use for connecting.

   USE [master]
   GO
   CREATE LOGIN [[<ad_user>]] FROM WINDOWS;
   GO

Using the SQL Server Management Studio (GUI Mode)

1. Connect to the database with the SQL Server Management Studio.
   
   
2. Open the Security branch, right-click on Logins, and select New Login.
   
   
3. Choose Windows Authentication for the login, and click Search.
   
   
4. Click Object Types…, select Groups and Users, and click OK.
   
   
   
5. Click Locations..., to choose the location wherein your user or group is residing, and click OK.
   
   
   
6. Enter the name of the group or user, click Check Names, select the name of your user or group, and click OK.
    
   If you have selected a group, all users in this group will be able to access the databases where this group is defined as the database owner. Also, if you selected a group, you should add at least one user who will become the main database owner.

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 <ad_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 [<ad_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 (<ad_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 <ad_user>.
   
   
10. In the User Mapping area, check the mapping of the UMS database, the user, and the default schema.
    
    
    

Configuring the UMS Services

1. Log into the UMS Server with the credentials configured for connecting to the UMS database on the Microsoft SQL Server.
   
   
2. Open services.msc and right-click the IGEL Remote Manager Server service.
   
   
3. Select Properties and navigate to the Log On tab.
   
   
4. Select This Account and use the Browse button to find the one that owns the SQL database.
   
   
   
5. 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 Instance)

1. In the UMS Administrator, set up a new SQL Server AD Native type data source.
   
   
   
   
2. Edit the data as follows:
   
   • Host: The Fully Qualified Host Name (FQDN) of the Microsoft SQL server (Would the following make sense here (taken from the MS SQL authentication section): 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)
   • 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.
   

Connecting the UMS to the Database (Cluster)

1. In the UMS Administrator, set up a new SQL Server Cluster AD Native type data source.
   
   
   
   
2. Edit the data as follows:
   
   • Host: The Fully Qualified Host Name (FQDN) of the Microsoft SQL server (Would the following make sense here (taken from the MS SQL authentication section): 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)
   • 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.
   

Setting up Microsoft Active Directory (AD) Authentication with Kerberos for the UMS Database

Prerequisites

For connecting the UMS Server to your UMS database with Microsoft Active Directory (AD) Kerberos authentication, the following components must be available:

• A Windows domain server
• The Microsoft SQL server on which the UMS database is running is located in the Windows domain
• The UMS Server and the UMS Administrator have access to the Windows domain
• The SQL service account has local administration rights to the UMS Server

Adding Users and a Group to the Windows Domain

Make sure that your Windows domain contains users who have the following permissions:

• Log in to the database server
• Log in to the database that is connected to the UMS
• Log in to the server with the UMS components

Adding the User or Group to SQL

Using the SQL Management Console

1. In SQL Server Management Studio, select New Query.
   
   

2. Use the following script to create the database login; replace <ad_user> with the AD user you want to use for connecting.

   USE [master]
   GO
   CREATE LOGIN [[<ad_user>]] FROM WINDOWS;
   GO

Using the SQL Server Management Studio (GUI Mode)

1. Connect to the database with the SQL Server Management Studio.
   
   
2. Open the Security branch, right-click on Logins and select New Login.
   
   
3. Choose Windows Authentication for the login, and click Search.
   
   
4. Click Object Types…, select Groups and Users, and click OK.
   
   
   
5. Click Locations..., to choose the location wherein your user or group is residing, and click OK.
   
   
   
6. Enter the name of the group or user, click Check Names, select the name of your user or group, and click OK.
    
   If you have selected a group, all users in this group will be able to access the databases where this group is defined as the database owner. Also, if you selected a group, you should add at least one user who will become the main database owner.

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 <ad_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 [<ad_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 (<ad_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 <ad_user>.
   
   
10. In the User Mapping area, check the mapping of the UMS database, the user, and the default schema.
    
    
    

Configuring the UMS Services

1. Log into the UMS Server with the credentials configured for connecting to the UMS database on the Microsoft SQL Server.
   
   
2. Open services.msc and right-click the IGEL Remote Manager Server service.
   
   
3. Select Properties and navigate to the Log On tab.
   
   
4. Select This Account and use the Browse button to find the one that owns the SQL database.
   
   
   
5. 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), 

FRAGE: Was muss ich machen, wenn ich die UMS unter Linux laufen habe?

Connecting the UMS to the Database (Single Instance)

1. In the UMS Administrator, click Change Password and enter the password required by your Microsoft SQL Server. FRAGE: Ist dieser Schritt hier richtig? (Habe den auf Verdacht hier eingefügt, ebenso wie bei der SQL Server Authentication. Aber wenn so order so die Passwortabfrage beim Aktivieren kommt, kann man sich den evtl. sparen?)
   
   
2. Set up a new SQL Server AD Kerberos type data source.
   
   
   
   
3. Edit the data as follows:
   
   • Host: The Fully Qualified Host Name (FQDN) of the Microsoft SQL server (Would the following make sense here (taken from the MS SQL authentication section): if you deploy MS SQL Server Always On Availability Groups, enter the domain name of the Always On Availability Group listener.)
   • Domain: The domain in which the <ad_user> is residing
   • Port: The port on which the Microsoft SQL Server listens for requests. (Default: 1433)
   • User: The <ad_user>; format: <domain_name>\<ad_user>
   • Schema: The database schema
   • Database / SID: The database name 
   • JDBC Parameter (double-click): 
     • sendStringParametersAsUnicode: false
     • trustServerCertificate: true
       
       
       
       
4. Select your database configuration and click Activate.
   
   
   
5. 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 Cluster AD Kerberos type data source.
   
   
   
   
2. Edit the data as follows:
   
   • Host: The Fully Qualified Host Name (FQDN) of the Microsoft SQL server (Would the following make sense here (taken from the MS SQL authentication section): 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)
   • 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.