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 : Page 3

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

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/article s/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/article s/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://ww w.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.



Bruce Sanderson was born in England in 1947 and emigrated to Canada with his parents when he was seven. After graduating from the BC Institute of Technology (BCIT) in 1967, Bruce moved to Ottawa, Ontario and worked for Bell Northern Research. While at BNR, he worked initially on support software (e.g. assembler, loader, data compiler, simulator) for the electronic telephone exchange then under development. Later, Bruce provided operating system programming support for IBM’s CP 67/CMS and VM/CMS. Besides diagnosing and fixing problems, Bruce was responsible for performance measurement.
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