Freeradius 3.0.17 Unable to connect mssql database with iodbc

[Giovanni Di Giorgio]

Hi, i'm trying to work with freeradius (ver 3.0.17 on ubuntu server 16.04)
and a mssql db as backend, using odbc drivers.

I've installed odbc driver from:
https://docs.microsoft.com/it-it/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

my odbc.ini:
"
[test]
Driver = ODBC Driver 17 for SQL Server
Database = radius
Server = 10.196.200.74
"

connection test:

"
***@radius:~# isql -v test sa ************
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
"
then i setup the database schema on mssql and i setup the freeradius
config's files:

my /etc/freeradius/mods-enabled/sql:

"
sql {
driver = "rlm_sql_iodbc"
dialect = "mssql"
server = "test"
login = "sa"
password = "*********"
radius_db = "radius"
acct_table1 = "radacct"
..........

"

but if i try to start freeradius in debug mode, the attempt to connect to
database fail and freeradius do not start

"
***@radius:~# freeradius -X
FreeRADIUS Version 3.0.17
...
# Loaded module rlm_sql
# Loading module "sql" from file /etc/freeradius/mods-enabled/sql
sql {
driver = "rlm_sql_iodbc"
server = "test"
port = 1433
login = "sa"
password = <<< secret >>>
radius_db = "radius"
read_groups = yes
read_profiles = yes
read_clients = no
delete_stale_sessions = yes
sql_user_name = "%{User-Name}"
default_user_profile = ""
client_query = "SELECT id,nasname,shortname,type,secret FROM nas"
........
rlm_sql (sql): Driver rlm_sql_iodbc (module rlm_sql_iodbc) loaded
and linked
........
# Instantiating module "sql" from file
/etc/freeradius/mods-enabled/sql
rlm_sql (sql): Attempting to connect to database "radius"
rlm_sql (sql): Initialising connection pool
pool {
start = 5
min = 3
max = 32
spare = 10
uses = 0
lifetime = 0
cleanup_interval = 30
idle_timeout = 60
retry_delay = 30
spread = no
}
rlm_sql (sql): Opening additional connection (0), 1 of 32 pending
slots used
rlm_sql_iodbc: SQLConnectfailed
"
Could you please help how to fix the problem?
Thanks in advance.
Gioele
-
List info/subscribe/unsubscribe? See http://www.freera

[Alan Buxey]

Hi

You've defined an IP address in the ini file ... In FR you've just got the
name 'test'. C and n your server resolve that to an IP address eg can you
'dig test' (or 'ping test' and see an IP address that matches your initial
file)?

Alan

Post by Giovanni Di Giorgio
Hi, i'm trying to work with freeradius (ver 3.0.17 on ubuntu server 16.04)
and a mssql db as backend, using odbc drivers.
https://docs.microsoft.com/it-it/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server
"
[test]
Driver = ODBC Driver 17 for SQL Server
Database = radius
Server = 10.196.200.74
"
"
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
"
then i setup the database schema on mssql and i setup the freeradius
"
sql {
driver = "rlm_sql_iodbc"
dialect = "mssql"
server = "test"
login = "sa"
password = "*********"
radius_db = "radius"
acct_table1 = "radacct"
..........
"
but if i try to start freeradius in debug mode, the attempt to connect to
database fail and freeradius do not start
"
FreeRADIUS Version 3.0.17
...
# Loaded module rlm_sql
# Loading module "sql" from file /etc/freeradius/mods-enabled/sql
sql {
driver = "rlm_sql_iodbc"
server = "test"
port = 1433
login = "sa"
password = <<< secret >>>
radius_db = "radius"
read_groups = yes
read_profiles = yes
read_clients = no
delete_stale_sessions = yes
sql_user_name = "%{User-Name}"
default_user_profile = ""
client_query = "SELECT id,nasname,shortname,type,secret FROM nas"
........
rlm_sql (sql): Driver rlm_sql_iodbc (module rlm_sql_iodbc) loaded
and linked
........
# Instantiating module "sql" from file
/etc/freeradius/mods-enabled/sql
rlm_sql (sql): Attempting to connect to database "radius"
rlm_sql (sql): Initialising connection pool
pool {
start = 5
min = 3
max = 32
spare = 10
uses = 0
lifetime = 0
cleanup_interval = 30
idle_timeout = 60
retry_delay = 30
spread = no
}
rlm_sql (sql): Opening additional connection (0), 1 of 32 pending
slots used
rlm_sql_iodbc: SQLConnectfailed
"
Could you please help how to fix the problem?
Thanks in advance.
Gioele
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? Se

[Giovanni Di Giorgio]

Hi,

Post by Alan Buxey
You've defined an IP address in the ini file ... In FR you've just got the
name 'test'. C and n your server resolve that to an IP address eg can you
'dig test' (or 'ping test' and see an IP address that matches your initial
file)?

"test" is the name of the data source, not the hostname of the mssql server
and I think that freeradius uses this reference at startup, if the "iodbc"
driver has been set in the "sql" configuration file. Infact if i set ,in
the "server" parameter, any value other than "test" I get:
"
rlm_sql_iodbc: SQLConnectfailed
rlm_sql_iodbc: IM002: [iODBC] [Driver Manager] Data source name not found
and no default driver specified. Driver could not be loaded
"
However I tried to set up the server so that it could resolve the hostname
"test" with the ip of the mssql server, but nothing has changed.

I checked with tcpdump.
During the startup of freeradius (freeradius -X), no connection attempt
from radius server to mssql server appears, .
If I use the command:
isql -v test sa *****
I can see connection from radius server to mssql serve on port 1433.

Thanks.
G.
-
List info/subscribe/unsubscribe? See

[Alan DeKok]

Post by Giovanni Di Giorgio
"test" is the name of the data source, not the hostname of the mssql server
and I think that freeradius uses this reference at startup, if the "iodbc"
driver has been set in the "sql" configuration file. Infact if i set ,in
"
rlm_sql_iodbc: SQLConnectfailed
rlm_sql_iodbc: IM002: [iODBC] [Driver Manager] Data source name not found
and no default driver specified. Driver could not be loaded
"

That's an error from the iODBC library. It means that you've misconfigured the data it needs.

Just typing that error into Google gets a FAQ:

http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/FAQ#%5BiODBC%5D%5BDriver%20Manager%5DData%20source%20name%20not%20found%20and%20no%20default%20driver%20specified.%20Driver%20could%20not%20be%20loaded%2C%20SQLSTATE%3DIM002

Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.htm