Oracle OLE DB and MTS

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.

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"

Data Types

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.

Gotcha’s

Oracle Services for MTS instructions not quite correct

The Oracle document Using Microsoft Transaction Server with Oracle8 explains fairly well what Oracle Services for MTS is and how to configure. However, there are two parts that are not quite correct.

  • Step 2 says to logoff and logon with the username that is to be used by the Oracle Services for MTS (e.g. MTSSYS). This is not necessary; you can complete the remaining steps while logged on with your normal NT administrative account. “Service accounts” are not associated with people and often don’t have the “logon interactively” right, so logging on with the Oracle Services for MTS account doesn’t make much sense.
  • If you are using OS Authentication with Oracle Services for MTS, there are special instructions in Appendix A of Using Microsoft Transaction Server with Oracle8 for configuring this. One of the instructions is to remove the username and password from the Oracle Manager for MTS Services panel for configuring a service. This is incorrect. If you remove the username and password, the dialog will not complete successfully and Oracle Services for MTS won’t work. Leave the username and password fields filled in. Although the values in these fields are not relevant when OS Authentication is used, their absence causes an obscure problem in the dialog that caused me much grief, as explained in the Introduction.

Oracle Services for MTS is not set to start automatically

Configuring Oracle Services for MTS using Oracle Manager for MTS Services, creates a new NT service. Unfortunately, at least in some cases, this service is not configured to start automatically. This means that Oracle Services for MTS will not be available after the operating system is restarted. Use the NT Services dialog (e.g. Control Panel, Services) to set the Oracle Services for MTS service to start automatically. The name of the (first instance of) Oracle Services for MTS service is OracleMTSService0.

Oracle Services for MTS may fail during start-up

When the Oracle Services for MTS is set to start automatically, it may fail during an operating system restart because the Oracle instance is not yet running. If you suspect this problem, look in the Oracle Services for MTS trace file (in the Oracle HomeoramtsTrace folder) for records like:

106953: [150] OracleMgr::Initialize - Error: GetOCIConn failed with error 81.
121955: [150] OracleMgr::Initialize - Start.
121975: [150] OracleMgr::GetOCIConn - Error: failed to connect. ORA-01033: ORACLE initialization or shutdown in progress

This can be a problem on slower servers. If you get this problem, use regedt32 to add the following registry value:

HKEY_LOCAL_MACHINE
System
CurrentControlSet
Services
OracleMTSService0
DependOnService REG_MULTI_SZ OracleServiceName

“OracleServiceName” is the name of the NT service for the Oracle instance. Normally this will be something like “OracleServiceabcd”, where abcd is the Oracle service name for the instance.

Hierarchical Recordsets not updateable

If you use the Data Shaping service to build a hierarchical ADO recordset, you will likely find that you can not make any changes to field values in the recordset. This applies to fields in the parent recordset as well as the child recordsets. If you attempt to change a field’s value using VB code such as:

Rs.fields(0).value = “abc”

you get a run time error to the affect that a multi-step operation has failed (not very enlightening). Oracle says this problem is fixed in Oracle 8.1.7.

If you use the recordset.clone method to create a copy of the recordset, that copy is not updateable either. So, if you need to bypass this problem, see MS KB article Q241202. HOWTO: Produce a Manufactured Hierarchical Recordset Base on an Existing Recordset. This is a good article and includes sample code you can copy that does the job.

Sidebars

Instance vs database

Oracle documentation distinguishes between an Oracle instance and an Oracle database.

A database is the set of files and associated permanent control information that constitute the persistent, managed data.

An instance is the set or processes and associated control information that manipulate the content of a database.

Since (unless Oracle Parallel Server feature is used) there is always a one-to-one relationship between an instance and a database, this is not always a useful distinction. Common usage tends to blur these distinctions, with the combined instance and database merely being referred to as an Oracle database. In this article, I’ve tried to use the terms as they are used in the Oracle documentation, but perhaps not always successfully.

Identifying and locating an Oracle instance

In a client-server or n-tier environment there must be a mechanism for the Oracle database client (which may be an MTS component) to locate and establish communication with the processes that comprise the target Oracle instance. Oracle provides several mechanisms for solving this problem. Net8, the part of Oracle that communicates between Oracle client programs and Oracle instance, implements these mechanisms.

