%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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%3CLINGO-SUB%20id%3D%22lingo-sub-1751141%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-1751141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F647010%22%20target%3D%22_blank%22%3E%40Liliam_Leme%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F296845%22%20target%3D%22_blank%22%3E%40Johannes_Vink%3C%2FA%3E%26nbsp%3BI%20ran%20into%20same%20error.%20When%20I%20added%20the%20Synapse%20Workspace%20Managed%20Identity%20as%20external%20user%20in%20the%20database%20and%20subsequently%20granted%20db_owner%20rights%20(instead%20of%20only%20db_datareader)%20to%20the%20external%20user%2C%20then%20I%20did%20not%20get%20this%20error.%20Not%20sure%20what%20role%20is%20minimally%20required%20for%20the%20external%20user%2C%20but%20db_datareader%20is%20not%20sufficient%20(also%20when%20you%20only%20need%20to%20read%20data%20in%20your%20pipeline)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752749%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-1752749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822503%22%20target%3D%22_blank%22%3E%40rebremer%3C%2FA%3E%26nbsp%3B%2C%20thanks%20for%20sharing.%20but%2C%20were%20you%20reading%20data%20from%20SQLPool%20with%20managed%20identities%20on%20the%20notebook%20and%20db_datareader%20failed%3F%20Is%20that%20what%20happened%3F%20Or%20were%20you%20trying%20to%20read%20the%20data%20from%20the%20pipeline%20and%20the%20permission%20was%20not%20enough%3F%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F296845%22%20target%3D%22_blank%22%3E%40Johannes_Vink%3C%2FA%3E%26nbsp%3B%20-%20I%20forgot%20to%20add%20more%20information%20about%20your%20question%3A%3C%2FP%3E%0A%3CP%3Ethere%20are%20limitations%20for%20managed%20identities%20on%20SQL%20Pool%20documented%20here%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcopy-into-transact-sql%3Fview%3Dazure-sqldw-latest%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcopy-into-transact-sql%3Fview%3Dazure-sqldw-latest%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EBut%20the%20syntax%20would%20be%20like%3A%20spark.read.option(%22useAzureMSI%22%2C%22true%22)%26nbsp%3B%20-%20anyway%2C%20there%20is%20some%20discussion%20about%20this%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F45261%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FMicrosoftDocs%2Fazure-docs%2Fissues%2F45261%3C%2FA%3E%26nbsp%3Band%20they%20updated%20the%20docs%20here%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdatabricks%2Fdata%2Fdata-sources%2Fazure%2Fsynapse-analytics%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdatabricks%2Fdata%2Fdata-sources%2Fazure%2Fsynapse-analytics%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAlternatively%2C%20if%20you%20use%20ADLS%20Gen2%20%2B%20OAuth%202.0%20authentication%20or%20your%20Azure%20Synapse%20instance%20is%20configured%20to%20have%20a%20Managed%20Service%20Identity%20(typically%20in%20conjunction%20with%20a%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fblog%2Fgeneral-availability-of-vnet-service-endpoints-for-azure-sql-data-warehouse%2F%22%20data-linktype%3D%22external%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EVNet%20%2B%20Service%20Endpoints%20setup%3C%2FA%3E%3CSPAN%3E)%2C%20you%20must%20set%3C%2FSPAN%3E%26nbsp%3B%3CCODE%3EuseAzureMSI%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3Bto%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Etrue%3C%2FCODE%3E%3CSPAN%3E.%20In%20this%20case%20the%20connector%20will%20specify%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EIDENTITY%20%3D%20'Managed%20Service%20Identity'%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3Bfor%20the%20databased%20scoped%20credential%20and%20no%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ESECRET%3C%2FCODE%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752919%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-1752919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F647010%22%20target%3D%22_blank%22%3E%40Liliam_Leme%3C%2FA%3E%20%2C%20my%20setup%20was%20as%20follows%3A%3C%2FP%3E%0A%3CP%3E-%20External%20SQLDW%20in%20which%20the%20Synapse%20workspace%20MI%20is%20added%20as%20external%20user%3C%2FP%3E%0A%3CP%3E-%20Spark%20notebook%20in%20Synapse%20workspace%20that%20retrieves%20data%20from%20external%20SQLDW%20(and%20builds%20an%20ML%20model)%3C%2FP%3E%0A%3CP%3E-%20Spark%20notebook%20is%20triggered%20using%20Synapse%20pipeline%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20following%20happens%3A%3C%2FP%3E%0A%3CP%3E-%20If%20I%20grant%20Synapse%20workspace%20MI%20user%20db_datareader%20rights%20in%20external%20SQLDW%2C%20then%20pipeline%20fails%3C%2FP%3E%0A%3CP%3E-%20If%20I%20grant%20Synapse%20workspace%20MI%20user%20full%20db_owner%20rights%20in%20external%20SQLDW%2C%20then%20pipeline%20runs%20successfully%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752986%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-1752986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822503%22%20target%3D%22_blank%22%3E%40rebremer%3C%2FA%3E%26nbsp%3Binteresting%20scenario.%20Let%20me%20check.%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

6 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.

Microsoft

@Liliam_Leme @Johannes_Vink I ran into same error. When I added the Synapse Workspace Managed Identity as external user in the database and subsequently granted db_owner rights (instead of only db_datareader) to the external user, then I did not get this error. Not sure what role is minimally required for the external user, but db_datareader is not sufficient (also when you only need to read data in your pipeline)

Microsoft

@rebremer , thanks for sharing. but, were you reading data from SQLPool with managed identities on the notebook and db_datareader failed? Is that what happened? Or were you trying to read the data from the pipeline and the permission was not enough?

@Johannes_Vink  - I forgot to add more information about your question:

there are limitations for managed identities on SQL Pool documented here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

But the syntax would be like: spark.read.option("useAzureMSI","true")  - anyway, there is some discussion about this here https://github.com/MicrosoftDocs/azure-docs/issues/45261 and they updated the docs here: https://docs.microsoft.com/en-us/azure/databricks/data/data-sources/azure/synapse-analytics

Alternatively, if you use ADLS Gen2 + OAuth 2.0 authentication or your Azure Synapse instance is configured to have a Managed Service Identity (typically in conjunction with a VNet + Service Endpoints setup), you must set useAzureMSI to true. In this case the connector will specify IDENTITY = 'Managed Service Identity' for the databased scoped credential and no SECRET.

Microsoft

@Liliam_Leme , my setup was as follows:

- External SQLDW in which the Synapse workspace MI is added as external user

- Spark notebook in Synapse workspace that retrieves data from external SQLDW (and builds an ML model)

- Spark notebook is triggered using Synapse pipeline

 

The following happens:

- If I grant Synapse workspace MI user db_datareader rights in external SQLDW, then pipeline fails

- If I grant Synapse workspace MI user full db_owner rights in external SQLDW, then pipeline runs successfully

 

Microsoft

@rebremer interesting scenario. Let me check.