Trying to figure out how to use the Oracle OLE DB Provider under MTS or COM+ and with Oracle’s OS Authentication? This article explains how these parts fit together and supplies information that will help you get started.

Introduction

A while ago, I was given the mission of architecting a distributed system using Microsoft “middleware” (ADO, COM, MTS, Windows NT) with an Oracle database. Seemed simple enough, surely its been done before thinks I. While I’m getting this organised, the target platform was switched from Windows NT to Windows 2000.

Since we wanted to integrate Oracle security with our existing NT Master/Resource domain structure, using Oracle’s OS Authentication was added to the mix.

I started by hunting up the relevant documentation, both Microsoft and Oracle. Not having any experience with Oracle before, relating the pieces of Oracle’s documentation to each other and fitting that into Microsoft’s MTS, VB and ADO documentation took some doing.

Oracle’s documentation is divided between “generic” documentation for all it’s platforms and platform specific documentation. Merging the concepts between them turned out to be an art in itself! Sorting out “Net Names”, naming methods and various administrative tools was interesting. Besides which the computer I had only had 64 MB of memory woops, can’t install the started database during Oracle Server installation without at least 96 MB! Since getting more memory was “out of the question”, had to do those steps separately more complications and strange terminology!

I got Oracle 8.1.5 installed under Windows 2000 Server configured as a Domain Controller. Hmm, OS Authentication doesn’t seem to work. Check with Oracle; Windows 2000 is not supported go directly to Jail; do not collect $200! Hmm. Maybe it’ll work in an NT 4.0 domain. Reconfigure Windows 2000 to be a member server (don’t need to re-install do that!). OK, now OS Authentication works.

I found out that Microsoft recommended using the Oracle client software version 8.0.5 with its OLE DB Provider and ODBC driver. I didn’t have that “old” version and didn’t see any future in going backwards, so on to Oracle’s OLE DB Provider. At that time, the only version of OLE DB I could find was the 8.1.5 Beta 2. Well, not to worry, it’ll be RTM by the time we get to application development. Oh. Oracle OLE DB Provider needs the Oracle Services for MTS patch. Oracle Services for MTS? What’s that?

Oh, sorry, that computer your using has been re-assigned. Start all over on another one. Life is wonderful! Ah, but by now I’ve done it so many times I can do it all in my sleep.

Followed the instructions for using Oracle Services for MTS with OS Authentication (another jigsaw puzzle). But, the configuration dialog doesn’t work as documented!

Found another computer and installed NT 4.0, NT Option Pack, SP5, Oracle 8.1.5, Oracle Services for MTS and configured the stuff. The Oracle Services for MTS configuration dialog still doesn’t work. OK; this time, Oracle supports it and I got some good help from local Oracle support staff. Turns out the instructions are wrong! Now everything works under NT. Used the new instructions on Windows 2000, still no go, so I decided to wait for Oracle 8.1.6. I went through the process again with Windows 2000 and Oracle 8.1.6 and wonderful, it all works.

I learned a lot from this experience and this article summarises what I learned. Hopefully, it will help you if you’re trying to set up this kind of environment.

Rather than try to cover all the bases, I’ve deliberately kept the focus narrow: using the Oracle OLE DB Provider with MTS/COM+ components on Windows NT 4.0 or Windows 2000 Server. Most of the information also applies to using Oracle ODBC Driver and Oracle’s OCI with MTS.

As I explain later, most of this article does not apply if you’re using the Microsoft supplied OLE DB Provider for Oracle or the Microsoft supplied ODBC driver for Oracle.

This article assumes that the pre-requisite software components are already installed, but not yet configured to work together. These are:

• Oracle Server and Client with at least one Oracle database
• Oracle Services for MTS
• Oracle OLE DB Provider
• MTS 2.0 only for NT 4.0 (COM+ Component Services is automatically installed on every Windows 2000 computer)

Rather than give step by step instructions, which would be quite lengthy, this article provides background information, ties the bits together and gives references to the necessary vendor supplied documentation and instructions.

References to MTS also apply to Component Services of COM+ under Windows 2000, except where differences are specifically mentioned.

Recall that MTS and DTC co-ordinate the activities of Resource Managers to implement the transactional properties (the ACID properties). In this case, the Resource Manager is the Oracle database manager.

There are two mechanisms supported by Oracle for enrolling database activities in an MTS transaction:

• XA transactions
• OLE transactions

There are at least five different “protocol stacks” that can be used to access an Oracle database from an MTS component. Figure 1 shows these stacks along with which mechanism they use to enrol in an MTS transaction.

