How to integrate Freeradius with Oracle Database XE(18c)?
Peter Lambrechtsen
peter at crypt.nz
Tue Mar 31 22:50:08 CEST 2020
I would think your connect information is wrong.
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)))"
Should be more like
server = "113.21.228.14"
port = 1521
login = "radius"
password = "radius"
# If you are using Oracle then use this instead
radius_db =
"DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=113.21.228.14)(PORT=1521))(CONNECT_DATA=(SID=XE)))"
Is your Database SID "XE" or "XEPDB1" as your "server" looks wrong to me
and should only be an IP address rather than a thin connect URL. As for the
"radius" user it will use the default schema associated to that user.
On Wed, Apr 1, 2020 at 6:41 AM Mark J. Bobak <mark at bobak.net> wrote:
> 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
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list