Configuring Oracle Database 11g Gateway for ODBC MS SQL Server

Environment

Oracle database 11.2
Oracle Linux Server release 5.7
Microsoft® ODBC Driver 11 for SQL Server - RedHat Linux
Microsoft SQL Server 2005,2008R2

Installing ODBC driver

Install the driver according to MS http://www.microsoft.com/en-us/download/details.aspx?id=36437

To verify that the ODBC Driver on Linux was registered successfully, execute the following command:
odbcinst -q -d -n "ODBC Driver 11 for SQL Server"

edit ~/.odbc.ini
and add

[infraUAT]
Driver=ODBC Driver 11 for SQL Server
Description=My Sample ODBC Database Connection
Trace=Yes
Server=gtpsql2
Port=1433
Database=InfraEnt_ITSD_UAT

"Driver" has to be same as output of odbcinst -q -d, which comes from /etc/odbcinst.ini

Test it
odbcinst -q -s
[infraUAT]

isql -v infraUAT user password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

Configuring Gateway

cd $ORACLE_HOME/hs/admin

If you use initdg4odbc.ora, then dg4odbc will be SID.

edit initdg4odbc.ora

S_FDS_CONNECT_INFO = infraUAT

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# you need to match HS_LANGUAGE to SQLS

HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252

# you might need the following HS_

#HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

#HS_NLS_LENGTH_SEMANTICS=CHAR

#nvarchars of a SQL Server are UCS2 character set

#HS_NLS_NCHAR=UCS2

# ODBC specific environment variables

set ODBCINI=/home/oracle/.odbc.ini

HS_LANGUAGE needs to match code page of SQL Server.

When Oracle use unicode AL32UTF8, it will fail to connect. See Doc ID 756186.1

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[

edit listener.ora and add new SID_DESC, where SID matches initdg4odbc.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

        (SID_NAME = dg4odbc)

        (ORACLE_HOME = /opt/oracle/product/se1/11.2.0.3)

        (PROGRAM = dg4odbc)

        (ENVS='LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql/lib64:/opt/oracle/product/se1/11.2.0.3/lib')

    )

  )

Restart listener

configure  tnsnames.ora

HS1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = tcp)(HOST = )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = dg4odbc)

    )

    (HS=OK)

  )

Configure DB Link

CREATE PUBLIC DATABASE LINK INFRA

 CONNECT TO DSUSER

 IDENTIFIED BY password

 USING 'hs1';

Problem 1 - code page conversion

Some symbols are not converted correctly, e.g. Microsoft double quote or single quote. I have not tested non-latin alphabets.

SELECT short_problem  FROM CL_CALL_LOGGING@infra where call_number=4234;

output

The printer on Level 5 is showing ‘perform printer maintenance’. 

Solution 1

as Oracle NLS_CHARACTERSET = AL32UTF8 (check view NLS_DATABASE_PARAMETERS), create a view in SQLS to CAST this column to unicode, then you can select from this view from Oracle.

CREATE VIEW TEST_VIEW1 AS

SELECT CALL_NUMBER, CAST(SHORT_PROBLEM AS NVARCHAR(200)) AS SHORT_PROBLEM

FROM DBO.CL_CALL_LOGGING;

in Oracle

SELECT CALL_NUMBER, SHORT_PROBLEM FROM TEST_VIEW1@INFRA WHERE CALL_NUMBER=4234;

output

The printer on Level 5 is showing ‘perform printer maintenance’.

Problem 2 - multibyte characters

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}

ORA-02063: preceding 2 lines from INFRA

If in SQLS a column is described as VARCHAR(200) and a value length is 200 and it contains some symbols which become multi-byte, then in Oracle it becomes longer than 200 bytes.

Solution 2

The same as Solution 1, CAST it to unicode on SQLS.

If you create similar or the same table in Oracle, define length in chars, e.g. VARCHAR(200 char)

It does not have to be NVARCHAR as in my case oracle DB is already UTF8.

Problem 3 - varchar(max)

When selecting varchar(max) from SQLS:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}

ORA-02063: preceding 2 lines from INFRA

Solution 3.1

I haven't found a good solution for this. You can CAST(COLNAME AS TEXT) or as NTEXT in SQLS, in thin case you receive LONG type in Oracle (can be saved and converted to CLOB). Which is ok for most values (Latin characters), but it still has wrong conversion for multi-byte/non-latin characters.

