Spark Notebook error: Java.sql.SQLException:User does not have permissions to perform this action

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