How to setup SQL Logging in ISA Server.
1. Create service account in Active Directory/User Manager.
2. Open SQL Enterprise Manager
3. Create new database in SQL Server (SQL Enterprise Manager)
4. Create login in SQL Enterprise Manager, using NT Account from step #1.
5. Assign login ‘db_owner’ security of newly created database.
6. Locate SQL Files on ISA Server CD (g:\isa\*.sql)
7. Open all 3 files individually and run from Query Analyzer. This will create tables and indexes.
8. Open Data Sources (ODBC) within Administrative Tools.
9. Create new System DSN by clicking on ‘Add‘.
10. Select SQL Server, and click ‘Finish‘.
11. Type in Name, and Description. Select your SQL server from dropdown. (click next)
12. Click Next
13. Select ‘Change the default database to:’ and select newly created database.
14. Click Next.
15. Click ‘Test Data Source…’
16. Click ‘OK’. As you can see everything is setup right so far.
17. Select ‘Monitoring Configuration’ and then ‘Logs’.
The next steps involve reconfiguring the 3 components in the above screenshot.
18. Double click on each of the 3 components individually and configure as instructed below.
19. Select ‘Database’
20. Make sure the DSN matches the one you just created.
21. Table name is ‘FirewallLog’ .
22. Click ‘Set Account’, and choose the account you created.
Note: In this example a local account was used, so no domain name is displayed.
23. Same as previous steps but Table Name is ‘PacketFilterLog’.
24. Same as previous steps but Table Name is ‘WebProxyLog’.
25. You are now done, here is an example SQL Query you can run to display ICMPs only.