/
Task 1.4(c): Set up Databases, E-Mail, and Proxy Server Connections, SQL Server Alternative

Badger TraCS Guides

Task 1.4(c): Set up Databases, E-Mail, and Proxy Server Connections, SQL Server Alternative

Owned by Geraldine Polster
Last updated: Apr 11, 2024 by David Malisch
7 min read

Task 1.4(c): Set up Databases, E-Mail, and Proxy Server Connections, SQL Server Alternative

Purpose: Connects the TraCS master computer to the TraCS SQL Server databases.

Requirements: Master computer, SQL Server/SQL Server Express installed and accessible on your network, SQL Server Management Studio, SQL Server administration skills.

TraCS will work with any edition of SQL Server, 2008 or better, including Express.

 

Did You Know?

See http://www.microsoft.com/sqlserver/en/us/product-info.aspx for information on the different editions of SQL Server available from Microsoft.

SQL Server Express is available free of charge from Microsoft. The download link is here: http://www.microsoft.com/en-us/download/details.aspx?id=29062

If you decide on the Express edition, you need to install ENU\x64\SQLEXPRWT_x64_ENU.exe if you have a 64-bit server or ENU\x86\SQLEXPRWT_x86_ENU.exe if your server is 32-bit. These EXEs contain the database engine as well as the tools necessary to administer the database.

When installing SQL Server Express, be sure to choose “Express” rather than “LocalDB.” LocalDB will not work with TraCS.

Please note that the Badger TraCS Help Desk does not perform SQL Server administration services. Your agency must be responsible for SQL Server administration and maintain the required skill set. The help desk’s knowledge is limited to the information presented here.

  1. Start SQL Server Management Studio (SSMS).

  2. Create the following databases.

    1. tracs_support

    2. tracs_data

    3. tracs_log

    4. tracs_users

    5. tracs_custom

 

USE master GO CREATE DATABASE tracs_support GO CREATE DATABASE tracs_data GO CREATE DATABASE tracs_log GO CREATE DATABASE tracs_users GO CREATE DATABASE tracs_custom GO

 

  1. Secure the TraCS databases created in step 2.

    1. Set up a user id and password in SQL Server that will be used for all TraCS generated SQL commands. The user ID needs the following permissions for SQL Server databases:

      1. Select, insert, delete, and update permissions for all tables in the TraCS databases

      2. Create table, alter table, create column, and alter column (for successful pack installation and migration of users)

      3. Alter database (for successful pack installation)

      4. They simplest way would be to give the TraCS user ID owner permissions

  2. Create the needed tables in the tracs_log database using the “log” SQL script. The script will be located in c:\programdata\tracs\Baseline Files\Scripts\ Baseline Log Total SQLServer.sql

Note: a common error is to run the script against the default database, which is typically not the tracs_log database. Either change the default database in SSMS to tracs_log or add the following lines to the script before running it—

USE [tracs_log] Go

  1. Open the TraCS Configuration Manager,

  2. Sign in with account 00001, password tracs.

  3. Click Setup tab

  4. Click the Database Connections button.

  5. Select Database Connection Strings.

  6. Click Import DB Connections.

    1. Expand the database connection string item.

    2. Select the Support, Data, Users, and Log entries. (Use the <Ctrl> key to select multiple entries at the same time.)

    3. Click OK.

  7. Edit the database connection for the Support database.

    1. Expand the Database Connection Strings item.

    1. Select Support.

    2. Change the Database to SQL Server.

    3. Choose Application User ID and Password.

    4. In the Server Name field, enter the name of the server hosting the SQL server databases. This is the same as the Data Source in the connection string.

    5. In the Database Name field, enter tracs_support. This will populate the Catalog portion of the connection string.

    6. Enter the user ID and password needed to access the database.

    7. Click Test to verify the connection string.

  8. Edit the database connection for the data database.

    1. Select Data under the Database Connection Strings item.

    2. Change the Database to SQL Server.

    3. Choose Application User ID and Password.

    4. In the Server Name field, enter the name of the server hosting the SQL server databases. This is the same as the Data Source in the connection string.

    5. In the Database Name field, enter tracs_data. This will populate the Catalog portion of the connection string.

    6. Enter the user ID and password needed to access the database.

    7. Click Test to verify the connection string.

