During a Standard installation of Microsoft ISA Server 2006, ISA will install a local MSDE (Microsoft SQL Server Database Engine) to provide logging for the Microsoft Firewall- and Webproxy service. It is possible to log the Firewall- and Webproxy service using a local Microsoft SQL Server 2005 database or a remote Microsoft SQL database.
Firewall Lockdown mode
Be careful when you move firewall logging to an external Microsoft SQL Server, because ISA Server uses a Firewall Lockdown mode that deactivates nearly all firewall functionality when logging cannot be enforced. It is possible to disable firewall lockdown mode (I provide a link on how to disable the Firewall Lockdown Mode at the end of this article), but I never recommend disabling this feature.
Get your copy of the German language “Microsoft ISA Server 2006 – Das Handbuch”
Installing Microsoft SQL Server 2005 Express Edition
After downloading the SQL Server Express Edition from the Microsoft website, extract the downloaded package and follow the installation instructions.
Figure 1: Extracting the Microsoft SQL Server 2005 package
Next, read, understand and accept the license agreement.
Before the Microsoft SQL Server setup can start, the installation wizard installs the Microsoft SQL Native Client and the Microsoft SQL Server 2005 Setup support files.
Figure 2: Install Microsoft SQL Server prerequisites
After installing the required setup files, the Microsoft SQL Server Installation Wizard starts.
Figure 3: Setup starts the SQL Installation Wizard
Before installing the Microsoft SQL Server database, the installation wizard will do some checks to ensure the machine has the necessary configuration for Microsoft SQL Server 2005 Express Edition.
Figure 4: System configuration check
Enter the registration information.
Select the components you would like to install. The Standard selection is enough to provide reliable database functionalities.
Figure 5: Feature selection
Create a new instance and specify the instance name ISALOG.
Figure 6: New “named instance” ISALOG
Specify the Local system account as service account.
Figure 7: Specify an account for the Microsoft SQL Database
Select Windows as the authentication mode.
Enable the User Instance.
Figure 8: Setup installs the required components
After setup has finished, restart the Server.
On the ISA Server side
Activate the System Policy for Remote SQL Logging. This allows ISA Server access to an internal Microsoft SQL Server database.
Figure 9: Allow system policy rules for Microsoft SQL Server access
Per default Microsoft SQL Server 2005 Express only allows access from local services to the database. You have to change the setting to allow remote connections with the help of the Microsoft SQL Server 2005 Surface Area Configuration.
Figure 10: SQL Server attack surface configuration
As a next step, you must check if the TCP/IP protocol is enabled for the ISALOG database and if the Network configuration uses a fixed port for SQL Server (1433).
Figure 11: Enable TCP/IP
Disable SQL Server Dynamic TCP Ports (delete the Null value) and specify a fixed port (1433) for all IP addresses.
Figure 12: Specify a fixed port
You have to restart the Microsoft SQL Server service after settings are applied.
Installing Microsoft SQL Server Management Studio Express
Microsoft SQL Server Management Studio Express is the Management console for managing many parts of Microsoft SQL Server. If you are using Microsoft SQL Server 2005 Express Edition, you must download and install the Microsoft SQL Server Management Studio Express separately from the database.
Figure 13: Installing Microsoft SQL Server Management Studio Express
Create a Database
After installing the Microsoft SQL Server Management Studio Express version, start the console and create a new database called ISALOGS.
Figure 14: Create SQL Database ISALOG
Create a new account in Active Directory and give this account Domain user rights and additional SQL Server rights to access the ISALOG database. This account will be used in the ISA Server Management Console for direct SQL access.
Create the database tables
The ISA Server 2006 installation CD contains two .SQL files, which can be used to create the required database tables into the ISALOG database.
Open FWSERV.SQL from the ISA Server 2006 program files CD into the Microsoft SQL Server Management Studio Express (File – Open). Before you execute the FWSRV.SQL script, extend the first line with the instruction to use the ISALOG database.
Figure 15: Extend the script to use the ISALOG database
Now it is possible to execute the script. If everything is fine, the new database table will be created.
Figure 16: Table FirewallLog
Now it is time to give the ISA-SQL Account the right to access the ISALOG database.
Figure 17: Add the ISA-SQL user to the ISALOG Database
Request a certificate for SQL Server connection encryption
Per default, ISA Server uses an encrypted connection to the Microsoft SQL Server. The connection will be established with the help of a Server Authentication certificate, so you have to install a certificate before establishing the connection. It is possible to use your own CA to create a certificate request or self signed certificate. There are detailed instructions on how to do this in the link section below.
Figure 18: Request a Server Authentication certificate
ISA side
Now it is time to change the Microsoft ISA Server Logging from MSDE to SQL. Start the ISA Server 2006 Management console, navigate to Logging option and change the logging options for the Microsoft Firewall Service (If you want to change the Logging for the Webproxy Service, the process is similar).
Enter the name of the SQL Server, the Standard SQL port (1433), the Name of the Firewall Table and Windows as the Authentication method. You must also specify the account which will be used to establish a connection. Click Test to test the SQL connection.
Figure 19: SQL Logging options
After changing the Logging options, click Apply to activate the settings and if everything is good ISA Server will now use a Microsoft SQL Server database for Firewall and Webproxy Logging.
You can now use all Microsoft SQL Server 2005 advanced features for your ISA Server database like automatic backups, Database shrinking and many more advanced features.
Conclusion
In this article I tried to show you how to configure ISA Server Firewall Logging to use a remote Microsoft SQL Server 2005 Express database. The process for logging with Microsoft SQL Server 2005 is nearly similar, so you can use this article for both versions. In this article I showed you only how to change the logging of the Microsoft Firewall service with a Microsoft SQL Server 2005 database. If you also want to log the Webproxy log into a Microsoft SQL Server 2005 database, you only have to create the Webproxy table for the ISALOG database and change the Webproxy logging in the ISA Management console to Microsoft SQL Server logging.
Related links
- Monitoring, Logging, and Reporting Features in ISA Server 2006
- Best Practices for Performance in ISA Server 2006
- Microsoft SQL Server Management Studio Express
- How to configure ISA Server 2004 and ISA Server 2006 to log data to an SQL Server database
- How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
