Task 1.4.2: Test database connection strings outside of TraCS

Purpose: You are unsure whether a computer can successfully reach the tracs_data SQL Server database and want to test the connection without using TraCS.

Field units connect to the tracs_data database to process citation number installation requests and sometimes require a slightly different connection string depending on the network environment in which they are deployed.  The TraCS configuration manager does not allow editing and testing of connect strings when running TraCS in mobile mode.

TraCS uses OLE DB to connect to SQL server.  Testing the connection using the ODBC administration tool in Windows is not sufficient since ODBC connection strings are different from OLE DB connection strings.

The following procedure will allow you to test an OLE DB connection to your tracs_data database.

• Create an empty txt file on your desktop.

• Rename the text file to testTraCS_Data.udl

• Double click the file—it should open in OLE DB Core Services.

Open

• Click on the Provider tab and select Microsoft OLE DB Provider for SQL Server.

• Click on the Connection tab and configure the settings to connect to tracs_data, for example:

Open

• Click the Test Connection button.  If you can connect successfully, skip to step 8.

• If the connection failed, you may need to make changes on the All tab.  Specific settings will be dependent on your network environment.  Please work with your network administrator and your SQL Server administrator to determine the required settings.  After the Test Connection button returns successful results, proceed to step 8.

Example Scenario:

The field unit is on a different domain from the SQL Server database and SQL Server is configured to only use the Named Pipes protocol.

Option 1: Establish a trust relationship between the two domains.  (Using the named pipes protocol with an un-trusted domain will not work.)

Option 2: Implement the TCP/IP protocol on SQL server.  Then, on the All tab, enter DBMSSOCN in the Network Library field to force the connection to use the TCP/IP protocol.

Use your new knowledge of the correct connection string information and/or changed network environment to configure TraCS.

• Rename testTraCS_Data.udl to testTraCS_Data.txt.

• Open testTraCS_Data.txt in notepad to review the tested connection string.

• At the master computer, update the Data_FieldUnit Db connection string with the updated information by (below are steps contained intask 1-6(c) of the Installation Guide):

  • Log into Configuration Manager.

  • Click on the Setup tab.

  • Click on the Database Connection button.

  • Expand the data by clicking on the “>” next to Database Connection Strings.

  • Click on Data_FieldUnit.

  • Update the connection string as needed based on what was determined in previous steps.

• Perform task 4-1(c) to create a new distribution.

• Run the new distribution on the field unit.

Note: If you have difficulty determining how to translate the connection string into TraCS settings, please send an email to badgertracs@dot.wi.govrequesting assistance.  Be sure to attach test TraCS_Data.udl to your message.

• Note: If your connectivity issues are due to firewalls, Proxies, or VPNs, please see this helpful article from Microsoft about default port assignments for SQL Server:

 https://msdn.microsoft.com/en-us/library/cc646023(v=sql.100).aspx

We cannot answer specific information on ports because we don’t know how agencies chose to configure SQL server on their network.