Setup SQL Server for bandwidth usage stats

You can set up SQL Server database using pre-created batch scripts or manually. Choose which one is more appropriate for you.

SQL Server setup using batch scripts

  1. Open folder "%PROGRAMFILES%\Microsoft ISA Server\Bandwidth Splitter\BWStats" on ISA Server computer. Copy the following files from this folder to SQL Server computer: BWStats.sql, grant_access.bat, setup_sql.bat.
  2. Log on to SQL Server computer locally or using Remote Desktop connection.
  3. Open command prompt (cmd.exe) and go to directory where you copied the files.
  4. Run setup_sql.bat. It has one optional parameter which allows you to specify name of database to be created. If parameter not specified, the default name is BWStats.
    setup_sql.bat
  5. Ensure that you don’t see any error messages in resulting output.
  6. Grant database access to user account which will be used for authentication.

    a) If you use Windows account, run (replace BWStats with desired database name if needed; replace Domain\User with domain account that you have created for this database):

    grant_access.bat (local) BWStats Domain\User
    b) If you use SQL Server account, run (replace BWStats with desired database name if needed; replace User and Password with desired SQL Server account name and password):
    grant_access.bat (local) BWStats User Password
    Note that this command will create a new SQL Server account based on this data.

Manual SQL Server setup

  1. Open folder "%PROGRAMFILES%\Microsoft ISA Server\Bandwidth Splitter\BWStats" on ISA Server computer. Copy the following file from this folder to SQL Server computer: BWStats.sql.
  2. On the computer running SQL Server, start SQL Server Management Studio and connect to server.
  3. Right-click Databases and select New Database...
  4. Specify database name, for example BWStats. Change other options if needed. Click OK to create database.
  5. Click File menu, select Open, then File... and open file BWStats.sql.
  6. Add the following lines to the top of the SQL script (if needed, replace BWStats with the name of the database you just created):
    Use BWStats
    Go
  7. Under the Query menu, click Execute.
  8. Expand Security node (under root server node, not under database), right-click Logins, and select New Login....
  9. Choose either Windows authentication or SQL Server authentication.
  10. a) If you use Windows account, in Login Name enter domain account that you have created for this database.
    b) If you use SQL Server account, in Login Name enter desired SQL Server account name. In Password, enter the password for the new account. Uncheck Enforce password expiration.
  11. In New Login window, switch to User Mapping page.
  12. Put a check mark beside database name you just created.
  13. In lower part of page, select db_owner database role.
  14. Click OK to save properties of new account.

Enabling remote access to SQL Server Express

By default, remote access is disabled in SQL Server Express editions. To enable SQL Server Express to accept remote connections, perform the following steps.

  1. Launch the SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 200x > Configuration Tools > SQL Server Configuration Manager).
  2. Expand SQL Server Network Configuration and click Protocols for SQLEXPRESS.
  3. Right click TCP/IP in the list of Protocols and select Enable.
  4. Now configure SQL Server to use fixed TCP listening port:
    1. Right-click TCP/IP and select Properties.
    2. Click on IP Addresses tab and scroll down to IPAll.
    3. Clear out the value for TCP Dynamic Ports.
    4. Set 1433 in TCP Port.
    5. Click OK to close properties window.
  5. Click SQL Server Services, right-click SQL SERVER (SQLEXPRESS) in the right pane and select Restart to restart SQL Server.
  6. If Windows Firewall is enabled on computer running SQL Server, you also need to add sqlservr.exe program or port 1433 to firewall exceptions list.

See also
Setup database for bandwidth usage stats
Setup MSDE for bandwidth usage stats
© BNTC Software. All rights reserved.