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:
That is it!
Liliam Leme
UK Engineer