MSDAORA connection from Excel macro

[Mat]

Sorry if this is a lame question, I am not a developer but more of a network
admin.

I have inherited an Excel macro that users the MSDAORA provider to establish
a connection to an Oracle database.

Looking on the Internet I have found that this users the "Microsoft OLE DB
connection for Oracle". My question is does the OLE DB still use the ODBC
drivers at a lower level? The reason I ask is that ODBC is apparently not
that secure in it sends username/passwords in clear text, and to secure the
connection by tunnelling it through SSH is the best option.

Is this still the case when using OLE DB for Oracle? Or does it in fact use
the oracle client installed on the computer for the connections to the
database, and if you use oracle's advanced security on the oracle client to
encrypt the database connection, will accessing the database via OLE DB
provide an encrypted connection to the database via the oracle client?

Thanks for you help.

Mat.

[Erland Sommarskog]

Mat (mat...@nospam.nospam) writes:
> I have inherited an Excel macro that users the MSDAORA provider to
> establish a connection to an Oracle database.
>
> Looking on the Internet I have found that this users the "Microsoft OLE
> DB connection for Oracle". My question is does the OLE DB still use the
> ODBC drivers at a lower level? The reason I ask is that ODBC is
> apparently not that secure in it sends username/passwords in clear text,
> and to secure the connection by tunnelling it through SSH is the best
> option.

MSDAORA is an OLE DB Provider for older versions of Oracle. I am told
that it does not work well with Oracle 10 and later. For this, you are
best of with Oracle's own OLE DB provider. (This may also apply to
Oracle 9.)

"Microsoft OLE DB connection for Oracle" sounds like a friendly name for
MSDAORA.

MSDAORA does not use ODBC; the OLE DB-over-ODBC provider is known as
MSDASQL.

> Is this still the case when using OLE DB for Oracle? Or does it in fact
> use the oracle client installed on the computer for the connections to
> the database, and if you use oracle's advanced security on the oracle
> client to encrypt the database connection, will accessing the database
> via OLE DB provide an encrypted connection to the database via the
> oracle client?

Whether you can use Oracle's advanced security with MSDAORA I don't
know, since I don't work with Oracle. But it follows from what I said
above, that you are likely to be better off with Oracle's own provider.
I believe their provider is available for download.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx