Hello Kader,
The following answer is stated under the assumption that you are trying to connect to the TDS / Dataverse endpoint to connect to the data:
I was testing this and using the supported authentication method.
The specific section identifying the auth requirements here:
https://docs.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query#authentication
When you are creating an ODBC connection we need to used 'AAD with MFA' . I noticed there is not a value that specifically states 'AAD with MFA'. However, I found the following documentation:
Using Azure Active Directory with the ODBC Driver - ODBC Driver for SQL Server | Microsoft Docs
(Windows driver only.) Azure AD Interactive Authentication uses Azure Active Directory Multi-Factor Authentication technology to set up connection. In this mode, by providing the login ID, an Azure Authentication dialog is triggered and allows the user to input the password to complete the connection. The username is passed in the connection string. server=Server;database=Database;UID=UserName;Authentication=ActiveDirectoryInteractive;
What this means is we need to use the following value when setting up the ODBC connection
However, when I set this up and try to run the 'Test Connection' I get the same error:
"Error - Invalid table name syscharsets for schema sys"
In order to understand this exception we need to understand what 'syscharsets' means
sys.syscharsets (Transact-SQL) - SQL Server | Microsoft Docs
Basically, syscharsets is a standard SQL System view which would show up in any traditional MSSql database. However, the TDS / Dataverse connection to your organization is not a connection to a standard SQL database and this view does not exist. With this in mind, I decided to query some data using the ODBC connection I created via powershell and found that the data extracted without error. This tells me that my ODBC connection is fine, but the Test Connection will never succeed due to the fact that the system view does not exist in the TDS/Dataverse connected organization.
My recommendation is to test your connection using Powershell. Here is a simple example I used replacing the sql text with:
select auditretentionperiod from organization
How to fetch data with ODBC DSN connections using ... - Qlik Community - 1712812
Thanks,
Ken Hubbard
Microsoft Dynamics Support
