Giving Glide access to your MS SQL server for CCH integration

Where you are looking to integrate your Glide system with your CCH system you shall need to do the following on the MS SQL Server that hosts CCH:

  1. Configure your MS SQL Server to allow remote connections.
  2. Create a user account through which the Glide system can access your server.
  3. Create the glideInt database on the same server (script below).
  4. Give the user the correct permissions to both the CCH and glideInt databases. The user you create will have READ ONLY access to your databases.

The user credentials you shall create will provide 'read only' access to the CCH database. CCH do not provide APIs to allow Glide to safely write data to the database and so the connector is restricted to reading data only. The main implication here is that you need to ensure you create new clients and update client details in your CCH program and allow the connector to pass these to Glide, clients created/updated in Glide will not feed back to CCH.

Detailed guidance for each of the 4 requirements are provided below. They should be completed so that Glide can test the connection to ensure that we can access the appropriate tables with the correct permissions. At a later stage in the setup process we shall need a user with data writer permission on the CCH tables to add triggers to the CCH tables, the triggers required depend on your requirements and so this is typically completed as phase 2, standard instructions can not be provided.

Step 1 -> Ensure your MS SQL Server instance is configured to allow remote access (it might already be set up correctly):

  1. Open the SQL Server Management Studio (SSMS) and log on to your DB locally. Right click the appropriate server name in the left pane and select Properties
  2. Select the Connections page in the left pane and ensure that the Allow remote connections to this server checkbox is selected. Exit SSMS.
  3. Open the SQL Server Configuration Manager, expand the SQL Server Network Configuration area, select Protocols for [Server name] and ensure that TCP/IP is enabled.
  4. If you have made changes you will need a SQL Server restart for the changes to take effect.

Step 2 -> To create the user accounts on MS SQL Server follow these steps:

  1. Open SQL Server Management Studio.
  2. Connect to the CCH SQL Server database.
  3. Open the Security folder.
  4. Right-click on the Logins folder and select New Login.
  5. Select SQL Server authentication.
  6. Provide the login name in the text box. You should determine this and advise the Glide team of the chosen username.
  7. Create a strong password for the user (advise Glide support of the password). Please be sure to create a very strong password, being a mixture of letters, numbers and characters of mixed case and avoiding words that are easy to guess.
  8. Untick Enforce password expiration (this would periodically break the integration).
  9. Click OK to create the account.

Step 3 -> Create the glideInt database:

  1. Open SQL Server Management Studio.
  2. Execute the script (below) to create the new database.

1---CreateGlideIntDatabase.sql

Step 4 -> 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. Drill into the new user created for Glide.
  4. Select the User mapping page.
  5. Tick the CCH database.
  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.
  9. Note that as part of the CCH connector setup we shall provide a series of triggers which we need adding to the CCH database. These will need to be added by a user with db_datawriter permissions. The triggers are dependant upon your specific needs and so tend to be added in phase 2 once the user credentials and connection have been confirmed as working.

In addition to the above you need to configure your firewall to allow incoming traffic from our integration server access the your SQL Server. Depending upon your setup this will likely need to be configured on both a main external firewall as well as the firewall on the MS SQL Server machine, this is covered in  this article.

Once you have configured both the firewall exceptions and created the user account advise Glide support of your external IP address and SQL user credentials, Glide will be able to connect to your CCH database.

Still need help? Contact Us Contact Us