How to integrate Freeradius with Oracle Database XE(18c)?
Mark J. Bobak
mark at bobak.net
Tue Mar 31 19:39:44 CEST 2020
Oracle guy here.
Alan is exactly correct. The error you're getting is from Oracle (ORA12154)
The error "could not resolve the connect identifier specified" means that
the client doesn't know how to talk to the Oracle server, based on what you
provided.
Oracle actually has a facility, 'oerr', that comes on all (non-XE?)
databases, helping with understanding and interpretation of Oracle error
messages. Here's the output for ORA-12154:
[oracle at miscr2 ~]$ oerr ora 12154
12154, 00000, "TNS:could not resolve the connect identifier specified"
// *Cause: A connection to a database or other service was requested using
// a connect identifier, and the connect identifier specified could not
// be resolved into a connect descriptor using one of the naming methods
// configured. For example, if the type of connect identifier used was a
// net service name then the net service name could not be found in a
// naming method repository, or the repository could not be
// located or reached.
// *Action:
// - If you are using local naming (TNSNAMES.ORA file):
// - Make sure that "TNSNAMES" is listed as one of the values of the
// NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA)
// - Verify that a TNSNAMES.ORA file exists and is in the proper
// directory and is accessible.
// - Check that the net service name used as the connect identifier
// exists in the TNSNAMES.ORA file.
// - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
// file. Look for unmatched parentheses or stray characters. Errors
// in a TNSNAMES.ORA file may make it unusable.
// - If you are using directory naming:
// - Verify that "LDAP" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Verify that the LDAP directory server is up and that it is
// accessible.
// - Verify that the net service name or database name used as the
// connect identifier is configured in the directory.
// - Verify that the default context being used is correct by
// specifying a fully qualified net service name or a full LDAP DN
// as the connect identifier
// - If you are using easy connect naming:
// - Verify that "EZCONNECT" is listed as one of the values of the
// NAMES.DIRETORY_PATH parameter in the Oracle Net profile
// (SQLNET.ORA).
// - Make sure the host, port and service name specified
// are correct.
// - Try enclosing the connect identifier in quote marks.
//
// See the Oracle Net Services Administrators Guide or the Oracle
// operating system specific guide for more information on naming.
In your case, it appears you're not using a TNSNAMES.ORA file or any kind
of directory. You seem to just have specified the information directly:
"DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=113.21.228.14)(PORT=1521))(CONNECT_DATA=(SID=XE)))"
This says "There's a database running on the host identified by
'113.21.228.14' and that host is running a listener process on port 1521.
That listener needs to know how to connect to the SID (system identifier)
named 'XE'."
So, the error, ORA-12154, is saying that some or all of the info you have
provided is incorrect. So, first, do you have radiusd and Oracle XE
running on the same server? Different servers? If the server is the same,
is 113.21.228.14 the IP address of said server? Make sure you can connect
to 113.21.228.14 on port 1521. (Try 'telnet 113.21.228.14 1521'. What
happens?) If you can't connect to that IP at that port, why not? If there
a firewall problem? If the DB and FR are on two different hosts ,you will
need to get communication between them working. If they are on the same
host, try uing '127.0.0.1' instead of '112.21.228.14'.
Hope that helps you on getting the connection working.
-Mark
On Tue, Mar 31, 2020 at 12:11 PM Sajib Nandi <sajib.aece at gmail.com> wrote:
> Thanks Alan Dekok for your replay.
> I am communicating with oracle expert.
>
> Thanks
> Nandi
>
>
> On Tue, Mar 31, 2020 at 6:32 PM Alan DeKok <aland at deployingradius.com>
> wrote:
>
> > On Mar 31, 2020, at 1:20 AM, Sajib Nandi <sajib.aece at gmail.com> wrote:
> > > I installed oracle-instantclient18.5
> > > yum localinstall oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm
> > > yum localinstall
> oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm
> > > yum localinstall oracle-instantclient18.5-devel-18.5.0.0.0-3.x86_64.rpm
> > >
> > > The various packages installed into subdirectories of
> > > /usr/lib/oracle
> > > /usr/include/oracle and
> > > /usr/share/oracle
> >
> > That's good.
> >
> > > I followed mentioned link for building the rlm_sql_oracl , I think this
> > > instraction not fully clear, I used below command for building the
> > > rlm_sql_oracle module intoFreeradius
> > > #cd
> > >
> >
> /usr/local/freeradius-server-3.0.20/src/modules/rlm_sql/drivers/rlm_sql_oracle
> > > #./configure --with-oracle-lib-dir=/usr/lib/oracle/18.5/client64/lib
> > > --with-oracle-include-dir=/usr/include/oracle/18.5/client64
> > > But make and make install command does not work
> > > I see there is no Makefile
> >
> > There should be a file 'src/modules/rlm_sql/drivers/rlm_sql_oracle/
> > all.mk
> >
> > You can edit this file directly if necessary.
> >
> > > 1. maybe I was wrong with building the rlm_sql_oracle module
> > > can you tell me how to build the rlm_sql_oracle module?
> >
> > That should work. And, see below...
> >
> > > 2. or I was wrong with oracle database connect:
> > > vi /usr/local/etc/raddb/mods-available/sql
> > > dialect = "oracle"
> > > driver = "rlm_sql_${dialect}"
> > > server = "radius/radius at 113.21.228.14:1521/xepdb1"
> > > radius_db =
> > >
> >
> "DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=113.21.228.14)(PORT=1521))(CONNECT_DATA=(SID=XE)))"
> > >
> > > is this right?
> > >
> > > when I go to debugging mode I got following Error
> > > radiusd -X
> > >
> > > ---------cut-----------
> > > ...
> > > rlm_sql (sql): Opening additional connection (0), 1 of 32 pending slots
> > used
> > > rlm_sql_oracle: Oracle logon failed: 'ORA-12154: TNS:could not resolve
> > the
> > > connect identifier specified '
> >
> > You build the rlm_sql_oracle module correctly. That's good.
> >
> > The issue now is Oracle. It doesn't like that "radius_db" string, *or*
> > something else. It's hard to say, as we're not Oracle people.
> >
> > A quick google search yields this, which might help:
> >
> > https://community.oracle.com/thread/3510766?start=0&tstart=0
> >
> > If it still fails after that, call Oracle. I presume you have
> support...
> >
> > Once rlm_sql_oracle is built and returning errors from Oracle, there's
> > little more we can do. The errors come from Oracle, not from FreeRADIUS.
> >
> > Alan DeKok.
> >
> >
> > -
> > List info/subscribe/unsubscribe? See
> > http://www.freeradius.org/list/users.html
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list