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 Active Directory (AD) authentication via Kerberos.
| Note |
|---|
Using Microsoft Active Directory (AD) to connect your UMS to a Microsoft SQL server requires a deep understanding of your environment. For most environments, it is recommended to use native SQL authentication. |
...
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
Creating a Kerberos Configuration File
The Kerberos configuration file contains the data the system needs to access the domain information.
To learn how a Kerberos configuration file looks, see the following example:
| Code Block |
|---|
[libdefaults]
default_realm = HEX.LOCAL
ticket_lifetime = 24h
[realms]
HEX.LOCAL = { kdc = 111.111.111.111 default_domain = HEX.LOCAL }
[domain_realm]
.hex.local = HEX.LOCAL
[appdefaults] |
For a detailed description of the content,
...
see https://web.mit.edu/kerberos/krb5-1.12/doc/admin/conf_files/krb5_conf.html.
| Info |
|---|
The domain does not have to be identical to the domain where the UMS is installed. |
Saving the Kerberos Configuration File
...
→ Save the Kerberos configuration file in the
...
directory <UMS installation directory>/rmguiserver/conf
...
with the
...
name krb5.conf
Creating the UMS Database
| Anchor | ||||
|---|---|---|---|---|
|
It is recommended to create a separate database with a specific schema for the UMS.
| Note |
|---|
Configuration HintsThe 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.
| Note |
|---|
Do NOT use the schema schema dbo for for the UMS database tables! |
<database_name>: The name for the UMS database<schema_name>: The name of the schema for the UMS databaseCode Block USE [master] GO CREATE DATABASE [<database_name>]; GO USE [<database_name>]; GO CREATE SCHEMA [<schema_name>]; GO
Using the GUI
In SQL Server Management Studio, right-
...
click Databases and select New Database.
...
Under General, give the database a name.
Optionally set additional parameters according to your company requirements.
...
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
| Info |
|---|
It is recommended to create a group in the Windos domain that will contain the users for the database and put the users for the UMS into this group. This group will become the owner of the UMS database, allowing all users in the group to work with the database. |
Adding the User or Group to SQL
| Info |
|---|
Note: If the AD user you are going to use to connect to the Microsoft SQL Server already has an SQL login entry, or is in a group with login access, you can skip this step and continue with with Configuring the UMS User, Schema, and Database Permissions. |
Using the SQL Management Console
In SQL Server Management Studio,
...
select New Query.
Use the following script to create the database login;
...
replace
<ad_user>
...
with the AD user you want to use for connecting.
Code Block USE [master] GO CREATE LOGIN [[<ad_user>]] FROM WINDOWS; GO
Using the SQL Server Management Studio (GUI Mode)
Connect to the database with the SQL Server Management Studio.
Open
...
the Security
...
branch, right-click
...
on Logins and select New Login.
...
Choose Windows Authentication
...
for the login, and click Search.
Click Object Types…,
...
select Groups and Users, and
...
click OK.
...
...
Click Locations..., to choose the location wherein your user or group
...
is residing, and
...
click OK.
...
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
| Anchor | ||||
|---|---|---|---|---|
|
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.
| Code Block |
|---|
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
In SQL Server Management Studio, open the database that was created in
...
...
Under Security > Users, right-
...
click New User.
...
Under General, search for your login name (<ad_user>) and give the user a name.
...
In
...
the Membership
...
area, give the user
...
the db_owner
...
role.
...
Go
...
to Security > Schemas and right-click
...
on New Schema.
Search for the <ums_user> as
...
the Schema owner
...
and provide
...
a Schema name.
...
...
Under Security > Users
...
in your UMS database, double-click on the <ums_user>.
...
Under General, set the default schema to <schema_name>.
...
...
Under Security > Logins > Users, double-click on the <ad_user>.
In
...
the User Mapping
...
area, check the mapping of the UMS database, the user, and the default schema.
...
Configuring the UMS Services
Log into the UMS Server with the credentials configured for connecting to the UMS database on the Microsoft SQL Server.
...
Open services.msc
...
and right-click
...
the IGEL Remote Manager Server
...
service.
...
Select Properties
...
and navigate to
...
the Log On
...
tab.
...
Select This Account
...
and use
...
the Browse
...
button to find the one that owns the SQL database.
...
Depending on whether you are using a single server or a cluster for your Microsoft SQL database, continue
...
...
Connecting the UMS to the Database (Single Instance)
| Anchor | ||||
|---|---|---|---|---|
|
Set up a
...
new SQL Server AD Kerberos
...
type data source.
...
...
Edit the data as follows:
Host: The Fully Qualified Host Name (FQDN) 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.
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
...
Select your database configuration and
...
click Activate.
...
Enter the username and the password for the connection.
...
Connecting the UMS to the Database (Cluster)
| Anchor | ||||
|---|---|---|---|---|
|
In
...
the UMS Administrator, set up a
...
new SQL Server Cluster AD Kerberos
...
type data source.
...
...
Edit the data as follows:
Host: The Fully Qualified Host Name (FQDN) of the Microsoft SQL server
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
...
Select your database configuration and
...
click Activate.
...
Enter the username and the password for the connection.
...