Issue connecting to databricks using JDBC (using service principal)

[mhr]

Below is my connection string I am attempting to use.   Connecting to databricks with a newly created service principal.    Getting the error below.  Any thoughts on what may be causing the error.   I can connect fine when using individual ID and token, this is my first time attempting to connect thru a service principal.   Thanks!

libname dbsap jdbc driverclass="com.databricks.client.jdbc.Driver"

url="jdbc:databricks://1036081183257708.8.gcp.databricks.com:443;

transportMode=http;ssl=1;AuthMech=11;Auth_Flow=1;httpPath=/sql/1.0/warehouses/ce232efbef7b00e9;

OAuth2Client=XXX;

OAuth2Secret=XXX;"

classpath="/sas/install/drivers" schema="ing_sap"

PRESERVE_TAB_NAMES=YES PRESERVE_COL_NAMES=YES;

getting this error:

ERROR: Error trying to establish connection: [Databricks][JDBCDriver](500151) Error setting/closing session: 401 Unauthorized .
ERROR: Error in the LIBNAME statement.

[SASKiwi]

Are you using SAS 9.4 or Viya? This link covers SAS 9.4 JDBC Databricks setup.

[mhr]

Sorry, using SAS 9.4 and I have used that documentation to successfully connect with a personalized token but was trying to use a service principal with a secret and getting the error above. 

[SASKiwi]

Looks like Azure service principals are supported on Viya using the SPARK engine.

I also asked Google if this is supported on SAS 9.4 and got this:

I've got no way of checking this so I suggest you try Google yourself:

[gwootton · Accepted solution]

From this documentation:
https://docs.databricks.com/aws/en/integrations/jdbc/authentication#oauth-machine-to-machine-m2m-aut...

The URL is:
jdbc:databricks://<server-hostname>:443;httpPath=<http-path>;AuthMech=11;Auth_Flow=1;OAuth2ClientId=<service-principal-application-id>;OAuth2Secret=<service-principal-oauth-secret>

It looks like you might need to use "OAuth2ClientId" instead of "OAuth2Client", not sure if those are aliased. A 401 suggests it's not getting the credentials or authentication is otherwise failing rather than an issue with authorization.

[mhr]

That worked!  Thank you so much.    I am having permissions errors now but that has to be on the databricks side of things and will need to get with the administrator to see how they setup the account.

ERROR: Prepare error: [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: 42501, Query: select vko***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: Error running query:
[INSUFFICIENT_PERMISSIONS] org.apache.spark.sql.AnalysisException: [INSUFFICIENT_PERMISSIONS] Insufficient privileges:, User does not have USE SCHEMA on Schema 'saras_prod.ing_sap'. SQLSTATE: 42501, at
org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:49),