Setup remote 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 Forefront Threat Management Gateway\Bandwidth Splitter\BWStats" on Forefront TMG 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 Forefront Threat Management Gateway\Bandwidth Splitter\BWStats" on Forefront TMG 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.

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