How to install and configure the Client Server ODBC driver for Sage MAS 200

The Client Server (CS) ODBC Driver allows remote workstations to print Crystal forms and reports using server-side ODBC processing. This option is available only in Sage MAS 200 4.10.1 and later. In earlier versions, only the client based ODBC driver is available.

Remote workstations using Terminal Service or Citrix and workstations connected through the local area network (LAN) should not use the CS ODBC Driver. These workstations typically run faster using the normal client ODBC driver.

Remote workstations running over a WAN link connected through a VPN are ideal candidates for using the CS ODBC Driver. Forms and reports will print faster from remote workstations that run over high speed, high bandwidth connections.

Remote workstations that currently use the Sage implementation of Crystal Enterprise (Web Reports) on version 4.10 can switch to the CS ODBC Driver.

The CS ODBC Driver has been tested and designed for use with Crystal reports and forms printed through the Sage MAS 200 Business Desktop (Launcher). It has not been tested for use with other ODBC applications (for example, SQL Server queries and Access queries). Sage Software does not support the use of other ODBC applications that may attempt to use the CS ODBC driver.

Possible Resolution:

Notes:

• Use extreme caution when editing the Registry. If the Registry is edited incorrectly, Windows may not function properly or may not run at all. Sage Customer Support is not responsible for assisting with editing the Registry. Always back up the registry before accessing it. If necessary, seek the assistance of a qualified computer technician.
• Sage Support is not responsible for assisting with the use of non-Sage products. For assistance, please see that product's online Help for instructions or contact their technical support department.
• Sage Customer Support is not responsible for assisting with network issues and takes no responsibility for changes made to your network or for errors in these instructions. Consult your network administrator before making changes.

I. Server Side Setup:

1. Install the CS ODBC Driver service.
   1. Browse to ..\Mas90\home\lib\_appserve\, and double-click CSODBCService.exe. This creates the "Providex IO Service" entry in the Windows Services applet.
   2. On the Windows Start menu, click Run. In the Open field, type SERVICES.MSC
   3. Click OK. This starts the Services applet.
   4. Double-click the Provide IO Service to open the Properties window.
   5. Click the General tab. Set the service startup type to Automatic, and click the Apply button. The service can run on the system account; there is no reason to change the log in.
   6. Click the Start button, and verify that the service starts successfully.
   7. If the service does not start verify these registry entries are present. They should look approximately the same except the values in "WidthColum[x]" will appear different.
      • HKLM\SOFTWARE\Best Software Canada Ltd.\Providex ODBC Server
      • HKLM\SOFTWARE\Sage Software\ProvideX File Server
   8. If the Best Software key is missing, remove the service using the CSODBCService.exe from step 1.a. Then re-create the service.
   9. If the Sage Software key does not have enough values, rename the key and attempt to start the service again, twice. The first attempt should re-create this key. The second attempt should start the service.  It may create and start in only one attempt.
   10. Click OK to close the ProvideX IO Service Properties window.

II. Sage MAS 200 Setup

Enable the CS ODBC Driver:

1. From any workstation, start Sage MAS 200.
2. Expand Library Master and Setup. Double-click System Configuration.
3. Click the ODBC tab.
4. Select the Enable C/S ODBC Driver check box.
5. In the ODBC Server Name field, enter the server name or the IP address.
6. Enter 20222 for the ODBC server port.
7. Clear the Enable for All Users check box if it is selected.
8. Click the Accept button.

Configure the Sage MAS 200 user logon:

1. At the server, start a Sage MAS 200 workstation.
2. Expand Library Master and Main. Double-click User Maintenance.
3. Select the first remote user and click the Preferences tab.
4. Select the Enable C/S ODBC Driver check box.
5. Clear the Use Crystal Web Reports check box if it selected.
6. Click the Accept button.
7. Repeat above steps for every remote user.

Test the CS driver on the server:

