How to connect to SQL Server using JDBC

Question

I am using Microsoft SQL Server 2008 and am trying to connect to the database using JDBC. Below is my code. The username and password use Windows authentication.

String url1 = "jdbc:sqlserver://ServerName;databaseName=v14testvp;user=USERNAME;password='';";
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    DriverManager.getConnection(url1);

I am getting the blow error when trying to connect

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'USERNAME'. ClientConnectionId:befb617f-8382-4388-ad98-a210ed0c3105

Can someone help me out on what to check to resolve the error. I have tried filling out both the user name and password in my url1 string, but I get the same error. I have also tried including the domain in the user name (domain\USERNAME). I'm relatively new to sql and java, so hopefully I'm just missing something simple.

EDIT:

I changed my code to below

String url1 = "jdbc:sqlserver://servername;databaseName=v14testvp;integratedSecurity=true;authenticationScheme=JavaKerberos";
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        DriverManager.getConnection(url1);

I am now getting the below error. I have the sqljdbc_auth.dll. Can someone point me in the right direction on how to resolve this?

com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:0e66f60e-958c-4c8e-85b9-484023f16ecf at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667) at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthInit(KerbAuthentication.java:140) at com.microsoft.sqlserver.jdbc.KerbAuthentication.GenerateClientContext(KerbAuthentication.java:268) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2691) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at com.ibm.atmn.tests.DumbyClass.DumbyTests(DumbyClass.java:52) Caused by: javax.security.auth.login.LoginException: Unable to obtain Princpal Name for authentication

Solution 1

You do not use authenticationScheme=JavaKerberos together with sqljdbc_auth.dll. If you have the DLL then leave out the authentication scheme and you will connect as the user logged into Windows.

If you want to connect as a different user or you are not on Windows then you can specify authenticationScheme=JavaKerberos. For this to work you need to supply these system properties:

• -Djava.security.auth.login.config=???
• -Djava.security.krb5.conf=???

See also Oracles documentation about Kerberos Requirements.

Solution 2

you have to setup kerberos related java properties

1) loginContext configuration file By default ,microsoft configured for you and this is optional.But if you want to control more about the behavior, you have to dive into the details. https://docs.oracle.com/javase/8/docs/jre/api/security/jaas/spec/com/sun/security/auth/module/Krb5LoginModule.html

2) kerberos configuration a) use external configuation then passed to java b) directly use java properties to set like System.setProperty("java.security.krb5.realm", ""); System.setProperty("java.security.krb5.kdc",)

Comments

[TestRaptor]

Thank you. I edited my original question. Would you be able to look at my new error?

[Mark Rotteveel]

If you want use sqlauth, then you should remove the authenticationScheme property or give it the appropriate value (see the second and third link in my answer).