How do workstations determine the server and database connection instructions?

Answer: ExpressMaintenance and ExpressRequest use an ini file to obtain SQL Server, database and other information for connecting to the SQL Server.

By default, ExpressMaintenance and ExpressRequest look for the connection file named ExpressMaintenance.ini.  However, you can have other connection ini files with any name and pass the connection file name to ExpressMaintenance via shortcut parameter.  The connection file must reside in the folder with the application exe file.  For example, ExpressMaintenance.ini must reside in the folder with Maintenance.exe.

The minimum contents of the connection file is the SQL Server and Database information as shown below.  In this example, the program will assume the remaining settings such as SQLLogin=etuser, SQLPassword=etrik22 and SQLDriver=MSSQL.

[Connection Info]
SQLServer=ActualSQLServerName
SQLDatabase=ActualDatabaseName (example: ExpressMaintenance)

If you are using Instances with your SQL Server, the SQL Server name must include the instance name (example: ActualServerName\SQLExpress).

The ini file is best edited using the Notepad text editor to insure no hidden characters are saved in the file.

Notice the space in “Connection Info”.  There is no space in the other entries of the ini file.

Also, make sure this file is in the folder with the program exe file (example: Maintenance.exe).

Be sure to grant users read permission to the connection file.

Test first at the server and make sure everything works correctly.

Next you will need to create shortcuts on the workstations that point to the exe file on the server computer (example: Maintenance.exe or ExpressRequest.exe).  Make sure they are pointing to the exe file in the same folder where you performed a test on the server computer.

The connection file can also contain additional optional entries as shown below.

SQLDriver=MSSQL – The specific MS SQL Driver type you wish to use when connecting to the SQL Server / Database.  Without this optional entry, ExpressMaintenance assumes built-in driver of ‘MSSQL’.  There are three additional options that can be used: ‘MSSQL-Native’, ‘MSSQL-ODBC’, and ‘MSSQL-Client’.

SQLLogin=etuser – The SQL login account to be used for connecting to the SQL Server.  The default value is ‘etuser’.  However, this can be any valid SQL Server login account that has full database ownership (dbo) permission to the database.

SQLPassword=etrik22 – The corresponding password for the SQL login account defined for connecting to the SQL Server.  The default value is ‘etrik22’ for the ‘etuser’ account.  However, this can be any valid password for the login SQL Server account being used.

SQLTimeout=10 – In some network environments, it might be that the workstations require several seconds or even a minute to find and connect to the SQL Server.  By default, ExpressMaintenance only allows 10 seconds to make the SQL Server connection.  You can increase the timeout allowance by adding the SQLTimeout line to the ExpressMaintenance.ini file.  For example, the line item of SQLTimeout=60 increases the allowed time to make the connection to 60 seconds.

UseWindowsAuthentication=Yes/No – Indicates if the program should try connecting to the SQL Server using windows authentication rather than SQL authentication.

UseWindowsLogin=Yes/No – Indicates if the program should obtain the users windows login and use it to login to ExpressMaintenance if it matches, thereby not prompting for the login and password.  This is not the same as the SQL Login account.  This pertains to the ExpressMaintenance account.

The same configuration settings apply to ExpressRequest.  ExpressMaintenance and ExpressRequest default to using ExpressMaintenance.ini unless another file name is passed as a shortcut parameter.  There is no need to place ini files on the workstations as they should reside in the folder with the application exe file.

If you are having trouble connecting from a workstation, make sure the ini file is setup as outlined above and the workstations have permission to read the file.  Also, increase the timeout setting to allow for slower network connections.

If the problem persists and you have checked the items above, the problem is likely a permission issue with the workstation logging into the SQL Server.  You should then perform an ODBC Windows level test connection at the bottom of this article.

You can instruct ExpressMaintenance to use the users Windows login to login to ExpressMaintenance, thus avoiding the ExpressMaintenance login screen.  For details on enabling or disabling the Windows login feature – click here.

Specifying Different Ini Files

ExpressMaintenance is capable of working with unlimited different databases.  You can setup separate databases for different locations or departments all within the same SQL Server.  You can then specify which database the program is connect to by using different ini files.  You can create multiple ini files with different names but all residing in the folder with the application.  By adding the ini file name as a shortcut parameter, ExpressMaintenance and ExpressRequest will use the specified ini file rather than the default ExpressMaintenance.ini file name.  Below is an example of a valid shortcut parameter with the customized ini file specified.  Passing just one parameter will always be interpreted by the program as the name of the ini file it is to use for connection info.

C:\ExpressTech\Maintenance.exe “MyIniFile.ini”

Creating Configuration Ini Files with the Utility (encrypted & non-encrypted)

The ExpressConfig.exe utility is available for setting up, testing and creating connection ini files.  The ExpressConfig.exe program also allows you to specify if the configuration information in the ini file is to be encrypted or non-encrypted.  Encrypted ini files are helpful in safeguarding your SQL login and password information.  You can use any valid SQL login and password that has been setup in your SQL Server and has full dbo permission to the database.  ExpressMaintenance and ExpressRequest will automatically detect that the settings are encrypted and decrypt the settings during execution of the program.  Click here to download ExpressConfig.exe installation program.

Using Parameters Instead of Ini File

You can use shortcut parameters rather than the ini file to direct the program connection.  This method is not recommended and will eventually be removed from the program logic.  If using parameters, all parameters must be used and must be separated by a space and within quotes.  Below is an example of valid shortcut parameters passing the specifics of the connection info.  Passing all parameters from the shortcut will force the program to ignore all ini files and use the parameters passed.

C:\ExpressTech\Maintenance.exe “ServerName” “ExpressMaintenance” “etuser” “etrik22” “No” “No”

SQLServer=ServerName
SQLDatabase=DatabaseName
SQLLogin=etuser
SQLPassword=etrik22
UseWindowsAuthentication=Yes/No
UseWindowsLogin=Yes/No

More Complex Server Definitions

The Server Name can be more complex such as the following”

SQLServer=tcp:ServerName\InstanceName,PortNumber

The above example specifies to use tcp for the connection, followed by a semicolon and then the SQL Server Name followed by the backslash and the SQL Server Instance Name followed by a comma and the port number on which the instance can be found.

To determine the port number for an instance, go to Start / Programs / Microsoft SQL Server / Configuration Tools / SQL Server Configuration Manager.  In the configuration manager, in left column, select SQL Server Network Configuration and open Protocols for MSSQLServer (Instance Name).    In right column, right click on TCP/IP and select Properties.  There you will find the port number being used by the instance of MS SQL Server Instance.  Also make sure TCP/IP protocol is enabled.

Testing With ConnectTest.exe

Included in the installation of ExpressMaintenance is a small program for testing SQL Server connections.  You will find the program file listed as ConnectTest.exe.  If you do not have the program in the folder with ExpressMaintenance, you can download the ConnectTest.exe program file from our website at the ExpressMaintenance Update Downloads page.

Testing With ODBC

For more information on testing the ‘etuser’ account and creating the account in SQL, please see the tech bulletin on Testing Connection via ODBC or the tech bulletin on creating the ‘etuser’.