1. Exit Sage MAS 200 workstation, and then start it again. Log on as a CS ODBC enabled user.
2. Expand Library Master and Reports. Double-click Company Listing, and preview the report.
3. If an ISAM error occurs, review all previous settings, paying close attention to step 1.g.
4. If the report previews successfully, close the report but do not exit Sage MAS 200.
5. Run the registry editor.
6. Rename the following key: HKLM\SOFTWARE\Sage Software\'ProvideX File Server.  (Rename only the ProvideX File Server key.)
7. Preview the company listing report again.
   • If you receive an ISAM error, that user is enabled, and the CS ODBC driver is installed and configured correctly. Rename the ProvideX File Server key back to the original name, and test again.
   • If you do not receive and ISAM error, and the report appears after renaming the registry key, the standard driver was being used. Double check the user log in preferences and the system configuration ODBC tab. Exit, and then restart the workstation. Test again.

Test printing from remote workstations:

1. From a remote workstation, start Sage MAS 200.
2. Expand Library Master and Reports. Double-click Company Listing, and preview the report.
3. If the report previews successfully, verify that other currently used reports and forms print and preview successfully.
4. Repeat the steps above for every remote workstation.

III. Server Side Troubleshooting

Verify that the ProvideX IO service is set to listen on port 20222:

1. On the Windows Start menu, click Run.
2. In the Open field, type cmd
3. Click OK.
4. Enter the following command, and then press ENTER:
   
   netstat -a -n |more
   
5. A numerical listing by port number appears: Verify that port 20222 is listed in the Local Address column. If it does not appear, press the spacebar to list more results.
6. If 20222 is not listed in the Local Address column, the client server ODBC driver is not running. Troubleshoot the ProvideX IO Server service.
7. After verifying that port 20222 is listening properly, type exit
8. Press ENTER to exit the command prompt.

Configure Firewall and Router Policies:

Configure the corporate firewall and router policies to allow port 20222 to be accessible from the remote workstations. If personal firewall software is also used on the server, configure it to allow port 20222 to be accessible from the remote workstations.

Configure the share for Sage MAS 200:

On the server, a share exists above the ..\Mas90\ folder that allows Sage MAS 200 LAN users and those connected through Terminal Services and Citrix to print Crystal reports and forms. For the remote user, note the different scenarios of share permissions:

• Remote user is granted Read/Write access through the share:
  • When printing a form or report from a Business Framework module, the user can either print to any of his or her Windows printers, or select Deferred. If Deferred is selected, the user can then print from Deferred to the printer.
  • When printing a form or report from a legacy module, all the remote user's printers appear. The Deferred printing option is not available.

• Remote user is granted Read Only access through the share:
  • When printing a form or report from a Business Framework module, the user can either print to any of his or her Windows printers, or select Deferred. However, if Deferred is selected, the user will not be able to print from Deferred to his or her printer. Another user with Read/Write access through the share will need to print the Deferred report.
  • When printing a form or report from a Legacy module, all the remote user's printers will appear. The Deferred printing option will not be available.

• Remoter user is granted No Access through the share:
  • When printing a form or report from a Business Framework module, the user can select Deferred only. However, the user cannot print from Deferred to his or her printer. Another user with Read/Write access through the share will need to print the Deferred report. If Crystal Enterprise / Web Reports is used instead of the CS ODBC Driver, the user will have the option both of Deferred and Crystal Enterprise.
  • When printing a form or report from a Legacy module, all the remote user's printers will appear. The Deferred printing option is not available.

III. Client Side Troubleshooting
Test the Client Connection to the ODBC server:

1. From a remote workstation, start Sage MAS 200, and log on so that the Business Desktop (Launcher) is visible.
2. On the Windows Start menu, click Run.
3. In the Open field, type cmd
4. Click OK.
5. Perform a simple ping test. Enter the following command, and then press ENTER:
   
   ping server or ip_address
   
   Replace <server or ip_address> with the server name or the server's IP address. If the reply is successful, proceed to the next step. If the reply is unsuccessful, this may indicate the router is blocking ICMP requests. Do not perform the telnet test below. Use a port scanning utility instead.
   
6. Perform a simple telnet test to see if port 20222 is available to the remote workstation. Enter the following command, and press ENTER:
   
   telnet server or ip_address 20222
   
   Replace server or ip_address with the server's name or IP address. For example if the server's name is Server1, type telnet SERVER1 20222.
   
   If a blinking cursor appears, this indicates that the connection is successful, otherwise, the connection failed. Verify that personal firewall software on the workstation and the server's firewall or router is configured correctly.
   
   Note: If the ping test above failed, the telnet test will also fail. If you are able to start Sage MAS 200 successfully but cannot use the ping and telnet commands, then the ICMP requests are likely being blocked at the router or firewall. Use a port scanning utility instead to verify port 20222 is available to the workstation.
   
