Forum Discussion

PhillipsKL's avatar
PhillipsKL
Frequent Visitor
3 months ago
Solved

Mirrored Databases Auditing

Hi everyone

 

I'm trying to set up a mirror of our production database, which needs to be compliant with PCI DSS, including full SQL audit logs. I have tried creating a KQL database to track changes on the mirror, but I am unable to pull the exact SQL queries which have been run. 

 

I need my audit logs to show the query which has been run, when it ran, and who ran it, in a retainable and unalterable format. How can I do this from a mirrored database?

 

Thanks

governance
security

5 Replies

  • Shai_Karmani's avatar
    Shai_Karmani
    Super User
    3 months ago

    Mirroring in Fabric replicates row level changes from the source into OneLake as Delta tables. It is data replication, not an audit pipeline, so the SQL statements executed against the source are not captured anywhere on the Fabric side. The KQL approach can show you what changed, but it will not give you the original query text or who ran it because that information never leaves the source.

    For PCI DSS quality auditing (full statement text, executor, timestamp, tamper resistant retention), enable native auditing at the source database. For Azure SQL DB or Managed Instance, that is Azure SQL Auditing writing to a Storage account with an immutable WORM policy, or to Log Analytics with a retention lock. For boxed SQL Server, use SQL Server Audit with the file target pointing to a path with immutable retention.

    Separately, if you also need to track who queried the mirrored copy from inside Fabric, that is captured in the Microsoft Purview audit log under Fabric activity events (workspace and SQL analytics endpoint reads). Treat it as a Fabric side companion to the source side audit, not a replacement.

    If this was helpful, please give a thumbs up and mark it as resolved.

    Thanks,
    Shai Karmani

    Let's connect in LinkedIn

    • PhillipsKL's avatar
      PhillipsKL
      Frequent Visitor
      3 months ago

      Hi Shai

      Thanks for your response, we've tried to pull the logs via Purview for queries against the mirror, however this only includes the access events but no SQL queries run against the mirror. Is there an additional part to the set up required to see the actual SQL run, who, and when?

       

      Thanks

  • v-abhinavmu's avatar
    v-abhinavmu
    Community Support
    3 months ago

    Hi PhillipsKL,

    Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Shai_Karmani for sharing valuable insights.

     

    Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

     

    Thank you for being part of the Microsoft Fabric Community.

  • deborshi_nag's avatar
    deborshi_nag
    Super User
    3 months ago

    Hi PhillipsKL 

     

    I assume you're interested in the following aspects for your mirrored database in Fabric:

     

    - Database plane auditing (at the SQL level, including which queries or scripts were executed and on which objects)

    - Platform plane auditing (such as who accessed or edited items, and any changes to permissions)

     

    When you set up a mirrored database in Fabric, the platform automatically provides a SQL analytics endpoint. You can use the SQL audit logs available for these endpoints, as outlined in the following link:

     

    SQL Audit Logs in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

     

    For platform-level actions (such as viewing, sharing, or changing permissions), Fabric works with the Microsoft Purview audit log.

    Track user activities in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

     

  • v-abhinavmu's avatar
    v-abhinavmu
    Community Support
    3 months ago

    Hi PhillipsKL,

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 


    Thank you.