The ProvideX ODBC Driver requires user ID, password and company code to connect. Creating a system DSN with this embedded allow you to connect without the validation step.

Does your Sage 100 use unified login?  Make sure the SQL service Windows (domain) account has Sage 100 access, and use a SQL login when connecting with SSMS (instead of Windows authentication).

Thank you for the response Kevin.  I'll have to look into your first question, I've been treating our Sage instance largely as a black box given the linked server works on our staging environment. Our staging sql environment runs with the same account as production, which is another problem in of itself, but that tells me the domain account it's running as has the necessary permissions.  There is a user defined in the DSN, and I just verified the connection string is the same as our staging environment.  If it's of any help, here's our connection string as produced from ODBC with some replacements-

DSN=SameAsSystemDSN; UID=user; PWD=pass; Directory=\\sage\MAS90; Prefix=\\sage\MAS90\SY\, \\sage\MAS90\==\; ViewDLL=\\sage\MAS90\Home\; Company=SameAsStaging; LogFile=\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer

It may also be worth noting that the only functional difference between our Staging and Prod is Prod is an availability group with multiple machines running in sync; however linked servers are excluded from the sync process, so I've assumed that to have no impact but I could be mistaken.

Configure SQL Server options

From MS SQL Server Management Studio

Scroll to Server Objects => Linked Servers => Providers

Right Click MSDASQL => Properties

The only options selected should be “Allow in Process” & “Level zero only”

Select the above options and click OK

From The SQL Server “New Query”

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Execute the query

I've verified the correct options are selected for the MSDASQL Provider, and ran that SQL again-

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

As far as I can tell, the solution here was to uninstall and reinstall the Sage ODBC drivers and recreate the connection from scratch.

Cheers to anyone that works for another five years down the road.