Skip to content

gwalkey/SSAS_DW_Logins

Repository files navigation

A Data Warehouse for tracking SQL Server Logins

SSAS DW Logins is a set of Microsoft technologies combined into a SQL Server Security Auditing Tool

Components:

  • Extended Event Session to track all SQL Server Login events saved to .XEL Files
  • ETL Process to move, load, clean and aggregate the XEL trace files from remote SQL servers into a central SQL database
  • A Transform step to load a DW Star schema database with the tracked Logins
  • An SSAS Tabular Model for slide-and-dice analysis
  • A sample Excel Pivot Table to view the historical Logins

Benefits

This solution allows you to answer questions like

  • Who logged in to my Server?
  • From what Hosts?
  • At what Time?
  • What Application did they use?
  • What Databases did they access?
  • Show me all hosts that accessed a certain database
  • Show me all access for any one person
  • Why is that app logging in 20,000 times per hour?
  • Connection string troubleshooting

ETL Performance

  • Using sys.fn_xe_file_target_read_file = 11 Hours
  • Using XEvent.Linq.dll and XECore.dll assemblies - 11 Minutes

Using the XEvent.Linq.dll and XECore.dll assemblies, we can load over 1M events per minute alt text

Inspiration by

Sample Report

alt text