The often-referenced MS KB Article Q193893 INFO: Using Oracle Databases with Microsoft Transaction Server applies only to the Microsoft stacks (the last two rows in Figure 1). Be aware that the Microsoft OLE DB Provider and ODBC Driver for Oracle prior to the one included in MDAC 2.5 may not work correctly with Oracle 8i (Oracle 8.1 and later); see MS KB article Q244661 for additional information.

Support for OLE Transactions is provided by Oracle in a component called Oracle Services for MTS. So, to use one of the Oracle protocol stacks with MTS, this component must be installed and configured. See Using Microsoft Transaction Server with Oracle8 in the Oracle Server for NT/2000 documentation set. This component ships on both the 8.1.5 and 8.1.6 Oracle Server for NT/2000.

There can be at most, one instance of Oracle Services for MTS for each Oracle instance (database). Oracle Services for MTS can be installed on the same computer as the Oracle instance, or a different computer. Oracle Services for MTS can be used in conjunction with an Oracle instance on any platform supported by Oracle, not just for Oracle on NT or Windows 2000 (e.g. most UNIX flavours).

Each instance of the Oracle Services for MTS works with exactly one Oracle instance, just as there is at most, one instance of MTS and DTC on any given computer.

Oracle Services for MTS implements the functions that MTS and DTC expect of a Resource Dispenser and a Resource Manager using OLE Transactions. These functions map MTS/OLE Transactions to Oracle transactions and work with MTS/DTC to commit or abort a transaction. The Oracle Services for MTS documentation explains this fairly well (see Web Site References – Documentation at the end of this article for where to find this documentation).

Oracle Services for MTS runs as an NT/2000 service. Every NT/2000 service runs in a security context associated with an NT/2000 user account. The user account used for the Oracle Services for MTS service must be known to the Oracle database and have these Oracle System Privileges:

• Create Public Synonym
• Drop Public Synonym
• Force any Transaction

and these Oracle Roles:

• Connect
• Resource
• Select_catalog_role

Connections in the MTS database connection pool are associated with a particular security context. To make good use of MTS connection pooling, the number of different security contexts must be limited. A common recommendation in this regard is to use the MTS component’s Identity when accessing a database. You can do this quite easily by using Oracle’s OS Authentication feature (see the Sidebar Oracle Security:) and appropriate assignment of NT accounts to MTS components’ Identity properties. If these NT accounts are then defined to be Oracle External OS Users and granted appropriate External Roles using Oracle Administrative Assistant for NT, connection pooling should work as intended.

Then, there is no need to specify a username and associated password for ADO connection’s User ID property. Oracle’s OLE DB Provider will automatically get the NT account from the current security context and pass this to Oracle. Oracle, via its OS Authentication feature for NT then knows what System Privileges and Roles to use for the database accesses. This can also make managing usernames and passwords simpler and reduces (or eliminates) the exposure of passwords, either in code or elsewhere.

Before you can use ADO to connect to an Oracle database, you have to know the Net Service Name associated with the Oracle instance of interest. See the Sidebar Identifying and locating an Oracle instance: for additional information. Only the people that set up the Oracle database can provide you with this name; it’s not the same for every Oracle installation.

Armed with the name to specify as the Data Source, the ADO Connection properties required to use the Oracle OLE DB provider are:

• Provider=OraOLEDB.Oracle;
• Data Source=NetServiceName;
• User ID=someOracleusername;
• Password=password for someOracleusername;

Some sample ADO “connect strings” are:

• To use a specific username and password (e.g. when not using OS Authentication):

• To use OS Authentication and the MTS Component’s Identity property for controlling permissions and rights in the Oracle database:

• to use the ADO Data Shaping service to create hierarchical recordsets:

When you use VB, ADO and Oracle OLE DB Provider, there are four environments, each with their own idea about data types.

• Native VB data types
• ADO data types
• OLE DB data types
• Native Oracle data types

ADO data types map almost directly into OLE DB data types, so that isn’t too much of a problem.

The Oracle OLE DB Provider documentation includes a table showing the mapping of Oracle data types to OLE DB data types (see Chapter 2 OraOLEDB Features; OraOLEDB Provider Specific Features section).

Although in a somewhat obscure place, the mapping of ADO to OLE DB data types is shown in the Enumerated Constants part of ADO programming reference documentation (MSDN; Platform SDK; Data Access Services; Microsoft Data Access Components (MDAC) SDK; Microsoft ActiveX Data Objects (ADO); ADO API Reference; ADO Enumerated Constants; DataTypeEnum).

When it comes to finding out what VB data type best maps ADO data types (and indirectly, Oracle data types), your kind of on your own but see http://www.able-consulting.com/ADODataTypeEnum.htm for some hints. Most are pretty obvious (e.g. varchar2 maps to string). VB has little or no concept of numeric precision and scale, so dealing correctly with some numeric Oracle data types may be a challenge.