NetResults ProblemTracker
Using Microsoft SQL Server 7.0

Using MS SQL Server 7.0

The following instructions apply to Microsoft SQL Server Version 7.0. SQL Server is a professional DBMS and by it's nature is very complex. This document assumes that you are very familiar with the administration and use of Microsoft SQL Server. Please refer to the documentation included with SQL Server for detailed instructions where necessary.

For questions regarding the use of SQL Server, please contact Microsoft Corporation technical support.

For performance and configuration reasons it is strongly recommended that you locate the web server and SQL Server on the same machine. It is also recommended that you install the latest Microsoft ODBC drivers if you intend to use SQL Server.

In order to use Microsoft SQL Server you must perform the following steps:

  1. Create the SQL Server Database
  2. Set up the ODBC Data Source
  3. Set up the SQL Server Network Connection
  4. Initialize the ProblemTracker Database

Advanced SQL Server administrators who wish to use NT Integrated authentication for ProblemTracker database access may wish to additionally read the Advanced Topics section.

Create the SQL Server Database

By default ProblemTracker uses a Microsoft Access database, however it can also be used with Microsoft SQL Server. SQL Server is a separately sold product available from Microsoft Corporation and is not bundled with ProblemTracker.

Step 1 - Create The Database

To use ProblemTracker with SQL Server, you must first create a database using SQL Server. You can create a database of any name, although it is recommended that you use the name "ptdev" for the code development version or "ptweb" for the web development version. Please make a note this name as you will need it when defining the ODBC data source. Follow these steps to create the Database:

  1. Start the Microsoft SQL Server Enterprise Manager
  2. Select the database server you wish to use and press the + button located next to it.
  3. Right click on the folder labeled "Databases" and select "New Database".
  4. In the dialog that appears, enter the database name ("ptdev", or "ptweb"), the initial database size, database file name, location, and press the "OK" button. It is recommended that you create an initial database size of 10 MB.

Step 2 - Adding a New SQL Server Login

The standard security scheme used to access SQL Server from ProblemTracker involves using SQL Server Standard authentication and adding a user login with a fixed userid and password. It is possible to use SQL Server NT Integrated authentication, however this is a more complicated process and is covered under the Advanced Topic section.

To set up database security, follow these steps:

  1. Start the Microsoft SQL Server Enterprise Manager
  2. Select the database server you wish to use and press the + button located next to it.
  3. Click on the + button next to the folder labeled "Security".
  4. Right click on the icon labeled "Logins" and select "New Login".
  5. Enter the name "PROBLEMTRACKER", select SQL Server Authentication, and then a password of "PROBLEMTRACKER" (do not enter the quotes).
  6. Click on the tab labeled "Server Roles" and select "Database Creators".
  7. Click on the tab labeled "Database Access" and select the database you just created for use with ProblemTracker.
  8. Click on OK.

Step 3 - Setting SQL Server Authentication

To correctly set up SQL Server authentication for use with ProblemTracker, please follow these steps:

  1. Start the Microsoft SQL Server Enterprise Manager
  2. Located the database server you wish to use, right click on it and select "Properties...".
  3. Click on the "Security" tab.
  4. Select "SQL Server And Windows NT"
  5. Click on OK.

Step 4 - Assign Database Permissions

