通过JDBC以Windows用户身份验证SQL Server实例 [英] Authenticating to a SQL Server instance as a Windows User via JDBC

问题描述

我必须为支持租户的Web应用程序支持多种数据库类型.除其他外，我通过使用net.sourceforge.jtds.jdbc.Driver类和连接字符串(例如"jdbc:jtds:sqlserver://192.168.1.189:1433/ApplicationName")成功地支持了Microsoft的SQL Server.这可行，但是要求用户在SQL Server实例中显式定义一个用户并启用SQL Server身份验证.

I'm having to support multiple database types for my tenant-enabled web application. Among others, I have successfully supported Microsoft's SQL Server, by using the net.sourceforge.jtds.jdbc.Driver class with a connection String like "jdbc:jtds:sqlserver://192.168.1.189:1433/ApplicationName". This works, but it requires that the user explicitly defines a user in the SQL Server instance and enables SQL Server authentication.

现在，不可避免地需要更改，我们应该支持通过Windows身份验证连接到SQL Server.显然，这需要对连接字符串进行某种更改，因为数据库服务器必须以某种方式能够区分传递到数据库连接中的凭据是针对SQL Server安装还是Windows OS中定义的用户.那是什么?

Now, inevitably, requirements changed, and we're supposed to support connecting to SQL Server via Windows Authentication. Evidently this requires some sort of change to the connection string, since the data base server must somehow be able to distinguish whether the credentials passed into the data base connection are for a user defined in the SQL Server installation or in the Windows OS. But what is it?

根据Internet上的建议进行操作，如果可以扩展到使用;useNTLMv2=true;domain=WORKGROUP扩展连接字符串. 似乎使数据库服务器知道我要以Windows用户身份进行身份验证，但是实际登录失败

Acting on advice from the internet, if progressed as far as extending the connection string with ;useNTLMv2=true;domain=WORKGROUP. That seems to make the data base server aware that I want to authenticate as a Windows user, but the actual log-in fails with

The login is from an untrusted domain and cannot be used with Windows authentication. (code 18452, state 28000)

现在，在我的测试设置中，J2EE应用程序和SQL Server实例实际上都在同一台计算机上(尽管在生产中可能不是)，并且仍然没有足够的信任度来登录此计算机.本身?显然，我在这里错过了很大一部分难题.要使SQL Server实例确信启动它的用户实际上可以通过JDBC登录，该怎么做?

Now im my testing set-up, both the J2EE app and the SQL server instance are in fact on the same machine (although in production they may not be), and still this computer isn't trusted enough to log on to itself? Evidently I'm missing a big part of the puzzle here. What does one have to do to convince an SQL Server instance that the user who started it can in fact log on to it via JDBC?

修改

由于我们已经尝试将Web应用程序与完整的Microsoft基础结构堆栈(SQL Server，Active Directory，域名服务...)集成在一起，已经付出了很多不成功的努力，所以我不得不限制这个问题:

Since we have already sunk too much unsuccessful effort trying to integrate our web application with a full Microsoft infrastructure stack (SQL Server, Active Directory, Domain Name Service...), I have to restrict this question:

有人知道一种通过 JDBC 使用用户帐户定义为"Windows用户" 访问 SQL Server 安装的方法吗? J2EE应用程序，无需，而不必使用Active Directory，运行Web应用程序的Windows计算机和专有DLL?赏金是该子问题的任何解决方案.整个问题显然太广泛了，无法在一个论坛帖子中回答.

Does anyone know a way to access an SQL Server installation with a user account defined as a "Windows User" via JDBC form a J2EE application, without having to use Active Directory, a Windows machine running the web application and a proprietary DLL? The bounty is for any solution of that sub-problem. The entire problem is clearly too broad to be answered in one forum post.

推荐答案

您所描述的内容显然是可行的.我有一个运行在独立服务器上的SQL Server 2008 R2 Express，并且能够通过Windows服务器上的Windows用户名/密码通过jTDS 1.3.1从单独的Windows计算机和Xubuntu 14.04框中进行连接.

What you describe certainly appears to be feasible. I have SQL Server 2008 R2 Express running on a stand-alone server and I was able to connect using a Windows username/password on that server via jTDS 1.3.1 from a separate Windows machine and from an Xubuntu 14.04 box.

在运行SQL Server的计算机上，我创建了一个名为"kilian"的Windows用户.在SQL Server本身中，我为NT AUTHORITY\Authenticated Users创建了SQL登录名.然后，在数据库(名为"myDb")中，为该SQL登录名创建了一个名为"AuthenticatedUsers"的用户.为了简单起见，我授予了该用户db_owner对数据库的权限.

On the machine running SQL Server I created a Windows user named 'kilian'. In SQL Server itself I created a SQL Login for NT AUTHORITY\Authenticated Users. Then in the database (named 'myDb') I created a User named 'AuthenticatedUsers' for that SQL Login. Just to keep things simple I gave that user db_owner rights on the database.

没有针对"kilian"的SQL登录名，也没有具有该名称的数据库用户.

There is no SQL Login for 'kilian' and no database User with that name.

然后，在其他两台计算机(Windows工作站和Xubuntu盒)中，我只运行了此

Then, from the other two machines (the Windows workstation and the Xubuntu box) I just ran this:

package com.example.jtdstest;

import java.sql.*;

public class JtdsTestMain {

    public static void main(String[] args) {
        try (Connection con = DriverManager.getConnection(
                "jdbc:jtds:sqlserver://192.168.1.137:52865/myDb" +
                    ";domain=whatever",
                "kilian",
                "4theBounty")) {
            try (Statement s = con.createStatement()) {
                String sql = "SELECT LastName FROM Clients WHERE ID=1";
                try (ResultSet rs = s.executeQuery(sql)) {
                    rs.next();
                    System.out.println(rs.getString("LastName"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }

    }

}

其他说明:

• 我不必包含useNTLMv2=true.无论有没有这个参数，我都可以连接.

• I did not have to include useNTLMv2=true. I was able to connect with or without that parameter.

I did 必须包含domain=来告诉SQL Server不要使用SQL身份验证，但是我提供的实际值没有区别. (我实际上使用了无论如何"，这不是服务器的名称，也不是服务器所属的工作组的名称.)

I did have to include domain= to tell the SQL Server not to use SQL authentication, but the actual value I supplied made no difference. (I literally used 'whatever', which was not the name of the server or the name of the workgroup to which it belongs.)

这篇关于通过JDBC以Windows用户身份验证SQL Server实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！