ONE Desktop ONE DQG ONE MDM ONE RDM ONE Runtime Server
latest (15.1.0) 14.5.x 13.9.x 13.8.x 13.7.x 13.6.x
User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

How to Connect to MS SQL Using Kerberos Authentication to Windows Active Directory

To use Windows Integrated Security for accessing MS SQL data sources, you need to configure Kerberos authentication on your machine.

The following guide explains how to configure Kerberos and enable Kerberos authentication for Data Processing Engine (DPE).

Kerberos Authentication is available only for self-managed or hybrid DPE.

Configure Kerberos

  1. Make sure you have the system default Kerberos configuration file krb5.conf. By default, it can be found in the /etc folder.

  2. Verify that the REALM and DOMAIN_REALM mappings have been correctly set. The following example illustrates the structure of the configuration file.

    Example of krb5.conf 
    cat /etc/krb5.conf
# To opt out of the system crypto-policies configuration of krb5, remove the
# symlink at /etc/krb5.conf.d/crypto-policies which will not be recreated.
includedir /etc/krb5.conf.d/

[logging]
default = FILE:/var/log/krb5libs.log
kdc = FILE:/var/log/krb5kdc.log
admin_server = FILE:/var/log/kadmind.log

[libdefaults]
dns_lookup_realm = false
ticket_lifetime = 24h
renew_lifetime = 7d
forwardable = true
rdns = false
pkinit_anchors = FILE:/etc/pki/tls/certs/ca-bundle.crt
spake_preauth_groups = edwards25519
# default_realm = EXAMPLE.COM
default_ccache_name = KEYRING:persistent:%{uid}

[realms]
# EXAMPLE.COM = {
# kdc = kerberos.example.com
# admin_server = kerberos.example.com
# }

[domain_realm]
# .example.com = EXAMPLE.COM
# example.com = EXAMPLE.COM

Verify authentication

  1. Verify that the authentication works properly. To do so, run the following kinit command:

    Make sure to update the Principal name accordingly.
    Example of the kinit command
    kinit -V -c krb5cc ata.admin@EXAMPLE.COM

    The expected output is as follows:

    kinit -V -c krb5cc ata.admin@EXAMPLE.COM
Using specified cache: krb5cc
Using principal: ata.admin@EXAMPLE.COM
Password for ata.admin@EXAMPLE.COM:
Authenticated to Kerberos v5

  2. In case the kinit command resulted in an error, check the error details.

    • The kinit command is not found. If there are no kinit components installed on the server, use one of the following commands depending on your OS.

      kinit -V -c krb5cc ata.admin@EXAMPLE.COM

Command 'kinit' not found, but can be installed with:

sudo apt install krb5-user        # version 1.17-6ubuntu4.1, or
sudo apt install heimdal-clients  # version 7.7.0+dfsg-1ubuntu1
      These commands apply only to Ubuntu OS. Packages for RHEL can be different. See your error message for details.

    • Failed to store credentials: Credentials cache permissions incorrect. A similar error occurs when sudo permissions are not used.

      kinit -V -c krb5cc ata.admin@EXAMPLE.COM
Using specified cache: krb5cc
Using principal: ata.admin@EXAMPLE.COM
Password for ata.admin@EXAMPLE.COM:
kinit: Failed to store credentials: Credentials cache permissions incorrect (filename: krb5cc) while getting initial credentials

      This means that you need to run the kinit command as a power user. Rerun the kinit command from step 1 with sudo permissions.

      sudo kinit -V -c krb5cc ata.admin@EXAMPLE.COM

Enable Kerberos authentication for DPE

After successfully authenticating with Kerberos, you are ready to start DPE.

  1. Navigate to the /etc/systemd/systemd/dpe.service file.

  2. Set the Environment variable to JAVA_OPTS=-Djava.security.krb5.conf=/etc/krb5.conf and save the changes. The following example illustrates the /etc/systemd/systemd/dpe.service file with the updated Environment variable.

    /etc/systemd/systemd/dpe.service 
    [Unit]
Description=Service wrapper for java application dpe
After=syslog.target

[Service]
Environment="JAVA_OPTS=-Djava.security.krb5.conf=/etc/krb5.conf"
User=dpe
Group=dpe
ExecStart=/opt/ataccama/one/dpe/bin/start.sh
ExecStop=
WorkingDirectory=/opt/ataccama/one/dpe
Restart=always
RestartSec=5
SyslogIdentifier=dpe

[Install]
WantedBy=multi-user.target

  3. Update Linux services.

    sudo systemctl daemon-reload

  4. Restart DPE.

    sudo systemctl restart dpe

Create a new data source

Once DPE is running and registered with Data Processing Module (DPM), you can now create an MS SQL data source in ONE.

For more detailed instructions, see Relational Database Connection.

Troubleshooting

This section is intended to provide support in case you are facing some of the most common authentication issues.

Connect to a data source using command line

To connect to an MS SQL data source directly from the command line, add the following properties to the kinit command:

Make sure to provide the correct database server and the port accordingly. 
sudo kinit -V -c krb5cc -S MSSQLSvc/mssql.db.hostname:1433 ata.admin@EXAMPLE.COM

The expected output is as follows:

sudo kinit -V -c krb5cc -S MSSQLSvc/mssql.db.hostname:1433 ata.admin@EXAMPLE.COM
Using specified cache: krb5cc
Using principal: ata.admin@EXAMPLE.COM
Password for ata.admin@EXAMPLE.COM:
Authenticated to Kerberos v5

Display your tickets

