• Sign In
  1. Messages

Problem with import of Labkey data into SAS

Installation Forum (Inactive)

View Message

view list print
Problem with import of Labkey data into SAS Lind-Thomsen  2015-12-21 05:09
Status: Closed
 
Hi

I'm trying to import a labkey list into SAS.

I have installed the SAS and Java libraries and added them to the paths in the sasv9.cfg file

I have created a _netrc file in my home directory (there is a copy attached with a changed password, but the password starts with a '-' which is kept), and I made an environmental variable named HOME pointing to my home directory.

SAS code:
%labkeySelectRows(dsn=mydata,
        baseUrl="http://rghorion001:8080/persimune",
        folderPath="/Persimune",
        schemaName="lists",
        queryName="mikrobiologi_pos_prover");

SAS error:
ERROR: Could not instantiate class org/labkey/remoteapi/sas/SASSelectRowsResponse at line 27 column 69.
ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.
dec 21, 2015 1:24:21 PM org.labkey.remoteapi.Command _execute
INFO: Requesting URL:
http://rghorion001:8080/persimune/query/Persimune/selectRows.api?apiVersion=9.1&query.queryName=mikrobiologi_pos_prover&schemaName=l
ists&query.maxRows=1
org.labkey.remoteapi.CommandException: Unauthorized
     at org.labkey.remoteapi.Command.throwError(Command.java:355)
     at org.labkey.remoteapi.Command._execute(Command.java:321)
     at org.labkey.remoteapi.Command.execute(Command.java:202)
     at org.labkey.remoteapi.query.SelectRowsCommand.execute(SelectRowsCommand.java:61)
     at org.labkey.remoteapi.sas.SASBaseSelectCommand.execute(SASBaseSelectCommand.java:65)
     at org.labkey.remoteapi.sas.SASSelectRowsResponse.<init>(SASSelectRowsResponse.java:54)
     at org.labkey.remoteapi.sas.SASSelectRowsResponse.<init>(SASSelectRowsResponse.java:44)

This indicates a login error, so I tried using Rlabkey to import the same list into R,
which works, so there isn't an error in the _netrc file (unless SAS and R parse/interpret it differently).

In the logfile, localhost_access_log I see:
[21/Dec/2015:13:15:36 +0100] "GET /persimune/query/Persimune/selectRows.api?apiVersion=9.1&query.showRows=all&query.queryName=mikrobiologi_pos_prover&schemaName=lists HTTP/1.1" 200 85737276 (for the R access)
[21/Dec/2015:13:24:22 +0100] "GET /persimune/query/Persimune/selectRows.api?apiVersion=9.1&query.queryName=mikrobiologi_pos_prover&schemaName=lists&query.maxRows=1 HTTP/1.1" 401 6444 (for SAS access)

There doesn't seem to be anything in the other log-files. (labkey.log, labkey-errors.log)

For good measure I restart SAS after each change.

Furthermore, if I add the userName and password to the SAS-macro call, then it also works.

My own conclusion is tha tit must på the SAS interpretation of the _netrc file that fails, but because it wotks with Rlabkey i'm not sure what kind of error it could be. Do you have any idea what might cause this?

Best wishes
Allan Lind-Thomsen

Labkey ver. 15.3-41022.16
SAS Ver. 9.4
Labkey Server: windows 8
Client windows 7
 
 
Jon (LabKey DevOps) responded:  2015-12-21 12:23
Hi Allan,

Thanks for bringing this to our attention. SAS 9.4 shouldn't be behaving this way with our API and last I checked in 15.2, things were working just fine with the netrc file, so I'd imagine that something may have gotten changed in 15.3 inadvertently.

I'll create a bug on this for further investigation and we will get back to you on this issue.

Thank you for your patience.

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2015-12-21 12:30
Investigative bug has been created.

https://www.labkey.org/home/Developer/issues/issues-details.view?issueId=25199
 
Jon (LabKey DevOps) responded:  2015-12-21 12:47
Hi Allan,

Can you try something for us? Take your _netrc file and make a copy of it as .netrc.

One of the ideas is that the code is looking for a .netrc file rather than a _netrc one, even though you're on a Windows environment.

Give this file change a try and let us know if you manage to authenticate.

Regards,

Jon
 
Lind-Thomsen responded:  2015-12-21 22:47
Hi Jon

I tried to copy the _netrc file to .netrc but still couldn't authenticate.

