AUDIT SESSION CHANGED action causes login failed

Question

AUDIT SESSION CHANGED action causes login failed

Graham 41

I have auditing enabled on my Azure SQL instance and I've noticed lately that whenever the AUDIT SESSION CHANGED action occurs, if a DB connection is attempted at that moment, it will fail with Login failed for user '<username>'. I've noticed this happening once a day in our logs when the audit session change occurs.

Is this expected behavior and we need to retry to connection or is there something else going on that I'm missing?

Thanks.

Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2021-10-25T15:28:12.557+00:00

Hi @Graham Thank you for posting your question and sorry for the delay.

Configure login auditing

Please refer to the below article for more details.
https://learn.microsoft.com/en-us/azure/security/develop/threat-modeling-tool-auditing-and-logging#threat-detection

Regards,
Oury
Graham 41 Reputation points

2021-12-02T12:45:43.11+00:00

Hi,

Sorry to bring this up again but the issue occurred again last night. When an AUDIT SESSION CHANGED event occurred, a connection that was attempted at the time (within 1s) failed with 'login failed for user'.

I checked and I already have auditing enabled on my Azure Sql server instance. Is there anything else that I need to do to enable auditing for logins? Note that the settings are enabled at the server level (not database level) as Azure says the server auditing settings will apply regardless of database-level settings.

Thanks
MarcusNilsson-8584 0 Reputation points

2024-11-15T09:12:30.1733333+00:00

Graham, did you ever get any wiser on this? I am experiencing the exact same problem.
Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2025-01-02T19:41:06.2266667+00:00

MarcusNilsson-8584

Looks like it can be due to server restart. Change in audit session will not pause any login. But server restart does, and audit session get stopped and started

But it should not happen every day. Can you please open a support ticket so we can further investigate.

Regards,

Oury
MarcusNilsson-8584 0 Reputation points

2025-01-08T08:26:46.62+00:00
Oury Ba-MSFT I have opened a support ticket and it WAS a server restart. but it will cause logins to fail.

I had a very interesting, and frustrating, conversation with the support engineers about this.
It can be summarized with:

Logins WILL fail during when this happens. The only documentation I can find about this outage is here: The resource is available during the operation, except a short reconfiguration that happens at the end of the operation and typically lasts up to 8 seconds..." https://learn.microsoft.com/en-us/azure/azure-sql/database/maintenance-window?view=azuresql

They insisted that retry-logic should handle this - but they also agreed that you should only have retry-logic for transient errors. And the error we get when this happens isn't considered "transient": Error 18456
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16&viewFallbackFrom=azuresqldb-current
(with the error message "Login failed for user '<token-identified principal>'" since we're using managed identity authentication with tokens), but instead a user-error.
If we add retry-logic to this error, the DOS-Guard might consider it a DOS attack and block the login requests.
So there really isn't any good solution to this problem.
I think the proper solution here would be for Azure SQL to throw another error in those occurrances, e.g. error 40613 "Database '%.*ls' on server '%.*ls' is not currently available. Please retry the connection later". But the response I felt when I suggested that, was that it will never happen.
Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2025-01-09T22:24:15.8133333+00:00

MarcusNilsson-8584 Thank you for sharing and sorry about the experiencing you encountered with our services.

Looks like it can be due to server restart ..change in audit session will not pause any login..but server restart does, and audit session get stopped and started.

This should not happen every day. But I would suggest reaching out if you encounter this again.

Regards,

Oury

Your answer

Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2021-10-25T15:28:12.557+00:00

Hi @Graham Thank you for posting your question and sorry for the delay.

Configure login auditing

Please refer to the below article for more details.
https://learn.microsoft.com/en-us/azure/security/develop/threat-modeling-tool-auditing-and-logging#threat-detection

Regards,
Oury
Graham 41 Reputation points

2021-12-02T12:45:43.11+00:00

Hi,

Sorry to bring this up again but the issue occurred again last night. When an AUDIT SESSION CHANGED event occurred, a connection that was attempted at the time (within 1s) failed with 'login failed for user'.

I checked and I already have auditing enabled on my Azure Sql server instance. Is there anything else that I need to do to enable auditing for logins? Note that the settings are enabled at the server level (not database level) as Azure says the server auditing settings will apply regardless of database-level settings.

Thanks
MarcusNilsson-8584 0 Reputation points

2024-11-15T09:12:30.1733333+00:00

Graham, did you ever get any wiser on this? I am experiencing the exact same problem.
Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2025-01-02T19:41:06.2266667+00:00

MarcusNilsson-8584

Looks like it can be due to server restart. Change in audit session will not pause any login. But server restart does, and audit session get stopped and started

But it should not happen every day. Can you please open a support ticket so we can further investigate.

Regards,

Oury
MarcusNilsson-8584 0 Reputation points

2025-01-08T08:26:46.62+00:00

Oury Ba-MSFT I have opened a support ticket and it WAS a server restart. but it will cause logins to fail.

I had a very interesting, and frustrating, conversation with the support engineers about this.
It can be summarized with:

Logins WILL fail during when this happens. The only documentation I can find about this outage is here: The resource is available during the operation, except a short reconfiguration that happens at the end of the operation and typically lasts up to 8 seconds..." https://learn.microsoft.com/en-us/azure/azure-sql/database/maintenance-window?view=azuresql

They insisted that retry-logic should handle this - but they also agreed that you should only have retry-logic for transient errors. And the error we get when this happens isn't considered "transient": Error 18456
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16&viewFallbackFrom=azuresqldb-current
(with the error message "Login failed for user '<token-identified principal>'" since we're using managed identity authentication with tokens), but instead a user-error.
If we add retry-logic to this error, the DOS-Guard might consider it a DOS attack and block the login requests.
So there really isn't any good solution to this problem.
I think the proper solution here would be for Azure SQL to throw another error in those occurrances, e.g. error 40613 "Database '%.*ls' on server '%.*ls' is not currently available. Please retry the connection later". But the response I felt when I suggested that, was that it will never happen.
Oury Ba-MSFT 20,926 Reputation points Microsoft Employee Moderator

2025-01-09T22:24:15.8133333+00:00

MarcusNilsson-8584 Thank you for sharing and sorry about the experiencing you encountered with our services.

Looks like it can be due to server restart ..change in audit session will not pause any login..but server restart does, and audit session get stopped and started.

This should not happen every day. But I would suggest reaching out if you encounter this again.

Regards,

Oury

Share via

AUDIT SESSION CHANGED action causes login failed

Your answer