To ensure that Kerberos authentication is enabled, use the klist command to display a list of currently cached Kerberos tickets.

klist -c krb5cc

The expected output is as follows:

klist -c krb5cc
Ticket cache: FILE:krb5cc
Default principal: ata.admin@EXAMPLE.COM

Valid starting      Expires             Service principal
16.2.2022 13:45:11  16.3.2022 23:45:11  MSSQLSvc/mssql.db.hostname:1433@EXAMPLE.COM
    renew until 1.4.2022 13:45:06

Enable debug logging

To get more detailed information about issues with Kerberos authentication, you can enable Kerberos debug logging.

To do so, execute the following command, then run the kinit command again.

For more information about how to run the kinit command, see step Verify authentication. 
export KRB5_TRACE=/dev/stdout

The expected output is as follows:

Connecting to a data source with debug logging enabled 
sudo kinit -V -c krb5cc -S MSSQLSvc/mssql.db.hostname:1433 ata.admin@EXAMPLE.COM
Using specified cache: krb5cc
Using principal: ata.admin@EXAMPLE.COM
[95161] 1630414420.469142: Getting initial credentials for ata.admin@EXAMPLE.COM
[95161] 1630414420.469143: Setting initial creds service to MSSQLSvc/mssql.db.hostname:1433
[95161] 1630414420.469145: Sending unauthenticated request
[95161] 1630414420.469146: Sending request (208 bytes) to EXAMPLE.COM
[95161] 1630414420.469147: Resolving hostname tes.EXAMPLE.COM
[95161] 1630414420.469148: Sending initial UDP request to dgram 172.18.51.130:88
[95161] 1630414420.469149: Received answer (211 bytes) from dgram 172.18.51.130:88
[95161] 1630414420.469150: Response was not from master KDC
[95161] 1630414420.469151: Received error from KDC: -1765328359/Additional pre-authentication required
[95161] 1630414420.469154: Preauthenticating using KDC method data
[95161] 1630414420.469155: Processing preauth types: PA-PK-AS-REQ (16), PA-PK-AS-REP_OLD (15), PA-ETYPE-INFO2 (19), PA-ENC-TIMESTAMP (2)
[95161] 1630414420.469156: Selected etype info: etype aes256-cts, salt "EXAMPLE.COMata.admin", params ""
Password for ata.admin@EXAMPLE.COM:
[95161] 1630414432.622729: AS key obtained for encrypted timestamp: aes256-cts/B7A3
[95161] 1630414432.622731: Encrypted timestamp (for 1630414432.784156): plain 301AA011180F32303231303833313132353335325AA10502030BF71C, encrypted E196B3BE3CA7E29C1F661E2039B3173BF21148A6A09269A0045D29D173D5EF305C78F0CD1676D322CF69678F2D57C97604070DAB1163579D
[95161] 1630414432.622732: Preauth module encrypted_timestamp (2) (real) returned: 0/Success
[95161] 1630414432.622733: Produced preauth for next request: PA-ENC-TIMESTAMP (2)
[95161] 1630414432.622734: Sending request (288 bytes) to EXAMPLE.COM
[95161] 1630414432.622735: Resolving hostname tes.EXAMPLE.COM
[95161] 1630414432.622736: Sending initial UDP request to dgram 172.18.51.130:88
[95161] 1630414432.622737: Received answer (114 bytes) from dgram 172.18.51.130:88
[95161] 1630414432.622738: Response was not from master KDC
[95161] 1630414432.622739: Received error from KDC: -1765328332/Response too big for UDP, retry with TCP
[95161] 1630414432.622740: Request or response is too big for UDP; retrying with TCP
[95161] 1630414432.622741: Sending request (288 bytes) to EXAMPLE.COM (tcp only)
[95161] 1630414432.622742: Resolving hostname tes.EXAMPLE.COM
[95161] 1630414432.622743: Initiating TCP connection to stream 172.18.51.130:88
[95161] 1630414432.622744: Sending TCP request to stream 172.18.51.130:88
[95161] 1630414432.622745: Received answer (1654 bytes) from stream 172.18.51.130:88
[95161] 1630414432.622746: Terminating TCP connection to stream 172.18.51.130:88
[95161] 1630414432.622747: Response was not from master KDC
[95161] 1630414432.622748: Processing preauth types: PA-ETYPE-INFO2 (19)
[95161] 1630414432.622749: Selected etype info: etype aes256-cts, salt "EXAMPLE.COMata.admin", params ""
[95161] 1630414432.622750: Produced preauth for next request: (empty)
[95161] 1630414432.622751: AS key determined by preauth: aes256-cts/B7A3
[95161] 1630414432.622752: Decrypted AS reply; session key is: aes256-cts/B06E
[95161] 1630414432.622753: FAST negotiation: unavailable
[95161] 1630414432.622754: Initializing FILE:krb5cc with default princ ata.admin@EXAMPLE.COM
[95161] 1630414432.622755: Storing ata.admin@EXAMPLE.COM -> MSSQLSvc/mssql.db.hostname:1433@EXAMPLE.COM in FILE:krb5cc
[95161] 1630414432.622756: Storing config in FILE:krb5cc for MSSQLSvc/mssql.db.hostname:1433@EXAMPLE.COM: pa_type: 2
[95161] 1630414432.622757: Storing ata.admin@EXAMPLE.COM -> krb5_ccache_conf_data/pa_type/MSSQLSvc\/mssql.db.hostname:1433\@EXAMPLE.COM@X-CACHECONF: in FILE:krb5cc
Authenticated to Kerberos v5

Was this page useful?