This article is contributed. See the original author and article here.
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!
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.