Microsoft JDBC and Microsoft SQL Server

For a recent project, I was asked to get the Microsoft JDBC working on Redhat with SQL 2016. In the instructions I found online to be blunt they were lacking especially because I am not a programmer. I hope that this post helps someone in the future attempting something similar. First JDBC for Windows and JDBC for Linux work a bit different. At the time I write this there is a version 6.2 for Windows, but the Linux driver is only at 4.2. This leads to one major difference 6.2 supports passing a username and password as part of the connect string for Kerberos on Windows while the 4.2 driver for Linux does not. Below is a java sample code that works on windows without getting into keytabs etc.

 I assume in all this you know you need to add a server principal name to your SQL Server service accounts and have already done that. Using "setspn -a" with a domain admin account. Once you have everything configured if you want to verify something is using Kerberos run this query on SQL "select * from sys.dm_exec_connections" and under auth scheme a Windows query will show Kerberos. The linux client only shows "unknown," but considering you are only able to configure it with Kerberos its pretty safe to assume it working with Kerberos if your query runs.

Windows working code

import java.sql.*;
public class connectURL {

public static void main(String[] args) {
// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://dbserver.example.com:1450;instanceName=instance1;databaseName=SomeDatabase;integratedSecurity=true;authenticationScheme=JavaKerberos;trustServerCertificate=true;userName=account@EXAMPLE.COM;password=password";

// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
        try {
        // Establish the connection.
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(connectionUrl);
            
            // Create and execute an SQL statement that returns some data.
            String SQL = "SELECT * FROM table1";
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            
            // Iterate through the data in the result set and display it.
            while (rs.next()) {
            System.out.println(rs.getString(1) + " " + rs.getString(2));
            }
        }
        
// Handle any errors that may have occurred.
catch (Exception e) {
e.printStackTrace();
}

finally {
if (rs != null) try { rs.close(); } catch(Exception e) {}
    if (stmt != null) try { stmt.close(); } catch(Exception e) {}
    if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}

References for the different parts of the connect string can be found with Microsofts JDBC documentation. 

Linux 

Linux is a bit tougher, as I said above it does not support passing the username and password as part of the connect string. While there is a very good security reason for this, it does make it a bit of a pain. So we have to use the built-in Kerberos support to help us out. There may be a better way of doing this but its how I went about it lifting code etc from other sources. First, you have to have your machine joined to a Kerberos Realm using "realm join -U user" or some other method. You should then be able to run a "KINIT" for an account and get a valid KERBEROS Ticket or log in using an AD account. 
Here is an example  krb5.conf, it doesn't take a lot of options etc for it work for the JDBC

KRB5.CONF

# Configuration snippets may be placed in this directory as well
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 = true
 dns_lookup_kdc= true
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true
 rdns = false
 default_realm = EXAMPLE.COM
 default_ccache_name = KEYRING:persistent:%{uid}

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

Create Keytab

You then need to add your service account to a keytab file this stores the password encrypted for your service account to use when connecting. Thie requires using KTUTIL you will need to have krb5workstation installed I can't remember if any other packages were required. So to create your keytab you will need something like the following. You may also need to use the command "kvno" to get the portion for "-k", but I am not covering that here and its also my understanding Windows doesn't use it anyway. 

  ktutil:  addent -password -p username@EXAMPLE.COM -k 2 -e aes256-cts
  Password for username@EXAMPLE.COM: [enter your password]
  ktutil:  wkt /home/usr/username.keytab

Java-Login.conf

The following piece was where I had some problem piecing things together as it was referred to in several articles, but not covered well. Essentially this following files helps connect the JDBC and Kerberos together for lack of a better way of putting it. 

I created it in my users home drive, but it doesn't really matter long as your code references it I called it .java-login.conf to hide it. I will also say this file is VERY case sensetive even in the useKeyTab portion it needs to be capitalized EXACTLY the same or you will get null error etc. 

java-login {
com.sun.security.auth.module.Krb5LoginModule required
refreshKrb5Config=true
useKeyTab=true
keyTab="/home/username/username.keytab"
debug=true
principal="username@EXAMPLE.COM"
doNotPrompt=true;
};

Working Linux Java code

I used eclipse because I had it I am sure there are other ways. I, of course, had to import the JDBC jar file to get everything to work. 

java.security.PrivilegedExceptionAction;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import javax.security.auth.Subject;
import javax.security.auth.login.LoginContext;
import javax.security.auth.login.LoginException;
public class main {
        public static void main(String[] args) {
                System.setProperty("java.security.auth.login.config", "/home//home/username/.java-login.conf");
                Subject subject = null;
                try {
                    LoginContext loginContext = new LoginContext("java-login");
                    loginContext.login();
                    subject = loginContext.getSubject();
                }
                catch (LoginException e)
                {
                    e.printStackTrace();
                }
        //  This application passes the javax.security.auth.Subject
        //  to the driver by executing the driver code as the subject
try {
                @SuppressWarnings("unchecked")
                Connection con = (Connection) Subject.doAs(subject, new PrivilegedExceptionAction() {
                                    public Object run() {
                                        Connection con = null;
                                    try {
                                         //
                                         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
                                         String url = "jdbc:sqlserver://dbserver.example.com:1450;instanceName=instance1;database=SomeDatabase;integratedSecurity=true;authenticationScheme=JavaKerberos;trustServerCertificate=true";
                                         con = java.sql.DriverManager.getConnection(url);
                                        }
                                     catch (Exception except) {
                                                except.printStackTrace();
                                     //log the connection error
                                           return null;
                                        }
                                        return con;
                                    }
                });
                String SQL = "select * from table1";
                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery(SQL);
                while (rs.next())
                {
                        System.out.println(rs.getString(1));
                }
}
catch (Exception e)     {
        e.printStackTrace();
}
        } // end of method main
} // end of class main


I hope this helps someone. It took me a couple days to figure out, but I got there. A programmer may know a better way to do this etc, but for me it worked for my purposes. Mostly I was just concerned with getting it working and proving it worked so that I could write a guide for other teams to at least get started using it if needed. 



Comments

Post a Comment

Popular posts from this blog

All things Organized

Image
As I mentioned in my last post I was tired of having a folder full of bookmarks to get to everything on my network...I have a bunch of stuff and it sucks. So I had seen someone discussing Organizr ( https://organizr.app/ ) a while back and thought yep its time to make this work. So I set up a new jail on my new FreeNas server as I mentioned in the last post and got to work. So after quite a bit of experimentation, reading, playing, downloading custom JPEGs etc this is where I ended up at. A landing page with all my links that can be easily updated as needed. There are some people out there doing some awesome things with it and I may add more things over time. So here is a few screenshots etc. Some items do not like being in an iframe and some I just let pop out to their own windows. Just depends on how they act.  As I also mentioned the other day I finally got around to getting Lidarr installed in a jail as well. ( https://lidarr.audio/ ) I have been a little l
Read more

Finished SVS upgrades

Image
 Finally replaced my last 2 Polk T-15 surrounds with SVS satellites, not a huge improvement in any way but call it my OCD I like having all the base channels the same. SVS was having a sale on them for 124 each in Piano black. The post on their outlet said they were missing threads on the back, but I can't see anything wrong with them and they have the wall mounting hardware on them...soooo i dunno maybe just a bit overstocked and wanted to get rid of some. I do need to find some better stands as the stands I have do not close tight enough to hold them. I just took some of the cloth from the box and wrapped some door/window shims with it to hold them for now until I find something better.  But it took the better part of 2 years slowly buying the pieces but my main stereo has all been upgraded.  SVS Prime Pinnacles - Piano Black SVS Prime Center - Black ash SVS Prime Elevation - Black ash SVS Satellites - Piano Black 2 Monolith 12 inch Subwoofers - Black ash 1 Polk 12 PSW-505  (prob
Read more

Little Healthcare/Cash for Clunkers Humor

Not much to post today but the following Clunker  Math The person who calculated this bit of information is now and has been a professor at The University of West Virginia in Morgantown, West Virginia for the last forty some years. A clunker that travels 12,000 miles a year at 15 mpg uses 800 gallons of gas a year. The government claims 700,000 clunkers have been replaced so that’s 224 million gallons saved per year. A vehicle  that travels 12,000 miles a year at 25 mpg uses 480 gallons of gas a year. So, the average Cash for Clunkers transaction will reduce gasoline consumption by 320 gallons per year. That  equates to a bit over 5 million barrels of oil~5 million barrels is about 5 hours worth of US consumption. More importantly, 5 million barrels of oil at $70 per barrel costs about $350 million dollars. So, the government paid $3 billion of our tax dollars to save  $350   million.... We  spent  $8.57  for every  $1.00  we saved .   I’m pretty sure they will do a bette
Read more