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, May 11 '13 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, May 11 '13 at 14:25
Does it work well with you when using SQL Server authentication user and password? — Mohammed Alaghbari, May 11 '13 at 14:55
@MohammedAlaghbari Probably not, as the error has nothing to do with windows authentication: the driver isn't even able to connect to the database server. — Mark Rotteveel, May 11 '13 at 20:00

score 21 · Answer 1 · edited Nov 25 '13 at 18:15

21

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 '13 at 18:15

Andrew Barber

39,603
20
94
123

answered Nov 25 '13 at 03:54

user3029620

1,179
8
4

1

How to do this on AIX system ? (sqljdbc_auth.dll won't work in AIX) – Kay Feb 17 '18 at 02:03
How it works on Linux – Damith Udayanga Nov 22 '21 at 14:34

score 17 · Answer 2 · edited Feb 26 '18 at 06:27

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.

score 11 · Answer 3 · answered Mar 27 '14 at 14:57

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 '14 at 14:57

kris

171
2
3

1

this is also a correct answer, plus setting integratedSecurity=true in the url. – Oli Oct 02 '17 at 17:00
3

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. – jsosnowski Sep 27 '19 at 10:41
Pretty much that. Rewriting the `-Djava.library.path` has unintended side effects. For example losing other system libraries that java loads. – Dragas Oct 24 '19 at 13:29

Abhishek Shah · Answer 4 · 2018-03-22T02:18:07.723

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.

score 4 · Answer 5 · answered Sep 16 '13 at 19:07

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 '13 at 19:07

Naveen Chakravarthy

819
2
15
30

2

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. – Vijay Shegokar Oct 02 '17 at 04:46

score 3 · Answer 6 · answered Sep 07 '20 at 07:35

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.

score 2 · Answer 7 · answered May 28 '18 at 07:20

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!

score 1 · Answer 8 · answered Aug 27 '15 at 15:29

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 '15 at 15:29

Brandon Langley

553
3
9

score 1 · Answer 9 · answered Oct 07 '21 at 22:51

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.

score 0 · Answer 10 · answered Mar 19 '19 at 04:50

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);

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. — jsosnowski, Sep 27 '19 at 10:36

Sergio · Answer 11 · 2020-09-04T13:16:58.120

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.

JDBC connection to MSSQL server in windows authentication mode

11 Answers11

Linked

Related