SUMMARY
SETTING UP THE SQL SERVER USER ACCOUNT ON THE SERVER
Note that this only needs to be done if you have your ILLiad Server on location. If you are a hosted server, skip down to the section on linking from the client machine.
On the ILLiad Server, open up the SQL Server Enterprise Manager. Expand the local SQL Server, then the Security section. Right-click on Logins and choose New Login. Create a login called illiadlink. Click SQL Server Authentication. Leave the password blank (or assign one - just make sure it matches all the way through). Under Database, choose ILLData. Click the Database Access tab at the top. Click the Permit check box for ILLData, and then click OK. (If you put in a password, it will ask you to confirm at this point).
Under the ILLData database, choose Users. Right-click on the illiadlink user and choose All Tasks .. Manage Permissions. Check the SELECT check box for each table in the list and click the OK button. Be sure that you only check the SELECT check box and that you click every table.
SETTING UP THE ODBC LINK ON THE CLIENT MACHINE
Open up the Control Panel, then the ODBC panel. Note: In Windows 2000, the ODBC panel is located under the Administrative Tools folder in the Control Panel window. Click on the tab at the top called System DSN. Click the Add button. You will get a list of available drivers to use. Select SQL Server and click Finish. Use this table to fill out the fields as necessary. Click Next to cycle through the available settings. Be sure to choose the illiadlink user and not any other user, as the illiadlink user is restricted to readonly access. Connecting as the sa user could allow you to accidentally delete records from your database. Click Test Data Source to make sure this works - it should say Tests Completed Successfully.
SETTING UP MICROSOFT ACCESS
Open Microsoft Access. Choose to create a new, blank database. Choose a database name, and hit Create. Next, you need to import the data. Choose File -> Get External Data -> Link Tables. Under Files of Type, choose ODBC Databases() The Select Data Source screen will be displayed. Click the Machine Data Source tab at the top. Click ILLiadLink and then OK. For LoginID, use illiadlink and leave the password (or one chosen above). A Link Tables dialog will be displayed. To Link all of the tables, click the Select All button, then OK. If any tables ask for you to Select Unique Record Identifier, click OK. When this is finished, the link is complete.
Note: These instructions were written using the current version of Microsoft's MDAC and Access 2000 as a guide. If you have different versions, your screens may be slightly different.
SUMMARY
Date ranges for ILLiad are consistent throughout the system, but may not be the same as other programs. The start date is inclusive the the date range but the end date is not inclusive. For example, a report for the month of May would be for 5/1/2002 - 6/1/2002. The dates assume that the time is 12:00am (midnight). For the ending date, the report would grab all records with a date less than 6/1/2002 12:00:00, or everything up until the end of 5/31/2002. Making the date ranges 5/1/2002 - 5/31/2002 would not grab any items from the 31st of that month.
This is particularly important for ILLiad reports with date ranges and generating invoices within the ILLiad Client.
SUMMARY
SETTING UP THE SQL SERVER USER ACCOUNT ON THE SERVER
Note that this only needs to be done if you have your ILLiad Server on location. If you are a hosted server, skip down to the section on linking from the client machine.
On the ILLiad Server, open up the SQL Server Enterprise Manager. Expand the local SQL Server, then the Security section. Right-click on Logins and choose New Login. Create a login called illiadlink. Click SQL Server Authentication. Leave the password blank (or assign one - just make sure it matches all the way through). Under Database, choose ILLData. Click the Database Access tab at the top. Click the Permit check box for ILLData, and then click OK. (If you put in a password, it will ask you to confirm at this point).
Under the ILLData database, choose Users. Right-click on the illiadlink user and choose All Tasks .. Manage Permissions. Check the SELECT check box for each table in the list and click the OK button. Be sure that you only check the SELECT check box and that you click every table.
SETTING UP THE ODBC LINK ON THE CLIENT MACHINE
Open up the Control Panel, then the ODBC panel. Note: In Windows 2000, the ODBC panel is located under the Administrative Tools folder in the Control Panel window. Click on the tab at the top called System DSN. Click the Add button. You will get a list of available drivers to use. Select SQL Server and click Finish. Use this table to fill out the fields as necessary. Click Next to cycle through the available settings. Be sure to choose the illiadlink user and not any other user, as the illiadlink user is restricted to readonly access. Connecting as the sa user could allow you to accidentally delete records from your database. Click Test Data Source to make sure this works - it should say Tests Completed Successfully.
SETTING UP MICROSOFT ACCESS
Open Microsoft Access. Choose to create a new, blank database. Choose a database name, and hit Create. Next, you need to import the data. Choose File -> Get External Data -> Link Tables. Under Files of Type, choose ODBC Databases() The Select Data Source screen will be displayed. Click the Machine Data Source tab at the top. Click ILLiadLink and then OK. For LoginID, use illiadlink and leave the password (or one chosen above). A Link Tables dialog will be displayed. To Link all of the tables, click the Select All button, then OK. If any tables ask for you to Select Unique Record Identifier, click OK. When this is finished, the link is complete.
Note: These instructions were written using the current version of Microsoft's MDAC and Access 2000 as a guide. If you have different versions, your screens may be slightly different.
SYMPTOMS
When attempting to use the ILLiad Web Pages, anytime a user presses any button, they'll receive either a 500 error or a specific error of "BDE cannot be initialized".
ERROR MESSAGES
In order to see the specific error reported in Internet Explorer, you need to make sure that the "Show Friendly HTTP Error Messages" option in IE is turned off. This is under the Tools|Internet Options|Advanced tab.
The error you will receive is:
Exception: EDBEngineError
Message: An error occurred while attempting to initialize the Borland Database Engine (error $2B05)
CAUSE
Permissions have changed on the ILLiad server. In many cases, these permissions could have changed in the past at some point, and just recently went into effect when the server was rebooted. The ILLiad Web DLL remains resident in memory once loaded, so in many cases, a problem won't be seen until a reboot.
RESOLUTION
You need to reset the permissions for the IUSR anonymous user. In the commands below, replace IUSR_ILLIAD with the anonymous user that your IIS web server is set to use. Also, if you have your web pages stored in a location other than c:\inetpub\wwwroot\illiad\*.* then you'll need to change that path as well (or any other paths that are different on your server). These commands will add the anonymous user access back to the necessary paths, leaving all other permissions settings unchanged.
Run the following 4 lines from a command prompt on your server:
cacls "c:\program files\*.*" /E /G IUSR_ILLIAD:R
cacls c:\windows\temp /E /G IUSR_ILLIAD:F
cacls c:\windows\system32\*.* /E /G IUSR_ILLIAD:R
cacls c:\inetpub\wwwroot\illiad\*.* /E /G IUSR_ILLIAD:R
This script gives read permission to the anonymous web user to the directories listed as well as write access to the temporary windows directory. Be sure to check path names and the value for the anonymous user in the script before running it on your server. Also, note that after changing permissions on the server, you should reboot the server to reset the web dll and verify that all changes have successfully applied.
MORE INFORMATION
The CACLS command will let you change permissions for any folders on your server:
CACLS: Displays or modifies access control lists (ACLs) of files
CACLS filename [/T] [/E] [/C] [/G user:perm] [/R user [...>
[/P user:perm [...> [/D user [...>
filename Displays ACLs.
/T Changes ACLs of specified files in
the current directory and all subdirectories.
/E Edit ACL instead of replacing it.
/C Continue on access denied errors.
/G user:perm Grant specified user access rights.
Perm can be: R Read
W Write
C Change (write)
F Full control
/R user Revoke specified user's access rights (only valid with /E).
/P user:perm Replace specified user's access rights.
Perm can be: N None
R Read
W Write
C Change (write)
F Full control
/D user Deny specified user access.
Wildcards can be used to specify more that one file in a command.
You can specify more than one user in a command.
Abbreviations:
CI - Container Inherit.
The ACE will be inherited by directories.
OI - Object Inherit.
The ACE will be inherited by files.
IO - Inherit Only.
The ACE does not apply to the current file/directory.