oauth_bearer does not seem to support encoded tokens

[js5]

Hello,

I am trying to set up a program calling rest API in order to trigger a databricks job. The issue I am facing is that I cannot encrypt the token and it only works if the oauth_bearer value is provided in plaintext. This gives me error 401:

filename pwfile 'testpw.txt';

proc pwencode in="foo" out=pwfile method=sas005;
run;

data _null_;
	infile pwfile truncover;
	input line :$200.;
	call symputx('dbxtoken',line);
run;

filename jpl 'testin.json';

proc json out=jpl;
	write values "job_id" "1234";
run;

filename jrp 'testout.json';

proc http url="https://dbc-abcd-1234.cloud.databricks.com/api/2.2/jobs/run-now"
	method=post
	in=jpl
	out=jrp
	oauth_bearer="&dbxtoken";
run;

Whereas this works as intended:

filename jpl 'testin.json';

proc json out=jpl;
	write values "job_id" "1234";
run;

filename jrp 'testout.json';

proc http url="https://dbc-abcd-1234.cloud.databricks.com/api/2.2/jobs/run-now"
	method=post
	in=jpl
	out=jrp
	oauth_bearer="&foo";
run;

Is this expected? And if so, what would be the alternative for not having to store the service principal secret in plaintext? Thanks!

[SASKiwi]

What does the Databricks job do? What about using SAS/ACCESS Interface to Databricks to execute a stored procedure running the processes you want instead? 

[js5]

The job copies data from SQL server bound as catalog to another schema. We are testing direct upload via ODBC in parallel, but unfortunately the performance is insufficient.

[SASKiwi]

I recommend you engage with Tech Support regarding the slow upload.

[js5]

I did engage both SAS and Databricks tech support, but I am exploring alternatives in parallel.

[Patrick · Accepted solution]

The 3rd party app with which SAS communicates doesn't "know" how SAS password encoding and decoding works. SAS must therefore send the password cleartext. This is also true for SAS Access where you're passing something like pw="{SAS005}6DDE667...." SAS will during runtime decode the password and send it cleartext to the database.

It appears such decoding is not implemented for proc http with oauth_bearer parameter and therefore you can't use a SAS encoded password but must send it cleartext.

Based on some Googling Databricks supports OAuth Token Federation - but SAS only fully supports it in the Viya version. ...and it would also require some setup.

For OAuth Bearer Token you could store your token in a secure location and access it during runtime (=not directly storing it in the script). Another option would be to wrap your code into a SAS macro that you compile secure into some permanent SAS catalogue - with the token provided as macro parameter when you compile the macro.