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.
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
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
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
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.
Scope of this article
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
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
- 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
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.
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.
Oracle Call Interface
Oracle OLE DB
Microsoft OLE DB Provider for
Microsoft ODBC Driver for
1 Protocol Stacks for accessing Oracle
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.
The Oracle OLE
DB Provider can be used with Oracle 8.1.5 and Oracle Services for
MTS on Windows NT 4.0. The OLE DB Provider was not shipped
on the Oracle Server CD. I’ve used the Beta 2 version
downloaded from the Oracle ftp site. The file name is
OraOLEDB_81520.exe. This includes an update to Oracle
Services for MTS, which must also be installed.
Oracle 8.1.5 can be installed and operated under NT 4.0 or
Windows 2000, but the 8.1.5 version of Oracle Services for MTS
doesn’t work with Windows 2000. Also, you probably won’t get any
help from Oracle if you install any version earlier than 8.1.6 on
The Oracle OLE
DB Provider is on the Oracle 8.1.6 for Windows NT and 2000 CD.
To add spice to life, Oracle also refers to Oracle 8.1.6 as Oracle
Patch Sets by Oracle) to Oracle Server, Oracle Services for MTS
and the Oracle OLE DB Provider are available on the Oracle ftp
sites for both 8.1.5 and 8.1.6. I’ve had the OLE DB Provider
working without and with these patchsets.
For Oracle 8.1.5,
the documentation for the OLE DB Provider is in the downloadable
file mentioned above. For Oracle 8.1.6, it is in the documentation
set on the Oracle Server CD (also available on Oracle’s Technet
and operation of the OLE DB Provider and Oracle Services for
MTS is essentially the same for Windows NT and Windows 2000,
at least, when the Windows 2000 Server is a member of an NT 4.0
This article assumes that the server is a member of an NT 4.0
domain. If you are using Oracle’s OS
Authentication (see the Sidebar Oracle Security)
and your Oracle Server is part of a Windows 2000 forest (i.e. using
Windows 2000 Active Directory), you may need additional
information that is not covered by this article.