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

MTS, Oracle Services for MTS and Oracle Server :

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

The Oracle Services for MTS NT service and Oracle security :

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.

Connecting ADO to Oracle via the Oracle OLE DB Provider :

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):
"Provider=OraOLEDB.Oracle;User ID=XYZ;Password=xyzpass;Data Source=NetServiceName"
· To use OS Authentication and the MTS Component's Identity property
for controlling permissions and rights in the Oracle database:
"Provider=OraOLEDB.Oracle; User ID=/;Data Source=NetServiceName"
· to use the ADO Data Shaping service to create hierarchical recordsets:
"Provider=MSDATASHAPE;Data Provider=OraOLEDB.Oracle; User ID=/;Data Source=NetServiceName"