Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 9

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

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?

Community Manager
Posts: 2,763

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

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

Occasional Contributor
Posts: 9

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

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.

Community Manager
Posts: 2,763

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

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.

SAS Employee
Posts: 7

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

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.

Community Manager
Posts: 2,763

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

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.

SAS Employee
Posts: 7

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

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
Occasional Contributor
Posts: 9

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

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
SAS Employee
Posts: 7

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

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";

 

 

Occasional Contributor
Posts: 9

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

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.
Occasional Contributor
Posts: 9

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

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.....

Occasional Contributor
Posts: 9

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

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.
Community Manager
Posts: 2,763

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

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.

Occasional Contributor
Posts: 9

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

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
_METAUSERE939334SAS metadata user ID
SYSUSERIDE939334SAS session host account

 

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

Community Manager
Posts: 2,763

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

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?

Ask a Question
Discussion stats
  • 18 replies
  • 258 views
  • 1 like
  • 3 in conversation