Setup local SQL Server Express database for bandwidth usage stats

To create a new database in SQL Server Express, follow these steps:

  1. Open command prompt (cmd.exe) on Forefront TMG computer.
  2. Go to BWStats folder:
    cd "%PROGRAMFILES%\Microsoft Forefront Threat Management Gateway\Bandwidth Splitter\BWStats"
  3. Run setup_msde.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.
  4. Ensure that you don’t see any error messages in resulting output.
  5. Grant database access to user account which will be used for authentication. (This step is optional if all 3 components of bandwidth usage reporting are installed on single server.)

    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)\MSFW 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. Note that this command will create a new SQL Server account based on this data):
    grant_access.bat (local)\MSFW BWStats User Password

Enabling remote access to SQL Server Express

By default, remote access is disabled in SQL Server Express editions. If you are going to install Bandwidth Splitter reporting service and its management web application on another server, then you also need to enable network access to the database. 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 MSFW.
  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. Open Administrative Tools > Services and restart service SQL Server (MSFW).

See also
Setup database for bandwidth usage stats
Setup remote SQL Server for bandwidth usage stats
© BNTC Software. All rights reserved.