Solution 3.2 - other ODBC drivers

try other drivers, e.g. Easysoft or Datadirect. I haven't tested it.

Solution 3.3 - non Oracle ETL

IBM Datastage works ok, when selecting as unicode CAST(col as NVARCHAR(max)) and keeping as LongNVarchar in DataStage to avoid Datastage conversion. It inserts into Oracle CLOB and looks ok.

Microsoft SSIS converts correctly, if destination table in Oracle is defined as NCLOB.

Documentation

Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)

How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)

SQLNET AUTHENTICATION_SERVICES in Windows environment

Environment:
Windows Server 2003 R2, Oracle 11.2.0.4


windows service OracleServiceSID is up
windows account is added in local group ORA_DBA
Oracle password file is created

SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora on server	none	nts	all
/ as sysdba	ORA-01017: invalid username/password; logon denied	ok	ORA-12641: Authentication service failed to initialize
local: sqlplus sys as sysdba	ok	ok	ORA-12641: Authentication service failed to initialize
local: sqlplus sys@tnsname as sysdba	ok	ok	ORA-12641: Authentication service failed to initialize
remote: sqlplus sys@tnsname as sysdba	ok	ORA-12638: Credential retrieval failed	ORA-12638: Credential retrieval failed
local: sqlplus system	ok	ok	ORA-12641: Authentication service failed to initialize
local: sqlplus system@tnsname	ok	ok	ORA-12641: Authentication service failed to initialize
remote: sqlplus system@tnsname	ok	ORA-12638: Credential retrieval failed	ORA-12638: Credential retrieval failed

for ORA-1031: Insufficient Privileges, check the following Oracle support document:

Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA (Doc ID 730067.1)

Generate tnsnames from OEM repository

Environment: Oracle 10g, 10.2
To get tnsnames from OEM, connect to OEM repository and run the following SQL.
p3.property_value and t.target_name can be the same, or one of them can include domain name. You can choose one or both.
Script:

select p3.property_value||', '||t.target_name||' = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = '||p1.property_value||')(PORT = '||p2.property_value||'))) (CONNECT_DATA = (service_name = '||p3.property_value||')))'
from SYSMAN.MGMT_TARGETS t, SYSMAN.MGMT_TARGET_PROPERTIES p1, SYSMAN.MGMT_TARGET_PROPERTIES p2, SYSMAN.MGMT_TARGET_PROPERTIES p3
where upper(t.TARGET_NAME) like 'DH%' -- filter targets if you don't want all of them
and t.target_type='oracle_database'
and t.target_guid = p1.target_guid
and t.target_guid = p2.target_guid
and t.target_guid = p3.target_guid(+)
and p1.property_name='MachineName'
and p2.property_name='Port'
and p3.property_name(+)='ServiceName'
order by 1;

Oracle Generic Heterogeneous Services and Transparent Gateways

RHEL 4.5 Oracle 10.2

1. install package unixODBC
2. install package freeTDS, if u don’t have it for ur linux go to www.freetds.org and get the source
3. configure /etc/freetds.conf (path can be different)
4. try to connect with tsql
5. configure files /etc/odbc.ini, /etc/odbcinst.ini, see www.unixodbc.org for details
6. try to connect with isql (user and password are obligatory parameters)
7. create and customize an initialization file for your Generic Connectivity agent $ORACLE_HOME/hs/admin/inithsodbc.ora

# HS init parameters

#

HS_FDS_CONNECT_INFO = mssql

HS_FDS_TRACE_LEVEL = off

HS_FDS_SHAREABLE_NAME = /usr/lib/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

8. add record in tnsnames.ora

HS1 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = tcp)(HOST = sydora2)(PORT = 1521))

)

(CONNECT_DATA =

(SID = hsodbc)

)

(HS=OK)

)

9. add record in listener.ora

SID_LIST_LISTENER_SYDORA2 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = hsodbc)

(ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1)

(PROGRAM = hsodbc)

(ENVS=LD_LIBRARY_PATH=/opt/oracle/app/oracle/product/10.2.0/db_1/lib:/usr/local/lib:/usr/lib)

)

)

10. restart listener
11. create and test db link
12. repeat steps on another node if it is a cluster and test it on each node