# Experiment: Kerberos Lifetime (including JDBC driver for SQL server)

###### tags: `work` `experiment`

## Kerberos user commands

### Will ticket renewal reset the renew lifetime?
1. Initialize TGT.

   [user@example ~]$ kinit [e-mail]
   Password for [e-mail]: 
   [user@example ~]$ klist
   Ticket cache: KCM:1000
   Default principal: [e-mail]
  
   Valid starting     Expires            Service principal
   02/04/23 10:03:06  02/04/23 20:03:06  krbtgt/[e-mail]
           renew until 02/11/23 10:03:02

This TGT:
- will expire at 02/04/23 20\:03\:06.
- can be renewed before 02/11/23 10\:03\:02.

2. Renew TGT

   [user@example ~]$ kinit -R [e-mail]
   [user@example ~]$ klist
   Ticket cache: KCM:1000
   Default principal: [e-mail]

   Valid starting     Expires            Service principal
   02/04/23 10:03:22  02/04/23 20:03:22  krbtgt/[e-mail]
           renew until 02/11/23 10:03:02

After renewing:
- The expire time is changed.
- **<u>The renew lifetime doesn't change.</u>**

Ans: No.

---

## JDBC driver for SQL server

### Experiment 1: If `userName` and `password` are provided in the JDBC connection string, what will happen if the TGT expires?

Connection string:

jdbc:sqlserver://{SQL_SERVER_HOST}:1433;
  database={DB_NAME};
  encrypt=true;
  trustServerCertificate=true;
  integratedSecurity=true;
  authenticationScheme=JavaKerberos;
  userName={KERBEROS_PRINCIPAL};
  password={PASSWORD};

Run an application which uses connection pool for DB connection, and check if there're 10 acitve connections by running ([ref](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-connections-transact-sql?view=sql-server-ver16))

SELECT * FROM sys.dm_exec_connections;


Refer to [this document](https://web.mit.edu/kerberos/krb5-devel/doc/user/tkt_mgmt.html), the default ticket lifetime is 10 hours on many systems.

Therefore, leave it until next day.

Check it again.


The `connect_time` changed! The interval is about 16 hours.

And again, next day.

The interval is about 24 hours.

Therefore, by the result of the experiment, I guess the driver will renew the TGT about every 8 hours.

But what will happen if the renew lifetime expires?

### Experiment 1.1: What will happen if the renew lifetime expires?
1. The default renew lifetime is 7 days, which is too long for an experiment. Modify the maximum lifetime of the ticket granted by the KDC.

Refer to [this document](https://www.ibm.com/docs/en/elm/6.0?topic=encryption-enforcing-algorithms-domain-clients), the Kerberos policy is at Group Policy Management Editor, <u>Default Domain Policy > Computer Configuration > Policies > Windows Settings > Security Settings > Account Policies > Kerberos Settings</u>.

Set these lifetime to the minimum value.
|Policy|Policy Setting|
|-|-|
|Maximum lifetime for service ticket|10 minutes|
|Maximum lifetime for user ticket|1 hours|
|Maximum lifetime for user ticket renewal|1 days|


2. Initialize a TGT to check the change is effective.

   [user@example ~]$ kinit [e-mail]
   Password for [e-mail]: 
   [user@example ~]$ klist
   Ticket cache: KCM:1000
   Default principal: [e-mail]

   Valid starting     Expires            Service principal
   02/06/23 17:10:02  02/06/23 18:10:02  krbtgt/[e-mail]
           renew until 02/07/23 17:10:02

**Checked!**

3. Run the same application as the previous experiment, and check if the connections exist.

4. Wait 1 hour and 1 day.

5. at tomorrow 8:48 AM.
Seems like the connections were renewed every hour. Wait until 9:48 to verify this assumption.
The connection were renewed automatically before expired!

at tomorrow 05:17 PM (more than 1 day after).

The connections are still there.

### Experiment 2: If `userName` and `password` aren't provided in the JDBC connection string, what will happen if the TGT expires?

1. The settings of this experiment is the same as experiment 1, except that the connection string doesn't have `userName` and `password`:

    jdbc:sqlserver://{SQL_SERVER_HOST}:1433;
      database={DB_NAME};
      encrypt=true;
      trustServerCertificate=true;
      integratedSecurity=true;
      authenticationScheme=JavaKerberos;

2. Run `kinit` manually (shorten lifetime to 1 minute), and run `klist` to check the TGT.

    [user@example ~]$ kinit -l 2m [e-mail]
       Password for [e-mail]: 
       [user@example ~]$ klist
       Ticket cache: KCM:1000
       Default principal: [e-mail]

       Valid starting     Expires            Service principal
       02/07/23 10:15:08  02/07/23 10:17:56  krbtgt/[e-mail]
               renew until 02/08/23 10:15:08

3. Run the same application, check if the connections exist.

4. Wait 2 minutes.
The connection weren't renewed. Maybe it's because the service ticket hasn't expired. Wait until 10\:25:08 (10 minutes after).

At 10\:28:14, still aren't renewed. Wait until the default TGT lifetime expired (11\:15:08, 1 hour after).

After 1 hour, the connection is no longer available.

And the application also throws some exceptions.


Ans: After TGT expired, the connection is no longer available.

### Experiment 3: Does JDBC driver for SQL server renew the TGT? or does it just initialize another new TGT?

#### How to test it
* <u>**Experiment 3.1**</u>: Check if the modification of passowrd will affect the TGT renewal.
1. Initialize a TGT with principal `principal` with TGT.
2. Modify the password of `principal`.
3. Renew TGT with `kinit -R`. Check if it's passed.
4. Re-initialize TGT with old passord. This step should fail.
* <u>**Experiment 3.2**</u>: Check if the modification of passowrd will affect JDBC driver when the TGT expires.
1. Run application with connection string containing `userName` and `password`.
2. Before the TGT expires, modify the password of the principal used for this connection.
3. Wait until the TGT expires, and if
1. the application throws errors, it means the driver is just re-initialize the TGT.
2. the application works fine, and the connections still exist, it means the driver renew the TGT.

#### Experiment 3.1
Check if the modification of passowrd will affect the TGT renewal.
1. Initialize a TGT with principal `principal` with TGT.

    [user@example ~]$ kinit [e-mail]
    Password for [e-mail]: 
    [user@example ~]$ klist
    Ticket cache: KCM:1000:875
    Default principal: [e-mail]

    Valid starting     Expires            Service principal
    02/07/23 17:06:40  02/07/23 18:06:40  krbtgt/[e-mail]
            renew until 02/08/23 17:06:40

2. Modify the password of `principal`.
3. Renew TGT with `kinit -R`. Check if it's passed.

    [user@example ~]$ kinit -R [e-mail]
    [user@example ~]$ klist
    Ticket cache: KCM:1000:875
    Default principal: [e-mail]

    Valid starting     Expires            Service principal
    02/07/23 17:07:25  02/07/23 18:07:25  krbtgt/[e-mail]
            renew until 02/08/23 17:06:40

**passed!**
4. Re-initialize TGT with old passord. This step should fail.

    [user@example ~]$ kinit [e-mail]
    Password for [e-mail]: 
    kinit: Password incorrect while getting initial credentials

Conclusion: The modification of passowrd won't affect the TGT renewal.

#### Experiment 3.2
1. get current connections
2. change password.
3. Wait utill the TGT expires.
4. The application throws exceptions, and the connections are gone!

Therefore, what JDBC driver for SQL server does for the TGT lifetime is to **get a new TGT** whenever the TGT is about to expire.