Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Oracle OLE DB and MTS

Trying to figure out how to use the Oracle OLE DB Provider under MTS or COM+ and with Oracles OS Authentication? This article explains how these parts fit together and supplies information that will help you get started. The focus is deliberately kept 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 Oracles OCI with MTS.


advertisement

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.

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 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.

Background

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.

Protocol Stack

Transaction Mechanism

Oracle Call Interface (OCI)

OLE Transactions

Oracle OLE DB Provider

OLE Transactions

Oracle ODBC Driver

OLE Transactions

Microsoft OLE DB Provider for Oracle

XA transactions

Microsoft ODBC Driver for Oracle

XA transactions

Figure 1 Protocol Stacks for accessing Oracle

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.

Oracle Versions

Oracle 8.1.5

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 Windows 2000.

Oracle 8.1.6

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 8.2.

Patch Sets

Updates (called 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.

Documentation

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 Web site).

Windows Versions

The configuration 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 Domain.

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date