topic Re: SAS Proc HTTP call to API Graph.Microsoft. in SAS Programming

SAS Proc HTTP call to API Graph.Microsoft.

smijoss1 — Fri, 19 Apr 2019 20:43:25 GMT

on SAS when i run a PROC HTTP for

https://graph.microsoft.com/v1.0/me/drives/<Drive-ID>/root:/%2FDocuments%2FSkype%20Recordings:/children

it lists out all the files in the folder and generates a pre-authenticated link. is there a way i can directly use this link to download the file via sas in next proc http call ? I get the following error message.

PS : I tired with and without the access token 😞

SAS GRID

SAS version : 9.04.01M4P110916

SAS server : Linux

Azure API Permissions :

Files.ReadWrite.All

User.Read

PROC SQL;
SELECT cats("'",_microsoft_graph_downloadUrl,"'") as web_dwnld_Url length=2000
into :_web_dwnld_Url
FROM  WORK.SMIJO t1
WHERE Item_Name = "CROSSWALK_GROUPS_TO_INCLUDE v201904.XLS";
QUIT;



filename fileout "%sysfunc(Pathname(Work))/CROSSWALK_GROUPS_TO_INCLUDE v201904.XLS";
%let _rc = %sysfunc(Fdelete(fileout));


proc http url=&_web_dwnld_Url.
method='GET' 
out = fileout;
headers "Authorization"="Bearer &access_token." ;
run;

ERROR: Secure communications error status 807ff019 description "SSL Error: Invalid subject name in partner's certificate. Subject
name must match machine name."
ERROR: SSL Error: Invalid subject name in partner's certificate. Subject name must match machine name.
ERROR: Call to tcpSockContinueSSL failed.

Re: SAS Proc HTTP call to API Graph.Microsoft.

ChrisHemedinger — Wed, 24 Apr 2019 16:59:21 GMT

You can accomplish this, but it requires some setup. I've documented the entire process here:

Using SAS to access and update files on Microsoft OneDrive (Office 365)

I've included links to utility macros on GitHub that can help.

Re: SAS Proc HTTP call to API Graph.Microsoft.

smijoss1 — Wed, 24 Apr 2019 17:37:12 GMT

That is the blog i refereed to get till here (THANK YOU For the Blog! the steps were well explained.)

I was able to get DriveID , and Item IDs by listing files inside a SP folder (i did find using relative path on URL method more easy as you could skip getting the Drive ID step)

I used the same method mentioned on the blog to download the file but its throwing "partner certificate does not match" error.

I did some search online and most said its because the domain on URL does not match the Machine.

So instead of using the Drive ID and Item-ID I tried to use the Pre-authenticated URL provided by API when i list files from folder.

it works well when i put it into chorme but in SAS it still gives the same "partner certificate does not match".

I raised a support request with SAS as i feel this might me a setup issue on SAS Grid. I will update you on what they say.

So far they they directed me to your Blog to read and give the logs accordingly.

Re: SAS Proc HTTP call to API Graph.Microsoft.

ChrisHemedinger — Wed, 24 Apr 2019 17:44:25 GMT

It might be a cert issue with Linux -- I wonder if you might need to try one of the SSL cert-related options. See this doc.

Re: SAS Proc HTTP call to API Graph.Microsoft.

smijoss1 — Sat, 27 Apr 2019 00:16:21 GMT

Hi Chris,

I tried the method mentioned ( i copied the subject name and Serial number from certificate i see in browser )

however SAS did not like it. (screenshot below)

I also tried %let SSLCALISTLOC=/gpfsFS2/home/o815539/test_SP_Cert/cert_SP_Cloud_Smijo.pem;

and that too didnt work.

i downloaded the Certificates ( as per http://javamemento.blogspot.com/2015/10/using-curl-with-ssl-cert-chain.html )

and tried it on my CURL command and it worked without any errors. ( without having to use -k or --insecure)

filename test pipe 'curl --cacert /gpfsFS2/home/o815539/test_SP_Cert/cert_SP_Cloud_Smijo.pem --url <Download URL> /aim/mmc/mmc_Share/TEMP/smijo.xlsx';

is there an option in proc Http where i could pass the certificate path ? i tried searching couldnt fine one.

Re: SAS Proc HTTP call to API Graph.Microsoft.

ChrisHemedinger — Sat, 27 Apr 2019 23:13:21 GMT

Are you running SAS with ENCODING=UTF8? That's what I recommend, as the Microsoft API can definitely return data that is not representable in the default latin1/wlatin1 encoding. The error you shared seems to indicate that the data you're getting cannot be transcoded as is...and SAS isn't very tolerant of that.

Re: SAS Proc HTTP call to API Graph.Microsoft.

smijoss1 — Fri, 17 May 2019 00:38:03 GMT

Enable Implicit Grant for the :/Content URL to redirect to download URL.

without this sas/curl output will only show URL