Impersonation level in COM+ x Oracle connection via MSDAORA.1

[Sandro Ney]

Please, HELP ME, if you can !

What I´m trying to do:
-----------------------------------------------------------------
Be able to authenticate Windows accounts to access directly a
Oracle database. With this, I want to live in a single sign-on
environment for multi-tier applications.

What I configured to put that solution to work:
-----------------------------------------------------------------

Windows Domain Controler: (based on Q283201 Microsoft article)
- Create account 'alice';
- Select 'Trusted for delegation' property for 'alice' account;
- Clear the 'Account is sensitive cannot be delegated' check box
for 'alice';
- Select 'Trustef for delegation' property for COM+ Server´s
hostname account in Active Directory.

Oracle database: (based in
otn.oracle.com/products/oracle8/htdocs/qa.htm)
- Create an user named 'alice' in the database;
- Set 'External Authentication' to on for 'alice' user;
- In the init.ora configuration file, be sure that
OS_AUTHENT_PREFIX
parameter is empty.
- Set necessary permissions to alice in some tables;

COM+ Server:
- Create an application called 'Security' that has Impersonation
Level=Delegate;
- The owner of that application is a local IUSR_hostname; (I tried
other)

Application Component: (Visual Basic 6.0)
- String connection is 'Provider=MSDAORA.1;Data Source=DESE; User
Id=/;
Password=;' ;
- I´m calling CoImpersonateClient before creating a
ADODB.Connection object;
Private Declare Function CoImpersonateClient Lib "ole32" ()
As Long
- I´m calling CoRevertToSelf after closing database connection;
Private Declare Function CoRevertToSelf Lib "ole32" () As
Long
- I´m logging with App.LogEvent to make sure that I´m working with
the
identity of the client account;
Private Declare Function GetUserName Lib "advapi32.dll" alias
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As
Long

Application Log Result:
Before CoImpersonateClient account is IUSR_hostname;
After CoImpersonateClient account is alice;
After CoRevertToSelf account is IUSR_hostname.
Error ORA - 01017: invalid user/password; loggon denied.

Internet Information Server:
- Create an application and disable its anonymous authentication;

PS: IIS and COM+ are in the same server.

---------------------------------------------------------------------------

Now, I tested parts of my solution. And the main part of the problem
is
that when I´m logged in Windows as 'alice' and open a SQL Plus 8
software,
I can log on using user=/ and empty password. Can select tables too.
So, single sign on between Oracle and Windows 2000 is working;

Logged on Windows 2000 with account 'alice', I sucessfuly executed a
program
in Visual Basic that connect to Oracle using a string connection with
user=/
and empty password. This reforce the theory that single sign on
between
Oracle and Windows 2000 is working.

Testing the solution:
Logging on client station with account 'alice' and trying to access
via Internet Explorer the application ASP page, I have an error: ORA -
01017:
invalid user/password; loggon denied.

Another test:
To prove that impersonation is working I made an application that read
a file
from a shared directory on my network, only 'alice' have permission
for that
shared directory. When 'alice' account try to read the file using a
component in the same COM+ application, and her permission are checked
to have access in the shared directyory, everything is fine and she
have in her browser, the content of the file.
Conclusion: Impersonation is really working !

My question is: Can a COM+ application using impersonation, open an
Oracle connection using in the string connection "User
Id=/;Password=;" ? Why can´t I do that ? What´s wrong in my solution ?

Please, if someone can help me, post a solution.

Thanks,
Sandro Ney.

[Slava]

Hi,
1) As far as I know, you need to set the impersonation level on a
client side too ( you didn't mention it in your description).

2) If you want a single DB sign on, I don't see why you need to jump
through all the hoops and use impersonation in the first place . Why
not just set the identity of the COM+ app to Alice account and be done
with it?

On 13 Sep 2002 07:41:32 -0700, sandr...@hotmail.com (Sandro Ney)
wrote:

[Sandro Ney]

Hi Slava,

Thanks for your help.

Answering you questions:

1) As far as I know, you need to set the impersonation level on a
client side too ( you didn't mention it in your description).

Ok. I really didn´t. But is this necessary even if I have an ASP
application ? (Where Alice´s credentials are sent to IIS for
authentication, and COM+ is in the same server). Isn´t this client
side configuration necessary just for client Visual Basic EXE
applications, for example ?

2) If you want a single DB sign on, I don't see why you need to jump
through all the hoops and use impersonation in the first place . Why
not just set the identity of the COM+ app to Alice account and be
done
with it?

Is there a way to set the identity of COM+ app to Alice
automatically, at execution time ? If yes, this could solve my
problem. I´m not able to do that automatically using
CoImpersonateClient in my component.

What I´m trying to say with 'Sigle SignOn' is that Alice just
logged once(in Windows). She doesn´t need to keep another user/pwd
with her. Since she is logged in Windows, all intranet applications
can be accessed by her only using the browser. And our Oracle´s dba
have to set up all permissions to her unique account. If this solution
work, I don´t know. But it could solve another problem: I could trust
in Oracle log as my audit tool. What is something that I don´t have if
using the identity of the COM+ app, where all transactions are
executed using only one db user.

I know that I´m losing the pool of db connections with that
configuration, because I want to connect to Oracle using credentials
of the client user logged on Windows(in this case, Alice), not using
credentials of the COM+ App Identity. In theory, I´m creating one pool
of one connection for each user. But I think that I´m creating a kind
of world in security for my corporation, where the programmer isn´t
responsible for security.

Thanks in advance,
Sandro Ney.

Slava <vy...@hotmail.com> wrote in message news:<4me4oug681ndf07s7...@4ax.com>...

[Slava]

Hi,
That sounds fine, but I think in your situation you must use "basic
authentication" in IIS to be able to delegate credentials over more
than one hop. ( Make sure to uncheck "Windows integrated
authentication"). You should probably use transport-level security
(ex: SSL) to prevent usernames and passwords being transmitted in
clear text when users are authenticating to IIS .

Slava

On 16 Sep 2002 08:00:57 -0700, sandr...@hotmail.com (Sandro Ney)

[Sandro Ney]

A T L A S T !!!

Now that entire solution is working, I feel that I have to comeback
here to thank you and share the last piece of the solution.

The solution to my problem is to use OSAuthent=1; in my OLEDB
connection string(I changed my provider from MSDAORA to
OraOLEDB.Oracle). Since my program was raising an error in the
adoCon.Open strConnect statement, I suspected that the provider was
barring my impersonated user. And it was really true. When reading
Oracle OLEDB documentation, I saw that OSAuthent parameter means to
open the connection with an Oracle External User Authentication, in
other words, trust in logged Windows account user.

Thanks Slava,
Sandro Ney.

Slava <vy...@hotmail.com> wrote in message news:<qn6couctc1746gb4a...@4ax.com>...

[Michael D. Long]

FYI - if you are using COM+ for transaction management with
the Oracle OLE DB provider, then you must be running Oracle's
Service for MTS/COM+. Otherwise, the Oracle provider cannot
participate in distributed transactions handled by DTC.

Michael D. Long
Microsoft MVP - Windows SDK

"Sandro Ney" <sandr...@hotmail.com> wrote in message
news:4213c3fb.0209...@posting.google.com...

[Slava]

Awesome! I'll put the solution into my notes.

Thanks,

Slava

On 18 Sep 2002 06:06:47 -0700, sandr...@hotmail.com (Sandro Ney)