Hi all, I have been experimenting with Microsoft SQL Server as a replacement for our current mysql servers [1]. I installed Microsoft's own "ODBC Driver 17 for SQL Server" and have tested against both SQL Server running on the same Linux machine as Freeradius itself, and SQL Server running on a Windows machine. In both cases I found the same (what appears to be a) bug, which I will describe below for the benefit of the archives, even though it is (probably) not a problem with freeradius. Because people hitting this are very likely to be searching with terms including "freeradius". Also there is a chance that it is somehow specific to the interaction with freeradius. Because it is hard to imagine even Microsoft releasing something with such a glaring fault. I'll also give a work-around. I think I will now try iodbc instead - anybody here used it? Right, the bug. Here's my custom attribute: ATTRIBUTE My-Int 3004 integer Here's part of the post-auth section in the "outer" site: update control { My-Int := "%{sql:SELECT 9999 }" My-Int := "%{sql:SELECT 10000 }" My-Int := "%{sql:SELECT '10000' }" Here's debug output: EXPAND %{sql:SELECT 9999 } --> 9999 My-Int := 9999 Executing select query: SELECT 10000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range ERROR: Error fetching row ERROR: Unknown error ERROR: SQL query failed: <INVALID> EXPAND %{sql:SELECT 10000 } --> My-Int := 0 Executing select query: SELECT '10000' EXPAND %{sql:SELECT '10000' } --> 10000 My-Int := 10000 As you can see, if an integer is more than four digits long, the driver throws an error. But if you return the number as a string, it works fine. I'd love to see the source code for this... [1] Everything else is SQL Server and our current integration between the two databases is rather clunky; a homogeneous environment makes sense, whatever I personally think of Microsoft <grin>.
On Jun 21, 2019, at 8:30 AM, Dom Latter <freeradius-users@latter.org> wrote:
I think I will now try iodbc instead - anybody here used it?
Occasionally, but not recently.
Executing select query: SELECT 10000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
That's ridiculous. Maybe if it was 256, or 2^16 or 2^31. But a 5-digit number? WTF. Alan DeKok.
On 21/06/2019 15:00, Alan DeKok wrote:
On Jun 21, 2019, at 8:30 AM, Dom Latter <freeradius-users@latter.org> wrote:
I think I will now try iodbc instead - anybody here used it?
Occasionally, but not recently.
Executing select query: SELECT 10000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
That's ridiculous.
Maybe if it was 256, or 2^16 or 2^31. But a 5-digit number? WTF.
Indeed. But four digits = four 8 byte characters = 32 bits. And five makes 40 bits. Hence by some amazingly flawed logic, 9999 is fine, and 10000 is "too big to go in an int". And that is the only plausible explanation I can think of. As I said, I would love to see the source code behind this...
On 21-06-19 16:27, Dom Latter wrote:
Maybe if it was 256, or 2^16 or 2^31. But a 5-digit number? WTF.
Indeed. But four digits = four 8 byte characters = 32 bits.
And five makes 40 bits. Hence by some amazingly flawed logic, 9999 is fine, and 10000 is "too big to go in an int".
And that is the only plausible explanation I can think of.
As I said, I would love to see the source code behind this...
Out of curiosity, does -999 work, and does -1000 fail? -- Herwin Weststrate
On 21/06/2019 15:29, Herwin Weststrate wrote:
On 21-06-19 16:27, Dom Latter wrote:
Maybe if it was 256, or 2^16 or 2^31. But a 5-digit number? WTF.
Indeed. But four digits = four 8 byte characters = 32 bits.
And five makes 40 bits. Hence by some amazingly flawed logic, 9999 is fine, and 10000 is "too big to go in an int".
And that is the only plausible explanation I can think of.
As I said, I would love to see the source code behind this...
Out of curiosity, does -999 work, and does -1000 fail?
Umm, I don't know, and I have uninstalled the Microsoft driver while I give iodbc a go - if /when I go back to it I will run that test.
On 21/06/2019 15:29, Herwin Weststrate wrote:
Out of curiosity, does -999 work, and does -1000 fail?
Yes: (9) Executing select query: SELECT -999 (9) EXPAND %{sql:SELECT -999 } (9) --> -999 (9) My-Int := 4294966297 (9) Executing select query: SELECT -1000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range (9) ERROR: Error fetching row (9) ERROR: Unknown error (9) ERROR: SQL query failed: <INVALID> (9) EXPAND %{sql:SELECT -1000 } (9) --> (9) My-Int := 0
Last time I saw this kind of behaviour it was down the 'SQL' err great decisions on how to assign a type to literals. Normally it can use the type of a field in a table to get it right but here.... not so much. I suspect the ODBC library might have an API to define what that default type is and that is something daft unless otherwise told. On 25/06/2019, 14:14, "Freeradius-Users on behalf of Dom Latter" <freeradius-users-bounces+alister.winfield=sky.uk@lists.freeradius.org on behalf of freeradius-users@latter.org> wrote: On 21/06/2019 15:29, Herwin Weststrate wrote: > > Out of curiosity, does -999 work, and does -1000 fail? Yes: (9) Executing select query: SELECT -999 (9) EXPAND %{sql:SELECT -999 } (9) --> -999 (9) My-Int := 4294966297 (9) Executing select query: SELECT -1000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range (9) ERROR: Error fetching row (9) ERROR: Unknown error (9) ERROR: SQL query failed: <INVALID> (9) EXPAND %{sql:SELECT -1000 } (9) --> (9) My-Int := 0 - List info/subscribe/unsubscribe? See https://eur01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.freeradius.org%2Flist%2Fusers.html&data=02%7C01%7Calister.winfield%40sky.uk%7C591e8afef22e4842cbd908d6f96f0339%7C68b865d5cf184b2b82a4a4eddb9c5237%7C0%7C1%7C636970652526764199&sdata=GsvKBvLsM6vLUXu%2Buzoqu%2BDfvlwdrqhYQp6fXzxWSWc%3D&reserved=0 -------------------------------------------------------------------- This email is from an external source. Please do not open attachments or click links from an unknown or suspicious origin. Phishing attempts can be reported by sending them to phishing@sky.uk as attachments. Thank you -------------------------------------------------------------------- Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trademarks of Sky Limited and Sky International AG and are used under licence. Sky UK Limited (Registration No. 2906991), Sky-In-Home Service Limited (Registration No. 2067075), Sky Subscribers Services Limited (Registration No. 2340150) and Sky CP Limited (Registration No. 9513259) are direct or indirect subsidiaries of Sky Limited (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD
On 25/06/2019 16:11, Winfield, Alister via Freeradius-Users wrote:
Last time I saw this kind of behaviour it was down the 'SQL' err great decisions on how to assign a type to literals. Normally it can use the type of a field in a table to get it right but here.... not so much. I suspect the ODBC library might have an API to define what that default type is and that is something daft unless otherwise told.
If I have understood you correctly: These are simplified example cases; this first reared its head when selecting int fields from tables. By casting the "int" to a "varchar" I have a work-around but this is not a satisfactory fix. The other option is I believe using freetds - does anybody have a comment on the relative merits?
On 25/06/2019 14:13, Dom Latter wrote:
(9) Executing select query: SELECT -1000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
I have tried using the sqlcmd tool; no such behaviour, as below. Anybody got a suggestion for something else I can use to test the driver? # sqlcmd -D -S BOGUS -U user -P password Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Data source name not found and no default driver specified. # sqlcmd -D -S REALSERVER -U user -P password 1> SELECT 10348712049871230498123740291387 2> go ---------------------------------- 10348712049871230498123740291387 (1 rows affected) 1> SELECT 1034871204987123049812374029138762398471629384612934871236948712693487126934871263948712364982376129784 2> go Msg 1007, Level 15, State 1, Server CN3-RAD-AUTH-DE, Line 1 The number '1034871204987123049812374029138762398471629384612934871236948712693487126934871263948712364982376129784' is out of the range for numeric representation (maximum precision 38).
On 25/06/2019 16:16, Dom Latter wrote:
On 25/06/2019 14:13, Dom Latter wrote:
(9) Executing select query: SELECT -1000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
I have tried using the sqlcmd tool; no such behaviour, as below.
Anybody got a suggestion for something else I can use to test the driver?
I found / modified a small python script as below; again, no problem with "large" integers... So that's *two* other programs / systems that do not object to five digit numbers. server = 'tcp:127.0.0.1' conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password) cursor = conn.cursor() cursor.execute('SELECT 1000000+234')
On 02/07/2019 10:46, Dom Latter wrote:
I found / modified a small python script as below; again, no problem with "large" integers...
And again, with PHP, "SELECT 123456789" works fine but a large number gets an error from the ODBC driver. SQLSTATE: 22003 Code: 1007 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The number '1000430295872340598237059823475034928750239875023948572309485' is out of the range for numeric representation (maximum precision 38).
On Jul 2, 2019, at 12:18 PM, Dom Latter <freeradius-users@latter.org> wrote:
On 02/07/2019 10:46, Dom Latter wrote:
I found / modified a small python script as below; again, no problem with "large" integers...
And again, with PHP, "SELECT 123456789" works fine but a large number gets an error from the ODBC driver.
SQLSTATE: 22003 Code: 1007 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The number '1000430295872340598237059823475034928750239875023948572309485' is out of the range for numeric representation (maximum precision 38).
That's all well and good, but what should *we* be doing differently? We're not Microsoft experts, or experts in ODBC. The ODBC layer was contributed by someone years ago, and we've maintained it since then. It mostly works, but new features require people who can delve into it and fix things. Alan DeKok.
On 02/07/2019 16:10, Alan DeKok wrote:
On Jul 2, 2019, at 12:18 PM, Dom Latter <freeradius-users@latter.org> wrote:
And again, with PHP, "SELECT 123456789" works fine but a large number gets an error from the ODBC driver. <snip>
That's all well and good, but what should *we* be doing differently?
Well, if I knew that...
We're not Microsoft experts, or experts in ODBC. The ODBC layer was contributed by someone years ago, and we've maintained it since then. It mostly works, but new features require people who can delve into it and fix things.
And that is what I am trying to do. I am looking at the following in rlm_sql_unixodbc.c /* Executing query */ { SQLCHAR *odbc_query; memcpy(&odbc_query, &query, sizeof(odbc_query)); err_handle = SQLExecDirect(conn->stmt, odbc_query, strlen(query)); } Is that a safe memcpy? It's a long time since I programmed in C... In any case probably not relevant to my immediate problem.
On 2 Jul 2019, at 12:21, Dom Latter <freeradius-users@latter.org> wrote:
On 02/07/2019 16:10, Alan DeKok wrote:
On Jul 2, 2019, at 12:18 PM, Dom Latter <freeradius-users@latter.org> wrote:
And again, with PHP, "SELECT 123456789" works fine but a large number gets an error from the ODBC driver. <snip> That's all well and good, but what should *we* be doing differently?
Well, if I knew that...
We're not Microsoft experts, or experts in ODBC. The ODBC layer was contributed by someone years ago, and we've maintained it since then. It mostly works, but new features require people who can delve into it and fix things.
And that is what I am trying to do. I am looking at the following in rlm_sql_unixodbc.c
/* Executing query */ { SQLCHAR *odbc_query;
memcpy(&odbc_query, &query, sizeof(odbc_query)); err_handle = SQLExecDirect(conn->stmt, odbc_query, strlen(query)); }
Is that a safe memcpy? It's a long time since I programmed in C...
It's likely copying a pointer from query to odbc_query to defeat const checks, because SQLExecDirect should likely take a const (read only) query string pointer, and doesn't. So yes... It's fine. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
On 2 Jul 2019, at 13:16, Arran Cudbard-Bell <a.cudbardb@freeradius.org> wrote:
On 2 Jul 2019, at 12:21, Dom Latter <freeradius-users@latter.org> wrote:
On 02/07/2019 16:10, Alan DeKok wrote:
On Jul 2, 2019, at 12:18 PM, Dom Latter <freeradius-users@latter.org> wrote:
And again, with PHP, "SELECT 123456789" works fine but a large number gets an error from the ODBC driver. <snip> That's all well and good, but what should *we* be doing differently?
Well, if I knew that...
We're not Microsoft experts, or experts in ODBC. The ODBC layer was contributed by someone years ago, and we've maintained it since then. It mostly works, but new features require people who can delve into it and fix things.
And that is what I am trying to do. I am looking at the following in rlm_sql_unixodbc.c
/* Executing query */ { SQLCHAR *odbc_query;
memcpy(&odbc_query, &query, sizeof(odbc_query)); err_handle = SQLExecDirect(conn->stmt, odbc_query, strlen(query)); }
Is that a safe memcpy? It's a long time since I programmed in C...
It's likely copying a pointer from query to odbc_query to defeat const checks, because SQLExecDirect should likely take a const (read only) query string pointer, and doesn't.
So yes... It's fine.
If you wanted to double check what was being passed, either break at that line in a debugger, or add: ERROR("Query executed: %s", (char *)odbc_query); somewhere after the memcpy, and it'll print out the query string in red. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
On Jul 2, 2019, at 6:21 PM, Dom Latter <freeradius-users@latter.org> wrote:
That's all well and good, but what should *we* be doing differently?
Well, if I knew that...
That is the problem. As background, the unfortunate thing is that the FreeRADIUS core team is pretty darn small (less than 5, by a lot.) And we're expected to be experts in: * RADIUS * TLS / SSL * certificates * CRLs * EAP * all EAP types * LDAP * SQL * Postgresql * MySQL * ODBC * ... * Operating systems * Linux * *BSD * OSX * etc. The list of overall technologies is pretty large. I can't think of another project which has (a) so few core members, (b) is so critical for so may people, and (c) has so many technologies behind it. Alan DeKok.
On 03/07/2019 07:53, Alan DeKok wrote:
On Jul 2, 2019, at 6:21 PM, Dom Latter <freeradius-users@latter.org> wrote:
That's all well and good, but what should *we* be doing differently?
Well, if I knew that...
That is the problem.
As background, the unfortunate thing is that the FreeRADIUS core team is pretty darn small (less than 5, by a lot.) And we're expected to be experts in:
* RADIUS
<snip> Yes, it's a lot of stuff. Anyway, I have run ltrace to grab calls to the MS ODBC driver, from both freeradius and a small python test script (which doesn't exhibit this weird bug). These are the calls (sorted alphabetically) that are used by Freeradius: SQLAllocHandle SQLBindCol SQLDisconnect SQLFetch SQLFreeStmt SQLNumResultCols SQLSetEnvAttr And these are the ones used by Python: SQLAllocHandle SQLDescribeColW SQLDisconnect SQLEndTran SQLExecDirectW SQLFetch SQLFreeHandle SQLFreeStmt SQLGetData SQLNumResultCols SQLRowCount SQLSetConnectAttr What is noticeably absent from the freeradius output is SQLExecDirect (the 'W' in the Python output just means it's Unicode); this despite that I have put debug output immediately before and after the line in the FR module that says: err_handle = SQLExecDirect(conn->stmt, odbc_query, strlen(query)); so I know it is doing it... or trying to... or something.
> On 4 Jul 2019, at 11:10, Dom Latter <freeradius-users@latter.org> wrote: > > > On 03/07/2019 07:53, Alan DeKok wrote: >> On Jul 2, 2019, at 6:21 PM, Dom Latter <freeradius-users@latter.org> wrote: >>>> That's all well and good, but what should *we* be doing differently? >>> >>> Well, if I knew that... >> That is the problem. >> As background, the unfortunate thing is that the FreeRADIUS core team is pretty darn small (less than 5, by a lot.) And we're expected to be experts in: >> * RADIUS > > > <snip> > > Yes, it's a lot of stuff. > > Anyway, I have run ltrace to grab calls to the MS ODBC driver, from both > freeradius and a small python test script (which doesn't exhibit this > weird bug). > > These are the calls (sorted alphabetically) that are used by Freeradius: > > SQLAllocHandle > SQLBindCol > SQLDisconnect > SQLFetch > SQLFreeStmt > SQLNumResultCols > SQLSetEnvAttr > > > And these are the ones used by Python: > > SQLAllocHandle > SQLDescribeColW > SQLDisconnect > SQLEndTran > SQLExecDirectW > SQLFetch > SQLFreeHandle > SQLFreeStmt > SQLGetData > SQLNumResultCols > SQLRowCount > SQLSetConnectAttr > Could we get the calls listed chronologically. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
On 04/07/2019 19:21, Arran Cudbard-Bell wrote:
Could we get the calls listed chronologically.
All from pyodbc.cpython-35m-x86_64-linux-gnu.so: SQLSetConnectAttr(0x16ae950, 102, 0, 0xfffffffb) = 0 SQLAllocHandle(3, 0x16ae950, 0x7ffe19138f70, 0) = 0 SQLFetch(0x1760e60, 0x1760e60, 0, 0) = 0 SQLGetData(0x1760e60, 3, 4, 0x7ffe19138f60) = 0 SQLFreeStmt(0x1760e60, 0, 0, 0) = 0 SQLFreeHandle(3, 0x1760e60, 0, 0) = 0 SQLAllocHandle(3, 0x16ae950, 0x7ffe19138f78, 0) = 0 SQLFetch(0x1760e60, 0x1760e60, 0, 0) = 0 SQLGetData(0x1760e60, 3, 4, 0x7ffe19138f64) = 0 SQLFreeStmt(0x1760e60, 0, 0, 0) = 0 SQLFreeHandle(3, 0x1760e60, 0, 0) = 0 SQLAllocHandle(3, 0x16ae950, 0x7ffe19138f80, 0) = 0 SQLFetch(0x1760e60, 0x1760e60, 0, 0) = 0 SQLGetData(0x1760e60, 3, 4, 0x7ffe19138f68) = 0 SQLFreeStmt(0x1760e60, 0, 0, 0) = 0 SQLFreeHandle(3, 0x1760e60, 0, 0) = 0 SQLAllocHandle(3, 0x16ae950, 0x7ffe19138f88, 0) = 0 SQLFetch(0x1760e60, 0x1760e60, 0, 0) = 0 SQLGetData(0x1760e60, 3, 4, 0x7ffe19138f6c) = 0 SQLFreeStmt(0x1760e60, 0, 0, 0) = 0 SQLFreeHandle(3, 0x1760e60, 0, 0) = 0 SQLAllocHandle(3, 0x16ae950, 0x7fc6d56fe570, 119) = 0 SQLFreeStmt(0x1760e60, 0, 0xffffffff, 0) = 0 SQLExecDirectW(0x1760e60, 0x7fc6d5712a90, 13, 2) = 0 SQLRowCount(0x1760e60, 0x7ffe19139140, 0xffffffff, 0x7fc6d56e6000) = 0 SQLNumResultCols(0x1760e60, 0x7ffe19139138, 0xffffffff, 0) = 0 SQLDescribeColW(0x1760e60, 1, 0x7ffe19138e70, 300) = 0 SQLFetch(0x1760e60, 23, 0xffffffff, 0x7fc6d56fe558) = 0 SQLGetData(0x1760e60, 1, 4, 0x7ffe19139138) = 0 SQLFetch(0x1760e60, 23, 0xffffffff, 0x7fc6d56fe558) = 100 SQLFreeStmt(0x1760e60, 0, 0xffffffff, 0x7fc6d7cc9000) = 0 SQLFreeHandle(3, 0x1760e60, 0xffffffff, 0) = 0 SQLEndTran(2, 0x16ae950, 1, 0) = 0 SQLDisconnect(0x16ae950, 0x16ae950, 0, 0) = 0 SQLFreeHandle(2, 0x16ae950, 0, 0) = 0 All from rlm_sql_unixodbc.so: SQLAllocHandle(3, 0x1b77a60, 0x1b6ff30, 0x7f99c886b74d) = 0 SQLAllocHandle(1, 0, 0x1be7040, 0xe4bca5c0) = 0 SQLSetEnvAttr(0x1bd9d20, 200, 3, 0) = 0 SQLAllocHandle(2, 0x1bd9d20, 0x1be7048, 0) = 0 SQLAllocHandle(3, 0x1bda320, 0x1be7050, 0x7f99c886b74d) = 0 SQLAllocHandle(1, 0, 0x1c11ea0, 0xe4bca5c0) = 0 SQLSetEnvAttr(0x1bd4ce0, 200, 3, 0) = 0 SQLAllocHandle(2, 0x1bd4ce0, 0x1c11ea8, 0) = 0 SQLAllocHandle(3, 0x1bd52e0, 0x1c11eb0, 0x7f99c886b74d) = 0 SQLAllocHandle(1, 0, 0x1c0e7a0, 0xe4bca5c0) = 0 SQLSetEnvAttr(0x1c19d60, 200, 3, 0) = 0 SQLAllocHandle(2, 0x1c19d60, 0x1c0e7a8, 0) = 0 SQLAllocHandle(3, 0x1c1a360, 0x1c0e7b0, 0x7f99c886b74d) = 0 SQLAllocHandle(1, 0, 0x1c61710, 0xe4bca5c0) = 0 SQLSetEnvAttr(0x1c41310, 200, 3, 0) = 0 SQLAllocHandle(2, 0x1c41310, 0x1c61718, 0) = 0 SQLAllocHandle(3, 0x1c41910, 0x1c61720, 0x7f99c886b74d) = 0 SQLNumResultCols(0x1be5820, 0x7ffe6b844d76, 0, 0) = 0 SQLBindCol(0x1be5820, 1, 1, 0x1cb1c10) = 0 SQLBindCol(0x1be5820, 2, 1, 0x1cb21e0) = 0 SQLBindCol(0x1be5820, 3, 1, 0x1cb2290) = 0 SQLBindCol(0x1be5820, 4, 1, 0x1ccbdd0) = 0 SQLBindCol(0x1be5820, 5, 1, 0x1cb1ad0) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b844e90, 0) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b844e90, 0) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b844e90, 0) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b844e90, 0) = 100 SQLFreeStmt(0x1be5820, 0, 0, 0xe4bca5c0) = 0 SQLNumResultCols(0x1be5820, 0x7ffe6b844d76, 0, 0) = 0 SQLBindCol(0x1be5820, 1, 1, 0x1cb1ad0) = 0 SQLBindCol(0x1be5820, 2, 1, 0x1cb16f0) = 0 SQLBindCol(0x1be5820, 3, 1, 0x1cb1500) = 0 SQLBindCol(0x1be5820, 4, 1, 0x1ccc000) = 0 SQLBindCol(0x1be5820, 5, 1, 0x1ccc170) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b844e90, 0) = 100 SQLFreeStmt(0x1be5820, 0, 0, 0xe4bca500) = 0 SQLAllocHandle(1, 0, 0x1cccb80, 0xe4bca5c0) = 0 SQLSetEnvAttr(0x1cccbb0, 200, 3, 0) = 0 SQLAllocHandle(2, 0x1cccbb0, 0x1cccb88, 0) = 0 SQLAllocHandle(3, 0x1ccd1b0, 0x1cccb90, 0x7f99c886b74d) = 0 SQLNumResultCols(0x1be5820, 0x7ffe6b844c96, 0, 0) = 0 SQLBindCol(0x1be5820, 1, 1, 0x1cb1500) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b844e90, 0) = 100 SQLFreeStmt(0x1be5820, 0, 0, 0xe4bca500) = 0 SQLNumResultCols(0x1c00590, 0x7ffe6b844d76, 0, 0) = 0 SQLBindCol(0x1c00590, 1, 1, 0x1cbc8c0) = 0 SQLBindCol(0x1c00590, 2, 1, 0x1cbc6b0) = 0 SQLBindCol(0x1c00590, 3, 1, 0x1cbc190) = 0 SQLBindCol(0x1c00590, 4, 1, 0x1d115d0) = 0 SQLBindCol(0x1c00590, 5, 1, 0x1cbc760) = 0 SQLFetch(0x1c00590, 0x1b4c150, 0x7ffe6b844e90, 0) = 0 SQLFetch(0x1c00590, 0x1b4c150, 0x7ffe6b844e90, 0) = 0 SQLFetch(0x1c00590, 0x1b4c150, 0x7ffe6b844e90, 0) = 0 SQLFetch(0x1c00590, 0x1b4c150, 0x7ffe6b844e90, 0) = 100 SQLFreeStmt(0x1c00590, 0, 0, 0xe4bca5c0) = 0 SQLNumResultCols(0x1c00590, 0x7ffe6b844d76, 0, 0) = 0 SQLBindCol(0x1c00590, 1, 1, 0x1cbbcb0) = 0 SQLBindCol(0x1c00590, 2, 1, 0x1cbcf80) = 0 SQLBindCol(0x1c00590, 3, 1, 0x1cbd030) = 0 SQLBindCol(0x1c00590, 4, 1, 0x1d121d0) = 0 SQLBindCol(0x1c00590, 5, 1, 0x1d12340) = 0 SQLFetch(0x1c00590, 0x1b4c150, 0x7ffe6b844e90, 0) = 100 SQLFreeStmt(0x1c00590, 0, 0, 0xe4bca500) = 0 SQLNumResultCols(0x1c00590, 0x7ffe6b844c96, 0, 0) = 0 SQLBindCol(0x1c00590, 1, 1, 0x1cbcf80) = 0 SQLFetch(0x1c00590, 0x1b4c150, 0x7ffe6b844e90, 0) = 100 SQLFreeStmt(0x1c00590, 0, 0, 0xe4bca500) = 0 SQLNumResultCols(0x1c4e0f0, 0x7ffe6b8459f6, 0, 0) = 0 SQLBindCol(0x1c4e0f0, 1, 1, 0x1d111c0) = 0 SQLFetch(0x1c4e0f0, 0x1b4c150, 0x7ffe6b845ad8, 0) = 0 SQLFreeStmt(0x1c4e0f0, 0, 0, 0xe4bca500) = 0 SQLNumResultCols(0x1be5820, 0x7ffe6b8459f6, 0, 0) = 0 SQLBindCol(0x1be5820, 1, 1, 0x1cbb8e0) = 0 SQLFetch(0x1be5820, 0x1b4c150, 0x7ffe6b845ad8, 0) = 0 SQLFreeStmt(0x1be5820, 0, 0, 0xe4bca500) = 0 SQLAllocHandle(1, 0, 0x1cacc60, 0xe4bca5c0) = 0 SQLSetEnvAttr(0x1d11f50, 200, 3, 0) = 0 SQLAllocHandle(2, 0x1d11f50, 0x1cacc68, 0) = 0 SQLAllocHandle(3, 0x1cb25e0, 0x1cacc70, 0x7f99c886b74d) = 0
On Jul 4, 2019, at 5:10 PM, Dom Latter <freeradius-users@latter.org> wrote:
Anyway, I have run ltrace to grab calls to the MS ODBC driver, from both freeradius and a small python test script (which doesn't exhibit this weird bug).
These are the calls (sorted alphabetically) that are used by Freeradius: ... And these are the ones used by Python: ... What is noticeably absent from the freeradius output is SQLExecDirect
That function is likely implemented in the library as a series of lower-layer calls.
(the 'W' in the Python output just means it's Unicode); this despite that I have put debug output immediately before and after the line in the FR module that says:
err_handle = SQLExecDirect(conn->stmt, odbc_query, strlen(query));
so I know it is doing it... or trying to... or something.
The question is what magic parameters to send to ODBC which say "don't do stupid things with numbers". Alan DeKok.
Dom Latter <freeradius-users@latter.org> wrote:
On 21/06/2019 15:00, Alan DeKok wrote:
On Jun 21, 2019, at 8:30 AM, Dom Latter <freeradius-users@latter.org> wrote:
I think I will now try iodbc instead - anybody here used it?
Occasionally, but not recently.
Executing select query: SELECT 10000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
That's ridiculous.
Maybe if it was 256, or 2^16 or 2^31. But a 5-digit number? WTF.
Indeed. But four digits = four 8 byte characters = 32 bits.
And five makes 40 bits. Hence by some amazingly flawed logic, 9999 is fine, and 10000 is "too big to go in an int".
And that is the only plausible explanation I can think of.
And here I was thinking 9999 errors were these days the sole province of fictional computers in the Fallout video game series.
On 21/06/2019 13:30, Dom Latter wrote:
Executing select query: SELECT 10000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
To recap - using rlm_sql_unixodbc to connect to SQL Server had the weird bug that any number more than four digits long was "out of range". Now I have used rlm_sql_unixodbc to connect to freetds and thence to SQL Server. It works (having had to rename my radius database to "Sample" because that is what it was looking for despite me not having "Sample" anywhere in the configuration) but this time numbers more than four digits long are simply truncated: (24) EXPAND %{sql:SELECT 123456 } (24) --> 1234 (24) EXPAND %{sql:SELECT '123456' } (24) --> 123456 (24) EXPAND %{sql:SELECT CAST(123456 as varchar) } (24) --> 123456 All very strange.
On 25/07/2019 13:04, Dom Latter wrote: <snip rlm_sql_unixodbc issues> I have gone back to rlm_sql_freetds. Unlike this time last week it is now working fine. Last week I had the following: I have tried rlm_sql_freetds but then I got this error rlm_sql_freetds: Server msg from "SQLTEST": severity(18456), number(14), origin(1), layer(1), procedure "none": Login failed for user 'radius'. despite that I could connect from the command line using "tsql" and the same credentials.
On Jul 25, 2019, at 9:04 PM, Dom Latter <freeradius-users@latter.org> wrote:
On 21/06/2019 13:30, Dom Latter wrote:
Executing select query: SELECT 10000 rlm_sql_unixodbc: 22003 [Microsoft][ODBC Driver 17 for SQL Server]Numeric value out of range
To recap - using rlm_sql_unixodbc to connect to SQL Server had the weird bug that any number more than four digits long was "out of range".
Now I have used rlm_sql_unixodbc to connect to freetds and thence to SQL Server. It works (having had to rename my radius database to "Sample" because that is what it was looking for despite me not having "Sample" anywhere in the configuration) but this time numbers more than four digits long are simply truncated:
(24) EXPAND %{sql:SELECT 123456 } (24) --> 1234
(24) EXPAND %{sql:SELECT '123456' } (24) --> 123456
(24) EXPAND %{sql:SELECT CAST(123456 as varchar) } (24) --> 123456
All very strange.
Seems like a legitimate issue. Could you open something on GitHub so it gets tracked. Thanks, -Arran
On 30/07/2019 09:59, Dom Latter wrote:
On 26/07/2019 06:24, Arran Cudbard-Bell wrote:
<rlm_sql_unixodbc issues>
Seems like a legitimate issue. Could you open something on GitHub so it gets tracked.
Okay Arran, a github issue is raised. Hope it suffices. NB I won't be working on this project again for two weeks but if there's a quick question on this thread (not on github) I'll try and respond.
On Tue, 30 Jul 2019 at 16:24, Dom Latter <freeradius-users@latter.org> wrote:
On 30/07/2019 09:59, Dom Latter wrote:
On 26/07/2019 06:24, Arran Cudbard-Bell wrote:
<rlm_sql_unixodbc issues>
Seems like a legitimate issue. Could you open something on GitHub so it gets tracked.
Okay Arran, a github issue is raised. Hope it suffices.
NB I won't be working on this project again for two weeks but if there's a quick question on this thread (not on github) I'll try and respond.
Do please test having applied the following: https://github.com/FreeRADIUS/freeradius-server/pull/3181/commits
participants (7)
-
Alan DeKok -
Arran Cudbard-Bell -
Brian Julin -
Dom Latter -
Herwin Weststrate -
Terry Burton -
Winfield, Alister