CODEWITHSUNDEEP
javasql-serverjdbc
TestRaptor
TestRaptor

Reputation: 1315

How to connect to SQL Server using JDBC

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

Upvotes: 2

Views: 28374

Answers (3)

Jeff Johnston
Jeff Johnston

Reputation: 2266

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.

Upvotes: 4

Tim
Tim

Reputation: 21

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

Upvotes: 2

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

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

Upvotes: 0

Related Questions