This guide shows you how to enable the ClientPay web service so that your users will be able to connect with the Elite Enterprise database when they use the ClientPay website.
What you will need:
ClientPay web service
SQL script to be run against the son_db database
A version of Microsoft Server, 2012 or greater, with IIS installed (version 7.0 or higher)
A .pfx file to create a secure endpoint in IIS
A static IP address or domain name associated with a static IP address
A port that we can use to contact the web service (ClientPay only accepts ports 443,444, 8080, and 32321)
The web service and script can be downloaded from a link you should have received when your organization signed up to use ClientPay. If you have not received this link, please contact [email protected]. IIS comes free with all Windows Server versions.
We can issue you a free .pfx file, or you can have one created by a certificate authority. Click here for instructions on how to install the certificate. Our .pfx files are self-signed. Although it is not traditionally best practice to use a self-signed certificate for Production purposes, this is a different use case from other web sites as our web servers should be the only entity that contacts your web service. We have the root certificate for our .pfx files so all communication between your web service and our web servers will be encrypted via https.
Decide how you will connect to the database
There are two different ways you can hook the web service up to the database. The first is to use Windows Authentication via Integrated Security. The second is to use SQL Authorization. Using integrated security means you will use the identity on the application pool as the credentials for logging into SQL Server. If you choose this you will need a corresponding login in SQL Server that has permission to access the ClientPay stored procedures in the son_db database. So if you have an Active Directory user called domainName\userName that you use for the Application Pool Identity, you must have a corresponding SQL Server login called domainName\userName with permission to run the ClientPay stored procedures in son_db. The domainName\userName SQL Server login must also be configured to use Windows Authentication as opposed to SQL Authentication.
If you choose not to use integration security, or if you can’t because you are installing the web service on a machine in a different domain from your Elite setup, you will still need to use a SQL Server login to access the ClientPay stored procedures, albeit with SQL Authentication. You will also need to enter the login name and the corresponding password into the connection string. Please see the section entitled Verifying the Web Service’s Connection Strings for more information on this subject as well as examples of what your connection string might look like.
PREREQUISITES
Please ensure that the following server roles are installed:
To install roles, open the Server Manager, select Add roles and Features, and click next (several times) until you get to a page entitled ‘Select Server Roles.’
Page 1 (of Select Server Roles)
Application Server (Version 2012 only)
Web Server (IIS)
Page 2
.NET 4.5 (or a later version)
ASP.NET 4.5
HTTP Activation (inside WCF Services, which itself is a node underneath the .NET option)
Installing a .pfx file
A .pfx file is needed to create a secure endpoint. Without one it is not possible to create an https endpoint.
In order for a .pfx file to be included on the list of those you can choose from for bindings, you must first install it in the personal store of the machine account on the machine you are installing the web service on. Please click here for instructions on how to do this if you are not sure.
Once this step is complete you should be able to use your .pfx file to bind your endpoint in IIS.
IIS CONFIGURATION
Create a folder to host the ClientPay web service. While you can place this anywhere you want, we recommend a location in C:\inetpub as this will run through IIS, and anyone looking for it in the future will probably assume is in C:\inetpub. You can call the folder anything you want but we recommend you call it something like “ClientPay Web Service” so that people in the future will know what it is. This folder will be referred to here as the ClientPay Web Service folder.
You will have downloaded a web service .zip folder from the link you got when your organization signed up for ClientPay. Once the folder inside inetpub is created, unzip it and place the bin folder, global.asax file and web.config file that are inside in the folder. These three files make up the web service.
Launch the “Internet Information Services (IIS) Manager” application from the start menu. Click “Sites” under the server name and then “Add Web Site…” to host the web service. Refer to Figure 1 below.
Figure 1
You can name the site anything you want, but we suggest something with ‘ClientPay’ so it will be easy to remember what it is doing. Assign the port and leave the “Host name” empty and IP address at “All Unassigned” unless you have a reason to assign a specific address. The port must be either 443,444, 8080, or 32321. You will want to configure your firewall and/or NAT rules to allow communications via the port you choose. If you have not yet installed a .pfx file (click here for instructions) just select ‘http’ for the binding type. You will be able to change this later by highlighting the site, clicking ‘Bindings’ on the right hand side (see Figure 2), creating a new binding and then deleting the original one afterward. Assign the port and leave the “Host name” empty and IP address at “All Unassigned” unless you have a reason to assign a specific address. The port must be either 443,444, 8080, or 32321.. Otherwise, select ‘https’ and the .pfx file you intend to use.
Figure 2
If you are using a Persolvent-generated .pfx, you will see a warning regarding an intermediate certificate. You can click OK and ignore this.
Figure 3
Figure 4
Click the ellipses button (“…”) and navigate to the ClientPay Web service folder you created earlier.
Figure 5
If you skipped creating a folder earlier, you can click the “Make New Folder” button. Refer to figure 6.
Figure 6
Now open up the Application Pools node, found right above the Sites node. You should see an application pool with the same name as the site you just created. If you double click on it you should see something that looks like Figure 7, below. Make sure the settings here look the same (they probably already do) and click Ok.
Figure 7
If you intend to use Integrated Security to give the web service database access, you will have to update the Identity on the application pool (see the section entitled Decide how you will connect to the database near the top of this document). Select the application pool and then click the “Advanced Settings” link. Refer to Figure 3 below. If you plan on using SQL Authorization, please skip these instructions and scroll down a page or so to Patching the Elite Enterprise Database.
Figure 8
Click on the ellipses button ("...") next in the Identity row and enter a Custom Account in the box that pops up (Refer to Figure 10).
Figure 9
Figure 10
Patching the Elite Enterprise Database
Patching your Elite Enterprise database does not change any of your existing data. It simply creates two tables and a number of stored procedures that allow the ClientPay® web application to communicate with your Elite Enterprise application. The script assumes the physical name of the database is “son_db”. If this is not correct, you will have to modify the script before running. The name of the database only appears on the very first line of the script so that is all you would need to modify.
Start the SQL Server Management Studio and login into the server.
Note: Persolvent recommends that you take a back-up of your Elite Enterprise database before continuing.
Open the .sql script included in your installation package and then click the “Execute” button. This will create new tables in your Elite Enterprise database. If the script runs successfully, it will confirm that one row was modified – this is a version number that we insert into a table we create to hold it.
Verifying the Web Service’s Connection Strings
Navigate to the ClientPay Web Service folder located in inetpub, the one you put the web service in earlier in the process. Within the web service folder, open the web.config file in notepad.
Within the web.config file, verify that the connectionStrings tag accurately reflect the topology and desired user access of your environment. See Figure 11 below for the exact location of the connectionStrings tag. If this is not correct, update them to reflect your environment and save.
Figure 11
The connection string will look something like this:
We do not recommend installing the web service on the same machine as SQL Server as SQL Server tends to take up all available RAM at times. However, if you only have one server then you have no choice, and there are a number of organizations that do this so it is not untenable. If this is the case, the “server” value in the connection string should be ‘(local)’. If this is not the case you will need to remove the parentheses and enter the name or IP address of the server that your SQL Server instance is on. If this server is in the same domain as the web server, you can use the server’s name or an internal IP address. If it is not in the domain you will need to use the external IP address. You can use the ipconfig command in a command prompt to get the internal address and websites like https://whatismyipaddress.com or www.ipchicken.com can tell you your external address.
Note: if you have a named instance of SQL Server, the “server” value will look like this:
server=machineNameOrIPAddress\instanceName;
Named instance are rare so if you are not sure if you have one, you probably don’t. But you can find out by opening SQL Server Configuration Manager (this should be installed on the machine where you have your SQL Server instance), selecting SQL Server, and viewing the information that comes up as a result. Next to where it says ‘SQL Server’ there will be a name in parentheses; if that name is MSSQLSERVER you do not have a named instance, and if it is something else, that is the name.
There are two ways for the web service to connect to SQL Server: integrated security, where the Active Directory account listed as the application pool’s identity makes the connection, or through the connection string where SQL Server credentials are simply entered into the connection string. The second option requires you to enter a username and a password into the connection string.
server=myServerAddress;database=son_db;user id=myUsername;
password=myPassword;
If you use integrated security, you must have a corresponding login in SQL Server that is able to access the son_db database. The name should match exactly, which often means the format domainName\userName.
appSetting Descriptions
TCPHostName - This is the IP or domain address for the server in which the Elite application is installed. This may be a different server from the ClientPay Web Service is installed. This can be the external or internal IP address (if it is in the same domain) of the appropriate server.
TCPPort - Leave as is unless you know it is different. 8007 is the default Elite Enterprise Web Service Port.
TCPTimeout - Verify that this amount is set to 30000.
TCPUserID - This should be a UserID within your system that is set-up within the Elite Enterprise database. It needs to have permission to post both operating and trust receipts.
TrustCommand – Verify that the physical location of the trust_load.exe file is as described within this default appSetting. If your system is different, update this designation to the physical location of the file. Usually the only difference might be what drive it is installed on.
CreditCommand – Verify that the physical location of the credit_load.exe file is as described within this default appSetting. If your system is different, update this designation to the physical location of the file.
TempDirectory – On the Elite application server, create a folder. You can call it anything you want but we recommend something like ‘ClientPay_Uploads’ as it will hold text files which are written as part of the receipt posting process. Update this appSettings with the physical location and name of this folder.
EliteInit – Leave as is.
CurrencyCode – If you use a currency code other than USD for US Dollars, update this accordingly otherwise leave as is.
LoggingEnabled – In conjunction with LogDirectory (see below), this will write Elite log entries related to receipt posting into a single text file. If you have problems posting a receipt, the information here can be very helpful. Log entries are appended to the file, so over time it will increase in size. We recommend leaving it off generally, but turning it on if you are having trouble posting a receipt and don’t know why. If this file gets too large for your taste you can simply delete it. A new file will be created for any entries that need to be written in the future.
LogDirectory – Log entries will be written into this folder. You can call it anything you want, but we suggest “Log” or “ClientPay Log.” Make sure this entry points to the location and name of the folder. Important: You will have to create this folder.
ReceiptEntryDirectory – (optional) Create another folder somewhere that the web service can access. You can call it anything you want but we recommend something like ‘ClientPay_Reciepts’ as it will hold text files that keep a log of receipts that have been written into the TempDirectory folder as the files written may be deleted by Elite after posting. If you do not care to have these records, you can leave this entry blank. Important: You will have to create this folder if you want to use this functionality.
Test the web service
If you have completed all the above steps, it is time to run some tests to see if the web service is running properly. To see if the web service is working, open a browser on the machine where it is installed. Type the following url into the browser where ‘portNo’ is the port number specified on the IIS binding. If you are using port 443 you can skip the colon and the port number entirely as 443 is assumed when no port is specified.
You will probably need to click through a certificate error. This is expected, so click through it. As this is the first time the service is being contacted, the call may take a little while (10-20 seconds, possibly). If everything is working, you should see a short string of json data in the browser. Alternatively, you might be prompted to download this data. If you are, do download it and then you should be able to see the json data in notepad. Either of these outcomes means the web service is installed properly. If you do not get the data, it means something is still not right. If this is the case, you will hopefully see an error message that gives you a clue as to what still needs to be done.
Test the database connection
If you got the json data from the first test, it is time to test the database connection. Use this url:
This call retrieves the accounts in your system. If there is a problem connecting to the database, it should also display the error instead. If you do get an error, SQL errors are often explicit enough to help you find whatever the problem is. The more generic sounding errors usually indicate that the web service is not finding your SQL Server instance. If that is the case, there is probably something off in the connection string – perhaps the server has the wrong name or IP address, or maybe you do have a named instance after all.
A successful call to this endpoint will look something like this:
{"Accounts":[{"AccountNumber":"BALA","IsTrust":false,"AccountDescription":"Bank of Arnold","AccountIndex":0,"AccountPK":null},{"AccountNumber":"WFP","IsTrust":true,"AccountDescription":"Wells Fargo Pooled Account","AccountIndex":0,"AccountPK":null},{"AccountNumber":"WFI","IsTrust":true,"AccountDescription":"Wells Fargo Investment Certificates","AccountIndex":0,"AccountPK":null},{"AccountNumber":"CASE1","IsTrust":true,"AccountDescription":"LB&B Escrow Account","AccountIndex":0,"AccountPK":null},{"AccountNumber":"BALA","IsTrust":true,"AccountDescription":"BANK OF AMERICA TRUST OF WASHINGTON DC","AccountIndex":0,"AccountPK":null},{"AccountNumber":"CPOP","IsTrust":false,"AccountDescription":"ClientPay Default Operating","AccountIndex":0,"AccountPK":null},{"AccountNumber":"CPTR","IsTrust":true,"AccountDescription":"ClientPay Trust Account","AccountIndex":0,"AccountPK":null}],"ErrorMessage":null}
Your data will look different but as long as you see accounts in there, it is working and you are ready for ClientPay support staff to test the connection from our Production servers.