topic Re: MS-SharePoint via REST-API and NTLM-authentication with proc http in SAS Procedures

MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Wed, 07 Jun 2017 15:47:18 GMT

Hi there,

I'm trying to simply check in and out some documents in a MS-SharePoint-environment. From a SAS-program.

Environment: Window Server

EG: 7.1

SAS-Foundation: 9.04.01M4P110916

I got the REST-API-documentation for the SharePoint and I read the great white papers written by

Joseph Henry about how to use RESTful services with SAS (SAS1927-2015 and SAS6363-2016).

In the environment I am working with, it seem I have to deal with the Windows NTML-authentication.

Did anyone out there tried to authenticate with NTLM against a SharePoint-Server and can tell me how to?

The most advanced try is as follows (I used the correct domain for <my-sharpointsite>):

proc http
method="GET"
url='http://<my-sharepointsite>/site/_api/web/lists("guid")/items?$select=Title,Products/Name&amp;$expand=Products/Name'
headerout=headers
out=resp
webusername="&username."
webpassword="&pwd."
HEADEROUT_OVERWRITE;
run;
%echofile(headers);

The response header looks like this (I shortened the string after NTML randomly, because I don't know if it contains the password in base64 encoding or so):

HTTP/1.1 401 Unauthorized
Server: Microsoft-IIS/7.5
SPRequestGuid: 3d5af452-6e18-4f51-a911-972d70d7c117
WWW-Authenticate: NTLM TlRMAAACAAIADgAAAAFgomivW0zcYRzy4cAAAIwAjABAAAAABgGxHQAAAA9FAFIARwBPAAIACABFAFIARwBPAAEAFABXAEUAQgBQAEUAAUgBQAC4ARQBSAEcATwADACgAVwBFAEIAATAAxADEAMwAuAGMAbwByAHAALgBlAHIAZwBvAAUAEgBSAE8ATwAEcAWm+HaLf0gEAAAAA
WWW-Authenticate: Negotiate
X-Powered-By: ASP.NET
MicrosoftSharePointTeamServices: 14.0.0.7175
X-MS-InvokeApp: 1; RequireReadOnly
Date: Wed, 07 Jun 2017 15:24:11 GMT
Content-Length: 0

As I found on the web (https://www.innovation.ch/personal/ronald/ntlm.html) NTLM uses a 4-way handshake, but I don't know how to handle that with a SAS-program.

Maybe there is an even easier way to do that using the SingleSignOn capabilities in this environment? Any ideas?

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

ChrisHemedinger — Wed, 07 Jun 2017 16:13:55 GMT

Have you tried AUTH_NTLM (requires SAS 9.4 M3 or later, I think)?

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Wed, 07 Jun 2017 16:28:36 GMT

I tried it right now - no change of the result.

But the point is: I think the reply-header told me, "you are on the right way with NTLM, here is some token or whatever" (see "WWW-Authenticate: NTLM TlRMT....." in the response header). I only don't know how to go on with it.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

ChrisHemedinger — Wed, 07 Jun 2017 16:57:00 GMT

Where is the SAS session? Also on Windows? Local or remote? Or on Linux?

If Local, then I'd guess your windows connection could used. If a remote Windows SAS, then you might need to have Trusted for Delegation enabled on the remote machine (Active Directory setting) to allow the credentials to pass across the network hop. If Linux (or another UNIX), then maybe something else is needed -- the full NTLM negotiation, or leveraging a Kerberos setup. I'm treading on thin knowledge here.

I consider PROC HTTP to be like a built-in cURL for SAS programs, as it supports most of what a command-line HTTP client should do. Sometimes finding an example of that can help you to transcribe the necessary options to the SAS version.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

JosephHenry — Wed, 07 Jun 2017 17:13:15 GMT

Where you are running the SAS session is quite important.

NTLM is typically used by a User that is logged into a Windows machine and that same user is being used to access sharepoint. In this case, a username and password are not needed on the proc statement, since the current logged in user will be used.

If you are running from a Linux machine, then you are pretty much out of luck using NTLM, as it is a Microsoft specific auth.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

ChrisHemedinger — Wed, 07 Jun 2017 17:43:06 GMT

If you have SAS Enterprise Guide and your SAS session is on UNIX, then you might try this custom task that accompanies a SAS paper:

Paper: Get data from a SharePoint List into a SAS data set

Task: Link to task download.

It's a bit of a pain to set up, but when working it can help to automate this process.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

JosephHenry — Wed, 07 Jun 2017 17:46:25 GMT

I noticed that the response also inlucdes Negotiate as an auth type.

Negotiate will work on linux systems as long as the kerberos libraries are installed.

You can force using Negotiate by using the proc http option AUTH_NEGOTIATE

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 07:59:30 GMT

Hello Chris,

thanks fpr your reply.

I am working in an Windows only environment, but there is absolutely no chance to get visual studio or installed or even to install some user written dlls somewhere.

I'm pretty sure, that there should be a way with SAS proc http only.....

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 08:18:37 GMT

I am running the session on a remote Windows server machine. So even if I am logged on to that machine I guess I have to send to the SharePoint service who I am in the proc statement.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

ChrisHemedinger — Thu, 08 Jun 2017 12:27:20 GMT

If you're connected using your account on Windows, even on the remote Windows machine, you should not have to pass additional credentials. Try running this program to verify your IDs (see more here😞

/* for use on Windows and Unix workspace servers */
%let _metauser = %scan(%sysget(METAUSER),1,'@');
/* for use on MVS, Stored Process, and Pooled Workspace servers */
/* %let _metauser = %scan(%sysfunc(getoption(METAUSER)),1,'@'); */
data ids;
  length name $ 16 purpose $ 40;
  label name="Value Name" purpose="Purpose";
  infile datalines dsd;
  input name purpose;
datalines;
SYSUSERID, SAS session host account
_CLIENTUSERID, Windows user ID in SAS EG
_CLIENTUSERNAME, Windows user name in SAS EG
_METAUSER, SAS metadata user ID
run;
proc sql;
  select t1.name,
    t2.value,
    t1.purpose
   from work.ids t1
   inner join sashelp.vmacro t2 on (t1.name = t2.name);
quit;

On the remote machine, the account that runs the object spawner (that launches your SAS session) needs to be Trusted for Delegation on that machine. See SAS admin doc here. This allows Windows to pass your authentication to the next network-connected resource. The same is required if using Windows authentication to connect from SAS to a SQL Server database, a mapped drive/UNC path, or a network printer.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 12:48:40 GMT

Using the AUTH_NEGOTIATE option looks like a good approach.

I tried this:

filename input "&PROJ_FS_F./sharepoint/input";
filename resp1 "&PROJ_FS_F./sharepoint/resp1";
filename resp2 "&PROJ_FS_F./sharepoint/resp2";
filename headers "&PROJ_FS_F./sharepoint/headers";


/* Touch, to get a token */
proc http
  method='GET'
  url='http://&base_url./sites/_api/lists'
  headerout=headers
  out=resp1
  AUTH_NEGOTIATE
  HEADEROUT_OVERWRITE;
run;
%echofile(headers);


/* Read the token from the header */
%global hcode;
%global hmessage;
%global token;
data _null_;
infile headers termstr=CRLF length=c scanover truncover;
input @'HTTP/1.1' code 4. message $255.
@'WWW-Authenticate: Negotiate ' loc $255.;
call symputx('hcode',code);
call symput('hmessage',trim(message));
call symput('token',trim(loc));
run;

%put &hcode. &hmessage.;
%put >>> &token.;

/* SharePoint API Documentation: CheckIn
url: http://site url/_api/web/GetFileByServerRelativeUrl('/Folder Name/file name')/CheckIn(comment='Comment',checkintype=0)
method: POST
headers:
    Authorization: "Bearer " + accessToken
    X-RequestDigest: form digest value

For Proc HTTP:
url="http://&base_url./sites/_api/web/GetFileByServerRelativeUrl('/ASK-TP-DS/Dokumente/Forms/test_jat.txt')/CheckIn(comment='Comment',checkintype=0)"

*/

/* SharePoint API Documentation: CheckOut

url: http://site url/_api/web/GetFileByServerRelativeUrl('/Folder Name/file name')/CheckOut(),
method: POST
headers:
    Authorization: "Bearer " + accessToken
    X-RequestDigest: form digest value

For Proc HTTP:
url="http://&base_url./sites/_api/web/GetFileByServerRelativeUrl('/ASK-TP-DS/Dokumente/Forms/test_jat.txt')/CheckOut()"

*/

/* prepare the input header: */
data _null_;
file input recfm=f lrecl=1;
  put "Authorization: Bearer &token.";
  put '0d'x'0a'x;
  put "X-RequestDigest: form digest value";
run;

proc http
method='POST'
url="http://&base_url./sites/_api/web/GetFileByServerRelativeUrl('/ASK-TP-DS/Dokumente/Forms/test_jat.txt')/CheckOut()"
in=input
headerout=headers
out=resp2
AUTH_NEGOTIATE
HEADEROUT_OVERWRITE;
run;
%echofile(headers);

and I got the following resp2:

HTTP/1.1 200 OK
Server: Microsoft-IIS/7.5
Date: Thu, 08 Jun 2017 12:27:01 GMT
Connection: close

Looks prety good, but nothing happend - means: no change in SharePoint.

By the way: the response-Header does not look that friendly:

HTTP/1.1 404 
Server: Microsoft-IIS/7.5
SPRequestGuid: 5cf0e00b-a663-482a-b4c1-42071760016a
X-SharePointHealthScore: 0
WWW-Authenticate: Negotiate oYGzMIGwoAMKAQChCwYJKoZIgvcSAQICooGbBIGYYIGVBgkqhkiG9xIBAgICAG+BhTCBgqADAgEFoQMCAQ+idjB0oAMCAReibQRrCAS6VR4ZJINk+sDg9hyaIk/OKbEQr504a6UhphgnBdy1XnIO/DIMogBdcAM+xdfNvoBOheaA2gprUsmt/mVyes0bxZr/ADApYUwaApSFTuiN0kk1o5MM4M/G7INx6XKIQ5zWNSS7tLjlzoY=
Persistent-Auth: false
X-Powered-By: ASP.NET
MicrosoftSharePointTeamServices: 14.0.0.7175
X-MS-InvokeApp: 1; RequireReadOnly
Date: Thu, 08 Jun 2017 12:27:00 GMT
Connection: close
Content-Length: 100

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 12:59:24 GMT

Hi Chris,

I started that SAS-program and got the following output:

Value Name Macro Variable Value Purpose

_CLIENTUSERID	'e939334'	Windows user ID in SAS EG
_CLIENTUSERNAME	'Albrecht, Jörg (IVQB4D-EXTERN)'	Windows user name in SAS EG
_METAUSER	E939334	SAS metadata user ID
SYSUSERID	E939334	SAS session host account

It is not really a surprise - what did you expected to see?

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

ChrisHemedinger — Thu, 08 Jun 2017 13:04:47 GMT

Yep, that's about right. Just making sure you weren't connected with some system account on the server session. Looks like you've made a little progress. Can you use any GET methods to retrieve meaningful information from your SharePoint resources?

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

JosephHenry — Thu, 08 Jun 2017 13:09:40 GMT

So why are you sending a bearer token while also using Kerberos?

1 quick tip:

an easier way to send the headers you were trying to send would be this:

proc http

....;

headers

"Authorization" = "Bearer &token."

"X-RequestDigest" = "form digest value";

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 13:44:49 GMT

Thanks for that quick tip. I tried that before, but as soon as I use this kind of syntax, the out remains empty and the header says "Content-Length: 0".
Looks like this shortcut is not working for me.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 14:00:32 GMT

Hi Chris, good idea.

I tried this instead of the last proc http step (see Post 8):

/* try to get folder entities - just to try a GET method

Microsoft API documentation:
url: http://site url/_api/web/GetFolderByServerRelativeUrl('/Shared Documents')
method: GET
headers:
    Authorization: "Bearer " + accessToken
    accept: "application/json;odata=verbose" or "application/atom+xml"

For Proc HTTP:
url="http://&base_url./sites/_api/web/GetFolderByServerRelativeUrl('/Shared Documents')"

*/

proc http
  method='GET'
  url="http://&base_url./sites/_api/web/GetFolderByServerRelativeUrl('/Shared Documents')"
  headerout=headers
  out=resp2
  AUTH_NEGOTIATE
  HEADEROUT_OVERWRITE;
run;
%echofile(headers);

But the resp2-File gives no meaningful answer. Even though the status is 200.

HTTP/1.1 200 OK
Server: Microsoft-IIS/7.5
Date: Thu, 08 Jun 2017 13:51:40 GMT
Connection: close

So I definitely have no clue, what happens in this proc http and from where it got the result...

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

JosephHenry — Thu, 08 Jun 2017 14:03:38 GMT

I am still not clear why you are using 2 form of authentication.

You are sending in a Bearer token (I am guessing this is Microsoft Claims based auth)

and are also using NTLM or Kerberos.

If your access token is still valid, try just using the token and add AUTH_NONE to your proc statement.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

j_l_seagull — Thu, 08 Jun 2017 14:22:45 GMT

Thank you Joseph.

This is something I don't understand either. The REST-API documentation written by Microsoft told me to send that ' Authorization: "Bearer " + accessToken' header.

So you want me to try this?:

/* prepare the input header: */
data _null_;
file input recfm=f lrecl=1;
  put "Authorization: Bearer &token.";
  put '0d'x'0a'x;
  put "X-RequestDigest: form digest value";
run;


proc http
  method='POST'
  in=input
  url="http://&base_url./sites/_api/web/GetFileByServerRelativeUrl('/ASK-TP-DS/Dokumente/Forms/test_jat.txt')/CheckOut()"
  headerout=headers
  out=resp2
  AUTH_NONE
  HEADEROUT_OVERWRITE;
run;
%echofile(headers);

This results in an empty resp2 and a headers that says:

HTTP/1.1 401 Unauthorized
Server: Microsoft-IIS/7.5
SPRequestGuid: 5f57611d-0ef9-4a30-a263-d5db0ca8bdf3
WWW-Authenticate: Negotiate
WWW-Authenticate: NTLM
X-Powered-By: ASP.NET
MicrosoftSharePointTeamServices: 14.0.0.7175
X-MS-InvokeApp: 1; RequireReadOnly
Date: Thu, 08 Jun 2017 14:05:35 GMT
Content-Length: 0

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

JosephHenry — Thu, 08 Jun 2017 14:31:07 GMT

Ok,

Try not using the bearer token.

I am not an expert in SharePoint, but I have worked with it in the past and know that it can be very odd sometimes (like sending 200 responses with no content)

I would suggest just trying the GET that you were doing before, but remove the Bearer token and try AUTH_NEGOTIATE.

Re: MS-SharePoint via REST-API and NTLM-authentication with proc http

akhozan — Mon, 25 Oct 2021 21:53:36 GMT

I am trying to connect using Active Directory from Linux SAS to my on Premise SharePoint using CURL. I am able to do the command from my desktop, but am getting 401 authentication errors when trying to authenticate from ssh command line. Would it be easier to me to request a local id, instead of trying to connect with AD?

Command:

curl -v --NTLM --user $USER -L "URL" --output file.xlsx

Error:

< HTTP/1.1 401 Unauthorized
< Server: Microsoft-IIS/8.5
< SPRequestGuid: 6fb9fc9f-e632-1097-77e6-b91901d368ed
< request-id: 6fb9fc9f-e632-1097-77e6-b91901d368ed
< X-FRAME-OPTIONS: SAMEORIGIN
< SPRequestDuration: 71
< SPIisLatency: 0
< WWW-Authenticate: Negotiate
* NTLM handshake rejected
* Authentication problem. Ignoring this.
< WWW-Authenticate: NTLM
< X-Powered-By: ASP.NET
< MicrosoftSharePointTeamServices: 16.0.0.4705
< X-Content-Type-Options: nosniff
< X-MS-InvokeApp: 1; RequireReadOnly
< Date: Mon, 25 Oct 2021 21:45:11 GMT
< Content-Length: 0
<
0 0 0 0 0 0 0 0 --:--:-- --:--:-- --:--:-- 0