Use provided username and password for JavaKerberos

[jedvardsson]

It would be great if the driver could pick up username and password from the connection settings instead of having to be provided through "kinit".

[Suraiya-Hameed]

If you are referring to the principal name and principal's Kerberos password used to generate TGT through kinit, using those values in connection string will defeat the purpose of Kerberos protocol.

Either TGT or keytab file is used by JavaKerberos to prove that client knows the password without actually revealing it, thus making it is more secure. You could always use username and password for authentication instead of going for integrated authentication like Kerberos.

[hoeflerb]

That's correct. However, mssqlc-jdbc can handle getting the TGT for the user if the user doesn't already have a token. That way they don't have to pull up a terminal and type 'kinit' before connecting. When you pass doNotPrompt=false to Krb5LoginModule, it will trigger a CallbackHandler if no valid credentials are found where a username and password can be entered to acquire a new TGT. But that CallbackHandler has to be provided by the interface, in this case mssql-jdbc.

Having spent a bunch of time dealing with the Kerberos ccache on OS X, I think this is better for the end user. Often the TGT is non-existent, expired, or stored in the memory cache where Krb5LoginModule can't get to it, which forces the user to do extra steps if they are running a client that needs to connect to MSSQL.

[hoeflerb]

Also, in our case where MS SQL Server is configured to only allow Kerberos/SSPI logins (which is not an uncommon configuration), simple password authentication is not an option.

[pierresouchay]

Actually, you don't need kinit with the JVM. You just need à JAAS config file named .java.login.config in the home directory of the user with a keytab for the technical user. It works on both mac, linux and windows very well.

About ticket expiration, it can be configured in the jaas config file.

[hoeflerb]

Yes, but then you need to maintain a keytab. While it works, there are two reasons why I don't like it generally: 1) You need to update the keytab every time you change your password (not a problem for a technical user, but not good for a general user), and 2) A keytab is basically your authentication token stored on disk. It can be easily stolen and used by someone else. If you password protect the token, then you have to unlock it before you can use it, which brings the problem back to needing a username/password dialog presented to the user.

[Suraiya-Hameed]

@hoeflerb JDBC driver currently doesn't support doNotPrompt=false. We will consider it for future enhancement.

[jedvardsson]

Yes, I understand the purpose of Kerberos, but how can I log in a windows domain user using username and password? It seems that only users local to the actual database server can be logged in using username and password (as @hoeflerb mentions this might be a MS SQL Server configuration issue, but it is outside of my control) .

The app that needs to authenticate is a server side app where password is kept in a protected file. At the moment there is no way we can run "kinit" when deploying the app.

[pierresouchay]

@jedvardsson I implemented it: #163

[Suraiya-Hameed]

@jedvardsson safe option to log in a windows domain user would be to use Kerberos constrained delegation. It is implemented in #178. Let us know if this works for you.

[pierresouchay]

@v-suhame do you plan as well to integrate PR #163 that supports username/passwords (and quicker failure in case of wrong credentials)?

[ajlam]

@pierresouchay, @jedvardsson, I'm trying to understand the scenarios in which authenticating using Kerberos and password is needed. Can you help provide some more examples?

[hoeflerb]

@ajlam The principle reason is that, for Kerberos authentication, the TGT has to be maintained by the user somehow. Sometimes there is a facility within the OS to acquire and renew the TGT automatically for the user, but this is not always the case. Another scenario might be the user needing a different TGT from the one provided automatically by the OS. In either case, the very user-unfriendly way of doing this is to tell your users to "kinit" before trying to make a database connection using your app. Since Krb5LoginModule supports the ability to acquire a TGT for the user with provided credentials, it seems mssql-jdbc should use it to provide a more user-friendly experience.

[pierresouchay]

@ajlam I connect to 2 databases kerberos enabled from several realms without trusts from a GUI, in each realm I have a different user in that realm.

My principal on my workstation is not known to those realms.

[ajlam]

@pierresouchay, @jedvardsson, @hoeflerb - apologies for the delay in getting this issue resolved. We're hoping to merge PR #163 within the next few releases.

[jedvardsson]

Great. I appreciate your work with this.

[jansohn]

I desperately need this feature, too. I hope this is merged soon!

[Suraiya-Hameed]

@hoeflerb, @jedvardsson, we have tested and merged PR #163, thanks to @pierresouchay! Lets leave this issue open until the same is fixed for IBM JVM.

[Suraiya-Hameed]

@hoeflerb @jedvardsson this issue is fixed by #163 and #254. Let us know if it works for you, so we can go ahead and close the issue.

[jedvardsson]

