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 11.2.0.2 / 12.1.0.2 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 21.0.0.0.0 - Production on Fri May 20 13:33:00 2022 Version 21.3.0.0.0 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 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL>
Okay, to use ODBC, you just omit username and password. Either in Excel or in Powershell.
$ORACLE_HOME_NAME = (Get-ItemPropertyValue HKLM:\SOFTWARE\Oracle\KEY_* -name ORACLE_HOME_NAME)
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"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual",$conn)).ExecuteScalar()
$conn.close()
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")
$conn.open()
$com=$conn.CreateCommand()
$com.CommandText="select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"
$com.ExecuteScalar()
$conn.close()
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=;")
$conn.Open()
$cmd=$conn.CreateCommand()
$cmd.CommandText= "select sys_context('userenv','AUTHENTICATED_IDENTITY') from dual"
$cmd.ExecuteScalar()
$conn.close()
This concludes my ODBC/ODP/OLEDB topic