sqlserver用windows方式验证登录踩过的坑
坑位一: 之前没用过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();
}
}
}
最新文章
- iOS开发知识点总结
- github 离线版本下载
- 【原】CentOS7上安装Xwiki8.2.1
- Python的注释
- WeUI首页、文档和下载 - 专为微信设计的 UI 库 - 开源中国社区
- return 和 exit
- Binding 中 Elementname,Source,RelativeSource 三种绑定的方式
- android bindService()
- windows下批量杀死进程
- win7+cygwin+hadoop+eclipse
- Html5移动端页面自适应布局详解(阿里rem布局)
- XOR (莫队)
- 使用TensorFlow实现回归预测
- path sum II(深度优先的递归实现掌握)
- 团队合作one
- EF Like
- POJ2391 Ombrophobic Bovines
- git fetch 和git pull 的差别
- C语言 &#183; 超级玛丽
- PriorityBlockingQueue 原理分析