How to connect to SQL Server using JDBC

2
votes

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

3
javasql-serverjdbc
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'USERNAME'. 1) Make sure username/pwd correct. 2) If you are accessing from remote machine, make sure remote access granted – kosa
@Nambari - The username/pwd are correct, and remote access is granted – TestRaptor
@TestRaptor Did you find a solution to the problem? – Nida Sahar

3 Answers

4
votes

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.

1
votes

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",)

0
votes

When connecting to SQL Server with Windows Authentication, you cannot use a username and password (see for example this answer). Instead you need to specify integratedSecurity=true and depending on the driver version and preference you need to use Kerberos authentication (and include authenticationScheme=JavaKerberos in the connection string) or load the right sqljdbc_auth.dll.

See Using Kerberos Integrated Authentication to Connect to SQL Server and Building the Connection URL