BookmarkSubscribeRSS Feed
Hans_Luc
Calcite | Level 5

I am trying to create a library using code that links to our Dynamics365 Sales database.  I am using a piece of software called CData to allow this link.

 

Currently it only works if I was to create the library manually by going to Tools > New Library, for name I used: PRM, for Engine, I selected: ODBC, for Data Source I chose: CData D365Sales Sys.  User ID and Password is the same as my windows login. And Options I've left blank.

 

The above manual method will force a browser to open with the message OAuth Authorization Successful twice, once for creating the library and another when clicking into the library in the SAS Explorer window.

 

Now my issue is that I am trying to mimic this in code form.  Below is the code I've tried using (I am using SAS 9.4):

%let dsn=CData D365Sales Sys;
libname prm odbc noprompt="dsn=&dsn;Trusted_Connection=yes" schema=DBO access=readonly; 

 

 

However, I get the following error message.  Hoping someone can elaborate what needs to be done as I am unclear from just the error message alone. i.e. where do I find the DSN manager and how to authenticate it?

 

ERROR: CLI error trying to establish connection: The driver requires a user prompt for OAuth

authentication which is not allowed by this application. Please use the DSN manager to

authenticate before using the driver in this application.

ERROR: Error in the LIBNAME statement.

 

2 REPLIES 2
SASKiwi
PROC Star

What OS does SAS run on? Your connection string is using Windows Authentication so will only work on Windows. Also your DSN needs to point to a single definition, not three. On Windows the DSN would be defined in the ODBC Administrator on the computer where SAS runs.

Hans_Luc
Calcite | Level 5
I'm using Windows 10. In the ODBC Administrator under the System DSN tab, the Name is "CData D365Sales Sys", Platform is "64-bit", Driver is "CData ODBC Driver for Dynamics 365 Sales"

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1378 views
  • 0 likes
  • 2 in conversation