Kerberos Protocol to allow AD domain accounts to connect to SQL database

[sai_sp]

Use-Case: 

Many customer's has the requirement to use windows domain account for Database connectors.

Steps: ONLY FOR NON EIC

1. Create login at SQL Server for the user which will be utilized at connector.
2. Create a ‘krb5.conf’ file containing information of the AD Domain and Domain Realm. Sample file is shared in the below link:

https://drive.google.com/file/d/15LXdo8rB9sOxufsWT6beYUf1840LsCpr/view?usp=sharing

• Please use the same case for domain/realm/kdc name as it is there in the attached sample file.

1. Create a key tab file for the user. Run the below command on Domain Controller machine to generate the keytab file. Keytab file has to be placed on the same server where SSM is running :
   • ktab -a U239093@SAVPOC.COM

                Here U239093@SAVPOC.COM is the UPN of the user on AD Server.

1. Create SQLJDBCDriver.conf(login conf file) as per the sample file attached. Only ketab file location and user principal has to be changed.

1. Remove old MS SQL driver jar(sqljdbc4.jar) from ECM/lib directory and replace with attached ‘mssql-jdbc-7.0.0.jre8.jar’.  

1. Add the below two properties in the Catalina startup.sh file:

            export JAVA_OPTS="$JAVA_OPTS -Djava.security.krb5.conf=/datadrive/sharedappdrive/saviynt/Kerbros/krb5.conf   -                Djava.security.auth.login.config=/datadrive/sharedappdrive/saviynt/Kerbros/SQLJDBCDriver.conf" .

        7. Take the application server restart .

        8. Sample connection URL is as below :

            jdbc:sqlserver://desktop-8gqhcvq.savpoc.com:1433;DatabaseName=SAVMSSQL;authenticationScheme=JavaKerberos;integratedSecurity=true;userName=${USERNAME};password=${PASSWORD}

Note : Use the same user and password at connector for which keytab file was generated.

Acceptance criteria: 

1. With the above config changes, I should be able to connect to MS SQL DB using Kerberos authentication using AD domain accounts.

2. I should be able to enter the user principal name and the password in USERNAME and PASSWORD in connection properties and the URL should be constructed as shown above using these values.

[rushikeshvartak]

Drive link is not working https://drive.google.com/file/u/0/d/15LXdo8rB9sOxufsWT6beYUf1840LsCpr/view?usp=sharing&pli=1

[RajeshA]

@rushikeshvartak 

Above drive link you pasted also not working. Can we have a updated one

@sai_sp Which email address are we talking here, is this email address U239093@SAVPOC.COM is for the service account we are using to authenticate with Microsoft SQL Server 

• ktab -a U239093@SAVPOC.COM

[rushikeshvartak]

use sample file - https://docs.saviyntcloud.com/bundle/Database-v24x/page/Content/Appendix.htm

krb5.conf

SQLJDBCDriver.conf

[RajeshA]

@rushikeshvartak and @sai_sp 

Our domain controller doesn't have java installed, so we generated the keytab file by using a member computer as per below note

I am going to open a Saviynt ticket to upload this on to the server

But This keytab file should be placed where on our client end ? Does it need to be stored in some location on member computer (on where keytab is generated) and have that path in SQLJDBCDriver.conf or need to be copied to domain controller and store it there and have that path in domain controller and store it in SQLJDBCDriver.conf

[Darshanjain]

Hi @RajeshA 

Place the keytab file where the ssm is running , ideally it should be domain controller and keep the path correctly in the conf file and try it out.

Thanks

Darshan

[RajeshA]

@Darshanjain 

SSM is not running on domain controller, it is cloud hosted. 

We need to place keytab file in two locations. Correct me if I am wrong

1. Place on server where Saviynt is running, since we are on 5.5 and using cloud version. This happens through a ticket with Saviynt .
2. Second location is where I have a question, Does this need to placed on a domain controller (where java is not installed, in my case) or it needs to placed in any domain controlled machine (where java is installed), from where I generated the key tab file.

[Darshanjain]

Hi @RajeshA 

Okay, please keep the key tab in the domain controller.

Thanks

Darshan

[Suresh1]

Hello @Darshanjain ,

We have raised an internal ticket with our infra team to have the generated Key Tab file placed in the DC. But our infra team is not okay with this approach as per the security and other constraints this file cannot be placed on the DC.

Is it okay to place this in any of the member computers ? 

or please let us know if we have any alternative workaround for this ?

Regards,

Suresh V.

[Suresh1]

Hello Team,

Could you please guide us here ?

Regards,

Suresh V.