@v-suhame I can't get it working, but I'm probably doing something wrong. I've checked out the master branch and built a 6.1.6-SNAPSHOT using maven. Tested it using the following program and .java.login.config

public class Main {

    public static void main(String[] args) throws SQLException {
        String url = "jdbc:sqlserver://myserver:1433;integratedSecurity=true;authenticationScheme=JavaKerberos";
        try (Connection ds = DriverManager.getConnection(url, "myuser", "mypassword"); Statement s = ds.createStatement()) {
            s.execute("select 1");
            try (ResultSet rs = s.getResultSet()) {
                rs.next();
                int i = rs.getInt(1);
                if (i != 1) {
                    throw new IllegalStateException("Expected 1 got " + i);
                }
            }
        }
        System.out.println("Success!");
    }
}

$ cat ~/.java.login.config
SQLJDBCDriver {
   com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true doNotPrompt=true;
};

The program results in the following stack trace:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:d7ae1e7f-cb2a-42a7-b21b-edb66cfb1e9e
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2347)
	at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:154)
	at com.microsoft.sqlserver.jdbc.KerbAuthentication.GenerateClientContext(KerbAuthentication.java:294)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:3945)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3070)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:82)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3034)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7026)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2390)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1972)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1651)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1497)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:860)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:565)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at Main.main(Main.java:13)
Caused by: javax.security.auth.login.LoginException: Unable to obtain Principal Name for authentication 
	at com.sun.security.auth.module.Krb5LoginModule.promptForName(Krb5LoginModule.java:841)
	at com.sun.security.auth.module.Krb5LoginModule.attemptAuthentication(Krb5LoginModule.java:704)
	at com.sun.security.auth.module.Krb5LoginModule.login(Krb5LoginModule.java:617)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at javax.security.auth.login.LoginContext.invoke(LoginContext.java:755)
	at javax.security.auth.login.LoginContext.access$000(LoginContext.java:195)
	at javax.security.auth.login.LoginContext$4.run(LoginContext.java:682)
	at javax.security.auth.login.LoginContext$4.run(LoginContext.java:680)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.login.LoginContext.invokePriv(LoginContext.java:680)
	at javax.security.auth.login.LoginContext.login(LoginContext.java:587)
	at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:148)
	... 15 more

[pierresouchay]

@jedvardsson Master branch does not have it... branch dev does

[hoeflerb]

Works as advertised. However, as a point of clarification, this does not enable doNotPrompt=false. It took me a few tries before I realized it was expecting user= and password= in the connection string.

[pierresouchay]

@jedvardsson and btw, remove your .java.login.config file or remove the attribute:
doNotPrompt=true (Which basically remove the ability to use the provided username and password)

[pierresouchay]

@hoeflerb doNotPrompt is not set when using default config. If you specify it in a jaas file, it will disable the feature however.

It should work with both getConnection(URL, login, password) or within the URL with getConnection(URL)

[hoeflerb]

@pierresouchay Is it possible to create an actual prompt dialog for the username and password? Or is that too complicated? Storing the username and password in the connection string/login.config file works and is easier to update for a non-technical user than a keytab, but an SSO login credential stored in a plaintext config file seems dangerous from a security perspective.

[pierresouchay]

Use a GUI such as DBeaver, or implement this in your app.

Prompting the user is not doable easily: in text mode? With a Swing dialog box? While it might work in your app, it might not in a server.

Most applications requesting a database have usually a way to query the user for username/password to connect to database, those patches make it doable while using Kerberos, nothing more

[hoeflerb]

Yes, ok, I see what you are saying. It would be difficult to write a generic mechanism for mssql-jdbc itself to prompt for the username/password because it would need to be tailored to the specific environment (text, gui, remote, headless, etc).

I'm not actually writing my own app. I'm using a commercial product that uses mssql-jdbc to connect to SQL Server via Kerberos. My interest in this stems from a problem on OS X, where JavaKerberos does not read the TGT from the memory cache, but instead attempts to look for it in a file cache. Since the TGT isn't there (hasn't been since ca 10.3), the Kerberos authentication mechanism for mssql-jdbc fails, so the user has to maintain a separate and independent ticket cache to use Kerberos with mssql-jdbc. I was hoping the doNotPrompt mechanism would be an easy enough workaround because I think it would be harder to implement support for the memory cache in JavaKerberos.

[pierresouchay]

@hoeflerb It is perfectly doable to have the ticket read from Memory cache, I am doing this myself (my Mac is integrated into an AD domain, so when I connect, I already have a Kerberos ticket with my identity).

kinit on Mac OS also supports using the Keychain for storing the user credentials for your cache, so for instance, launching the product with kinit --keychain not requesting the user to write the password each time.

[hoeflerb]