7. Test the client UNC path.
   1. Start Sage MAS 200 from a remote workstation.
   2. On the Sage MAS 200 File menu, click Run.
   3. Type *INFO
   4. Click OK.
   5. Note the Installed Directory Path. It should be a valid UNC path to the server's ..\Mas90\ folder.
   6. Right-click the installed directory path, and click Copy (or press CTRL+C).
   7. On the Window Start menu, click Run.
   8. Right-click in the Open field, and click Paste (or press CTRL+V).
   9. Click OK. Windows Explorer should start fairly quickly.
   10. If a Windows challenge response dialog appears prompting for a username and password to access a network resource, this indicates the user logged into the remote workstation was not authenticated through the server's share. Correct this problem before proceeding to the next step. The requirement is that from the workstation, entering the UNC path to the ..\Mas90\ folder on the server must result in an unchallenged Windows Explorer window. 
       
       Note: If the Windows Explorer window opens unchallenged, but appears slowly, the remote workstation may be experiencing a slow name resolution issue. If that is suspected, one possible solution is to exit Sage MAS 200, and edit the workstation's local SOTA.INI file. In the [Servers] section, change the PATH= clause so that the server name is replaced by the IP address. For example, change
       
       PATH=\\SERVER1\APPS\200V410\MAS90 
       
       to the following:
       
       PATH=\\IP_Address\APPS\200V410\MAS90 
       (replace IP_Address with server's IP address)
       
   11. After the ..\Mas90\ folder can be viewed through the UNC path, in the same Windows Explorer window, click File, point to New, and click Shortcut.
   12. If the Create Shortcut window opens, click Cancel.
   13. If an "Access Denied" message appears, this indicates that permissions on the share are not set up correctly. Review the "Configure the Share for Sage MAS 200" section again to verify that the share is correctly configured.
       
       Note: Do not select the Enable C/S ODBC Driver check box for workstations connected through a LAN, Terminal Services, or Citrix.

Change the SOTAMAS90 DSN Configuration:

1. At the remote workstation, close the Sage MAS 200 Business Desktop.
2. On the Windows Start menu, click Run.
3. In the Open field, enter ODBCAD32
4. Click OK.
5. Click the User DSN tab, and select the SOTAMAS90 DSN. Click the Configure button.
6. Click the Basic tab. Verify that the database directory contains the path to the ..\Mas90\ folder relative to the server. The workstation's UNC path to the server should no longer appear.
7. Click the Server tab.
   • Verify that Server name or IP field contains the correct server name or IP address.
   • Verify that TCP/IP Port field is set to 20222.
8. Click the Debug tab. Click the Apply button to save any changes.
9. Click the Test Connection button. A database logon prompt should appear. Enter the company code, user logon, and password. This tests the ODBC server and returns the number of tables in the dictionary. The following message should appear: "Connection succeeded. Data source includes xxx tables."
10. Repeat the steps above for every remote workstation.

IV. Additional Notes

• Reports and Forms run slower the first time.
  The first time a Crystal form or report is printed from the remote workstation using the CS ODBC driver, it takes longer to print because the report is cached locally under the following folder:
  
  C:\Documents and Settings\All Users\Application Data\Sage Software\Cache\MAS 200
  
  If the .rpt file for the report does not already exist on the client side in the above folder, or if a newer version of the .rpt file exists on the server side, then the .rpt file is copied from the server. The second time the report is run, the .rpt file is not copied, and it prints faster.
  
  Note: Remote users must have sufficient Windows permissions on their local computer for Sage MAS 200 to copy the .rpt files in the location noted above on the user's local drive.
  

• The CS ODBC driver is limited 15 concurrent users.
  The CS ODBC driver license allows a maximum of 15 concurrent users running a Crystal reports or form simultaneously. This limitation does not affect LAN, Terminal Services, or Citrix users using the regular client based ODBC driver.