How to configure ISA Server 2000 to log on to a SQL Server database (300211)

MORE INFORMATION

1. You must set up the SQL server with a database file for each service that you want logged from ISA Server:
   1. On the SQL server, start Enterprise Manager.
   2. Connect to the SQL server that you want to host the database files.
   3. On the Tools menu, click SQL Query Analyzer.
   4. On the File menu, click Open.
   5. Locate "ISA CD":\Isa.
   6. Open one of the following .sql files, which depends upon the ISA Server service that you want to set up to log to an SQL database:
      • For the Firewall service, open the Fwsrv.sql file.
      • For Packet Filtering, open the Pf.sql file.
      • For the Web Proxy service, open the W3proxy.sql file.
      NOTE: You must have a separate database for each ISA Server service on each ISA Server.
      
      
   7. Add the following lines to the top of each script:

      Create database <database name>
      Go
      
      Use <database name>
      Go
      						

      Note The database is not required to have a specific name, you can use whatever name that you want.
      
      
   8. On the Query menu, click Execute.
   9. Close the Query Analyzer. Your database is created. Then, update the database (press the F5 key).
2. You must set up the SQL server to accept the Open Database Connectivity (ODBC) data connection from the ISA Server-based computer. There are two possible logon methods for the SQL server. You can use either method if your ISA Server is located in the same Windows 2000 domain as the SQL server, but if your ISA Server is not located in the same Windows 2000 domain, you must set up an SQL Server account:
   1. Start the SQL Enterprise Manager.
   2. Connect to the SQL server that you set up to host your databases.
   3. Expand Microsoft SQL Servers.
   4. Expand SQL Server Group.
   5. Expand your server.
   6. Expand Security.
   7. Right-click Logins.
   8. Click New Login.
   9. In the Database list, select the database that ISA will be logging to.
   10. If you are located in the same domain as ISA Server, you can click Windows Authentication. This step does not work in a Microsoft Windows NT 4.0 domain as you cannot specify a computer account:
       1. Under Name, type: domainname\isaservername$
       2. Click the Database Access tab.
       3. Click the databases that this logon method can access (the databases that you created earlier), and then click OK.
   11. If you are located on a different domain than ISA Server, you must use SQL Server Authentication:
       1. The Name field is not required to have a specific name. Use a name to describe the logon method.
       2. Enter a password for this logon method.
       3. Click the Database Access tab.
       4. Select the databases that this logon method can access (the databases that you created earlier), and then click OK.
   12. In the left pane, expand Databases, expand the ISA Server logging database name, and then click Tables.
   13. Right-click the table that will store the ISA Server data, and then click Properties.
       
       By default, the table name for Firewall service logging is FirewallLog and the table name for Web Proxy logging is WebProxyLog.
   14. On the General tab, click Permissions.
   15. For the ISA_Server_Name$ computer account or for the SQL Server account, click to select the SELECT check box and the INSERT check box, and then click OK two times.
   16. If you ran both .sql files to generate both the FirewallLog table and the WebProxyLog table, repeat steps m through o for the second table.
   17. Stop and then start the SQL server service.
3. Set up the ODBC data source on the ISA Server-based computer:
   1. Under Administrative Tools, click the Data Sources (ODBC).
   2. Click the System DSN tab.
   3. Click Add.
   4. Click the SQL server driver.
   5. Click Finish.
   6. Name the data source with the same name that you used for the database file.
   7. You can enter a description or leave it blank.
   8. Enter the name of the SQL server.
   9. Click Next.
   10. There are two options for database authentication. These options correspond to the account that you set up in step two:
       • Click With Windows NT authentication using the network login ID to use the ISA Server-based computer account for authentication. This option can only be used in a Windows 2000 domain.
       • Click With SQL Server authentication using a login ID and password entered by the user to use an SQL account for authentication.
   11. Click Next, and then use the default settings for the remainder of the wizard requests.
4. You must set up ISA Server to log on to the SQL database:
   1. Start the ISA Server Microsoft Management Console (MMC).
   2. Expand Monitor Configuration.
   3. Click Logs.
   4. Right-click the log that you want to set up, and then click Properties.
   5. On the Log tab, click Database.
   6. Enter the name of the data source name (DSN) that you created.
   7. If you used the preceding SQL Server logon method, you must enter the SQL logon method for the database that is under "Use This Account". If you used Windows NT authentication, you can leave this box blank.
   8. Stop and then restart the services that you just set up to log on to the SQL server.