1.Y* – Resolve SQL database connection errors?

Article sections

    Answer:

    The SQL Database Connection Error indicates the workstation is unable to connect to the SQL Server and thus the program is offering to create the database.  You do not want to attempt to create the database if you know it already exists and you are able to run the program at the server computer.  The problem usually stems from one of the following issues.

    1. The workstations are unable to read the ExpressMaintenance.ini file and therefore when they run the program, it does not know what SQL Server and Database to connect to. This would be a permission issue of the ExpressMaintenance.ini file from workstations.
    2. The SQL Server was not setup to allow mixed mode authentication (Windows & SQL Authentication).
    3. There is not a valid SQL Login account by which the program can connect and thus it is failing.

    To address item 1 above, make sure the ExpressMaintenance.ini file is readable from the workstations so Express Maintenance (Maintenance.exe) can open and read the file when the user executes it.  The only contents of the ini file that are required are the following:

    [Connection Info]
    SQLServer=YourServerNameHere
    SQLDatabase=YourDatabaesNameHere (example: ExpressMaintenance)

    For more information on the ExpressMaintenance.ini file, please see the following link:
    How do workstations determine the server and database connection instructions (1.6)

    To address item 2 above, make sure your SQL Server is set to Mixed Mode Authentication.  This can be done by checking the properties of your SQL Server in the MS SQL Server Management Studio.  Please see the following link:
    Change authentication mode for SQL Server (3.1)

    To address item 3 above, you must create the ‘etuser’ SQL Login account in your SQL Server.  But first, you need to understand how Express Maintenance performs it connection to the SQL Server and Database.  This is how Express Maintenance works when you run Maintenance.exe.

    1. Opens the ExpressMaintenance.ini file to get connection instructions.  If it is unable to open the file, it generates the error.
    2. Gets the required SQL Server and Database information from the ExpressMaintenance.ini file.
    3. It also looks to see if there are lines specifying the SQL Login and Password it should use.  By default, these lines should not exist allowing the program to use it’s expected SQL login.
    4. If there is not Login and Password lines, Express Maintenance attempts to connect to the SQL Server using the SQL Authentication with Login account of ‘etuser’ and the password of ‘etrik22’.
    5. If the SQL Login of ‘etuser’ fails, Express Maintenance attempts to connect using Windows Authentication.
    6. If Windows Authentication then fails, the program generates the SQL Database Connection Error.

    In most cases, the Windows authentication to the SQL Server will be successful at the server or an Admin’s computer but not at workstations.  You can check the login and authentication used by running Express Maintenance and going to File / View Connection Info and looking in the 3rd paragraph of information.

    If the Connection Info indicates the program is connecting to the SQL Server using Windows Authentication rather than SQL Authentication this is a sure sign ‘etuser’ is either not setup or the SQL Server is not set to Mixed Mode Authentication.

    In order to properly setup the ‘etuser’ login account, you should do the following using Microsoft SQL Management Studio.

    1. Make sure your SQL Server is setup for Mixed Mode Authentication (Windows and SQL Server Authentication).
    2. Create an SQL Login Account in the SQL Server of ‘etuser’ with a password of ‘etrik22’.
    3. Give ‘etuser’ full database ownership (dbo) of the Express Maintenance database.
    4. Test running the program from your computer and go to File / View Connection Info.
    5. If the program shows SQL Authentication used and the SQL Login Account of ‘etuser’ then you should be good and workstations should work as well.

    This is not unique to version 9 of Express Maintenance.  This is exactly how version 8 worked as well.

    Below are some links that will help you addressing this issue and following the steps above:


    Change authentication mode for SQL Server (3.1)


    Create a special EM User with Studio Manager (1.7)


    How do workstations determine the server and database connection instructions (1.6)

    You can test connecting to the SQL Server and Database using Windows ODBC by following steps in this link.  If you are not able to connect to the SQL Server and Database using SQL Authentication and the ‘etuser’ login account from Windows ODBC, Express Maintenance is not going to be able to connect either.  If you are able to connect to the SQL Server and Database using SQL Authentication and the ‘etuser’ login account from Windows ODBC, then Express Maintenance should connect as well as long as it can read the ExpressMaintenance.ini file.
    How can I test a Server/Database connection from a workstation (3.2)