ODBC/OLEDB/ODP and Kerberos

Precondition : sqlplus can connect to the database server using a kerberos ticket.

I wrote The long long route to Kerberos – Laurent Schneider in 2014, but most of the bugs in / are no longer interesting, so probably you should be fine to just read Configuring Kerberos Authentication (oracle.com)

let’s see

sqlplus /@db01

SQL*Plus: Release - Production on Fri May 20 13:33:00 2022
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Last Successful login time: Fri May 20 2022 12:50:44 +02:00
Connected to:
Oracle Database 19c Enterprise Edition Release - Production

Okay, to use ODBC, you just omit username and password. Either in Excel or in Powershell.

Add-OdbcDsn -name "DB01" -DriverName ("Oracle in "+$ORACLE_HOME_NAME) -SetPropertyValue @("Server=DB01") -dsntype user
$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=DB01"
(new-Object Data.Odbc.OdbcCommand("select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual",$conn)).ExecuteScalar()
Remove-OdbcDsn -name "DB01" -DsnType user

Just don’t specify User Id and Password

To use ODP.NET, it is almost the same, but there is a dirty trick

$ORACLE_HOME = (Get-ItemPropertyValue HKLM:\SOFTWARE\Oracle\KEY_* -name ORACLE_HOME)
Add-Type -Path $ORACLE_HOME\ODP.NET\bin\4\Oracle.DataAccess.dll
$conn = New-Object Oracle.DataAccess.Client.OracleConnection("User Id=/;Password=;Data Source=DB01")
$com.CommandText="select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"

Here, userid is not empty, but slash. What a joke ! One tool use “” and the other “/”

Ancient Oracle versions use 2.x instead of 4.

What’s more, if you use the Managed Driver, you’ll need the latest 21.6.1 version to allow Kerberos (not tested) New Oracle Features for .NET by Release

The same SLASH Convention also applies to OLEDB. You can either create an empty test.udl file on your desktop to test or use the following code

$conn = New-Object System.Data.OleDb.OleDbConnection ("Provider=OraOLEDB.Oracle;Data Source=DB01;User ID=/;Password=;")
$cmd.CommandText= "select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"

This concludes my ODBC/ODP/OLEDB topic