Hello all, I am trying to configure FreeRadius 3.0X with remote MSSQL and unixODBC, all basic tests have been passed sucefully. However, when I run radtest, on my radiusd server, I get the following error - [root@radius-1 ~]# radtest testnesma@1024.corp.nesma.net.sa 112233 localhost 0 testing123 Sent Access-Request Id 108 from 0.0.0.0:45102 to 127.0.0.1:1812 length 102 User-Name = "testnesma@1024.corp.nesma.net.sa" User-Password = "112233" NAS-IP-Address = 212.71.40.32 NAS-Port = 0 Message-Authenticator = 0x00 Cleartext-Password = "112233" Received Access-Reject Id 108 from 127.0.0.1:1812 to 127.0.0.1:45102 length 20 (0) -: Expected Access-Accept got Access-Reject Debug output. (0) Received Access-Request Id 108 from 127.0.0.1:45102 to 127.0.0.1:1812 length 102 (0) User-Name = "testnesma@1024.corp.nesma.net.sa" (0) User-Password = "112233" (0) NAS-IP-Address = 212.71.40.32 (0) NAS-Port = 0 (0) Message-Authenticator = 0xcae0ae6cc9464e071bedb1119c825542 (0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default (0) authorize { (0) policy filter_username { (0) if (&User-Name) { (0) if (&User-Name) -> TRUE (0) if (&User-Name) { (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@[^@]*@/ ) { (0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # if (&User-Name) = notfound (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) [chap] = noop (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: Looking up realm "1024.corp.nesma.net.sa" for User-Name = "testnesma@1024.corp.nesma.net.sa" (0) suffix: No such realm "1024.corp.nesma.net.sa" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) [files] = noop (0) sql: EXPAND %{User-Name} (0) sql: --> testnesma@1024.corp.nesma.net.sa (0) sql: SQL-User-Name set to 'testnesma@1024.corp.nesma.net.sa' rlm_sql (sql): Reserved connection (0) (0) sql: EXPAND SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = '%{User-Name}' ORDER BY id (0) sql: --> SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id (0) sql: Executing select query: SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id The 'rlm_sql_null' driver CANNOT be used for SELECTS. Please update the 'sql' module configuration to use a real database. Set 'driver = ...' to the database you want to use. (0) sql: WARNING: Cannot do check groups when group_membership_query is not set rlm_sql (sql): Released connection (0) Need 5 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (5), 1 of 27 pending slots used (0) [sql] = notfound (0) [expiration] = noop (0) [logintime] = noop (0) pap: WARNING: No "known good" password found for the user. Not setting Auth-Type (0) pap: WARNING: Authentication will fail unless a "known good" password is available (0) [pap] = noop (0) } # authorize = ok (0) ERROR: No Auth-Type found: rejecting the user via Post-Auth-Type = Reject (0) Failed to authenticate the user (0) Using Post-Auth-Type Reject (0) # Executing group from file /usr/local/etc/raddb/sites-enabled/default (0) Post-Auth-Type REJECT { (0) sql: EXPAND .query (0) sql: --> .query (0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (1) (0) sql: EXPAND %{User-Name} (0) sql: --> testnesma@1024.corp.nesma.net.sa (0) sql: SQL-User-Name set to 'testnesma@1024.corp.nesma.net.sa' (0) sql: EXPAND insert into RADONLINE (USERNAME,NASIDENTIFIER,NASPORT,ACCTSESSIONID,TIME_STAMP,FRAMEDIPADDRESS,NAS PORTTYPE,SERVICETYPE,CALLERID) values ('%{User-Name}','%{NAS-IP-Address}',%{NAS-Port},'%{Acct-Session-Id}',Cast ('%G' as datetime),'%{Framed-IP-Address}','%{NAS-Port-Type}','%{Framed-Protocol}','%{ Calling-Station-Id}') (0) sql: --> insert into RADONLINE (USERNAME,NASIDENTIFIER,NASPORT,ACCTSESSIONID,TIME_STAMP,FRAMEDIPADDRESS,NAS PORTTYPE,SERVICETYPE,CALLERID) values ('testnesma@1024.corp.nesma.net.sa','212.71.40.32',0,'',Cast ('39' as datetime),'','','','') (0) sql: Executing query: insert into RADONLINE (USERNAME,NASIDENTIFIER,NASPORT,ACCTSESSIONID,TIME_STAMP,FRAMEDIPADDRESS,NAS PORTTYPE,SERVICETYPE,CALLERID) values ('testnesma@1024.corp.nesma.net.sa','212.71.40.32',0,'',Cast ('39' as datetime),'','','','') (0) sql: SQL query returned: success (0) sql: 1 record(s) updated rlm_sql (sql): Released connection (1) (0) [sql] = ok (0) attr_filter.access_reject: EXPAND %{User-Name} (0) attr_filter.access_reject: --> testnesma@1024.corp.nesma.net.sa (0) attr_filter.access_reject: Matched entry DEFAULT at line 11 (0) [attr_filter.access_reject] = updated (0) [eap] = noop (0) policy remove_reply_message_if_eap { (0) if (&reply:EAP-Message && &reply:Reply-Message) { (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE (0) else { (0) [noop] = noop (0) } # else = noop (0) } # policy remove_reply_message_if_eap = noop (0) } # Post-Auth-Type REJECT = updated (0) Delaying response for 1.000000 seconds
On Sep 7, 2020, at 4:58 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
I am trying to configure FreeRadius 3.0X with remote MSSQL and unixODBC, all basic tests have been passed sucefully.
You need to configure the "sql" module, to set "dialect", and maybe "driver". This is documented in the comments in mods-available/sql.
However, when I run radtest, on my radiusd server, I get the following error ... [root@radius-1 ~]# radtest testnesma@1024.corp.nesma.net.sa 112233 localhost 0 testing123
We do NOT need to see the output of "radtest". This is documented everywhere.
... The 'rlm_sql_null' driver CANNOT be used for SELECTS.
Please update the 'sql' module configuration to use a real database.
Set 'driver = ...' to the database you want to use.
That seems pretty clear. It helps to read the comments in the module, and the debug messages that the server produces. Alan DeKok.
Thanks for your email. Based on the documentation, I updated the sql config file as below, please find the below uncommented lines only FYR. and refer to the attached full file sql. Thanks in advance for your kind assitnace in this. Specific section, Remote sqlserver/database information =============================================================== server = "RADIATOR" ### this is DNS as per the /etc/odbc.ini port = "1433" login = "nbauthuser" password = "nbauthuserpass" ======================================================================== ===================================================================================================================== [root@radius-1 mods-available]# grep -v '#' sql sql { mssql dialect = "mssql" rlm_sql_unixodbc driver = "rlm_sql_null" sqlite { filename = "/tmp/freeradius.db" busy_timeout = 200 bootstrap = "${modconfdir}/${..:name}/main/sqlite/schema.sql" } mysql { tls { ca_file = "/etc/ssl/certs/my_ca.crt" ca_path = "/etc/ssl/certs/" certificate_file = "/etc/ssl/certs/private/client.crt" private_key_file = "/etc/ssl/certs/private/client.key" cipher = "DHE-RSA-AES256-SHA:AES128-SHA" tls_required = yes tls_check_cert = no tls_check_cert_cn = no } warnings = auto } postgresql { send_application_name = yes } mongo { appname = "freeradius" tls { certificate_file = /path/to/file certificate_password = "password" ca_file = /path/to/file ca_dir = /path/to/directory crl_file = /path/to/file weak_cert_validation = false allow_invalid_hostname = false } } server = "RADIATOR" port = "1433" login = "nbauthuser" password = "nbauthuserpass" acct_table1 = "CM_WriteCDRnew" postauth_table = "RADONLINE" authcheck_table = "NB_Authentication_Nesma" authreply_table = "NB_Authentication_Nesma" delete_stale_sessions = yes pool { start = ${thread[pool].start_servers} min = ${thread[pool].min_spare_servers} max = ${thread[pool].max_servers} spare = ${thread[pool].max_spare_servers} uses = 0 retry_delay = 30 lifetime = 0 idle_timeout = 60 } client_table = "nas" group_attribute = "SQL-Group" $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf } -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Monday, September 7, 2020 2:57 PM To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: auth-type-error On Sep 7, 2020, at 4:58 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
I am trying to configure FreeRadius 3.0X with remote MSSQL and unixODBC, all basic tests have been passed sucefully.
You need to configure the "sql" module, to set "dialect", and maybe "driver". This is documented in the comments in mods-available/sql.
However, when I run radtest, on my radiusd server, I get the following error ... [root@radius-1 ~]# radtest testnesma@1024.corp.nesma.net.sa 112233 localhost 0 testing123
We do NOT need to see the output of "radtest". This is documented everywhere.
... The 'rlm_sql_null' driver CANNOT be used for SELECTS.
Please update the 'sql' module configuration to use a real database.
Set 'driver = ...' to the database you want to use.
That seems pretty clear. It helps to read the comments in the module, and the debug messages that the server produces. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 7, 2020, at 8:16 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
Based on the documentation, I updated the sql config file as below, please find the below uncommented lines only FYR. and refer to the attached full file sql.
No, don't do that. Read the documentation and follow the instructions. http://wiki.freeradius.org/list-help I told you to check "dialect" and "driver". You didn't do that. There's no point in asking questions if you're going to ignore the answers you get. Alan DeKok.
In addition to the below email. my odbc.ini and odbcinst.ini [root@radius-1 ~]# /etc/odbc.ini [RADIATOR] Driver = ODBC Driver 17 for SQL Server Server = nbdb.cyberia.net.sa Database = mydatabase ======================================== /etc/odbcinst.ini ==================================================== ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1 UsageCount=1 ===================================================== Test my DSN through isql. Works fine. [root@radius-1 ~]# isql -v RADIATOR nbauthuser nbauthuserpass +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> -----Original Message----- From: MEjaz [mailto:mejaz@cyberia.net.sa] Sent: Monday, September 7, 2020 3:17 PM To: 'FreeRadius users mailing list' <freeradius-users@lists.freeradius.org> Subject: RE: auth-type-error Thanks for your email. Based on the documentation, I updated the sql config file as below, please find the below uncommented lines only FYR. and refer to the attached full file sql. Thanks in advance for your kind assitnace in this. Specific section, Remote sqlserver/database information =============================================================== server = "RADIATOR" ### this is DNS as per the /etc/odbc.ini port = "1433" login = "nbauthuser" password = "nbauthuserpass" ======================================================================== ===================================================================================================================== [root@radius-1 mods-available]# grep -v '#' sql sql { mssql dialect = "mssql" rlm_sql_unixodbc driver = "rlm_sql_null" sqlite { filename = "/tmp/freeradius.db" busy_timeout = 200 bootstrap = "${modconfdir}/${..:name}/main/sqlite/schema.sql" } mysql { tls { ca_file = "/etc/ssl/certs/my_ca.crt" ca_path = "/etc/ssl/certs/" certificate_file = "/etc/ssl/certs/private/client.crt" private_key_file = "/etc/ssl/certs/private/client.key" cipher = "DHE-RSA-AES256-SHA:AES128-SHA" tls_required = yes tls_check_cert = no tls_check_cert_cn = no } warnings = auto } postgresql { send_application_name = yes } mongo { appname = "freeradius" tls { certificate_file = /path/to/file certificate_password = "password" ca_file = /path/to/file ca_dir = /path/to/directory crl_file = /path/to/file weak_cert_validation = false allow_invalid_hostname = false } } server = "RADIATOR" port = "1433" login = "nbauthuser" password = "nbauthuserpass" acct_table1 = "CM_WriteCDRnew" postauth_table = "RADONLINE" authcheck_table = "NB_Authentication_Nesma" authreply_table = "NB_Authentication_Nesma" delete_stale_sessions = yes pool { start = ${thread[pool].start_servers} min = ${thread[pool].min_spare_servers} max = ${thread[pool].max_servers} spare = ${thread[pool].max_spare_servers} uses = 0 retry_delay = 30 lifetime = 0 idle_timeout = 60 } client_table = "nas" group_attribute = "SQL-Group" $INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries.conf } -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Monday, September 7, 2020 2:57 PM To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: auth-type-error On Sep 7, 2020, at 4:58 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
I am trying to configure FreeRadius 3.0X with remote MSSQL and unixODBC, all basic tests have been passed sucefully.
You need to configure the "sql" module, to set "dialect", and maybe "driver". This is documented in the comments in mods-available/sql.
However, when I run radtest, on my radiusd server, I get the following error ... [root@radius-1 ~]# radtest testnesma@1024.corp.nesma.net.sa 112233 localhost 0 testing123
We do NOT need to see the output of "radtest". This is documented everywhere.
... The 'rlm_sql_null' driver CANNOT be used for SELECTS.
Please update the 'sql' module configuration to use a real database.
Set 'driver = ...' to the database you want to use.
That seems pretty clear. It helps to read the comments in the module, and the debug messages that the server produces. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Thanks for the information Thanks for your tips, Hello, As advised, I have tried set the MSSQL driver in SQL file but when I set this, ran into different problem with "could not link driver rlm_sql_mssqlo" Whereas I have successfully installed this MS-tools and I have made sure as below. [root@radius-1 ~]# rpm -qa | grep odbc msodbcsql17-17.6.1.1-1.x86_64 [root@radius-1 ~]# rpm -qa | grep -i unix unixODBC-devel-2.3.7-1.rh.x86_64 unixODBC-2.3.7-1.rh.x86_64 would this be enough or do we need to install any other dependencies. Please assist. ============================================================================================== Could not link driver rlm_sql_mssql: /usr/local/lib/rlm_sql_mssql.so: cannot open shared object file: No such file or directory Make sure it (and all its dependent libraries!) are in the search path of your system's ld /usr/local/etc/raddb/mods-enabled/sql[2]: Instantiation failed for module "sql" =========================================================== -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Monday, September 7, 2020 2:57 PM To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: auth-type-error On Sep 7, 2020, at 4:58 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
I am trying to configure FreeRadius 3.0X with remote MSSQL and unixODBC, all basic tests have been passed sucefully.
You need to configure the "sql" module, to set "dialect", and maybe "driver". This is documented in the comments in mods-available/sql.
However, when I run radtest, on my radiusd server, I get the following error ... [root@radius-1 ~]# radtest testnesma@1024.corp.nesma.net.sa 112233 localhost 0 testing123
We do NOT need to see the output of "radtest". This is documented everywhere.
... The 'rlm_sql_null' driver CANNOT be used for SELECTS.
Please update the 'sql' module configuration to use a real database.
Set 'driver = ...' to the database you want to use.
That seems pretty clear. It helps to read the comments in the module, and the debug messages that the server produces. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 7, 2020, at 10:47 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
As advised, I have tried set the MSSQL driver in SQL file but when I set this, ran into different problem with "could not link driver rlm_sql_mssqlo"
SYou need the correct driver installed. As I've said repeatedly, the comments in the sql module tell you exactly what to do. And no, the comments do *not* say "use driver rlm_sql_mssql". Instead, they give explicit instructions.
Whereas I have successfully installed this MS-tools and I have made sure as below.
[root@radius-1 ~]# rpm -qa | grep odbc msodbcsql17-17.6.1.1-1.x86_64 [root@radius-1 ~]# rpm -qa | grep -i unix unixODBC-devel-2.3.7-1.rh.x86_64 unixODBC-2.3.7-1.rh.x86_64
would this be enough or do we need to install any other dependencies. Please assist.
None of those options are an "rlm_sql" driver. You can tell, because they don't have "rlm_sql" in their name. You need to search your local system for the *correct* driver. Use the names which are in the "sql" module configuration. It really does help to READ THE DOCUMENTATION, and to READ THE COMMENTS IN THE CONFIGURATION FILES. Most of your questions are answered in the documentation and/or the configuration files. Just.... read them. Alan DeKok.
Dear alan, Thanks for putting me in the right direction. Yes, all the answers are available in the comment section, indeed great help however driver Problem is fixed after setting the right driver. Really sorry to bother you but one more issue below is in short attached is the full debug. Would be great help, if you can take look. 0) suffix: Checking for suffix after "@" (0) suffix: Looking up realm "1024.corp.nesma.net.sa" for User-Name = "testnesma@1024.corp.nesma.net.sa" (0) suffix: No such realm "1024.corp.nesma.net.sa" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop+ (0) [files] = noop (0) sql: EXPAND %{User-Name} (0) sql: --> testnesma@1024.corp.nesma.net.sa (0) sql: SQL-User-Name set to 'testnesma@1024.corp.nesma.net.sa' rlm_sql (sql): Reserved connection (0) (0) sql: EXPAND SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = '%{User-Name}' ORDER BY id (0) sql: --> SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id (0) sql: Executing select query: SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id rlm_sql_unixodbc: 42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'select'. (0) sql: ERROR: Unknown error (0) sql: ERROR: SQL query error getting reply attributes rlm_sql (sql): Released connection (0) -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Monday, September 7, 2020 6:58 PM To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: auth-type-error On Sep 7, 2020, at 10:47 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
As advised, I have tried set the MSSQL driver in SQL file but when I set this, ran into different problem with "could not link driver rlm_sql_mssqlo"
SYou need the correct driver installed. As I've said repeatedly, the comments in the sql module tell you exactly what to do. And no, the comments do *not* say "use driver rlm_sql_mssql". Instead, they give explicit instructions.
Whereas I have successfully installed this MS-tools and I have made sure as below.
[root@radius-1 ~]# rpm -qa | grep odbc msodbcsql17-17.6.1.1-1.x86_64 [root@radius-1 ~]# rpm -qa | grep -i unix unixODBC-devel-2.3.7-1.rh.x86_64 unixODBC-2.3.7-1.rh.x86_64
would this be enough or do we need to install any other dependencies. Please assist.
None of those options are an "rlm_sql" driver. You can tell, because they don't have "rlm_sql" in their name. You need to search your local system for the *correct* driver. Use the names which are in the "sql" module configuration. It really does help to READ THE DOCUMENTATION, and to READ THE COMMENTS IN THE CONFIGURATION FILES. Most of your questions are answered in the documentation and/or the configuration files. Just.... read them. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 7, 2020, at 2:01 PM, <mejaz@cyberia.net.sa> <mejaz@cyberia.net.sa> wrote:
Thanks for putting me in the right direction. Yes, all the answers are available in the comment section, indeed great help
however driver Problem is fixed after setting the right driver.
That's good...
Really sorry to bother you but one more issue below is in short attached is the full debug. Would be great help, if you can take look. ... (0) sql: EXPAND SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = '%{User-Name}' ORDER BY id (0) sql: --> SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id (0) sql: Executing select query: SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id rlm_sql_unixodbc: 42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'select'.
You've edited the default queries. The queries you've created are broken. Just... READ THEM. Even if you fix the query so that it has the correct syntax, you're going to have other problems. You can't just invent queries and expect FreeRADIUS to magically do what you want. Just use the default configuration. Use the default queries. Make SMALL changes. Test the changes. This is all documented. I have no idea why you're making this so complicated. You're just making random changes without paying attention to how anything works. All this does is break things, and annoy people. You are working HARD to make it complex, and to break things. Do less work. Do more thinking. Understand things before making changes. Alan DeKok.
Thank you so much for your guidance, I will go step by step for quries.conf. -----Original Message----- From: Freeradius-Users [mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Monday, September 7, 2020 9:25 PM To: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Subject: Re: auth-type-error On Sep 7, 2020, at 2:01 PM, <mejaz@cyberia.net.sa> <mejaz@cyberia.net.sa> wrote:
Thanks for putting me in the right direction. Yes, all the answers are available in the comment section, indeed great help
however driver Problem is fixed after setting the right driver.
That's good...
Really sorry to bother you but one more issue below is in short attached is the full debug. Would be great help, if you can take look. ... (0) sql: EXPAND SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = '%{User-Name}' ORDER BY id (0) sql: --> SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id (0) sql: Executing select query: SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id rlm_sql_unixodbc: 42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'select'.
You've edited the default queries. The queries you've created are broken. Just... READ THEM. Even if you fix the query so that it has the correct syntax, you're going to have other problems. You can't just invent queries and expect FreeRADIUS to magically do what you want. Just use the default configuration. Use the default queries. Make SMALL changes. Test the changes. This is all documented. I have no idea why you're making this so complicated. You're just making random changes without paying attention to how anything works. All this does is break things, and annoy people. You are working HARD to make it complex, and to break things. Do less work. Do more thinking. Understand things before making changes. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi, alaan. So far I have successfully achieved the below request type through radpingutility * Authentication * Accounting start * Accounting stop Now only remaining is to set default simultaneous-use to 1. As i want to limit user's behavior, such as a username can login only once at the same time... But got error as “rlm_sql_unixodbc: 22002 [Microsoft][ODBC Driver 17 for SQL Server]Indicator variable required but not supplied (2) sql: ERROR: Error fetching row” debug is attached fyi. 1、modifiy default and inner-tunnel in # Session database, used for checking Simultaneous-Use. Either the radutmp # or rlm_sql module can handle this. # The rlm_sql module is *much* faster session { #radutmp # See "Simultaneous Use Checking Querie" in sql.conf sql } 2、modify queris.coonf in /usr/local/etc/raddb/mods-config/sql/main/mssql, below statment i have copeied from /usr/local/etc/raddb/mods-config/sql/main/mysql/quries.conf, as it was not exists by default under the msssql/queries.coonf # added this simul_count_query to enable simultaneous use checking ####################################################################### # Simultaneous Use Checking Queries ####################################################################### # simul_count_query - query for the number of current connections # - If this is not defined, no simultaneous use checking # - will be performed by this module instance # simul_verify_query - query to return details of current connections # for verification # - Leave blank or commented out to disable verification step # - Note that the returned field order should not be changed. ####################################################################### simul_count_query = "\ SELECT COUNT(*) \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL" simul_verify_query = "\ SELECT \ radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, \ callingstationid, framedprotocol \ FROM ${acct_table1} \ WHERE username = '%{SQL-User-Name}' \ AND acctstoptime IS NULL" 3、add a entry to the table radgorucheck INSERT INTO `radgroupcheck` ( `id` , `GroupName` , `Attribute` , `op` , `Value` ) VALUES ( NULL , ’user’, ’Simultaneous-Use’, ’:=’, ’1’ ); 4. update the user "test" to the group "user" -----Original Message----- From: mejaz@cyberia.net.sa [mailto:mejaz@cyberia.net.sa] Sent: Monday, September 7, 2020 9:02 PM To: 'FreeRadius users mailing list' <freeradius-users@lists.freeradius.org> Subject: RE: auth-type-error Dear alan, Thanks for putting me in the right direction. Yes, all the answers are available in the comment section, indeed great help however driver Problem is fixed after setting the right driver. Really sorry to bother you but one more issue below is in short attached is the full debug. Would be great help, if you can take look. 0) suffix: Checking for suffix after "@" (0) suffix: Looking up realm "1024.corp.nesma.net.sa" for User-Name = " <mailto:testnesma@1024.corp.nesma.net.sa> testnesma@1024.corp.nesma.net.sa" (0) suffix: No such realm "1024.corp.nesma.net.sa" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop+ (0) [files] = noop (0) sql: EXPAND %{User-Name} (0) sql: --> <mailto:testnesma@1024.corp.nesma.net.sa> testnesma@1024.corp.nesma.net.sa (0) sql: SQL-User-Name set to 'testnesma@1024.corp.nesma.net.sa' rlm_sql (sql): Reserved connection (0) (0) sql: EXPAND SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = '%{User-Name}' ORDER BY id (0) sql: --> SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id (0) sql: Executing select query: SELECT select password, replyattr, checkattr FROM NB_Authentication_Nesma WHERE Username = 'testnesma@1024.corp.nesma.net.sa' ORDER BY id rlm_sql_unixodbc: 42000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'select'. (0) sql: ERROR: Unknown error (0) sql: ERROR: SQL query error getting reply attributes rlm_sql (sql): Released connection (0) -----Original Message----- From: Freeradius-Users [ <mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org> mailto:freeradius-users-bounces+mejaz=cyberia.net.sa@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Monday, September 7, 2020 6:58 PM To: FreeRadius users mailing list < <mailto:freeradius-users@lists.freeradius.org> freeradius-users@lists.freeradius.org> Subject: Re: auth-type-error On Sep 7, 2020, at 10:47 AM, MEjaz < <mailto:mejaz@cyberia.net.sa> mejaz@cyberia.net.sa> wrote:
As advised, I have tried set the MSSQL driver in SQL file but when I set this, ran into different problem with "could not link driver rlm_sql_mssqlo"
SYou need the correct driver installed. As I've said repeatedly, the comments in the sql module tell you exactly what to do. And no, the comments do *not* say "use driver rlm_sql_mssql". Instead, they give explicit instructions.
Whereas I have successfully installed this MS-tools and I have made sure as below.
[root@radius-1 ~]# rpm -qa | grep odbc
msodbcsql17-17.6.1.1-1.x86_64
[root@radius-1 ~]# rpm -qa | grep -i unix
unixODBC-devel-2.3.7-1.rh.x86_64
unixODBC-2.3.7-1.rh.x86_64
would this be enough or do we need to install any other dependencies. Please assist.
None of those options are an "rlm_sql" driver. You can tell, because they don't have "rlm_sql" in their name. You need to search your local system for the *correct* driver. Use the names which are in the "sql" module configuration. It really does help to READ THE DOCUMENTATION, and to READ THE COMMENTS IN THE CONFIGURATION FILES. Most of your questions are answered in the documentation and/or the configuration files. Just.... read them. Alan DeKok. - List info/subscribe/unsubscribe? See <http://www.freeradius.org/list/users.html> http://www.freeradius.org/list/users.html
On Sep 20, 2020, at 11:33 AM, MEjaz <mejaz@cyberia.net.sa> wrote:
So far I have successfully achieved the below request type through radpingutility
* Authentication * Accounting start * Accounting stop
That's good.
Now only remaining is to set default simultaneous-use to 1. As i want to limit user's behavior, such as a username can login only once at the same time...
But got error as “rlm_sql_unixodbc: 22002 [Microsoft][ODBC Driver 17 for SQL Server]Indicator variable required but not supplied (2) sql: ERROR: Error fetching row” debug is attached fyi.
Ok...
1、modifiy default and inner-tunnel in
For pretty much the last time... we do NOT need to see config files. If I haven't gotten that point across before, please pay attention now. If you keep posting config files when told not to, I will just start ignoring your messages.
2、modify queris.coonf in /usr/local/etc/raddb/mods-config/sql/main/mssql,
below statment i have copeied from /usr/local/etc/raddb/mods-config/sql/main/mysql/quries.conf, as it was not exists by default under the msssql/queries.coonf
Don't do that. The databases have different syntaxes. You *cannot* just copy queries from one config file to another one. There's a *reason* that the configuration files are different. Go fix the queries to be correct for MSSQL. How? I don't know. I don't run MSSQL. Alan DeKok.
participants (3)
-
Alan DeKok -
MEjaz -
mejaz＠cyberia.net.sa