Categories
oid security

ODBC and EZCONNECT or my way out of active directory

The traditional way of connecting Excel (or Access) to Oracle (and other databases) is to use ODBC and TNSNAMES. You install an Oracle client, you create a connection and specify your tnsnames connection alias as server, here below DB01

tnsnames.ora
DB01 = 
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)(host=srv01)(Port=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=DB01)
    )
  )

But the file is often managed centrally and deployed to the client by the DBA via home scripts. The syntax is pretty ugly and very soon you’ll see, a space or a parenthesis is missing and the loss of service is complete…

When working with Windows, one guy may try to setup active directory resolution. Before Exchange 2003, the schema was extended and that’s it, it works. But later, Microsoft made things more secure, among others by disabling anonymous bind and probably later by enforcing SSL, and one day you’ll see, the connection no longer works. Also, the schema extension could not be reverted, so it is not a thing you do just for fun in production

While there are white papers and blog articles on using authenticated bind, I could not find any support note.
Note 361192.1 mentions :
When anonymous operations are disabled, anonymous operations performed against Active Directory will fail
And note 455031.1 mentions :
Configuring Non-Anonymous LDAP Access Prerequisites: – A working LDAP naming environment should already exist between a client and OID (not Active Directory)
While note 1587824.1 refers the white paper Configuring Microsoft Active Directory for Oracle Net Naming , it clearly states This document is provided for information purposes only
So when AD changes, chances are, you will get an issue. Maybe in 2020Q1 according to https://portal.msrc.microsoft.com/en-us/security-guidance/advisory/ADV190023

Clearly, if you need more, you should go for an Oracle directory server like OID.

But if you need less? maybe you could go for easy connect (EZCONNECT). This is a zero-configuration setup that puts all the configuration out of the DBA tasks. So it seems to be better.

There are plenty of examples on how to use it, even with SSL and RAC and so on. In its simplest form you’ll use

sqlplus scott/tiger@srv01:1521/DB01

instead of

sqlplus scott/tiger@DB01

So a little bit more details but no more ActiveDirectory and no more tnsnames.ora.

But does it work with Excel and family? actually yes

You need to specify
Service Name : //srv01:1521/db01

If you don’t prefix with //, it doesn’t work.

Conclusion: if you are not willing to maintain local tnsnames and struggling with ActiveDirectory security enhancement, consider easyconnect but be aware of the additional slashes in the server name

Categories
linux sybase unix

Unix ODBC Sybase

very similar to Unix ODBC Oracle

instead of tnsnames, the connections are defined in $SYBASE/interfaces.

the odbc.ini must exists as well in $SYBASE.

if you test with unixODBC-devel, keep in mind to use /usr/bin/isql and not $SYBASE_OCS/bin/isql

$ODBCSYSINI/odbc.ini
[syb]
Driver = Sybase16
DSN = syb
ServerName=SYB01

$ODBCINI/odbcinst.ini
[Sybase16]
Description = Adaptive Server Enterprise
Driver = /u01/app/sybase/product/16.0/DataAccess64/ODBC/lib/libsybdrvodb.so

$SYBASE/odbc.ini
[syb]
DSN = syb
ServerName=SYB01

$SYBASE/interfaces
SYB01
master tcp ether srv01.example.com 15000
query tcp ether srv01.example.com 15000

/usr/bin/isql -v syb user01 passw01
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>

Categories
linux unix

Unix ODBC Oracle

To connect via ODBC, check https://laurentschneider.com/wordpress/tag/odbc

This article is related to Unix/Linux. Often you have a fat client written in C, while java uses JDBC instead of ODBC.

Okay, it’s pretty easy, if you have an oracle client, you probably already have libsqora.so.xx.1 in your LD_LIBRARY_PATH. In this case you can connect using ODBC.

What you need is an odbc.ini where you defined your connections
[DB01]
Driver = OracleODBC18
DSN = DB01
ServerName = DB01

and an odbcinst.ini where you define your driver
[OracleODBC18]
Description = Oracle ODBC driver for Oracle 18
Driver = /u01/app/oracle/product/18.1.0/client_64/lib/libsqora.so.18.1

the name / location and options may depend on your software / driver vendor.

ODBC uses TNSNAMES, so it really easy, you just the odbc entry ServerName = DB01 that matches
DB01 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = srv01.example.com)(Port = 1521))(CONNECT_DATA = (SERVICE_NAME=DB01.example.com)))
in tnsnames.ora.

ODBC full client allows you to use all connection features like LDAP and SSL.

To test it, I installed unixODBC-devel
sudo yum install unixODBC-devel
export ODBCSYSINI=/home/user1/odbc
cd $ODBCSYSINI
vi odbc.ini odbcinst.ini

the machine-wide ODBCSYSINI is /etc. You can chose to define the ODBCINI user-wide (/home) and the ODBCSYSINI machine-wide (/etc). I wouldn’t use machine-wide passwords. But configuring the drivers only once may be an option. If you are root and you have not too many drivers/versions/bitcode.

