[2005] Windows Integration with Oracle Issue

[ducky]

Has anyone had success implementing an ASP.NET 2.0 appliction to use windows authentication via Micorosoft's trusted subsystem model to connect to an Oracle database? I have the model working for our application connecting to a SQL Server database as the msdn article details, but have an odd issue with the implementation when connecting to an Oracle database.

The issue is that the first browser session to a page that attempts to connect to the database always fails with an Oracle error "ORA-01017: invalid username/password; logon denied". All further browser session to the same page have successfull connections to the Oracle database as the domainname\machinename$ as expected and work fine. So what is happening that the first attempt to connect with integrated security always fails?

Windows 2003/IIS 6 web server
.NET 2.0 application (using the Microsoft managed provider for Oracle System.Data.OracelClient)
Connection string is "Data Source=myDB;Persist Security Info=False;Integrated Security=Yes;"
Oracle 10g database
domainname\webservermachinename$ is setup as a user in the Oracle database.

[mendhak]

Is there some sort of a profiling or tracing you can perform to see exactly what credentials are being passed? Maybe a security audit? Could it be the aspnet worker process being passed the first time?

[ducky]

Thanks mendhak. I was able to get our DBA to do an audit as you suggested. It appears the application is trying initially to logon with the os user 'NT Authority\Anonymous Logon' from the web server machine. All other login attempts after the first use the correct os user domainname\webservermachinename$

The application pool identity is set to use the Network Service account and its my understanding this is the worker process and should force all integrated security connection requests to use domainname\webservermachinename$

So I'm still looking for the mysterious culprit reason for the initial connection thats using anonymous.

[mendhak]

Try adding

Integrated Security=SSPI

to the connection string.

[ducky]

Tried the SSPI connection string, and it still occurs. I switched the code over to use the Oracle ODP.Net provider and the same phenomenon happens. So my assumption is it is not a bug with the provider .dll since it occurs with two different providers. My next best guess is it must somehow be tied to the worker process (network service) identifying itself initially or connection pooling somehow.

In the mean time I've coded a work around by issuing a connection request in the application_start of the global.aspx to essentially eat the initial failure, so all sessions never have trouble creating a connection.

[mendhak]

This is a tough one. I did a bit of searching and found this

Windows Authentication

This article has discussed using Oracle user IDs to authenticate users to the database. It is also possible to use the Windows operating system to authenticate users to Oracle , allowing single sign-on capabilities. I covered this in detail in Implementing Windows Authentication from Oracle, in Windows IT Pro magazine. Using Windows authentication requires a modification to the connection string:

"Data Source=ORCL10g;User Id=/;"

The slash (/) tells Oracle that Windows authentication will be used. The Password connection string attribute is removed, because it is used only when making an Oracle database connection. If Password is left in the connection string when Windows authentication is used, it is ignored.

With Windows authentication, either the Windows user must belong to a privileged Windows group such as ORA_DBA on the Oracle server or external authentication must be enabled. External authentication is not recommended, because it is less secure than access through group membership.

Try it with the forward slash and User ID in your connection string.

There's also an article here on how to set up Oracle (and presumably the code) to work with Windows Authentication. There may be code or connection string details in there.