SAS PROC SOAP Updates Sharepoint List

Question

I am trying to update the Sharepoint list (2007) using SAS (9.3) via PROC SOAP (SAS lives on Unix GRID). Basic authentication is enabled on the sharepoint site (since PROC SOAP cannot authenticate through NTLM).

I can easily pull data from a Sharepoint list using the XML Libname mechanism, but I cannot transfer XML PROC SOAP data back to the Sharepoint list web service. In particular, I am trying to add and update items in a sharepoint list via http://[intranet_site]/sites/IT/_vti_bin/Lists.asmx

SAS LOG Output:

 18399 %let RESPONSE=RESPONSE; 18400 proc soap in=REQUEST 18401 out=&RESPONSE 18402 url="http://[intranet_site]/sites/IT/_vti_bin/Lists.asmx" 18403 webusername="[username]" 18404 webpassword="[password]" 18405 webdomain="[domain]" 18406 SOAPACTION="http://schemas.microsoft.com/sharepoint/soap/UpdateListItems" 18407 ; 18408 run; ERROR: org.springframework.ws.client.WebServiceTransportException: Unauthorized [401] 

I confirmed through SOAPUI that the XML passed to the List.asmx web service is valid (I can actually create and update list items as expected in Sharepoint when executed manually through SOAPUI.

Since the error clearly indicates that for some reason, user authentication provided in PROC SOAP does not turn it into a Sharepoint (I have administrator rights in Sharepoint, so I must have the correct rights). What is confusing about this is that I can pass the same credentials through XML Libname and distract the data just fine ...

** Questions **

• If I pass my credentials through PROC SOAP, why can't I authenticate to Sharepoint?
• Is there any other job to transfer this XML to Sharepoint (IE: XML LIBNAME or PROC HTTP capable of supporting this by passing to XML)?
• What is the problem: in SAS with PROC SOAP (in how I call the procedure) or using Sharepoint?

... for completeness only, the XML example passed to PROC SOAP can be seen below (SOAP 1.1 - which must be supported by PROC SOAP):

 <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <UpdateListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <listName>{851DDBB5-1765-444D-9012-0210F006A4AF}</listName> <updates> <Batch OnError="Continue" ListVersion="1"> <Method ID="1" Cmd="New"> <Field Name='ID'>NEW</Field> <Field Name='Title'>DUMMY</Field> [...shortened for space...] </Method> </Batch> </updates> </UpdateListItems> </soap:Body> </soap:Envelope> 

Answer 1

I refused to use SAS PROC SOAP to interact with SharePoint. NTLM authentication was too large to overcome obstacles. I looked at a rather bleak future, manually executing SOAP XML several times a day SOAPUI indefinitely ... Currently, SAS is not enough when it comes to NTLM authentication function (based on discussions with SAS support, they plan to build this functionality there, but it is still being tested at this point).

Then I found a solution: cURL !!!
Additional curl information found here: http://curl.haxx.se/

Since I use SAS EG on Linux, and CURL is installed on the server, I can use the SAS X command to connect curl commands to the console. Combining this with sleep allows the hang time to pass the XML SOAP to the SharePoint web service and capture the response. After that, I pull out the response file to analyze it for errors. If an error is detected, I send an error notification to the support group to debug it. So far, it works quite well.

Sample code found below:

 DATA _NULL_; x "cd /prg/SOAP_Request"; /*Navigate to where my XML is stored */ x "curl -X POST -k --negotiate -u 'MyUsername':MyPassword --digest --ntlm -H ""SOAPAction: ""http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"" "" -H ""Content-Type: text/xml; charset=UTF-8"" -d @request_&CLIENTID._&Exets..xml http://Shrpnt/sites/IT/_vti_bin/Lists.asmx > /prg/SOAP_Response/response_&CLIENTID._&Exets..xml"; /* issue the CURL command */ x=sleep(20,1); /* Have SAS sleep for 20 seconds to allow CURL to process */ run; 

(To test the CURL command, I used Putty to make sure that I can successfully execute the HTTP POST and get / grab the response. After that I converted the code so that it could dynamically retract the saved XML file and save the response using the same methodology.

Hope this helps everyone who faces this situation. Now I can use SAS and manage SharePoint lists.

Answer 2

Announcement 1) I assume that the test with SOAPUI is a Windows check on Windows, your situation with SAS is Unix for Windows, probably where the problem is. I would advise contacting SAS support with your question, they should have already tried ... I would also recommend getting some SAS on Windows and trying everything. From what I was looking at unix and sharepoint, authentication seems to be the source of the problems, so it is not specific to SAS.

Ad 2) XML libname is actully for reading and writing static xml files - no request, no response. PROC HTTP cannot perform SOAP.

Announcement 3) I bet the problem is with basic authentication and Unix.