Lead your team forward
OCT 24 / 9AM ET Register nowHow 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
-
Make sure you have the system default Kerberos configuration file
krb5.conf
. By default, it can be found in the/etc
folder. -
Verify that the
REALM
andDOMAIN_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
-
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 commandkinit -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
-
In case the
kinit
command resulted in an error, check the error details.-
The
kinit
command is not found. If there are nokinit
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 thekinit
command from step 1 withsudo
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.
-
Navigate to the
/etc/systemd/systemd/dpe.service
file. -
Set the
Environment
variable toJAVA_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 updatedEnvironment
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
-
Update Linux services.
sudo systemctl daemon-reload
-
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?