JDBC connection to MSSQL server in windows authentication mode

Question

In my following programme I am trying to connect MSSQL Server using jdbc in windows authentication. But getting following error

import java.io.*;
import java.sql.*;
import java.util.GregorianCalendar;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

class  Cms_truncate
{
    public static void main(String[] args) 
    {
         Calendar cal = new GregorianCalendar();

         //String name="cmscim";
         //String filename = "D:\\programs\\Tomcat 6.0\\webapps\\timescape\\canteen_scheduller\\CMS_CSV\\cms_cim\\"+ name+"-"+cal.get(Calendar.YEAR) +"-" +(cal.get(Calendar.MONTH)+1) + "-"+cal.get(Calendar.DATE)+".csv";
         Connection conn = null;
         String url = "jdbc:sqlserver://localhost:1433;databasename=CMS_TIMES_MAIN;integratedSecurity=true";
         String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
         String userName = ""; 
         String password = "";
         Statement stmt;
         try
         {

         Class.forName(driver);//.newInstance();
         conn = DriverManager.getConnection(url,userName,password);
         String query = "select * from cim where sapId=10025331";
         stmt = conn.createStatement();
         int flag = stmt.executeUpdate(query);
         System.out.println("flag = "+flag); 
         conn.close();
        System.out.println("");
         } catch (Exception e) {
         e.printStackTrace();
         }

    }
}

I am using SQL Server in windows authentication mode. Do I need to do set up other things to connect MSSQL using jdbc in windows authentication.

ERROR:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
    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 Cms_truncate.main(Cms_truncate.java:28)

Well have you set up SQL server to allow TCP/IP connections on port 1433? It sounds like you haven't. — Jon Skeet
– Jon Skeet, Commented May 11, 2013 at 14:16
@yatinbc: Go into your SQL Server administration tool, and enable TCP-IP connections. If that's not enough information for you, search the web for how to do this - I'm sure it's covered in a lot of places. — Jon Skeet
– Jon Skeet, Commented May 11, 2013 at 14:25

Andrew Barber · Accepted Answer · 2013-11-25 18:15:26Z

22

Using windows authentication:

String url ="jdbc:sqlserver://PC01\inst01;databaseName=DB01;integratedSecurity=true";

Using SQL authentication:

String url ="jdbc:sqlserver://PC01\inst01;databaseName=DB01";

edited Nov 25, 2013 at 18:15

Andrew Barber

40.3k20 gold badges97 silver badges124 bronze badges

answered Nov 25, 2013 at 3:54

user3029620

1,5471 gold badge10 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kay Over a year ago

How to do this on AIX system ? (sqljdbc_auth.dll won't work in AIX)

Damith Udayanga Over a year ago

How it works on Linux

Omar Einea · Accepted Answer · 2018-02-26 06:27:17Z

Try following these steps:

Add the integratedSecurity=true to JDBC URL like this:

Url: jdbc:sqlserver://<<Server>>:<<Port>>;databasename=<<DatabaseName>>;integratedsecurity=true

Make sure to add the sqljdbc driver 4 or above version (sqljdbc.jar) in your project build path:

java.sql.DatabaseMetaData metaData = connection.getMetaData();
System.out.println("Driver version:" + metaData.getDriverVersion());

Add the VM argument for your project:
- Find the sqljdbc_auth.dll file from DB installed server (C:\Program Files\sqljdbc_4.0\enu\auth\x86), or download from this link.
- Place the dll file in your project folder and specify the VM argument like this: VM Argument: -Djava.library.path="<<DLL File path till folder>>"
  
  NOTE: Check your java version 32/64 bit then add 32/64 bit version dll file accordingly.

kris · Accepted Answer · 2014-03-27 14:57:54Z

11

You need to add sqljdbc_auth.dll in your C:/windows/System32 folder. You can download it from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 .

answered Mar 27, 2014 at 14:57

kris

1712 silver badges3 bronze badges

3 Comments

Oli Over a year ago

this is also a correct answer, plus setting integratedSecurity=true in the url.

jsosnowski Over a year ago

In my opinion it's not correct to put this dll in Windows\System32 directory. It'll be better to put in other new directory and add this to PATH variable.

Dragas Over a year ago

Pretty much that. Rewriting the -Djava.library.path has unintended side effects. For example losing other system libraries that java loads.

Abhishek Shah · Accepted Answer · 2018-03-22 02:18:07Z

7

From your exception trace, it looks like there is multiple possibility for this problem

1). You have to check that your port "1433" is blocked by firewall or not. If you find that it is blocked then you should have to write "Inbound Rule". It if found in control panel -> windows firewall -> Advance Setting (Option found at Left hand side) -> Inbound Rule.

2). In SQL Server configuration Manager, your TCP/IP protocol will find in disable mode. So, you should have to enable it.

edited Mar 22, 2018 at 2:18

answered May 21, 2016 at 13:47

