search

microsoft / mssql-jdbc

The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity with SQL Server through the standard JDBC application program interfaces (APIs).
MIT License
1.05k stars 424 forks source link

AG + Kerberos + ReadOnly intent is broken #1646

Closed synologic closed 3 years ago

synologic commented 3 years ago

Driver version

9.4.0

SQL Server version

Microsoft SQL Server 2016 (SP2-CU15-GDR) (KB4583461) - 13.0.5865.1 (X64) Oct 31 2020 02:43:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Client Operating System

RedHat 7.9.2009

JAVA/JVM version

OpenJDK 1.8.0_292

Table schema

N/A

Problem description

  1. Expected behaviour: Trying to connect to an AG secondary replica using applicationIntent=ReadOnly. Intended behavior would be a succesful connection, however i'm receiving the error below. Authentication is kerberos with domain account
  2. Actual behaviour: It seems the redirect is not handled properly, SPN for the AG is only present on the primary replica, however it seems the client tries to authenticate using the wrong server name while authenticating
  3. Error message/stack trace:
  4. Any other details that can be helpful:

JDBC trace logs

java.sql.SQLException: Cannot create PoolableConnectionFactory (Integrated authentication failed. ClientConnectionId:671eefdc-8390-428b-ba54-2c3373b23da8) at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:516) at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:55) at sun.reflect.GeneratedMethodAccessor150.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254) at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:105) at com.sun.proxy.$Proxy105.getConnection(Unknown Source) at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:256) at org.apache.nifi.processors.standard.AbstractDatabaseFetchProcessor.setup(AbstractDatabaseFetchProcessor.java:236) at org.apache.nifi.processors.standard.GenerateTableFetch.onTrigger(GenerateTableFetch.java:249) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1174) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Integrated authentication failed. ClientConnectionId:671eefdc-8390-428b-ba54-2c3373b23da8) at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:669) at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:544) at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:753) at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:512) ... 21 common frames omitted Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:671eefdc-8390-428b-ba54-2c3373b23da8 at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3151) at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthHandShake(KerbAuthentication.java:178) at com.microsoft.sqlserver.jdbc.KerbAuthentication.generateClientContext(KerbAuthentication.java:209) at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:4979) at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3988) at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:85) at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3932) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2713) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861) at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:55) at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:355) at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:115) at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:665) ... 24 common frames omitted Caused by: org.ietf.jgss.GSSException: No valid credentials provided (Mechanism level: Server not found in Kerberos database (7)) at sun.security.jgss.krb5.Krb5Context.initSecContext(Krb5Context.java:772) at sun.security.jgss.GSSContextImpl.initSecContext(GSSContextImpl.java:248) at sun.security.jgss.GSSContextImpl.initSecContext(GSSContextImpl.java:179) at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthHandShake(KerbAuthentication.java:158) ... 40 common frames omitted Caused by: sun.security.krb5.KrbException: Server not found in Kerberos database (7) at sun.security.krb5.KrbTgsRep.(KrbTgsRep.java:70) at sun.security.krb5.KrbTgsReq.getReply(KrbTgsReq.java:226) at sun.security.krb5.KrbTgsReq.sendAndGetCreds(KrbTgsReq.java:237) at sun.security.krb5.internal.CredentialsUtil.serviceCredsSingle(CredentialsUtil.java:482) at sun.security.krb5.internal.CredentialsUtil.serviceCreds(CredentialsUtil.java:340) at sun.security.krb5.internal.CredentialsUtil.serviceCreds(CredentialsUtil.java:314) at sun.security.krb5.internal.CredentialsUtil.acquireServiceCreds(CredentialsUtil.java:169) at sun.security.krb5.Credentials.acquireServiceCreds(Credentials.java:490) at sun.security.jgss.krb5.Krb5Context.initSecContext(Krb5Context.java:695) ... 43 common frames omitted Caused by: sun.security.krb5.Asn1Exception: Identifier doesn't match expected value (906) at sun.security.krb5.internal.KDCRep.init(KDCRep.java:140) at sun.security.krb5.internal.TGSRep.init(TGSRep.java:65) at sun.security.krb5.internal.TGSRep.(TGSRep.java:60) at sun.security.krb5.KrbTgsRep.(KrbTgsRep.java:55) ... 51 common frames omitted

Reproduction code

N/A

synologic commented 3 years ago

Connection String: jdbc:sqlserver://SQL_SERVER;databaseName=DefenceSuite;integratedSecurity=true;authenticationScheme=JavaKerberos;applicationIntent=ReadOnly;multiSubnetFailover=true

Logging in directly to any of the replicas on the AG works, it just doesn't work with conneting to the ag listener

synologic commented 3 years ago

Tracing showed a misconfiguration in the readonly URLs. Fixed and it works