|
Syteview's DataGrabber programs use an ODBC Data Source to replicate data from your Progress database server to your Microsoft SQL Server. These programs are usually run on the computer that hosts your SQL Server, though they can be run on a different computer to increase the resources available to your SQL Server. You will need to create an ODBC Data Source on Whichever computer you use to run the DataGrabber programs. Note: In order to create an ODBC connection to a Progress database you must be using Progress Version 9.1B or greater, and you had to have selected the SQL-92 Server Engine when you installed the Progress database server. See the Progress Installation screen below: 
Warning #1: Installing Progress with the SQL-92 Server Engine can be dangerous. It will allow anyone with an ODBC capable program (Microsoft Word, Excel, Access, etc.) and the proper ODBC driver to access your data. By default your Progress database has no security at all. Everyone has wide-open access to your entire Progress database. They can retrieve any data or even modify or delete your data. Warning #2: Progress Knowledge Base articles seem to imply that you need to have an Open Interface Broker (OIB) process running in order to make an ODBC connection to your Progress database from a remote computer. THIS IS NOT THE CASE. Any computer with the Merant ODBC driver (or possibly an other ODBC driver) installed on it and with the proper HOST and SERVICES file entries will be able to connect to your Progress database if the database was installed with the SQL-92 Server Engine. No security and an open door. Not a pretty sight. If you have installed Progress with the SQL-92 Server Engine you have to take steps to protect your data. The recommended Progress solution to this problem is to use the native Progress security mechanisms. From the Progress Database Administration program you enable security on your database, setup security administrators, disable blank userid access, setup database users and apply permissions to database tables and fields for each of these users. This is a difficult process and it also means you must keep your Syteline™ users’ names, passwords and permissions synchronized in two places, Syteline™ and the Progress database. Not an impossible task, but it is another chore for your overworked database administrator and the alternative, a wide open ODBC connection, is definitely not a good idea. Note: After setting up users, passwords and permissions on your database using Progress’ native security mechanisms you might be tempted to then grant everyone wide open access to your database with Syteline’s™ built in security system, relying on Progress’ security mechanisms to protect the security of your data. This would be a mistake. Progress’ native security is enforced only at compile time for compiled (.r) code and Syteline’s™ code has been compiled with permission to access the entire database. This means that anyone running Syteline’s™ compiled (.r) code can do absolutely anything in the database as far as Progress’ native security mechanisms are concerned. You must use Syteline’s™ security mechanisms to protect your data from within the Syteline™ program. See the Progress Knowledge Base Article: 15817 - Database Security Enforced at Compile Time - Not at Runtime at the Progress Knowledge Center for more information on this matter. This might tempt you to reverse the process and secure your database by totally locking down your Progress database using the native Progress security mechanisms, opening up only the tables and fields that you wish to allow to be accessed through the ODBC driver. Syteline’s™ compiled (.r) code will still be able to access anything it needs and you can use Syteline’s security mechanisms to protect your data from within the Syteline™ program just like you do now. Great idea. Easy to setup. Easy to manage. BUT IT WON’T WORK. Syteline™ does not use only compiled (.r) code. There are many uncompiled (.p) programs sprinkled throughout the Syteline™ codebase and Progress’ native security mechanisms do come into effect for uncompiled (.p) code. So when you lockdown your database with the Progress security mechanisms Syteline’s™ uncompiled (.p) code will not be able to run. The end result: In order to run most Syteline™ procedures you will need to use the Progress security mechanisms to grant each Syteline™ user permission to the Progress tables that are used by each procedure that each Syteline™ user runs and in order to prevent Syteline’s™ compiled (.r) code from being able to access your entire database you will need to use Syteline’s™ security mechanisms to restrict this access. What a mess. Twice the work. Twice as many chances for mistakes. And how exactly do you go about figuring out exactly which tables each Syteline™ procedure will be using? A better end result: Install the Progress database server on your computer twice in two separate sub-directories with two different sets of configuration files. For the first Progress database server installation install only the Progress 4GL Server Engine. Normally this will be the Progress database server you are currently using during the day to support your Syteline™ end-users. You don’t have to make any changes to your current configuration, just make sure that it wasn’t installed with the Progress SQL-92 Server Engine. For the second Progress database server installation install only the Progress SQL-92 Server engine. This installation will be used during the evening hours when the Syteview DataGrabbers will be connecting to your Progress database and transferring the data to your Microsoft SQL Server. Edit the .pf parameter file that you use to startup this database server and change the Host parameter (-H) to localhost. This will allow the Progress database server to startup as usual, but it will only accept connections from the computer that it is running on. All remote connections will be rejected. Since there is no way for a remote user to connect to your database server there is no need to use the native Progress security mechanisms to protect it. Only trusted processes running on your local computer can access the database. Finally, use the Windows Task Scheduler to setup the following tasks (or something similar that works with your schedule):
The only downside to this configuration is the downtime during the early morning hours when your Syteline™ users will not be able to connect to a database server. If your database server has to be online 24 hours a day then try to find a few hours during the weekend to run the Syteview DataGrabbers. Syteview provides you with DataLink programs that can keep your Progress database and your Microsoft SQL Server database in-sync during the rest of the week. Note: DataLink programs are designed to move data from your Progress database to your Microsoft SQL Server database without using an ODBC connection. If your Progress database server has to be online 24 hours a day, seven days a week then there may still be a way to use this configuration. If you are online 24/7 then you are probably doing some sort of online backup. You can restore a copy of your Progress database from one of your online backups to a second computer running a Progress database server as configured in the second installation above (the one with the SQL-92 Server Engine setup to run with the localhost parameter) and then run the Syteview DataGrabbers on this second computer. Not bad. Easy to configure. Online 24/7. No changes to the way you are currently managing your Syteline security. No changes to the way you are currently doing your backups. And no way for a remote user to connect to your database server through an ODBC connection. Now that we have a secure way to connect to your Progress database server through an ODBC connection lets move on to creating the ODBC Data Source that we will be connecting through. Startup the Progress database server for the server installation that uses the SQL-92 Server Engine. Open the Administrative Tools folder in the Control Panel on the computer that is running your Progress database server. If you are running Progress database servers on two different computer, open the Administrative Tools folder on the one that was installed with the SQL-92 Server Engine and is using the localhost parameter. In the Administrative Tools folder open the Data Sources (ODBC) applet. Switch to the System DSN tab and then click the Add Button. 
Highlight the Merant Progress SQL92 driver and then click the Finish button. 
On the next screen fill in the following fields:
After filling in these fields, click the Test Connect button to test the Progress-ODBC Data Source you just created. 
After clicking the Test Connect button you will see the following screen. Click the OK button to initiate the test. 
Hopefully you will see the following screen: 
If you are having problems connecting with the Progress-ODBC Data Source, first verify that the correct Progress database server is running. You should be running the second server installation. The one with the SQL-92 Server Engine. If the correct database server is running and you still cannot connect, temporarily shutdown the database server and then check to see if you can logon to the Progress database with the Progress Data Administration program using the sysprogress Username. If you can’t refer to the Progress documentation to fix this problem. If you can logon to the Progress Data Administration program then your problem is most likely in the configuration of the Progress-ODBC Data Source that you just created. Startup the Progress database server again and then delete the Progress-ODBC Data Source that you just created and try creating it again. Good luck. |