I noticed one thing about the urls in the log:
In the R-request it says: apiVersion=8.3&query.showRows=all
while in the SAS request it says: apiVersion=9.1&query.maxRows=1

maybe more interesting in the URL-request from SAS that works it also uses:
query.showRows=all like in the R-request,

I checked the versions of the plugins:
Rlabkey version is 2.1.128
and I downloaded: LabKey15.3-41116.23-ClientAPI-SAS.zip for SAS

best wishes
Allan
 
adam responded:  2015-12-22 06:32
Hi Allan,

That '-' is just a standard ASCII dash, right? Are there any other non-ASCII characters in your netrc file (e.g., in your actual password)?

Thanks,
Adam
 
Lind-Thomsen responded:  2016-01-05 03:41
Hi Adam

Sorry for the delay.

Yes it is a standard dash.

Best wishes
Allan
 
Jon (LabKey DevOps) responded:  2016-01-06 17:09
Hi Allan,

I'm grasping at straws here, but can you test this again by creating another user with a password that doesn't have any special characters (just alpha-numeric only), update your netrc file with those credentials and try using the SAS query again?

I'm just curious to see whether if it is just the password being a problem or if it is something else entirely.

Regards,

Jon
 
Lind-Thomsen responded:  2016-02-02 05:19
Hi Jon
Sorry for the long delay in answering, but I have had computer problems which prevented me from testing your suggestion.
But now I have and removing the dash, so the password is alphnumeric only, doesn't help.

Best wishes
Allan
 
Jon (LabKey DevOps) responded:  2016-02-03 13:54
Hi Allan,

Thanks for the update! I've added an additional note to the bug ticket.

I've been doing some extensive research on this problem, but haven't made any gains in figuring out a cause for the issue. I am wondering if perhaps utilizing a web debugging tool like Fiddler (http://www.telerik.com/fiddler) or network trace tool like Wireshark (https://www.wireshark.org/) might give us a little bit more insight here. Although the stack error and the HTTP response (401 error) show that it is a problem with authorization, we know that the username and password are obviously right since the netrc file works with R without issue. But there's got to be some kind of either disconnect between passing the actual credentials or possibly something else being passed with the username/password combo that is causing the invalid authentication. I'm hoping that maybe if you run Fiddler on the machine that is making the request (and the authentication), it might show the credentials being passed across.

Give this article a look and let me know if you think this might be something you can possibly run on your machine?

http://www.telerik.com/blogs/api-testing-with-telerik-fiddler

Regards,

Jon
 
Jon (LabKey DevOps) responded:  2016-02-03 21:00
Hi Allan,

Before you go the tracing route, we hadn't considered the Audit Log within LabKey.

If you were to go into the Admin Console and look at the User Events in the Audit Log, we can see whether that SAS connection is actually authenticating or not from there and can at least determine whether it's passing the correct username.

Can you do a test by using SAS API like before and then see what the Audit Log shows for that action?

Regards,

Jon
 
Lind-Thomsen responded:  2016-02-04 06:45
Hi Jon

I looked in the user event log and there is no entry when I use the .netrc file through SAS, but there is an entry in localhost_access_log on the server.
I will try to look at the network analysis programs, but it might take a few days.

Best wishes
Allan
 
Jon (LabKey DevOps) responded:  2016-02-04 18:21
Hi Allan,

We know that the SAS API is being called on, otherwise we wouldn't have seen the entry in the localhost access log file. We know the credentials are good since you were able to use that netrc file with your R client successfully.

We're wondering whether that SAS client is even seeing that netrc file at all in order to be used. Typically the netrc file is going to be located in the user's home directory, but maybe that file has to be placed elsewhere. Can you possibly copy your netrc file and place it on the root of the C;\ drive, then retry the connection again?

Regards,

Jon
 
Lind-Thomsen responded:  2016-02-04 22:57
Hi Jon

That was spot on. It seems that SAS import doesn't use the HOME variable to find the _netrc file, but just look in the root directory.

Thanks a lot for your help.

Best wishes
Allan
 
adam responded:  2016-02-05 15:40
Our netrc parser uses the official Java approach for finding the home directory: System.getProperty("user.home")

Which JVM version is specified in your SAS configuration? Looks like Java 8 tried to make this property more reliable by following a more official algorithm.

Potentially interesting reading: http://stackoverflow.com/questions/585534/what-is-the-best-way-to-find-the-users-home-directory-in-java

Adam