SETTING THE APPLICATION ROLE CAN CAUSE IS_MEMBER TO return NULL

In the simplest terms an application role is one which allows access to a database through an application.

As simple as that may sound there are some clauses which make these quite an interesting feature.

An application role is like a plain canvas, open to the imagination of the painter. Unlike the built in database roles they have no members of their own, and also need to be explicitly enabled (disabled by default).

Application roles are not partial to any authentication mode and work well with both. They are activated by using sp_setapprole .

Living vicariously through the GUEST account they can access databases only through permissions granted in those databases to the guest user account.

Application role does not even try to contend with the security offered by Windows groups. We know the security offered by windows is far more granular and if given an opportunity an obvious choice for all applications.

There might be a need where the Application role and IS_MEMBER function to query Windows Group membership need to coexist. But this is not allowed by SQL server design.

The IS_MEMBER function indicates whether the current user is a member of the specified Microsoft Windows group or Microsoft SQL Server database role.

By SQL server design behavior once the Application role has been enabled the effective permissions for that connection switches context from the user permissions to the application role permissions.

INFORMATION

IS_MEMBER function uses a Windows API (GetTokenInformation: https://msdn.microsoft.com/en-us/library/aa446671(VS.85).aspx ) to query the Group Membership information using the Access Token that is built when a user logs into the machine. This token wouldn’t be accessible to a user whose authentication level is limited to the SQL Server instance and not to the machine as a whole.

This is the primary reason our IS_MEMBER query return a “NULL” when we try to query this with our application role.

So if we need to find way to query Windows Group membership (Local/Domain) while still using Application Roles we need to find alternatives to IS_MEMBER.

How this could be reproduced is shown as follows:

Executing this script will demonstrate how the IS_MEMBER query returns a “NULL” within an application role.

<<REPRO>>

SELECT IS_MEMBER('BUILTIN\Administrators'); -- Returns 1

GO

CREATE APPLICATION ROLE test_app_role WITH PASSWORD='fdsd896#gfdbfdkjgh700mM';

GO

DECLARE @cookie varbinary(8000);

EXEC sp_setapprole 'test_app_role', 'fdsd896#gfdbfdkjgh700mM', @fCreateCookie = true, @cookie = @cookie OUTPUT;

-- REPLACE THIS GROUP NAME TOO

SELECT IS_MEMBER('BUILTIN\Administrators'); -- Returns NULL

EXEC sp_unsetapprole @cookie;

GO

SELECT IS_MEMBER('BUILTIN\Administrators'); -- Returns 1

GO

DROP APPLICATION ROLE test_app_role;

GO

<</REPRO>

Amrutha Varshini,
SE, Microsoft Sql Server

Reviewed by

Amit Banerjee,
Technical Lead, Microsoft Sql Server