Sql Server ODBC Linked Server

I’m trying to create a linked server in SQL server 2017, linked system is odbc (criteria criOdbc)

Test 1 Form SQL Server SSMS

select * from OPENROWSET('MSDASQL','DSN=criteria64;uid=xxxx;pwd=yyyy;','select * from table where key<10')

–> Give this error “Error converting data type DBTYPE_DBDATE to date.”

Test 2 Form SQL Server SSMS

select * from OPENROWSET('MSDASQL','DSN=criteria64;uid=xxxx;pwd=yyyy;','select key from table where key<10')

–> Works perfectly

Test 3 In c# (load form odbc then bulk insert in sql server)

OdbcConnection acc = new OdbcConnection("DSN=criteria64;uid=xxxx;pwd=yyyy;")
OdbcCommand cmd = new OdbcCommand("select * from table where key<10", acc);
....

–> Works perfectly

Test 4 connect the same table in MS Access as linked table

–> Works perfectly

Conclusion

  • Msdasql (Provider OLE DB Microsoft for ODBC) –> Doesn’t work well with date and some other type .net
  • System.Data.Odbc (.NET Framework
    Data Provider for ODBC) –> Works perfectly

Final question

Is possible to use NET Framework Data Provider for ODBC in linked server? (cant find any documentation)

If not

Is there any kind of connector (odbc based) tath I can try to use in linked server in alternative to “msdasql” ?

Thank you for your time