Note: The database connection configuration editor also contains an option for trusted security, also known as “Windows Authentication.” We do not recommend this setting. Using trusted security implies that each user may also interact with the TraCS databases outside of the TraCS program because SQL Server permissions have been granted on the individual user’s account. Application User ID and Password is more secure.

  1. Edit the database connection for the Users database.

    1. Select Users under the Database Connection Strings item.

    2. Change the Database to SQL Server.

    3. Choose Application User ID and Password.

    4. In the Server Name field, enter the name of the server hosting the SQL server databases. This should be the same as what you would enter for the Data Source portion of the connection string.

    5. In the Database Name field, enter tracs_users. This will populate the Catalog portion of the connection string.

    6. Enter the user ID and password needed to access the database.

    7. Click Test to verify the connection string.

    8. Click Apply to save your work. (Important: If missed, next step will fail.)

  2. Run a batch file to create the tables for tracs_users database:

  3. C:\Program Files (x86)\TraCS\Baseline Files\Distribution\Total\TraCS Baseline Total Users.bat

  4. Edit the database connection for the Log database.

    1. Select Log under the Database Connection Strings item.

    2. Change the Database to SQL Server.

    3. Choose Application User ID and Password.

    4. In the Server Name field, enter the name of the server hosting the SQL server databases. This should be the same as what you would enter for the Data Source portion of the connection string.

    5. In the Database Name field, enter tracs_log. This will populate the Catalog portion of the connection string.

    6. Enter the user ID and password needed to access the database.

    7. Click Test to verify the connection string.

  5. Edit the database connection for the Custom database.

    1. Select Custom under the Database Connection Strings item.

    2. Change the Database to SQL Server.

    3. Choose Application User ID and Password.

    4. In the Server Name field, enter the name of the server hosting the SQL server databases. This should be the same as what you would enter for the Data Source portion of the connection string.

    5. In the Database Name field, enter tracs_custom. This will populate the Catalog portion of the connection string.

    6. Enter the user ID and password needed to access the database.

    7. Click Test to verify the connection string.

  6. Edit the SMTP connection to enable e-mail message delivery. (Optional)

    1. Select the SMTP entry.

    2. Click the add button.

    3. Enter e-mail in the Name field.

    4. Click OK.

    5. Expand the SMTP item.

    6. Select e-mail.

    7. Complete the panel on the right:

      Note: consult your e-mail administrator if you need help completing this panel.

      1. In the Description field, enter e-mail.

      2. In the Server field, enter the address of your e-mail server. (For example: mail.myserver.com)

      3. In the port field, enter the port number, typically 25.

      4. In the timeout field, enter 240.

      5. In the UserID and Password fields enter the login credentials to be used when connecting to the e-mail server.

      6. In the From Address field, enter the “from” e-mail address to be used with messages coming from TraCS.

      7. In the Display Name field, enter the display name to be associated with the “from” e-mail address.

  7. Configure the web proxy server if your agency uses a proxy server to reach the Internet.

    1. Expand the Proxy item.

    2. Select Defined Proxy.

    3. Complete the panel on the right:

      1. Note: consult your proxy server administrator if you need help completing this panel.

      2. Complete the URL and Port fields as recommended by your proxy server administrator.

      3. If a user id and password are required to connect to the Internet through the proxy server, enter the required information in the Domain, UserID and Password fields.

  8. Click OK.

  9. Close the configuration manager.

 

 

Related content

email badgertracs@dot.wi.gov or call 608-267-2096