%3CLINGO-SUB%20id%3D%22lingo-sub-1543255%22%20slang%3D%22en-US%22%3ESpark%20Notebook%20error%3A%20Java.sql.SQLException%3AUser%20does%20not%20have%20permissions%20to%20perform%20this%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543255%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20working%20on%20this%20case%20last%20week%20with%20permission%20error%20on%20the%20spark%20notebook%2C%20so%20basically%20the%20scenario%20was%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.%20Loading%20data%20from%20another%20database%20to%20DB%20container.%3C%2FP%3E%0A%3CP%3E2.%20Loading%20Data%20from%20Datawarehouse%20using%20Spark%20Notebook%3C%2FP%3E%0A%3CP%3EWhen%20the%20second%20step%20was%20executed%20the%20error%20bellow%20was%20throw%3A%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EError%3A%20java.sql.SQLException%3A%20com.microsoft.sqlserver.jdbc.SQLServerException%3AUser%20does%20not%20have%20permissions%20to%20perform%20this%20action%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20the%20error%20message%20is%20pretty%20clear%3A%20%3CSTRONG%3EThis%20is%20a%20permission%20error.%3C%2FSTRONG%3E%20The%20solution%20was%20also%20simple%20as%20the%20message.%3C%2FP%3E%0A%3CP%3EWe%20created%20a%20SQL%20User%20on%20the%20Db%20for%20this%20process%20specific.%20As%20this%20process%20requires%20only%20data%20reader%20permission%20that%20was%20the%20one%20given%20to%20the%20user.%3C%2FP%3E%0A%3CPRE%20class%3D%22hljs%22%3E%3CCODE%3E---Run%20on%20Master%20DB%3CBR%20%2F%3ECREATE%20LOGIN%20loginmame%20%20%20%20WITH%20PASSWORD%20%3D%20Lalala!0001'%0A%0AGO%0A%0A%20%0A---Run%20on%20SQL%20DW%20DB%0ACREATE%20USER%20username%0A%0AFOR%20LOGIN%20loginname%0A%0AWITH%20DEFAULT_SCHEMA%20%3D%20dbo%0A%0AGO%0A%0A%20%0A%0A--%20Add%20user%20to%20the%20database%20role%0A%0AEXEC%20sp_addrolemember%20N'db_datareader'%2C%20N'username'%0A%0AGO%0A%0AGRANT%20CONNECT%20TO%20username%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAfter%20that%20we%20changed%20the%20notebook%20process%20to%20run%20using%20the%20SQL%20User%2FPassword%20that%20we%20just%20created.%20As%20it%20follows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ESpark%20script%20using%20SQL%20User%20to%20be%20executed%20on%20the%20notebook%20(spark%20Scala)%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22hljs%22%3E%3CCODE%3Eval%20df%20%3D%20spark.read.%0A%0Aoption(Constants.SERVER%2C%20%22Workspacename.sql.azuresynapse.net%22).%0A%0Aoption(Constants.USER%2C%20%22user%20%22).%0A%0Aoption(Constants.PASSWORD%2C%20%22password%22).%0A%0Asqlanalytics(%22Databasename.dbo.tablename%22)%0A%0A%20df.show(1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAlso%20as%20figure%26nbsp%3B%201%20exemplifies%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22spark_notebook.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207622i22C06350D0224338%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22spark_notebook.png%22%20alt%3D%22spark_notebook.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThat%20is%20it!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELiliam%20Leme%3C%2FP%3E%0A%3CP%3EUK%20Engineer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1543255%22%20slang%3D%22en-US%22%3E%3CP%3EWhile%20running%20spark%20notebook%20I%20hit%20the%20error%3A%26nbsp%3B%3CSTRONG%3EError%3A%20java.sql.SQLException%3A%20com.microsoft.sqlserver.jdbc.SQLServerException%3AUser%20does%20not%20have%20permissions%20to%20perform%20this%20action%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1543255%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20Security%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Spark%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Support%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543331%22%20slang%3D%22en-US%22%3ERe%3A%20Spark%20Notebook%20error%3A%20Java.sql.SQLException%3AUser%20does%20not%20have%20permissions%20to%20perform%20this%20acti%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543331%22%20slang%3D%22en-US%22%3E%3CP%3EAnd%20is%20this%20also%20possible%20with%20using%20a%20managed%20identity%3F%20It%20does%20not%20look%20like%20it%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdatabricks%2Fdata%2Fdata-sources%2Fazure%2Fsynapse-analytics%23--authentication%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdatabricks%2Fdata%2Fdata-sources%2Fazure%2Fsynapse-analytics%23--authentication%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544050%22%20slang%3D%22en-US%22%3ERe%3A%20Spark%20Notebook%20error%3A%20Java.sql.SQLException%3AUser%20does%20not%20have%20permissions%20to%20perform%20this%20acti%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544050%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20could%20use%20the%20AAD.%20You%20could%20also%20change%20the%20context%20of%20the%20execution%20using%20the%20notebook%20on%20the%20pipeline%20and%20than%20work%20in%20a%20different%20way%20for%20authentication.%20For%20the%20notebook%20let%20me%20look%20into%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

I was working on this case last week with permission error on the spark notebook, so basically the scenario was: 

1. Loading data from another database to DB container.

2. Loading Data from Datawarehouse using Spark Notebook

When the second step was executed the error bellow was throw:

Error: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException:User does not have permissions to perform this action

 

So the error message is pretty clear: This is a permission error. The solution was also simple as the message.

We created a SQL User on the Db for this process specific. As this process requires only data reader permission that was the one given to the user.

---Run on Master DB
CREATE LOGIN loginmame WITH PASSWORD = Lalala!0001' GO ---Run on SQL DW DB CREATE USER username FOR LOGIN loginname WITH DEFAULT_SCHEMA = dbo GO -- Add user to the database role EXEC sp_addrolemember N'db_datareader', N'username' GO GRANT CONNECT TO username;

After that we changed the notebook process to run using the SQL User/Password that we just created. As it follows.

 

Spark script using SQL User to be executed on the notebook (spark Scala):

val df = spark.read.

option(Constants.SERVER, "Workspacename.sql.azuresynapse.net").

option(Constants.USER, "user ").

option(Constants.PASSWORD, "password").

sqlanalytics("Databasename.dbo.tablename")

 df.show(1)

Also as figure  1 exemplifies:

spark_notebook.png

That is it!

 

Liliam Leme

UK Engineer

2 Comments
Senior Member

And is this also possible with using a managed identity? It does not look like it:

https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/azure/synapse-analytics#--authen...

Microsoft

You could use the AAD. You could also change the context of the execution using the notebook on the pipeline and than work in a different way for authentication. For the notebook let me look into it.