Customise connection string
General information about the connection string
The ACMP server uses the connection string to connect to the SQL Server. It is created and filled by the ACMP setup during installation. After installation, the connection string can also be adjusted manually.
The connection string contains various attributes, such as the provider or the data sources used, but also security information of the respective user, such as the user name or the password.
The connection string is generated by ACMP and stored in the installation directory. It is a persistent file that can be accessed by the ACMP Server at any time. The file always has the same attributes. Some values and properties can be changed by a free entry, for others only the two values True and False are available. The connection string is implicitly filled with the data you enter during installation (e.g. database used, user name, password, etc.). To do this, the information you enter in the installation wizard is transparently transferred to the file and stored there. The connection string can also be used to establish an encrypted connection between the SQL Server and the ACMP Server.
Implicit entry of the connection string data via the installation wizard
It may be necessary to adapt the existing session data in the following cases, for example
- - Migration of another SQL Server (e.g. from MS SQL Server 2012 to 2019).
- - Relocation of the SQL Server to another host
- - Change of user credentials (the ACMP database login is changed)
- - Migration of a database
- Data transfer to be encrypted afterwards
Invoking the db.~co and db.con files
The files to customise the connection string are located in the server directory of the ACMP Server (e.g. C:\Program Files (x86)\Aagon\ACMP Server). The files you need to customise the connection string are the db.~co and db.con files.
The aim is to change the db.con file because this is used by the ACMP Server to connect to the SQL Server. This file is normally encrypted if the ACMP Server has been successfully started at least once. To be able to modify and customise the data in a meaningful way, you need the structure and the readable content of the connection string. This data can be found in the db.~co file, as the plain text can be extracted from it.
Navigate to the server directory of the ACMP Server and open the file db.~co with a text editor of your choice (e.g. Notepad++). A connection string with various attributes will open. In the following figure, for example, these are attributes such as the provider, the user ID or the SQL Server instance.
Content of the db.~co file
Note:
ACMP does not use the db.~co file directly to read information. Rather, it is intended as a template in which you can enter your values and transfer them to the db.con file.
When you then open the other file, the db.con file, you will see an encrypted content consisting of a long string of numbers and letters. Because of the encryption, you will no longer be able to see the position of the password attribute, for example.
Encrypted db.con file
Note:
If the correct values are entered for the five attributes "Provider", "Password", "User ID", "Data Source" and "Initial Catalog", the encryption of the db.con file is automatic. After the ACMP Server has successfully connected to the SQL Server, the db.con file is encrypted so that the connection string is no longer in clear text in the file, but can only be decrypted by the ACMP Server.
Customising attributes in the connection string
You can customise several categories of information within the connection string. These include user-specific information (password and user ID) and database-related information (data source and initial catalogue). To create an encrypted connection between the servers, change the attributes of the Use encryption for data and Trust server certificate entries.
First, go back to the db.~co file where the readable attributes are stored. Copy the contents of this file and switch back to the db.con file. Now find the attributes you want to change or add to the existing attributes.
Warning:
It is important that you enter the values correctly. Errors or typing mistakes can result in the ACMP Server not being able to connect to the SQL Server and therefore no login is possible.
Nachfolgend einige Erklärungen zu den Werten:
| Attributes | Description |
| Provider=SQLNCLI11.1 | The provider used here is the Microsoft SQL Server Native Client, version 11.1. The access library used to access the SQL Server should not be changed. |
| Password=(clear) | The password refers to the user ID/user for the SQL Server used within the connection string. If an incorrect password is entered, an error message is generated when the ACMP Server is started. In addition you will find an entry in StartUpError.txt (C:\Program Files (x86)\Aagon\ACMP Server) why ACMP could not be started successfully. Enter the required password here. |
| Persist Security Info=True | A value of True means that security information (e.g. user ID or password) can be retrieved. If the value is set to False, problems and error messages may occur when connecting to the ACMP Server. Possible values that can be entered True or False |
| User ID=ACMPDBUser | The User ID information refers to the SQL login with which the ACMP logs on to the SQL Server. A successful login is a prerequisite for starting the ACMP Server. |
| Initial Catalog=ACMP | The entered User ID (SQL Login) should have DB Owner rights of the database. This is the database name. |
Data Source=ServerNameEnter.aagon.local\SQLEXPRESS
| Represents the SQL Server instance. The extension .aagon.local corresponds to your local domain. \SQLEXPRESS is the instance name of the SQL Server to which you want to connect (here: Microsoft SQL Server Express). |
Use encryption for data=false
| The data sent via a transfer can also be encrypted. To do this, the value must be set to True, otherwise no encryption will take place. In this case the data/values of a table are encrypted so that they cannot be intercepted or read. The value False can be used if only network internal transfer paths are used that do not cross the Internet. Possible values that can be entered True or False |
MARS Connection=False
| This value is disabled by default. To enable MARS Connection, set the value to True. This function allows you to run several batches through a single connection. Possible values that can be entered True or False |
DataTypeCompatibility=80
| Default value of the system requirements for the OLE DB driver for SQL Server. |
Trust Server Certificate=False
| When set to True, all certificates are considered valid. If set to False, a certificate check is forced as each certificate is checked individually during the connection process. Possible values that can be entered: True or False |
For the changes to be applied, you must save and close the file. If you are still running ACMP Console, exit the application.
Note:
If you correct or change values in the db.con file, it is a good idea to close the ACMP Console, otherwise you may lose data. Unsaved content may be lost. If you do not close the application during your work, you will get a message that the current session has expired and needs to be restarted.
Now open the ACMP Server via the service administration "Services" and close the server with the same name. Wait a moment until the service is closed on the local computer and then restart it. The adjusted conditions will be loaded and a newly encrypted db.con file will be displayed.
Note:
You can use the StartUpError.txt file to check that the server has been successfully restarted.
Exit Service Management ACMP Server
The updated db.con file will have a different sequence of numbers and letters after the ACMP server has been restarted, due to the re-entry of your data.
Encrypt connection string
The connection string is automatically encrypted if you have entered the values of the attributes "Provider", "Password", "User ID", "Data Source" and "Initial Catalog" correctly. You must enter these five attributes correctly to establish a connection between the ACMP Server and SQL Server. If you enter an incorrect value in the db.con file, the file can be saved but no connection can be established. Therefore, always check your entries to ensure a successful connection between the two servers.
Encryption of the connection string is used to prevent other users from reading the authentication credentials.
Establishing an encrypted connection between ACMP Server and SQL Server
The user credentials passed can also be encrypted in the connection string. To do this, when editing the attributes, set the Use encryption for data value to True to encrypt the transmission. This will encrypt the data so that it can only be read by the ACMP Server. In addition to the attribute mentioned here, you will need to customise the other values. Update and save the db.con file so that it can be read by the ACMP Server when it is restarted.
See also: Enabling encryption