How to give the connector access to SQL Server

To set up the connection between your Glide system and your CCH system, you will need to do the following on the SQL Server that hosts CCH.

This tutorial assumes that you will host the connector on the SQL Server machine. If you decide to host the connector on a different machine then further configuration may be necessary.

Step 1 -> To create the user account on SQL Server follow these steps:

1. Open SQL Server Management Studio.
2. Connect to the CCH SQL Server database instance.
3. Open the Security folder.
4. Right-click on the Logins folder and select New Login.
5. Select SQL Server authentication.
6. Enter a login name and password. Make a note of these credentials, as you will need to enter them into the connector's configuration file later.
7. Untick Enforce password expiration (this would periodically break the integration).
8. Click OK to create the account.

Step 2 -> Create the glideInt database:

1. Open SQL Server Management Studio.
2. Execute the script found in the CCH Connector package that you will have been provided with. The filename is "Setup_CreateGlideIntDatabase.sql".

Step 3 -> Once you have created the user account and glideInt database you need to assign permissions following these steps:

1. Open SQL Server Management Studio.
2. Open the Security folder.
3. Double-click on the new login created for Glide to open its properties.
4. Select the User mapping page.
5. Tick the CCH database that will typically be called "Central".
6. Under database role membership tick db_datareader.
7. Click OK to save the changes.
8. Repeat steps 5, 6, and 7 for the glideInt database, but also tick db_datawriter under database role membership.

Step 4 -> Ensure that the CCH application user has permissions to read and update the glideInt database.

The CCH application will have a user on the SQL Server, and we need to ensure that this user has appropriate permissions on the newly created glideInt database. Depending on how your CCH system has been set up, this may happen automatically, but you must check and grant permissions if required.

Often, the user is called vpmuser, but this may vary on your instance.

1. Open SQL Server Management Studio.
2. Open the Security folder.
3. Drill into the appropriate user, e.g. vpmuser.
4. Select the User mapping page.
5. Tick the glideInt database.
6. Under database role membership, tick db_datareader and db_datawriter.
7. Click OK to save the changes.

Step 5 -> Create the database triggers:

1. Open SQL Server Management Studio.
2. Execute the script to create the database triggers. This script can be found in the CCH Connector package that you will have been provided with and is called "Setup_CreateTriggers.sql".

The triggers will fire when a new client is added to CCH or when an existing client is updated. They will add a new row to the glideInt database table, which is then interrogated by the connector every 15 minutes.

Once you have completed these steps, return to continue the connector setup process.