We have a solution in place to apply table level security via sql endpoints . The security group doesn't gets resolved at DB level while adding the security groups to the respective roles .
We are using pyODBC to execute SQL Security commands with a Service Principal. The commands
- Create a custom role
- Add a Security Group to the role
When executing these commands with the pyODBC and a Service Principal, it errors out with the following:
'42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Principal \'SEC-GLO-DaaS-NBI-Users\' could not be resolved. Error message: \'Server identity is not configured.
Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity"
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal?view... (33134) (SQLExecDirectW)'
Note:
The Service Principal has admin rights on the workspace.
We can execute the same commands as a standard user that is an admin on the workspace.
We have tried the following which hasn't resolved the issue:
- Removing the Service Principal from the workspace and re-adding the SP - no difference
- Testing in clean environment just to be sure & it fails with the same error
We suspect something is changed for sql endpoint accessed via service principal, probably missing roles as per the error message above.
