sqlserver去掉密码强度验证_sqlserver用windows方式验证登录踩过的坑

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

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

八月 19, 2020 9:33:43 上午 com.microsoft.sqlserver.jdbc.AuthenticationJNI 警告: Failed to load the sqljdbc_auth.dll cause : no sqljdbc_authinjava.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.(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_authinjava.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.(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.(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版本不对,我用的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 classSqlServerWinCon {public static voidmain(String[] args) {

GetResutls1();

GetResutls2();

}public static voidGetResutls1() {//Declare the JDBC objects.

Connection con = null;

CallableStatement cstmt= null;

ResultSet rs= null;try{//Establish the connection.

SQLServerDataSource ds = newSQLServerDataSource();//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 voidGetResutls2() {

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

}

}

}