Abhishek Shah

1,4341 gold badge18 silver badges26 bronze badges

Comments

Naveen Chakravarthy · Accepted Answer · 2013-09-16 19:07:27Z

4

You need to enable the SQL Server TCP/IP Protocol in Sql Server Configuration Manager app. You can see the protocol in SQL Server Network Configuration.

answered Sep 16, 2013 at 19:07

Naveen Chakravarthy

8392 gold badges16 silver badges31 bronze badges

1 Comment

Vijay Shegokar Over a year ago

Answer is correct, we need to Enable TCP/IP by going to above path. But after doing this also need to restart the SQL Server service to take our changes. You can find this service in same --- Server Configuration Manager app.

U_R_Naveen UR_Naveen · Accepted Answer · 2020-09-07 07:35:35Z

3

i was getting error as "This driver is not configured for integrated authentication" while authenticating windows users by following jdbc string

jdbc:sqlserver://host:1433;integratedSecurity=true;domain=myDomain

So the updated connection string to make it work is as below.

jdbc:sqlserver://host:1433;authenticationScheme=NTLM;integratedSecurity=true;domain=myDomain

note: username entered was without domain.

answered Sep 7, 2020 at 7:35

U_R_Naveen UR_Naveen

7847 silver badges8 bronze badges

Comments

TheConstructor · Accepted Answer · 2018-05-28 07:20:09Z

For the current MS SQL JDBC driver (6.4.0) tested under Windows 7 from within DataGrip:

as per documentation on authenticationScheme use fully qualified domain name as host e.g. server.your.domain not just server; the documentation also mentions the possibility to specify serverSpn=MSSQLSvc/fqdn:port@REALM, but I can not provide you with details on how to use this. When specifying a fqdn as host the spn is auto-generated.
set authenticationScheme=JavaKerberos
set integratedSecurity=true
use your unqualified user-name (and password) to log in

As this is using JavaKerberos I would appreciate feedback on whether or not this works from outside Windows. I believe that no .dll is needed, but as I used DataGrip to create the connection I am uncertain; I would also appreciate Feedback on this!

Brandon Langley · Accepted Answer · 2015-08-27 15:29:59Z

1

If you want to do windows authentication, use the latest MS-JDBC driver and follow the instructions here:

https://msdn.microsoft.com/en-us/library/gg558122(v=sql.110).aspx

answered Aug 27, 2015 at 15:29

Brandon Langley

5513 silver badges9 bronze badges

Comments

Furkan · Accepted Answer · 2021-10-07 22:51:33Z

1

You should copy your .dll => here "mssql-jdbc_auth-9.4.0.x64.dll" in that folder => sqljdbc_9.4\enu\auth\x64 to your Java JDK/bin.

Shortly: => sqljdbc_9.4\enu\auth\x64\mssql-jdbc_auth-9.4.0.x64.dll to Program Files\Java\jdk-16\bin

If it doesnt work, you can open your SQL Server 2019 Configuration Manager -> SQL Server Network Configuration , all protocol's name should be Enabled. After that you should click on (TCP/IP) / IP Addresses, you will see that IPALL-> TCP PORT should be 1433.

answered Oct 7, 2021 at 22:51

Furkan

111 bronze badge

Comments

Sidharth Taneja · Accepted Answer · 2019-03-19 04:50:28Z

0

After struggling a lot, I finally found a solution, here we go -

Download the file jtds-1.3.1.jar and ntlmauth.dll and save it in Program File -> Java -> JDK -> jre -> bin.

Then use the following code -

String pPSSDBDriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
Class.forName(pPSSDBDriverName);
DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
conn = DriverManager.getConnection("jdbc:jtds:sqlserver://<ur_server:port>;UseNTLMv2=true;Domain=AD;Trusted_Connection=yes");
stmt = conn.createStatement();
String sql = " DELETE FROM <data> where <condition>;
stmt.executeUpdate(sql);

answered Mar 19, 2019 at 4:50

Sidharth Taneja

5646 silver badges7 bronze badges

1 Comment

jsosnowski Over a year ago

Don't put it in JDK/JRE bin directory! It'll fail if you update JDK/JRE. Better put ntlmauth.dll file in a custom, new directory anywhere you want (not jdk location) and add this directory to the PATH variable.

Sergio · Accepted Answer · 2020-09-04 13:16:58Z

0

Nop, you have a connection error, please check your IP server adress or your firewall.

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

ping yourhost (maybe the ping service was blocked, but try anyway).
telnet yourhost 1433 (maybe blocked).
Contact the sysadmin with the results.

edited Sep 4, 2020 at 13:16

answered Sep 2, 2020 at 21:00

Sergio

193 bronze badges

Collectives™ on Stack Overflow

JDBC connection to MSSQL server in windows authentication mode

11 Answers 11

2 Comments

Comments

3 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

2 Comments

Comments

3 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related