SQL Server connectivity Issue

[Key123]

I have a new M8 setup and we are connecting to SQL server using Access engine through provided Datadirect drivers 

LIBNAME SQLSRVT SQLSVR Datasrc=MyDatasrc SCHEMA=MySchema authdomain="SQLSRV";

Authentication is successful but it does not list Schema tables in the library

I have some users connecting to SQL server using long Libname definitions:

LIBNAME SQLSRVT1  SQLSVR NOPROMPT="Driver=SAS Institute, Inc 8.2 SQL Server Wire Protocol;
AuthenticationMethod=9; Database=MYDB ; HostName= MyHost\SSINST; PortNumber=Myport;
Domain=MYDOMAIN; LogonID=[username]; Password=[password];" Schema=MySchema;

Library assignment is successful and it list tables. Can you suggest where this is going wrong?

Below are the ODBC.ini entries for libname using AuthDomain

[DataSRC]
Driver=/sashome/AccessClients/9.4/SQLServer/lib/S0sqls28.so
Description=SAS Institute, Inc 8.2 SQL Server Wire Protocol
AuthenticationMethod=9
Database=MyDB
HostName=MyHost\SSINST
PortNumber=MYPORT
EncryptionMethod=6
ValidateServerCertificate=0
Trusted_Connection=Yes
QuotedId=yes
IntegratedSecurity=false
TrustServerCertificate=yes
CryptoProtocolVersion=TLSv1.2, TLSv1.1,TLSv1, SSLv3, SSLv2
SSLLibName=/usr/lib64/libssl.so.1.1
CryptoLibName=/usr/lib64/libcrypto.so.1.1

[SASKiwi]

You can only list tables in an SQL Server database via a SAS LIBNAME if you provide correct database and schema names. Without these set the LIBNAME will assign correctly at the server level but can only be used in a limited way like with SQL Passthru for example. Here is one way:

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=ODBC Driver 18 for SQL Server;Trusted Connection=yes;";

proc sql;
 connect using sqlsrvr;
  create table Want  as 
  select * from connection to sqlsrvr
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable] A
   LEFT JOIN [MyDatabase2].[MySchema2].[MyTable2] B
   ON A.Key1 = B.Key1
   )
  ;
quit;

[LinusH]

I'm a little bit confused by these statements:

"... it does not list Schema tables...".

What do you mean by schema tables?

"... and it list tables...".

What tables gets listed?

Please provide examples of what you see in SAS, and what's available in in SQL Server.