ODBC Connection from SAS 9.4 M6 on Windows Server Cannot see tables.

[Tom]

Did you check the various options for the LIBNAME statement to see if any can help you?

https://documentation.sas.com/doc/sk/pgmsascdc/v_060/acreldb/p11x7o76auf85bn1ev4m0oty0bno.htm

In particular try the DRIVER_TRACE and related options so you can see what SQL SAS is generating when it tries get the list of files.

[thesasuser]

Hello @Tom 
Thanks. I have tried using driver_trace option. But I have the error as follows

WARNING: Libref DBRK is not assigned. 
27 option VALIDMEMNAME=EXTEND VALIDVARNAME=ANY;
28 libname dbrk odbc dsn="dbrk" user="client_id" password=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 
28 ! schema="catalog.schema" driver_trace=ALL; 
                              ____________ 
22 ERROR: Libref DBRK is not assigned. 
ERROR: Error in the LIBNAME statement. 
ERROR 22-7: Invalid option name DRIVER_TRACE.

Thanks once again

[Tom]

Unfortunately it does not look like that option works for ODBC.  

https://documentation.sas.com/doc/da/pgmsascdc/v_060/acreldb/p11x7o76auf85bn1ev4m0oty0bno.htm

Engines:	BIGQUERY, HADOOP, JDBC, SPARK
Data source:	Google BigQuery, Hadoop, JDBC, Spark

[Nigel_Pain]

It's worth trying the SASTRACE system option to see what is actually being sent and received via ODBC:

SAS Help Center: SASTRACE= SAS System Option

[thesasuser · Accepted solution]

Thanks @Nigel_Pain  @Tom  @ballardw  @JuanS_OCS @Ksharp 
I appreciate the help all of you have provided. All the suggestions were of great help.
I worked with SAS tech Support and under their   guidance, the issue was finally resolved.
Thanks SAS Tech Support for your help.
I am summarizing the steps so that they help others in need.
Step 1: Configure the odbc manager at the OS level.
  The authentication information, server details go here. Th interface is quite intuitive and one can fill up the details with the help of Datawarehouse/databricks team.
In my case they were   hostname, database name, the http options, OAuth details

Step2 The libname statement  that worked is as follows:

libname [YR_LIBREF] ODBC preserve_tab_names=yes schema=[SCHEMA_NAME] noprompt="DSN=[DSN_NAME];catalog=[CATALOG_NAME];Schema=[SCHEMA_NAME];";

I have used [PARAMETER]for values that are needed to be input.

[npm_sas]

Thank you! the libname statement is very helpful. I ended up using username/password for step 1. Would you be able to share more details on step 1 regarding OAuth options? Thanks again

[SASKiwi]

@npm_sas  - What database are you connecting to? For some databases like SQL Server you can use IWA - Integrated Windows Authentication (Trusted Connection=yes) like this and avoid any user credentials:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

[thesasuser]

In order to use OAuth options, while configuring the ODBC manager select the Authentication Mechanism as OAuth2.
The next step would be to fill the necessary Oauth2,HTTP and SSL  options as applicable at your place and you should be good.
No authentication statement would be needed in the libname statement.