Why is AD FS 2016 executing this SQL query?

Question

We cannot figure out why AD FS 2016 is executing a certain SQL query on a SQL attribute store.

The problem first arose when we renamed a database in preparation to decommission that database. We renamed it to see if anything was using it.
After renaming, then AD FS 2016 started throwing an error that it could not log in to the database. This wouldn't be surprising except for the fact that the SQL query it's executing cannot be found anywhere in the claim rules of any relying party or OAuth registration.
So, is there a location where a global policy can be placed that will be executed on every request for a token and applied to every relying party because that SQL query is not on the relying party to which the token is being requested?

Here is the full error that AD FS 2016 reports in its event viewer.

An Error occurred while executing a query in SQL attribute store.

Additional Data Connection information: POLICY3907: Server=REDACTED;Database=REDACTED. Query: SELECT [REDACTED] FROM [REDACTED].[REDACTED] WHERE [REDACTED]=@PARAMETER0 Parameters: REDACTED,

User Action Examine the exception details to take one or more of the following actions if applicable. Verify that the connection string to the SQL attribute store is valid. Make sure that the SQL attribute store can be reached by the connection string and the SQL attribute store exists. Verify that the SQL query and parameters are valid.

Exception details: Microsoft.IdentityServer.ClaimsPolicy.Engine.AttributeStore.Sql.SqlAttributeStoreQueryExecutionException: POLICY3904: Execution of query:'SELECT [REDACTED] FROM [REDACTED].[REDACTED] WHERE [REDACTED]=@PARAMETER0' with parameters:'REDACTED,' failed. Connection information:'POLICY3907: Server=REDACTED;Database=REDACTED.'. ---> System.Data.SqlClient.SqlException: Cannot open database "REDACTED" requested by the login. The login failed. Login failed for user 'REDACTED'. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.IdentityServer.ClaimsPolicy.Engine.AttributeStore.Sql.SyncQueryExecutor.BeginExecuteQuery(String query, List1 queryParameters, AsyncCallback callback, Object state)
--- End of inner exception stack trace --- at Microsoft.IdentityServer.ClaimsPolicy.Engine.AttributeStore.Sql.SyncQueryExecutor.BeginExecuteQuery(String query, List1 queryParameters, AsyncCallback callback, Object state) at Microsoft.IdentityServer.ClaimsPolicy.Engine.AttributeStore.Sql.SqlAttributeStore.BeginExecuteQuery(String query, String[] queryParameterValues, AsyncCallback callback, Object state) at Microsoft.IdentityServer.ClaimsPolicy.Language.AttributeLookupIssuanceStatement.BeginEvaluate(IEnumerable1 matchedClaims, PolicyContext policyContext, AsyncCallback callback, Object state)

System.Data.SqlClient.SqlException (0x80131904): Cannot open database "REDACTED" requested by the login. The login failed. Login failed for user 'REDACTED'. at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.IdentityServer.ClaimsPolicy.Engine.AttributeStore.Sql.SyncQueryExecutor.BeginExecuteQuery(String query, List1 queryParameters, AsyncCallback callback, Object state) ClientConnectionId:a7e6a99c-b3c5-495d-be39-7d700321a5c3 Error Number:4060,State:1,Class:11

score 1 · Answer 1 · answered Dec 18 '21 at 15:35

1

MFA evaluation can happen at global level

https://learn.microsoft.com/en-us/archive/blogs/ramical/under-the-hood-tour-on-multi-factor-authentication-in-adfs-part-1-policy

Check the MFA settings via Get-AdfsAdditionalAuthenticationRule . They might have rules defined to use that attribute store.

https://learn.microsoft.com/en-us/powershell/module/adfs/get-adfsadditionalauthenticationrule?view=windowsserver2022-ps

answered Dec 18 '21 at 15:35

maweeras

783
4
12

Thank you, @maweeras. That wasn't the issue in this case, but it is good information to know for future scenarios. – Toby Artisan Jan 12 '22 at 15:23

score 1 · Accepted Answer · answered Jan 12 '22 at 15:30

The issue turned out be a custom rule that was added to the Active Directory claims provider trust. So, every application that used Active Directory for claim rules would have that rule executed, which then queried the database, even if the application didn't need that value.

So, I removed that claim rule from Active Directory. Here is a screenshot where I found the rule.

Why is AD FS 2016 executing this SQL query?

2 Answers2