One way is to have a set of configuration files on each Oracle client computer that has the required information. These files provide a mapping between a TNS or Net Service name and the instance, which is often on a different computer.

Another mechanism, which avoids having to replicate the mapping information to all clients, is the Oracle Name Service. Instances of the Oracle Name Service on different computers, in conjunction with the Listener service, co-operate to maintain the required name to computer and instance mappings in an “Enterprise”. A client only needs to know how to contact a Name Service instance and can then use that service to find out how to communicate with any Oracle instance known to any of the Name Service instances.

The Net Service Name exposed by the Oracle Name Service or recorded in the TNSName configuration files is used in the ADO Connection’s Data Source property to identify the particular Oracle instance that is to be associated with that connection. The Oracle Client uses whatever Oracle name resolution mechanisms it has been configured to resolve the Net Service Name to the target Oracle instance.

This Net Service Name is also used by the Oracle Services for MTS to establish communication with the particular Oracle instance it is configured to co-operate with.

Oracle Security

Security for any database is an important issue. Oracle has a fairly sophisticated built in security system. However, when users use many services, including one or more Oracle databases, maintaining the user information separately for each service gets to be a user and administrative nightmare. Users get multiple user names and passwords to remember and the passwords have to be changed every so often.

It may be possible, in the general, theoretical sense, to keep all security related information in one place. However, particularly when products from multiple vendors are used together, we have no way to reach this utopia yet. Each service has unique things that need to be “secured” and the same user may have different rights and permissions for different instances of a service. However, there are ways to reduce the problem and Oracle provides facilities for this.

Instead of “authenticating” users by passwords stored in the Oracle database, Oracle allows you to use the authentication service of the operating system it is installed on or more sophisticated “enterprise” schemes, such as Kerberos. Using the operating system’s authentication service is called OS Authentication.

Even though authentication is handled by the operating system, each user must still be identified to Oracle and be granted the appropriate System Privileges and Roles. On Windows NT and Windows 2000, this is done using the Oracle Administration Assistant for NT. Using this tool you can create External OS Users and grant External OS Roles to them.

See the Oracle Administrator’s Guide, Chapter 8: Authenticating Database Users with Windows for additional information.

Web Site References – Documentation

Microsoft

MS KB Article Q193893 INFO: Using Oracle Databases with Microsoft Transaction Server Using Microsoft Transaction Server with Oracle8 (does not apply if the Oracle supplied Oracle OLE DB Provider is used; see Background:)

http://support.microsoft.com/support/kb/articles/Q193/8/93.ASP

MS KB article Q241202: HOWTO: Produce a Manufactured Hierarchical Recordset Base on an Existing Recordset.

http://support.microsoft.com/support/kb/articles/Q241/2/02.ASP

MS KB article Q244661: INFO: Microsoft Oracle ODBC Driver and Provider Support Connectivity to Oracle 8I

http://support.microsoft.com/support/kb/articles/Q244/6/61.ASP

ADO Data Types

http://msdn.microsoft.com/library/psdk/dasdk/mdae8o19.htm

Other

Data Types Cross-Reference (ADO, OLE DB, VB etc.)

http://www.able-consulting.com/ADODataTypeEnum.htm

ADO Connect strings for various providers, including Oracle’s OLE DB Provider

http://www.able-consulting.com/ADO_Conn.htm

About the author

Since 1975, Bruce has lived in Victoria and worked for the BC Assessment, first as an application programmer, and now as System Architect. Part of his job is solving problems others can’t solve and figuring out how to best use new technologies in the BCA applications. This often involves “breaking new ground” and is well known by his co-workers for devouring vendor documentation and digging out how things work.

Over the years, Bruce has worked on many different computer systems and become fluent in several programming languages, including 360 Assembler, COBOL, PL/1 and VB. Having worked with it for many years, he is an expert in IBM’s IMS DB/DC.

Bruce is also an avid reader and frequent contributor to various Microsoft software newsgroups.

Bruce is married and has three children; two boys and a girl.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles:

©2023 Copyright DevX - All Rights Reserved. Registration or use of this site constitutes acceptance of our Terms of Service and Privacy Policy.

Sitemap