CODEWITHSUNDEEP
sql-server-2016
LastTribunal
LastTribunal

Reputation: 1

SQL Row Level Security combined with SQL Token Authentication

I have RLS implemented in Azure SQL2016 DB, and it works fine when using UserID and Password in connection string. However, When I use Token authentication (which works without RLS) instead of u/p, RLS doesn't work. I get the expected error when inserting:

The attempted operation failed because the target object 'server.dbo.mytable' has a block predicate that conflicts with this operation. If the operation is performed on a view, the block predicate might be enforced on the underlying table. Modify the operation to target only the rows that are allowed by the block predicate.The statement has been terminated

Upvotes: 2

Views: 1216

Answers (1)

phil
phil

Reputation: 3658

Integrated security still works in my instance if I include an initial catalog with the connection string

<!-- Does not work -->
ConnectionString="Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"


<!-- Works -->
ConnectionString="Data Source=(localdb)\MSSQLLocalDB;Integrated Security=True;initial catalog=MyDatabase;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True"

Upvotes: 1

Related Questions