You must give the ProblemTracker login sufficient permissions before ProblemTracker is able to access SQL Server. To do this, follow these steps:

  • Start the Microsoft SQL Server Enterprise Manager
  • Select the database server you wish to use and press the + button located next to it.
  • Select the database you wish to use, right click on it, and select "Properties...".
  • Click on the "Permissions" tab.
  • In the table displayed, locate the row labeled "PROBLEMTRACKER" and click in all the boxes the row.
  • Press "OK".

    Set up the ODBC Data Source

    You must export the MS SQL Server database you created in the previous section as an ODBC system data source (System DSN) using the ODBC Administrator located in the Control Panel (icon labeled either "32bit ODBC" or "ODBC").

    The name of the System DSN is determined by the version of ProblemTracker installed, as shown in this table:

    ProblemTracker Version System DSN Name
    Software Development #1 ptdev
    Web Site Development ptweb
    Software Development #2 ptdev2
    Software Development #3 ptdev3
    Software Development #4 ptdev4

    ODBC Administrator


    Using ODBC Administrator Style "B"

    Please follow these steps to export the ProblemTracker database as a System DSN:

    1. Start the Control Panel ( Start->Settings->Control Panel ) and double click on the icon labeled "ODBC" or "32-bit ODBC".
    2. In the dialog that appears, click on the tab labeled "System DSN".
    3. Press the button labeled "Add...".
    4. In the dialog that appears, select "SQL Server" from the list of installed drivers and press "Finish".
    5. Depending upon the SQL Server driver you have installed, you will see either a single dialog or a wizard style series of dialogs that allow you to specify the System DSN.

      Single Dialog
      In the "Data Source Name:" field enter the       appropriate DSN name. In the "Description:" field, enter a meaningful description (like "ProblemTracker Database"), and in the "Server:" field select the host where SQL Server is running. Now click on the "Options >>" button and enter the SQL Server database name in the "Database Name" field. Press OK.

      Wizard Style
      On the first page, enter the appropriate DSN name in the "Name:" field, a meaningful description (like "ProblemTracker Database") in the "Description:" field, and select the host where SQL Server is running in the "Server:" field. Press "Next >". Select the option labeled "With SQL Server authentication using a login ID and password entered by the user", check the box labeled "Connect to SQL Server to obtain default settings for the additional configuration options:", and enter PROBLEMTRACKER in both the Login ID and Password fields. Press "Next >". Check the box labeled "Change the default database to:" and enter the SQL Server database name. Press "Next >", "Next >", "Finish", and "OK".

    6. Press "OK"

    Set up the SQL Server Network Connection

    If you are running the web server and SQL Server on two different machines, the chances are that you will receive this message when you run ProblemTracker:

    [Microsoft][ODBC SQL Server Driver][DBNMPNTW]ConnectionOpen (CreateFile()).

    This message indicates that you must perform some additional configuration before your web server can communicate with SQL Server (Note that if you use a Netscape Server, or have the web server and SQL Server running on the same host you probably will not experience this problem).

    The problem occurs because by default SQL Server and the SQL Server ODBC driver use named pipes to communicate. Named pipes require a local area network authentication to establish the the connection between the web server host and the database server host, in addition to the SQL username/password. To resolve this problem you can either set up a common user between the SQL Server host and the web server host, or you can use TCP/IP sockets instead of named pipes to communicate with SQL Server.

    Solution 1 - Use TCP/IP Sockets Instead Of Named Pipes (RECOMMENDED)

    To set up your ODBC DSN to use TCP/IP sockets to communicate with SQL Server, please follow these steps:

    (Note: if the SQL Server Client Configuration Utility (windbver.exe) is installed, you can just run it, click on the "Net Library" tab, under "Default Network" select "TCP/IP Sockets", and press "Done")

    For SQL Server ODBC Driver Version 3.50

    1. Select the data source and double click
    2. Press "Next >"
    3. In the Password field enter PROBLEMTRACKER
    4. Click on the "Client Configuration..." button. In the dialog displayed (SQL Server Client Configuration Utility) click on the "Net Library" tab and under "Default Network" select "TCP/IP Sockets".
    5. Press Done to dismiss the SQL Server Client Configuration Utility.
    6. Press "Next >", "Next >", "Next >", "Finish", and "OK".

    For SQL Server ODBC Driver < Version 3.50 (Version 2.65)

    1. Select the data source and double click
    2. Clear the checkbox marked "Use Trusted Connection".
    3. In the field labeled "Network Library" enter DBMSSOCN.DLL
    4. Press OK.

    To set up SQL Server to use TCP/IP sockets, run the SQL Server Network Utility and verify that the TCP/IP Server Network Library is loaded (it is loaded by default).

    Solution 2 - Use Named Pipes, Add A Common User

    This method is currently unavailable for Windows 2000 Server users

    If you are using Microsoft IIS, you can set up a common user in one of two ways:

    • Add a user with the same name as the default anonymous user defined on the web server machine (IUSR_host) using the same password and permissions on the host where SQL Server is running.
    • Change the anonymous user from the default (IUSR_host) to a domain user (Domain\User) visible on the host where SQL Server is running.
    If you are using a Netscape server, you can set up a common user by starting the server as a user that is also known to the host where SQL Server is running. This user can either be defined locally to the SQL Server host, or via the Windows NT Domain, but it must have the same username and password as the user used to start the Netscape Server. You can specify the user to start the server by starting the Windows Control Panel, double clicking on the Services icon, selecting the Netscape server entry, and pressing the Startup button.

    Advanced Topics - Using NT Integrated Authentication

    This method is currently unavailable for Windows 2000 Server users

    The recommended way to set up database connectivity between ProblemTracker and SQL Server is using SQL Server Standard authentication. By using Mixed authentication, you can allow ProblemTracker access to SQL Server while also preserving NT Integrated Authentication for any other access.

    If for some reason it is unacceptable to allow use of SQL Server Standard authentication for ProblemTracker, you can set up your web server to allow ProblemTracker to use NT Integrated Authentication.

    Selecting a Domain User Account

    To use NT Integrated Authentication you must first select a user account from your Windows NT Domain that has sufficient user group permissions assigned to it under SQL Server (refer to the online help provided with the Microsoft Windows NT SQL Server for details). This account will be used to run the ProblemTracker application by your web server.

    Setting up Microsoft IIS & NT Peer Web Services 2.0 & 3.0

    In order to use NT Integrated Authentication, you must set up IIS so that the anonymous user account (or the actual user account if security is applied) is an Windows NT Domain user account recognized and granted sufficient privileges by SQL Server. To set the anonymous user account, follow these steps:

    1. Start the Internet Service Manager.
    2. Double click on the WWW Service name.
    3. Enter the appropriate Username and Password in the Anonymous Logon box.
    4. Press OK.

    Setting up Microsoft IIS & NT Personal Web Server 4.0

    In order to use NT Integrated Authentication, you must set up IIS so that the anonymous user account (or the actual user account if security is applied) is an Windows NT Domain user account recognized and granted sufficient privileges by SQL Server. To set the anonymous user account, follow these steps:

    1. Start the Internet Service Manager.
    2. Select the web server, right click on it and select "Properties"
    3. Select the Directory Security tab.
    4. Press the "Edit..." button under "Anonymous Access And Authentication Control".
    5. Check the "Allow Anonymous Access" box and press the "Edit..." button.
    6. Enter the appropriate Username and Password in the Anonymous Logon box.
    7. Press OK, OK, OK.

    Setting up Netscape FastTrack

    In order to use NT Integrated Authentication, you must set up Netscape network service so that it runs as a Windows NT Domain user account recognized and granted sufficient privileges by SQL Server. To set the anonymous user account, follow these steps:

    1. Start the Windows NT Control Panel and double click on the Services icon.
    2. Locate and select the Netscape Server entry and press the "Startup..." button.
    3. In the "Log On As" box select the "This Account" option, select a user, and enter the appropriate password.
    4. Enter the appropriate Username and Password in the Anonymous Logon box.
    5. Press OK, OK.