06-07-2017 11:47 AM
I'm trying to simply check in and out some documents in a MS-SharePoint-environment. From a SAS-program.
Environment: Window Server
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&$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: 184.108.40.20675 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?
06-07-2017 12:28 PM
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.
06-07-2017 12:57 PM
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.
06-07-2017 01:13 PM
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.
06-07-2017 01:43 PM
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:
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.
06-07-2017 01:46 PM
06-08-2017 08:48 AM
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: 220.127.116.1175 X-MS-InvokeApp: 1; RequireReadOnly Date: Thu, 08 Jun 2017 12:27:00 GMT Connection: close Content-Length: 100
06-08-2017 09:09 AM
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:
"Authorization" = "Bearer &token."
"X-RequestDigest" = "form digest value";
06-08-2017 09:44 AM
06-08-2017 03:59 AM
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.....
06-08-2017 04:18 AM
06-08-2017 08:27 AM
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.
06-08-2017 08:59 AM
I started that SAS-program and got the following output:
|_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?
06-08-2017 09:04 AM
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?