ASKs FOR LOG ANALYTICS IN SYNAPSE WORKSPACE

Question

1.- I would like to know how I can view the login, session failure and logout of a user in the synapse workspace log analytics when they enter the synapse studio?

2.-I would like to know why in the SqlSecurityAuditEvents table of the synapse workspace log analytics, in the actionid field when it is DBAF it only shows the IP when the user tries to authenticate to the database through synapse studio and why it does show the user t- sql when trying to authenticate by SQL SERVER MANAGEMENT?

3.- If I try to authenticate the database in SQL SERVER MANAGEMENT through Active Directory, would it also show me in DBAF the user who had a failed authentication or would it only show me the IP?

4.- In what situation is when the user shows the DBAF actionName. Due to not having permissions to the database or a network error?

Answer 1

Hi @Gabriel Lazo

Welcome to Microsoft Q&A platform and thanks for posting your question here.

1. To view the login, session failure, and logout of a user in the Synapse Workspace Log Analytics when they enter the Synapse Studio, you can use the SQL Server Audit feature. SQL Server Audit enables you to create server audits, which can capture and log server-level events and database-level events. You can then use Synapse Workspace Log Analytics to analyze the audit logs and extract the required information. 
   Kindly refer this support documentation  Auditing - Azure SQL Database and Azure Synapse Analytics | Microsoft Learn 
2. Regarding the SqlSecurityAuditEvents table of the Synapse Workspace Log Analytics, when the user tries to authenticate to the database through Synapse Studio, it only shows the IP in the actionid field because the user is not providing any T-SQL commands. However, when the user tries to authenticate by SQL Server Management, it shows the user T-SQL because the user is providing T-SQL commands. 
   You can find more information in the SQL Server Audit (Database Engine) documentation.
3. This is because the Synapse Workspace Log Analytics captures the authentication events from the SQL Server Management Studio and records them in the SqlSecurityAuditEvents table. The table contains information about the user who attempted to authenticate, the IP address from which the authentication attempt was made, and the authentication result.Therefore, if the authentication attempt fails, the Synapse Workspace Log Analytics will record the user who attempted to authenticate, along with the IP address. This information can be used to troubleshoot authentication issues and identify potential security threats. 
   Kindly refer this article for more information Monitor Azure Synapse Analytics Using Log Analytics (c-sharpcorner.com)
4. The DBAF actionName in the SqlSecurityAuditEvents table of the Synapse Workspace Log Analytics indicates that an authentication attempt was made to access a database, and the attempt failed due to an authorization failure. This means that the user who attempted to authenticate did not have the necessary permissions to access the database.The authorization failure can occur due to various reasons, such as incorrect login credentials, insufficient permissions, or a misconfigured security policy. It is also possible that the authentication attempt failed due to a network error, but in that case, the error message would be different from the authorization failure message. 
   You can find more information in SQL Authentication in Azure Synapse Analytics - Azure Synapse Analytics | Microsoft Learn I hope this helps! Let me know if you have any further questions or concerns.

Answer 2

Q1: To view login, session failure, and logout activities in Synapse Workspace, you'll typically look at the audit logs or diagnostic settings. These are stored in Log Analytics and can be queried using the Kusto Query Language (KQL).

Q2: The SqlSecurityAuditEvents table captures various SQL security-related events. The ActionId field specifies the type of action that was logged. DBAF typically indicates a failed database authentication.

• If you see just the IP for a user trying to authenticate through Synapse Studio, it could be due to the way the Synapse Studio manages its connections and what it logs by default.
• On the other hand, SQL Server Management Studio (SSMS) may provide a different level of logging detail because it interacts more directly with databases. It’s possible that SSMS logs the T-SQL used during the connection attempt, hence providing more granularity.

Q3: When you try to authenticate to a database in SQL Server Management Studio using Active Directory, the audit should capture details related to the authentication. Whether it shows the user or just the IP might depend on the logging settings and the nature of the failed authentication. Typically, Azure logs try to capture as much pertinent information about the event as possible to aid in troubleshooting, so there's a good chance you'll see the user information if the authentication fails at the AD level.

Q4: The DBAF action typically denotes a "Database Authentication Failed" event. This could be due to various reasons:

• Incorrect credentials (username/password mismatch).
• The user doesn't have the necessary permissions to access the database.
• There might be network-related issues preventing the authentication handshake.
• Connection string issues or invalid parameters.
• Server-level firewall rules that prevent the connection.