Make sure your DSN is the same architecture as your SQL instance.  (32-bit vs 64-bit).

Thanks for the response, Kevin. Verified its the same.

Thanks for the response, Kevin. Verified its the same.

Try changing the SQL Service account to a user account with appropriate permissions (instead of NT Service\MSSQLSERVER).  The linked server functions through the SQL Service, not your user session, so maybe that is the problem.

System DSN, not User.  (A User DSN won't be seen from the perspective of a service... similarly make sure the DSN's paths are local or UNC, not mapped drives).

I think the error is different if these steps aren't done, but here are my notes with a few extra things in the setup which you might have missed.

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

Create the linked server in SQL Management Studio

Linked Servers Right Click => New Linked Server

Linked Server Name = DSN NAME

Server Type = Other Data Source

Provider = Microsoft OLE DB Provider for ODBC Drivers

Product name = DSN NAME

Data source = DSN NAME

Define a Connection String(Provider String)

Driver={MAS 90 4.0 ODBC Driver}; UID=userid; PWD=password; Company=ABC; Directory=\\UNC to ….\MAS90; LogFile=C:\PVXODBC.LOG; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SILENT=1; SERVER=NotTheServer

I read somewhere that the DirtyReads / BurstMode references need to be removed, but I'm not sure that is necessary.

Here is a "DROP & CREATE" script for a linked server that does not use a DSN but instead references the driver.  You do need to make sure that have the correct architecture of the providex driver so it matches SQL Server.  Make sure to update the paths to the MAS90 directory and the user|company and password..

USE [master]
GO

/****** Object:  LinkedServer [SOTAMAS90]    Script Date: 1/22/2021 7:07:03 PM ******/
EXEC master.dbo.sp_dropserver @server=N'SOTAMAS90', @droplogins='droplogins'
GO

/****** Object:  LinkedServer [SOTAMAS90]    Script Date: 1/22/2021 7:07:04 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SOTAMAS90', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'driver={MAS 90 4.0 ODBC Driver}; Directory=C:\Sage\Sage 100 2018\MAS90; Prefix=C:\Sage\Sage 100 2018\MAS90\SY\, C:\Sage\Sage 100 2018\MAS90\==\; ViewDLL=C:\Sage\Sage 100 2018\MAS90\Home\; CacheSize=4; DirtyReads=1; BurstMode=1; StripTrailingSpaces=1; SERVER=NotTheServer; '
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SOTAMAS90',@useself=N'False',@locallogin=NULL,@rmtuser=N'user|MFG',@rmtpassword='########'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SOTAMAS90', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

This should create a linked server like this.

Another benefit of handling security like this is that you can use statements like the following query to switch companies without having to set up a linked server for each one.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SOTAMAS90',@useself=N'False',@locallogin=NULL,@rmtuser=N'user|ABC',@rmtpassword='password'
GO

select * from openquery([SOTAMAS90], 'select * from ci_item')
GO

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SOTAMAS90',@useself=N'False',@locallogin=NULL,@rmtuser=N'user|MFG',@rmtpassword='password'
GO

select * from openquery([SOTAMAS90], 'select * from ci_item')
GO

As Kevin said, you do need to make sure that whatever account the SQL Server service is running under has the appropriate NTFS permissions for the MAS90 directory.

@David Speck - Thanks for the excellent approach for creating and using the SQL Linked Server approach. I am looking forward to trying this out for a customer that is having performance issues and that require data extracts from 4 different companies.