Sage 100

So for systems that don't have ODBC Security enabled yet have third party software querying Sage 100 data, I have always just set up a user called ODBC, gave it a randomly generated password, and didn't assign any companies and roles to it.  I've done this for Excel queries and other software querying too so a legitimate user and its password wasn't being exposed in a plain text connection string on in the registry.  This was merely so it could authenticate via the ODBC driver and this was sufficient.  I just encountered a system running Sage 100 version 2021.2, which was just upgraded from 2019, where if the user doesn't have a role assigned for the target company or All companies, then the records are returned but every field is blank, just like when ODBC Security is enabled and the user doesn't have a role with access to the table checked.

EDIT: I edited my original post to include the product update version as I have determined that this behavior does not occur in 2021.0 but was introduced in either PU1 or PU2.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Hmm, wonder if something else was the cause.  I even tried toggling ODBC security.  It wasn't until I gave the user a role for either the target company or All companies that I got data displayed in the fields.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 10-29-2021 17:12
From: Kevin Moyes
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Also, this was on a 2021.2 system.  What PU did you test it on?

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 10-29-2021 17:22
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Hmm, wonder if something else was the cause.  I even tried toggling ODBC security.  It wasn't until I gave the user a role for either the target company or All companies that I got data displayed in the fields.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 10-29-2021 17:12
From: Kevin Moyes
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

2021.1 Standard
Edit:
I'll retest after I've installed PU2.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 10-29-2021 17:24
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Also, this was on a 2021.2 system.  What PU did you test it on?

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 10-29-2021 17:22
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Hmm, wonder if something else was the cause.  I even tried toggling ODBC security.  It wasn't until I gave the user a role for either the target company or All companies that I got data displayed in the fields.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:12
From: Kevin Moyes
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

Just tested on my own 2021.0 system and I did not have to assign a company or role.  Going to install PU2 and report back.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 10-29-2021 17:24
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Also, this was on a 2021.2 system.  What PU did you test it on?

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 10-29-2021 17:22
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Hmm, wonder if something else was the cause.  I even tried toggling ODBC security.  It wasn't until I gave the user a role for either the target company or All companies that I got data displayed in the fields.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:12
From: Kevin Moyes
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

I guess I should have started with 2021 PU1 but after installing 2021 PU2, I am now getting blank fields on my development system when using a user that does not have a company and role assigned.  So it was either in PU1 or PU2 for 2021 that this was changed.  I checked the release notes for PU2 and didn't find anything that detailed this changed.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------

Original Message:
Sent: 10-29-2021 17:31
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Just tested on my own 2021.0 system and I did not have to assign a company or role.  Going to install PU2 and report back.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 10-29-2021 17:24
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Also, this was on a 2021.2 system.  What PU did you test it on?

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:22
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Hmm, wonder if something else was the cause.  I even tried toggling ODBC security.  It wasn't until I gave the user a role for either the target company or All companies that I got data displayed in the fields.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:12
From: Kevin Moyes
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON

Definitely something in PU2... I get blanks now.


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 10-29-2021 17:39
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I guess I should have started with 2021 PU1 but after installing 2021 PU2, I am now getting blank fields on my development system when using a user that does not have a company and role assigned.  So it was either in PU1 or PU2 for 2021 that this was changed.  I checked the release notes for PU2 and didn't find anything that detailed this changed.

------------------------------
David Speck II
Tennessee Software Solutions
------------------------------


Original Message:
Sent: 10-29-2021 17:31
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Just tested on my own 2021.0 system and I did not have to assign a company or role.  Going to install PU2 and report back.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:24
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Also, this was on a 2021.2 system.  What PU did you test it on?

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:22
From: David Speck II
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

Hmm, wonder if something else was the cause.  I even tried toggling ODBC security.  It wasn't until I gave the user a role for either the target company or All companies that I got data displayed in the fields.

------------------------------
David Speck II
Tennessee Software Solutions


Original Message:
Sent: 10-29-2021 17:12
From: Kevin Moyes
Subject: Notice: Sage 100 2021 Won't Let Users Without A Role Query Data Via ODBC

I was hopeful Sage added this as a new security feature... but I was able to run queries of 2021 data with a new user without any Role permissions (tested with 32-bit and 64-bit DSN's).

32-bit query works...

64-bit query works...

Are you sure ODBC security is not enabled in your test system?

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON