Microsoft SQL Server Connection and Security

There are many options for connecting to an SQL Server and many ways to configure security. We recommend that you keep things simple by using the most flexible interconnect, TCP/IP, and follow Microsoft's prefered security solution, Windows Authentication.

Some users feel there is also a need to use SQL Server based Authentication, so it is also included in this turorial.

However, as noted below, the Loadman Fleet Manager gives you the ability to choose any connection/security method that the SQL Server supports. But those other methods are not documented here. See SQL Server documentation.

Connection Strings
A connection string is instructions to the SQL Server Client software providing instructions on how to connect to the database.
There are two places in the Loadman Fleet Manager user interface where you are asked to enter SQL Server connection strings. They are the the Database Connection tab in the Mainteneance Tools and in the setup for realtime data export.
Simplified Connection Strings
The Loadman Fleet Manager supports a simplified connection string format where the user is only required to enter as little as one item and the rest of the (sometimes complex) string is automagically generated.

The format of the data that you are asked to provide is the three values server\instance;database which are ...
If you have a standard installation and all of your computers are in the same Domain or Workgroup, the connection string for the Master is localhost and the connection string for Satellites are SQLServerComputerName. These fields are then processed used to generate the connection string as follows...
Provider=SQLNCLI;Data Source=server\instance;Integrated Security=SSPI;DataTypeCompatibility=80;Initial Catalog=database;
Custom Connection Strings
To allow flexibility, if the connection string starts with a ! it will not be processed by the Loadman Fleet Manager before sending it to the SQL Server. That is, the connection string following the ! is sent unaltered. So one could connect to a database named LFM_Data on the machine OurServer by entering the connection string ...
!Provider=SQLNCLI;Data Source=OurServer;Integrated Security=SSPI;DataTypeCompatibility=80;Initial Catalog=LFM_Data;

Note that the fields Provider=SQLNCLI and DataTypeCompatibility=80 are required by the Loadman Fleet Manager.

Security and Permissions
There are two methods for providing security for the SQL Server. The first, Windows Authentication, is recommended for most users. SQL Server Authentication is documented for those whose network configuration requires it.
Windows Authentication
Windows authentication means that the remote database user must be authenticated (as a valid Windows user) on the computer hosting the SQL Server. If you have a domain based network, this is accomplished with the domain configuration tools, your domain admistrator will need to make these settings. If not on a domain (such as set up to use a Workgroup), then one can still use windows authentication by creating a mirror of the remote user's account on the server. This means that you need to create a user account on the server with the same username and password as the remote user.

For a user to access the SQL Server, they must either have administrator privledges on the server computer or the SQL Server needs to be configured to allow their connection. To configure a user, do the following using the SQL Server Management Studio. From the left pane at the server (or instance) level select Security:Logins and add the user in the form of Computer\User Then in the right pane right click the user and select properties. From the left pane of the property window select User Mapping and then check the database that you are granting access to. In the database roles below, check db_datareader, db_datawriter and public as shown below.

SQL Server Authentication
First you need to configure the SQL Server to support SQL Server Authentication follow. Using the SQL Server Management Studio right click the top entry in the Object Explorer tree and select properties then select security from the list on the left. Select SQL Server and Windows Authentication mode as shown.

To add a user, expand the Object Explorer tree as shown below and right click on Logins and select New Login. Enter the user's name, select SQL Server Authentication, and set the password. Then select LoadmanFleetManager as the default database. Note that the option User must change password at next login is not supported by the SQL Server Express and thus must not be selected.

The user must be given permission to access the database. Right click on the new user, select properties, and then select User Mapping. Select the database "LoadmanFleetManager". Then enable db_datareader, db_datawrite, and public as shown. The user that will be used for the Master Computer connection must also be given db_ddladmin and db_backupoperator that is shown in red.

Now restart the SQL Server. Select the top entry in the Object Explorer tree, right click, and select Restart.

The connection string for the user thus created is
!Provider=SQLNCLI;Data Source=TheServer\SQLEXPRESS;User ID=George;Password=AstroJetson;DataTypeCompatibility=80;Initial Catalog=database;

Populating the SQL Database before first use
Before using the SQL database, tables must be created and then populated with your current data (or for new users, the basic default values). This is accomplished from the maintenance tools view as seen below. The steps to accomplish this are ...
  1. Make sure that the "Current Database" is set to your "Jet 4" database. If it is not...
  2. Make sure that the SQL connection string works by pressing "Test".
  3. Press "Copy data and set new connection".
  4. Press "Save and start Loadman Fleet Manager".
  5. When the Loadman Fleet Manager starts, select menu items "Help" : "Folders" and right above the "Close" button you will see your database connection type, it will say "SQL Server ...."