sqlserver用windows方式验证登录踩过的坑

摘要：

坑1：我以前没有使用Windows身份验证登录到sqlserver。如何将连接字符串写入凹坑2：链路已连接，但启动错误报告于2020年8月19日上午9:33:43。com上。sqlserver。jdbc。AuthenticationJNI＜clinit＞警告：未能加载sqljdbc_auth.dllcause:nosqljdbc_aauthinjava.library.pathco

坑位一: 之前没用过windows验证方式登录sqlserver,连接串怎么写

坑位二: 链接上了,但是启动报错

八月 19, 2020 9:33:43 上午 com.microsoft.sqlserver.jdbc.AuthenticationJNI <clinit>
警告: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_auth in java.library.path
com.microsoft.sqlserver.jdbc.SQLServerException: 没有为集成身份验证配置驱动程序。 ClientConnectionId:8167e4ba-3567-4871-bdaa-fee41511e3fa
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1667)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<init>(AuthenticationJNI.java:60)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2229)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
    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.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)
    at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)
    at OThinker.datasource.TestSqlbyDS.GetResutls1(TestSqlbyDS.java:29)
    at OThinker.datasource.TestSqlbyDS.main(TestSqlbyDS.java:9)
Caused by: java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
    at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1867)
    at java.lang.Runtime.loadLibrary0(Runtime.java:870)
    at java.lang.System.loadLibrary(System.java:1122)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<clinit>(AuthenticationJNI.java:35)
    ... 12 more

坑位三: sqljdbc_auth.dll依赖包加了,但还是报错

Exception in thread "main" java.lang.UnsatisfiedLinkError: com.microsoft.sqlserver.jdbc.AuthenticationJNI.GetDNSName(Ljava/lang/String;[Ljava/lang/String;Ljava/util/logging/Logger;)I
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.GetDNSName(Native Method)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.GetDNSName(AuthenticationJNI.java:109)
    at com.microsoft.sqlserver.jdbc.AuthenticationJNI.<init>(AuthenticationJNI.java:63)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2229)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
    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.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)
    at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)
    at OThinker.datasource.TestSqlbyDS.GetResutls1(TestSqlbyDS.java:29)
    at OThinker.datasource.TestSqlbyDS.main(TestSqlbyDS.java:9)

坑位四:启动报错

com.microsoft.sqlserver.jdbc.SQLServerException: 登录失败。该登录名来自不受信任的域，不能与集成身份验证一起使用。 ClientConnectionId:120bcc53-18a5-4dc2-bb7f-5addbaf041b0
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)
    at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)
    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.SQLServerDataSource.getConnectionInternal(SQLServerDataSource.java:621)
    at com.microsoft.sqlserver.jdbc.SQLServerDataSource.getConnection(SQLServerDataSource.java:57)
    at OThinker.datasource.TestSqlbyDS.GetResutls1(TestSqlbyDS.java:29)
    at OThinker.datasource.TestSqlbyDS.main(TestSqlbyDS.java:9)

填坑一:

　　网上相关资料也不多,但是多查查还是有的,去掉账号和密码,加上integratedSecurity=true

　　#jdbc.username=sa
　　#jdbc.password=1qaz2wsx#EDC
　　jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=test;integratedSecurity=true
　　jdbc.dbType=sqlserver

填坑二:

　　下载sqljdbc_auth.dll包:https://www.microsoft.com/en-us/download/details.aspx?id=11774

填坑三:

　　这个问题应该是sqljdbc_auth.dll版本不对,我用的sqlserver2017版本,但是sqljdbc_auth.dll是在网上下的4版本,所以报坑位三的错误,解决方案,用填坑二的连接在官网下载最新版

填坑四:

　　将servername设置为localhost我原先用的127.0.0.1,具体为什么还不了解,欢迎大佬留下评论

　　jdbc:sqlserver://localhost:1433;DatabaseName=test;integratedSecurity=true

附sqljdbc_auth.dll 4版本的包:

　　链接：https://pan.baidu.com/s/1pnUDPcNLF6A297mYTM0uuw

　　提取码：0x46

示例代码:两种方法都可以,用一种测试就行将数据库和获取查询集合结果字段换成自己的就可以

package koukay.datasource;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

import java.sql.*;

public class SqlServerWinCon {
    public static void main(String[] args) {
        GetResutls1();
        GetResutls2();
    }

    public static void GetResutls1() {
        // Declare the JDBC objects.
        Connection con = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;

        try {
            // Establish the connection.
            SQLServerDataSource ds = new SQLServerDataSource();
//            ds.setUser("sa");
//            ds.setPassword("123456");
            ds.setIntegratedSecurity(true);
//            ds.setServerName("127.0.0.1");//数据库实例名
            ds.setServerName("localhost");//数据库实例名
            ds.setPortNumber(1433);
            ds.setDatabaseName("test");//Database Name
            con = ds.getConnection();

            // Execute a SQL that returns some data.
            cstmt = con.prepareCall(" select top 10 * from test ");//Sql

            rs = cstmt.executeQuery();

            // Iterate through the data in the result set and display it.
            while (rs.next()) {
                System.out.println("HeartBeatTimeout: " + rs.getInt("HeartBeatTimeout"));
                System.out.println("NetworkFailureSleepTime: " + rs.getInt("NetworkFailureSleepTime"));
                System.out.println("MemoryUsageRate: " + rs.getFloat("MemoryUsageRate"));
            }
        } // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                } catch (Exception e) {
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (Exception e) {
                }
            }
        }
    }

    public static void GetResutls2() {
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//        String URL = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test";
        String URL = "jdbc:sqlserver://localhost:1433;integratedSecurity=true;DatabaseName=test";
        Connection con = null;
        ResultSet rs = null;
        Statement st = null;
        String sql = "select * from test";
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(URL);
            st = con.createStatement();
            rs = st.executeQuery(sql);
            if (rs != null) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int countCols = rsmd.getColumnCount();
                for (int i = 1; i <= countCols; i++) {
                    if (i > 1) System.out.print("; ");
                    System.out.print(rsmd.getColumnLabel(i));
                }
                System.out.println("");
                while (rs.next()) {
                    System.out.println(rs.getInt("HeartBeatTimeout"));
                    System.out.println(rs.getInt("NetworkFailureSleepTime"));
                    System.out.println(rs.getFloat("MemoryUsageRate"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            if (rs != null) rs.close();
            if (st != null) st.close();
            if (con != null) con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

sqlserver用windows方式验证登录踩过的坑

相关文章

phoenix 索引实践

JDBC面试问题

bcp命令详解转载

JavaWeb项目之博客系统（二）上

Sql Server2008R2与IDEA的连接

JAVA调用数据库存储过程

最新文章

随机推荐

思享工具箱导航

JSON工具

格式化转换

加解密编码

文本数字

网络

站长

计算

其他

对照列表