Windows Authentication for MSSQL Server

This feature is available in v42 and later.

Microsoft offers two authentication methods for MSSQL databases: SQL Server authentication and Windows authentication. Windows authentication is Microsoft's recommended authentication method, as it leverages Linux's Kerberos Ticket Granting Ticket (TGT) and provides a higher level of security than SQL Server authentication. As a result, implementing Windows authentication can reduce potential compliance overhead and facilitate installations and upgrades in Microsoft ecosystems.

By default, MSSQL databases in Hyperscience use SQL Server authentication. The steps required to enable Windows authentication for MSSQL Server access depend on which type of ccache you’re using: KCM or FILE.

KCM ccache

This article provides one illustrative way to perform the setup. You should adapt it as needed to fit your organization's Active Directory configuration, security policies, operational processes, and automation tools.

Create an Active Directory (AD) user that will be used by Hyperscience to authenticate to SQL Server.
Create the database and grant proper permissions to the AD user.
Set up Kerberos TGT on every machine running Hyperscience.

Add relevant information to the “.env” file and start the application.

Create an AD User for Windows authentication.

New-ADUser -Name "hsuser" -SamAccountName "hsuser" -AccountPassword (Read-Host -AsSecureString "Enter Password") -Enabled $true
Set-ADUser -Identity "hsuser" -PasswordNeverExpires $true
Get-ADUser hsuser

Export keytab for the user (hsuser).

ktpass -princ hsuser@EXAMPLE.COM -mapuser hsuser@EXAMPLE.COM -pass <password> -out C:\hsuser.keytab -ptype KRB5_NT_PRINCIPAL -crypto AES256-SHA1

Configure SQL Server to allow hsuser to log in.

CREATE LOGIN [EXAMPLE\hsuser] FROM WINDOWS;
USE [YourDatabase];
CREATE USER [EXAMPLE\hsuser] FOR LOGIN [EXAMPLE\hsuser];
ALTER ROLE db_owner ADD MEMBER [EXAMPLE\hsuser];
SELECT name, type_desc FROM sys.database_principals WHERE name = 'EXAMPLE\hsuser';

You should also perform any other additional configuration as outlined in MSSQL.

Copy keytab to RHEL and set the required permissions.

sudo cp /path/to/hsuser.keytab /etc/hsuser.keytab
sudo chown 1000:1000 /etc/hsuser.keytab
sudo chmod 600 /etc/hsuser.keytab

Allow containerized Hyperscience to access the SSSD Kerberos credential socket.
Hyperscience runs in containers, so you must allow containerized processes to access the SSSD Kerberos credential socket via the SELinux policy.
Create the following policy module in a file named container_sssd_socket.te:
```
module container_sssd_socket 1.0;
require {
    type container_t;
    type sssd_var_run_t;
    class sock_file { read write getattr open };
    class unix_stream_socket { connectto };
}
allow container_t sssd_var_run_t:sock_file { read write getattr open };
allow container_t sssd_var_run_t:unix_stream_socket connectto;
```
Then, compile and install the policy module:
```
checkmodule -M -m -o container_sssd_socket.mod container_sssd_socket.te
semodule_package -o container_sssd_socket.pp -m container_sssd_socket.mod
sudo semodule -i container_sssd_socket.pp
```
This policy is required because, by default, SELinux restricts container processes (container_t) from accessing the SSSD socket (sssd_var_run_t). The above steps create and install a custom policy module to grant the needed access.

Set up the systemd service and timer for TGT renewal.

This guide assumes your OS user with uid 1000 is also named hsuser. Adjust commands and configuration if your environment differs.

# /etc/systemd/system/hsuser-tgt-renew.service
sudo tee /etc/systemd/system/hsuser-tgt-renew.service > /dev/null <<EOF
[Unit]
Description=Renew Kerberos TGT for hsuser
[Service]
Type=simple
User=hsuser
ExecStart=/usr/bin/kinit -kt /etc/hsuser.keytab hsuser@EXAMPLE.COM
EOF

# /etc/systemd/system/hsuser-tgt-renew.timer
sudo tee /etc/systemd/system/hsuser-tgt-renew.timer > /dev/null <<EOF
[Unit]
Description=Periodic Kerberos TGT renewal for hsuser
[Timer]
OnBootSec=1min
OnUnitActiveSec=30min
[Install]
WantedBy=timers.target
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now hsuser-tgt-renew.timer

Modify the “.env” file.

Update your “.env” file with the following example configuration:

FORMS_DB_TYPE=mssql
FORMS_DB_HOST=SQLSERVER-SPN.EXAMPLE.COM
FORMS_DB_NAME=YourDatabase
FORMS_DB_USER=
FORMS_DB_PASS=
SQLSERVER_OPTIONS_EXTRA_PARAMS=Encrypt=Yes;TrustServerCertificate=Yes;Trusted_Connection=Yes;
HS_KERBEROS_KCM_SOCKET=/run/.heim_org.h5l.kcm-socket
KRB5CCNAME=KCM:

Provide any other applicable extra parameters as listed in MSSQL.

Verify the configuration.
Run ./run.sh manage check and ensure that there are no errors.

Your Hyperscience installation should now be configured to access SQL Server using Windows authentication.

If you are installing a new Hyperscience, you can proceed with the rest of the installation steps.

FILE ccache

This document provides one illustrative way to perform the setup. You should adapt it as needed to fit your organization's Active Directory configuration, security policies, operational processes, and automation tools.

Create an Active Directory (AD) user that will be used by Hyperscience to authenticate to SQL Server.
Create the database and grant proper permissions to the AD user.
Set up Kerberos TGT on every machine running Hyperscience.