6

I am trying set up RLS and want to leverage AD groups. DB is created in Azure, and I know AD is working as I can connect using AD acount with SSMS

First test Local Machine, is working as expected (img 1)

enter image description here

Now try Azure

enter image description here

The IS_MEMBER() appears to not work as expected in Azure for AD Groups, but it is recognising it a as valid AD domain of the Domain/group (see first 2 lines)

Any ideas?? I want to be able to have a view, that has the ADGroups and then put Row Level Security RLS to filter the view for different groups

Cheers

Mark

Mark West
  • 61
  • 3

2 Answers2

1

This is quite old tread but I was facing similar issue, so if someone encountered it, hope this might help.
I had RLS implemented on SQL Server on IaaS and it was based on Active Directory Groups checks by IS_MEMBER() function. (I have sync with AAD, but here used config with local AD). It worked well until I migrated to PaaS (Azure SQL DB Single instance).

First thing was that while on IaaS I used to refer to AD groups from local AD domain controler:
domain\groupname
CREATE LOGIN [domain\groupname] FROM WINDOWS
CREATE USER [domain\groupname] FOR LOGIN [domain\groupname]
select is_member('domain\groupname')

In PaaS I needed to create users for groups and reference to them without domain, as I had to switch to use integrated Azure Active Directory:
groupname
CREATE USER [groupname] FROM EXTERNAL PROVIDER
select is_member('groupname')

Second thing was something that looks like a bug (currently have open ticket with MS)
While IS_MEMBER worked correctly for test users in PaaS, I had problem with Admin Accounts. Taking for example user1 who was in [adminGroup] on DataDB IS_MEMBER always returned 0, no matter which AAD group I was checking. What was more strange, when changing to Master db IS_MEMBER worked fine there for any AAD group!
(offtopic: on master db users where also created to avoid problem with SSMS default option to reach to master DB first, and without addition there non-admin users would get connection error (so either users needed to specify in SSMS connection options destination DB, or be added with connect rights to master DB))

It turned out that having [adminGroup] being set up as Azure Active Directory Admin on Azure SQL Server was reason for this strange behavior. I changed in our code to use [adminGroup2] for RLS, and removed admins (users) in AAD from [adminGroup] which was kept as SQL Server Azure Active Directory Admin. And with just this change IS_MEMEBER started to work just as expected!

Other workaround I have found here, would be to use IS_MEMBER('db_owner') for admin group (as those default roles checks are working fine, but this needs additional changes to security config and would not solve problems for other cases then admins, but actually looks like this is the only problem here.

Waiting to hear response from MS about this bug, as I doubt that that would be implemented by design...

shimon893
  • 176
  • 4
1

Thanks for letting us know via Support. IS_MEMBER is only supported for database roles or Windows groups. Azure Active Directory groups are not supported as of today, but we are looking at this for the future.

I have updated the doc to make this more clear: https://learn.microsoft.com/en-us/sql/t-sql/functions/is-member-transact-sql

regards

Andreas

Andreas Wolter
  • 653
  • 3
  • 10