Now try to connect :
isql DB01 scott tiger
SQL> select * from scott.emp;
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO|
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00| 800 | | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00| 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00| 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00| 2975 | | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00| 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00| 2850 | | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00| 2450 | | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00| 3000 | | 20 |
| 7839 | KING | PRESIDENT| | 1981-11-17 00:00:00| 5000 | | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00| 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00| 1100 | | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00| 950 | | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00| 3000 | | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00| 1300 | | 10 |
+-------+-----------+----------+-------+--------------------+----------+----------+-------+
SQLRowCount returns -1
14 rows fetched
SQL>

Categories
powershell

Add-OdbcDsn

I wrote a few odbc articles using ODBCCONF in my blog, so I edit them because ODBCCONF will be removed; read https://docs.microsoft.com/en-us/sql/odbc/odbcconf-exe

Using Powershell Add-OdbcDsn is much easier

PS> remove-OdbcDsn -name DB01 -dsntype User
PS> Add-OdbcDsn -name DB01 -DriverName
"Oracle in client12201" -DsnType "User"
-SetPropertyValue @("Server=DB01")
PS> Get-OdbcDsn

Name : DB01
DsnType : User
Platform : 64-bit
DriverName : Oracle in client12201
Attribute : {Password, StatementCache, ...}

PS> remove-OdbcDsn -name DB01 -dsntype User

Categories
powershell sybase

powershell odbc sybase

Oracle goes there ==> …/oracle-odbc-hello-world-with-powershell.html

To test Sybase ODBC driver with Powershell, it’s not much different

  1. configure the DsN with odbcconf or %SYSTEMROOT%\SysWOW64\odbcconf for the 32bits drivers under Windows 64.

  2. cmd /c "%SYSTEMROOT%\SysWOW64\odbcconf.exe /a {configdsn ""Adaptive Server Enterprise"" ""DSN=helloworld|database=mydb|port=25000|server=srv01""}"

    The name of the driver is important. It is probably also called “Adaptive Server Enterprise” on your machine.

  3. test it, same as in Oracle. If you need to test the 32bits drivers under 64bits windows, use the 32bits powershell %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe

  4. $conn = New-Object data.odbc.odbcconnection
    $conn.ConnectionString = "dsn=helloworld;uid=scott;pwd=tiger"
    $conn.Open()
    (new-Object Data.Odbc.OdbcCommand("select 'Hello World'",$conn)).ExecuteScalar()
    $conn.close()

Note: odbcconf will be removed in future release, prefer Add-OdbcDsn

Categories
windows

ODBC 32bits for Windows 64bits

Windows-On-Windows 64-bit (WOW64) enables you to run 32bits applications in 64bits OS.

You will see there is another powershell, another registry, another ODBC tool, another Oracle client.


%SYSTEMROOT%\syswow64\WindowsPowerShell\v1.0\powershell.exe

First, we run powershell(x86)


Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
C:\oracle\product\11.2.0\client_32 client32bit_11203

Only the Oracle 32bit client is displayed

cmd /c "%SYSTEMROOT%\syswow64\odbcconf.exe /a {configdsn ""Oracle in client32bit_11203"" ""DSN=helloworld32|SERVER=DB01""}"

We registered ODBC with a wow64 configurator (odbcconf) or assistant (odbcad32).

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld32;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

For the 64 bits version, it boils down to the same as odbc 32 bit on 32 bit os


%SYSTEMROOT%\system32\WindowsPowerShell\v1.0\powershell.exe

Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME ORACLE_HOME_NAME
----------- ----------------
C:\oracle\product\11.2.0\client_64 client64bit_11203

cmd /c "%SYSTEMROOT%\system32\odbcconf.exe /a {configdsn ""Oracle in client64bit_11203"" ""DSN=helloworld64|SERVER=DB01""}"

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld64;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

Do not get confused by Windows32 and WOW64.

Windows32 is the default windows system directory with a bad name from upon a time where 32 meant something very big (compared to 16bit software); and WOW64 which is a special directory to run 32bits windows application on a 64bits os.

Note: odbcconf will be removed in future release, prefer Add-OdbcDsn

Categories
windows

Oracle ODBC hello world with powershell

Demo :

cmd /c "odbcconf.exe /a {configdsn ""Oracle in OraClient11g_home1"" ""DSN=helloworld|SERVER=DB01""}"

Create a helloworld data source connecting to your DB01 tns alias in your OraClient11g_home1 Oracle Home.

It is easy to get the Oracle Home name with

Get-itemproperty HKLM:\SOFTWARE\ORACLE\*| Select-Object ORACLE_HOME,ORACLE_HOME_NAME

ORACLE_HOME ORACLE_HOME_KEY
----------- ---------------
C:\oracle\product\11.1.0\client_1 OraClient11g_home1
C:\oracle\product\11.2.0\client_1 OraClient11g_home2

Then we create the connection (as we did in ADO or ODP) :

$conn = New-Object Data.Odbc.OdbcConnection
$conn.ConnectionString= "dsn=helloworld;uid=scott;pwd=tiger;"
$conn.open()
(new-Object Data.Odbc.OdbcCommand("select 'Hello World' from dual",$conn)).ExecuteScalar()
$conn.close()

Note: odbcconf will be removed in future release, prefer Add-OdbcDsn