@pierresouchay when you say you are reading the ticket from the memory cache, do you mean you are using mssql-jdbc with integratedAuthentication=true;authenticationScheme=JavaKerberos;useTicketCache=true and it works just like that with your managed identity? Or are you using a keytab? I tried for many hours to get JavaKerberos to read from the memory cache. Ultimately, the only way I was able to make it work was to kinit -c a new TGT into a new file cache. I dug into the code at one point and there were explicit comments indicating that the memory cache wasn't supported. It actually greps around in the filesystem to find the file cache and reads the TGT directly (ie: it doesn't use userspace apis to do this).

[jedvardsson]

Great. My test as specified above works as expected, i.e., getConnection(url, username, password) works.

[pierresouchay]

@hoeflerb useficketCache & friends have to be set in .java.login.config file.

On my side, I use keytab for servers and user/password from GUI, but I think it is working. You may try with a recent JVM

[hoeflerb]

@pierresouchay it also works from the connection string. I've used both. But anyway, the point is that unless you are using a file cache, useTicketCache does not work. The only reason that's a problem is because all of the ticket managing utilities on OS X get and renew tickets from the memory cache, and they unfortunately don't respect environment variables like KRB5CCNAME which, if they did, would allow me to move everything to a single per user file cache.

Only current workarounds that I have found are:
a) kinit -c before attempting connection with mssql-jdbc. The resulting file cache will be completely invisible to the system tools, so this has to be explicitly managed by the end user.
b) keytabs, which are good for machine accounts, but hard to secure and manage for TGT-granting accounts.

[v-nisidh]

@jedvardsson I am closing this issue as you already able to test patch. Please feel free to reopen or create new issue depend on your tests & observation.

[sbuettne]

Hello @pierresouchay,
short question. Will that patch make it into the master in the near future?
Thanks in advance,
Sebastian

[pierresouchay]

@sbuettne It has been included here: https://github.com/Microsoft/mssql-jdbc/releases/tag/v6.1.7

So it is already merged in master.

So, ask MSFT employees when it will be included for a public release, but it is still available as version 6.1.7 preview.

[yarrasuresh9999]

@pierresouchay @jedvardsson please help
Hi, I'm trying to connect to MS SQL db using JavaKerberos by provided user name and password, below is my java code

public static void main(String[] args) {
		Connection con = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		try {
			// Establish the connection. 
			SQLServerDataSource ds = new SQLServerDataSource();
			ds.setIntegratedSecurity(true);
			ds.setAuthenticationScheme("JavaKerberos");
			ds.setServerName("myserver");
			ds.setPortNumber(65000); 
			ds.setDatabaseName("user");
			ds.setUser("user");
			ds.setPassword("password");
			con = ds.getConnection();
		// Execute a stored procedure that returns some data.
            		cstmt = con.prepareCall("{call dbo.uspGetEmployeeManagers(?)}");
            		cstmt.setInt(1, 50);
            		rs = cstmt.executeQuery();

	        	// Iterate through the data in the result set and display it.
	        	while (rs.next()) {
	            		System.out.println("EMPLOYEE: " + rs.getString("LastName") + 
	            			", " + rs.getString("FirstName"));
	            		System.out.println("MANAGER: " + rs.getString("ManagerLastName") + 
	            			", " + rs.getString("ManagerFirstName"));
	            		System.out.println();
	        	}
	        }
		// Handle any errors that may have occurred.
	    	catch (Exception e) {
	    		e.printStackTrace();
	    	}
	}

and set the .java.login.config as below and using mssql-jdbc-6.2.2.jre8.jar (download from http://www.java2s.com/example/jar/m/download-mssqljdbc622jre8jar-file.html)

cat ~/.java.login.config
SQLJDBCDriver {
   com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;
};

I'm getting below error

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '{my AD user name}'. ClientConnectionId:3a3d0513-1f87-42d8-a799-b9f43374e1a6
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:256)
	at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:108)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$1LogonProcessor.complete(SQLServerConnection.java:4014)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4292)

looks like its not using the provided username and password to establish connection, did I miss anything here? please let me know how to solve this.

[yrro]

I'm trying to connect to MS SQL db using JavaKerberos by provided user name and password
[...]
cat ~/.java.login.config
SQLJDBCDriver {
com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;
};

If you set useTicketCache=true then the userName and password connection properties will be ignored.

(It is annoying that the JDBC Driver for [MS] SQL Server deviates from the Krb5LoginModule default behaviour in this manner, forcing users to have to use a fiddly* JAAS Login Configuration File just to revert this setting back to its default value. It would be really nice if we could have a connection string property that causes the driver to not configure the useTicketCache option...)