Java Windows 身份验证模式下到 MSSQL 服务器的 JDBC 连接

JDBC connection to MSSQL server in windows authentication mode

提问by yatinbc

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

在我的以下程序中，我试图在 Windows 身份验证中使用 jdbc 连接 MSSQL Server。但得到以下错误

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.

我在 Windows 身份验证模式下使用 SQL Server。我是否需要设置其他设置以在 Windows 身份验证中使用 jdbc 连接 MSSQL。

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)

回答by Naveen Chakravarthy

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.

您需要在 Sql Server Configuration Manager 应用程序中启用 SQL Server TCP/IP 协议。您可以在 SQL Server 网络配置中看到该协议。

回答by user3029620

Using windows authentication:

使用 Windows 身份验证：

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

Using SQL authentication:

使用 SQL 身份验证：

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

回答by kris

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.

您需要在 C:/windows/System32 文件夹中添加 sqljdbc_auth.dll。您可以从http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774下载它。

回答by Brandon Langley

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

如果要进行 Windows 身份验证，请使用最新的 MS-JDBC 驱动程序并按照此处的说明进行操作：

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

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

回答by Abhishek Shah

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.

1）。您必须检查您的端口“1433”是否被防火墙阻止。如果您发现它被阻止，那么您应该编写“入站规则”。如果在控制面板 -> Windows 防火墙 -> 高级设置（在左侧找到选项） ->入站规则中找到它。

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

2）。在 SQL Server 配置管理器中，您的TCP/IP协议将处于禁用模式。所以，你应该启用它。

回答by Satish

Try following these steps:

尝试执行以下步骤：

1. Add the integratedSecurity=trueto 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.dllfile 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.

1. integratedSecurity=true像这样添加到 JDBC URL：

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

2. 确保在您的项目构建路径中添加 sqljdbc 驱动程序 4 或更高版本（sqljdbc.jar）：

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

3. 为您的项目添加 VM 参数：

   • 从已安装 DB 的服务器中查找sqljdbc_auth.dll文件(C:\Program Files\sqljdbc_4.0\enu\auth\x86)，或从此链接下载。

   • 将 dll 文件放在您的项目文件夹中，并像这样指定 VM 参数：VM 参数： -Djava.library.path="<<DLL File path till folder>>"

     注意：检查您的 java 版本 32/64 位，然后相应地添加 32/64 位版本的 dll 文件。

回答by TheConstructor

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

对于在 Windows 7 下从 DataGrip 中测试的当前 MS SQL JDBC 驱动程序 (6.4.0)：

1. as per documentation on authenticationSchemeuse fully qualified domain name as host e.g. server.your.domainnot 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

1. 根据authenticationScheme 的文档，使用完全限定的域名作为主机，例如server.your.domain不仅仅是server；该文档还提到了指定的可能性serverSpn=MSSQLSvc/fqdn:port@REALM，但我无法向您提供有关如何使用它的详细信息。将 fqdn 指定为主机时，将自动生成 spn。
2. 放 authenticationScheme=JavaKerberos
3. 放 integratedSecurity=true
4. 使用您不合格的用户名（和密码）登录

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!

由于这是使用 JavaKerberos，我将不胜感激有关这是否适用于 Windows 外部的反馈。我相信不需要 .dll，但是当我使用 DataGrip 创建连接时，我不确定；我也很感激对此的反馈！

回答by Sidharth Taneja

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

苦苦挣扎，终于找到了解决办法，开始吧——

Download the file jtds-1.3.1.jarand ntlmauth.dlland save it in Program File -> Java -> JDK -> jre -> bin.

下载文件jtds-1.3.1.jar并将ntlmauth.dll其保存在 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);