Gyrus Knowledge Base
Article Number:		04002103
Article Title:		NEW: DBMS Alternate Connection

Training Wizard 2001 3.0.23

Prior to Training Wizard 2001, client machines were forced to use the "Wizard32" ODBC data source created by the Training Wizard installation program. This data source was required to connect to the database as the "Wizard" database user. The "Wizard" database user owned all of the tables, views and procedures used by Training Wizard and Self Service Training. Because most database systems requires database users to have unique names, it was difficult to create a second Training Wizard database on the same database server. When using Oracle, you had to create a second instance. When using Microsoft SQL Server, it was next to impossible.

Training Wizard 2001 is now more flexible. Although the installation program will still create a "Wizard32" ODBC data source that Training Wizard will use by default, you are now able to create your own ODBC data source for Training Wizard to use. In addition, you can create your Training Wizard database so that it is owned by a database user other than "Wizard". This makes it easier to have multiple Training Wizard databases on the same database server.

By default, when you create a Training Wizard database, the database objects (tables, views, procedures, etc.) are owned by a database user named "Wizard". By editing the scripts (Oracle and Microsoft SQL Server only) used to create the Training Wizard database, you can create these objects so that they are owned by whatever database user you choose. For example:

For Oracle, edit the ORAUSER.SQL script so that the last three statements create and work with a user other than Wizard. For example, to use a database user named "Lamont", those last three statements would read:

CREATE USER LAMONT IDENTIFIED BY LAMONT DEFAULT TABLESPACE WIZ_DATA_SPACE TEMPORARY TABLESPACE WIZ_TEMP_SPACE;
GRANT UNLIMITED TABLESPACE TO LAMONT;
GRANT WIZARD_SUPERVISOR TO LAMONT WITH ADMIN OPTION;

You would then log on as the Lamont user within SQL*Plus and run the remaining scripts.

For Microsoft SQL Server, edit the MSINIT.SQL script so that the database, physcial storage, login and user are all named something other than "WIZARD". For example, to use "Lamont" instead of "Wizard", the MSINIT.SQL file for Microsoft SQL Server 7.x would look like this:

CREATE DATABASE Lamont
ON
( NAME = Lamont_Data,
FILENAME = 'c:\mssql7\data\Lamont_data.mdf',
SIZE = 100 )
LOG ON
( NAME = Lamont_Log,
FILENAME = 'c:\mssql7\data\Lamont_log.ldf',
SIZE = 40 )
GO

USE Lamont
GO

sp_addlogin Lamont, Lamont, Lamont
GO

sp_grantdbaccess 'Lamont', 'Lamont'
GO

sp_addrolemember 'db_owner', 'Lamont'
GO

You would then log on as the Lamont user within Query Analyzer and run the remaining scripts.

Once you have created your alternate database, you must create an ODBC datasource that connects to it. This must be done on each client machine on which Training Wizard will run and connect to the alternate database.

After you have created the alternate database ODBC datasource, you need to use a special utility to configure Training Wizard to use the new ODBC datasource. The utility is located in the same directory as the Training Wizard application and is called TWdbconopt.exe.

The TWdbconopt.exe utility is used to define "alternate connections" for Training Wizard to use. When you first run this utility, there will be no connections defined. Click the Add button to add a new connection. Each alternate connection requires two pieces of information:

• The name of the ODBC data source to use.
• The name of the database user that owns the Training Wizard database. When using the standard database creation scripts, the name of this user is "Wizard". Appropriate changes to these scripts will allow the Training Wizard database to be owned by another user. Using the examples from above, you would enter "Lamont" here.

After defining an alternate connection, you have several choices:

• Click the Test Connection button to verify the connection. This will return with a simple success or fail message. If it fails, then normal database connection troubleshooting must be performed. Note that the first time you attempt to connect using your new "alternate connection", you will see a dialog that asks you to enter the database password for the database user that you are connecting with (i.e. "Lamont"). You only have to enter this one time. Once it has been entered correctly, the password is encrypted and stored in a file on the client machine. Training Wizard will read that file in the future instead of prompting for the password. If you have multiple clients to configure with the same "alternate connection", you can copy this file to each client machine. The file is stored in the Training Wizard directory and is named using this syntax: data_base_user.key (for example, "LAMONT.KEY").
• Click the Set Default button to make the selected connection the one that Training Wizard, EvalTracker and the Import Utility will use to connect to a database.
• Check the Prompt for DBMS Connect option to have a list of possible connections presented to you when you run Training Wizard, EvalTracker or the Import Utility. With this option checked, the concept of a Default connection is ignored. When you run Training Wizard, while the Splash Screen graphic is visible, a prompt will appear that you can use to select which connection you wish to use.

Finally, do not confuse the Training Wizard login screen with a database connection. When the Training Wizard logon screen appears, you are already logged into the database. The Training Wizard logon screen is used to identify which Training Wizard application user you are, and therefore what application security privileges you will have.

04002143 - Summary Of Changes In Training Wizard 2001 3.0.23

THIS INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. GYRUS SYSTEMS DISCLAIMS ALL WARRANTIES, EITHER EXPRESSED OR IMPLIED. GYRUS SYSTEMS SHALL NOT BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS, OR SPECIAL DAMAGES, EVEN IF GYRUS SYSTEMS HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

For additional assistance send email to support@gyrus.com or phone technical support at 1-800-284-9787
Gyrus Systems, Inc. is located at Gyrus Center, 620 Moorefield Park Drive, Richmond, VA 23236-3692.
Office Phone: (804) 320-1414 Office Fax: (804) 320-2323 Located on the web at www.gyrus.com