I know that SQL Server's RLS uses Windows Authentication, but is it possible to use external authentication? Like Google or Microsoft auth?
I am doing testing on it using C# Web API 2 can't figure out how.
Thanks in advance!
1 Answer
The Row-Level-Security feature does not use Windows Authentication. You are creating your own function, that is used as filtering predicated. For example, you can use built-in function USER_NAME() (check the official documentation for the full working example):
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';
This function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').
You can use also sp_set_session_context and SESSION_CONTEXT to build more complex logic. For example, you can calculated the security access in your .net code and set session context for the SQL session your are opening. Then, using SESSION_CONTEXT you can check the access in your filtering function.
