CODEWITHSUNDEEP
javasql-server-2005jdbc
yatinbc
yatinbc

Reputation: 625

JDBC connection to MSSQL server in windows authentication mode

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)

Upvotes: 23

Views: 179370

Answers (11)

Furkan
Furkan

Reputation: 11

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.

Upvotes: 1

U_R_Naveen UR_Naveen
U_R_Naveen UR_Naveen

Reputation: 788

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.

Upvotes: 3

Sergio
Sergio

Reputation: 19

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.".

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

Upvotes: 0

Sidharth Taneja
Sidharth Taneja

Reputation: 564

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

Upvotes: 0

TheConstructor
TheConstructor

Reputation: 4465

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

  1. 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.
  2. set authenticationScheme=JavaKerberos
  3. set integratedSecurity=true
  4. 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!

Upvotes: 2

Abhishek Shah
Abhishek Shah

Reputation: 1424

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.

Upvotes: 7

Satish
Satish

Reputation: 181

Try following these steps:

  1. Add the integratedSecurity=true to JDBC URL like this:

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

  2. 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());

  3. 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.

Upvotes: 18

Brandon Langley
Brandon Langley

Reputation: 552

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

Upvotes: 1

kris
kris

Reputation: 171

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 .

Upvotes: 11

user3029620
user3029620

Reputation: 1527

Using windows authentication:

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

Using SQL authentication: 

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

Upvotes: 22

Naveen Chakravarthy
Naveen Chakravarthy

Reputation: 839

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.

Upvotes: 4

Related Questions