topic Re: Sql Error on MultiNode cluster, but fine on SingleNode in Get Started Discussions

Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Thu, 17 Oct 2024 17:43:42 GMT

If I run the following code on a cluster in SingleNode mode it works fine, but if I run the exact same cell on a MultiNode Cluster It throws:

SparkConnectGrpcException: (java.sql.SQLTransientConnectionException) Could not connect to address=(host=HOSTm)(port=PORT)(type=master) : (conn=715518) Connections using insecure transport are prohibited while --require_secure_transport=ON.

code:

secret_scope="myScope" user = dbutils.secrets.get(secret_scope, "MyUser") password = dbutils.secrets.get(secret_scope, "MyPass") url = dbutils.secrets.get(secret_scope, "MyURL") #format: jdbc:mysql://DOMAIN:PORT/SCHEMA host = dbutils.secrets.get(secret_scope, "MyHost") options = { "url": url, "query": "select 1 as id", "user": user, "password": password, "useSSL": "True", "sslmode": "required", "ssl" : "{ \"ca\" = \"/dbfs/databricks/certs/aws-global-bundle.pem\"}", "serverSslCert": "dbfs:/databricks/certs/aws-global-bundle.pem", "isolationLevel":"READ_UNCOMMITTED", "enabledSslProtocolSuites":"TLSv1.2", } df = spark.read.format('JDBC').options(**options).load() df.display()

Any ideas? seems like maybe its some spark setting I'm missing.

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Fri, 18 Oct 2024 02:26:54 GMT

Try moving the .pem files from dbfs to WSFS or Volume

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Fri, 18 Oct 2024 15:14:51 GMT

I tried both and get the same error.

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Fri, 18 Oct 2024 15:33:53 GMT

changed to:

"serverSslCert": "/Volumes/catalog/schema/volume/aws-global-bundle.pem"
got the same error
to confirm its not an issues with the pem file I was able to view the contents of the file with:
dbutils.fs.head("/Volumes/catalog/schema/volume/aws-global-bundle.pem")

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Fri, 18 Oct 2024 15:48:16 GMT

Try this code from Shared Compute

dbutils.fs.ls("/dfbs/databricks/")

I see you are using /dbfs/databricks under SSL key.

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Fri, 18 Oct 2024 16:19:40 GMT

Thanks for the quick reply.

dbutils.fs.ls("dbfs:/databricks/certs/") [FileInfo(path='dbfs:/databricks/certs/aws-global-bundle.pem', name='aws-global-bundle.pem', size=152872, modificationTime=1728053118000)]

current state:

secret_scope="myScope" user = dbutils.secrets.get(secret_scope, "MyUser") password = dbutils.secrets.get(secret_scope, "MyPass") url = dbutils.secrets.get(secret_scope, "MyURL") #format: jdbc:mysql://DOMAIN:PORT/SCHEMA host = dbutils.secrets.get(secret_scope, "MyHost") options = { "url": url, "query": "select 1 as id", "user": user, "password": password, "useSSL": "True", "sslmode": "required", "serverSslCert": "dbfs:/databricks/certs/aws-global-bundle.pem", "isolationLevel":"READ_UNCOMMITTED", "enabledSslProtocolSuites":"TLSv1.2", } df = spark.read.format('JDBC').options(**options).load() df.display()

Same error:

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Tue, 22 Oct 2024 03:48:35 GMT

I see you gave a thumbs up; if the solution worked, can you please Accept it?

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Tue, 22 Oct 2024 14:48:48 GMT

Sorry, I was just trying to get you attention back on this post. No the issue is not resolved.

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Tue, 22 Oct 2024 15:09:02 GMT

Have you granted ANY FILE access?

https://docs.databricks.com/en/dbfs/unity-catalog.html#how-does-dbfs-work-in-shared-access-mode

GRANT SELECT ON ANY FILE TO `<user@domain-name>`

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Tue, 22 Oct 2024 15:33:40 GMT

Thanks for looking into this,
I was able to grant myself select on any file, but it did not resolve the issue

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Tue, 22 Oct 2024 19:01:16 GMT

Will it be possible to paste the cluster config screenshot of the ones that work and the ones that fail?

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Tue, 22 Oct 2024 19:37:52 GMT

Can you try with this config?

options = { 
"url": url, 
"query": "select 1 as id", 
"user": user, 
"password": password, 
"useSSL": "true", # Use lowercase 'true' 
"sslmode": "VERIFY_CA", 
"serverSslCert": "/dbfs/databricks/certs/aws-global-bundle.pem", 
"isolationLevel": "READ_UNCOMMITTED", 
"enabledSslProtocolSuites": "TLSv1.2", }

There are some minor changes please see whether this works in both cluster modes.

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Tue, 22 Oct 2024 19:45:59 GMT

unfortunately, same error.
Emailed the cluster config json files to our SA, whom you are working with.
the difference is:
works:
"data_security_mode": "NONE",
"spark_conf": {
"spark.master": "local[*, 4]",
"spark.databricks.cluster.profile": "singleNode"
},

doesn't work:
"spark_conf": {}
"data_security_mode": "USER_ISOLATION"

Re: Sql Error on MultiNode cluster, but fine on SingleNode

gchandra — Tue, 22 Oct 2024 20:12:05 GMT

data_security_mode": "NONE": This is a non-Unity Catalog Cluster. No Governance enforced.

"data_security_mode": "USER_ISOLATION": This is a UC Shared Compute cluster that has certain limitations when accessing Low-Level APIs, RDDs, and dbfs/data bricks folders.

If the .pem files are copied under /Workspace/Shared or /Volumes you should be able to access them via

/Workspace/Shared/file.pem
/Volumes/path/file.pem

Please make sure READ access to these folders is available.

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Tue, 22 Oct 2024 21:26:49 GMT

so I copied the pem file into a volume:

/Volumes/catalog/schema/volume/aws-global-bundle.pem

and to the workspace:

/Workspace/Shared/global-bundle.pem

data sec mode: None cannot read this file and so throws the error:

Failed to find serverSslCert file. serverSslCert=/Volumes/catalog/schema/volume.....

this is expected since UC is not enabled basically in this mode.
I get the same thing if I try to reference it at the workspace location: /Workspace/Shared/global-bundle.pem.

But really I want it to work with data security mode User isolation. the result is the same error as before using both locations:

What is interesting to me is its either not getting to the part where it looks for this pem file, OR its getting passed it but erroring out afterwards. To test this I tried a bogus file location in the volume

/Volumes/catalog/schema/volume/bad.pem

Same error. Which makes me thing its erroring before its even going to look for that file....

Re: Sql Error on MultiNode cluster, but fine on SingleNode

brandocomando8 — Wed, 23 Oct 2024 17:18:25 GMT

Any ideas on what to try next?