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'
---Run on SQL DW DB
CREATE USER username
FOR LOGIN loginname
WITH DEFAULT_SCHEMA = dbo
-- Add user to the database role
EXEC sp_addrolemember N'db_datareader', N'username'
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):