When working with enterprise n-tier applications, you may encounter access control systems that are data-driven but implemented via the application tier. That is, when the user’s credentials are submitted via the application GUI, the application tier uses its service credentials (for example, a minimally-privileged Windows login) to query its data tier tables to authenticate the user and authorize access.
Such enterprise application access models may need to reconcile user account lists periodically with security groups on the Active Directory (AD) domain for access to related assets such as file shares. It becomes advantageous to be able to query AD group membership from a SQL Server query session to be able to identify required changes in the membership and forward them to domain administration for processing.
If you’re a DBA for such an application and have sysadmin privileges, your go-to command would be xp_logininfo. Just pass in the name of the domain group and the ‘members’ argument and you’ll get back a list of the group’s members in the resultset, ready for comparison with the app-tier’s internal listing.
But, if you need to use your application service account and want to automate this with a scheduled job or SSIS package, you’re probably going to hit a roadblock asking your DBA to elevate your service account to sysadmin or, alternatively execute privs on the one extended stored procedure in the master DB. You may be directed to use PowerShell cmdlets, like Get-ADGroupMember, but they can’t run inline within a T-SQL query.
Thinking outside the box, your best approach to compare your local user listing with your AD group may be using a linked server configured with the ADSI provider.
A Note on Domain Setup
One of the challenges of making demos with AD content is working on a domain where you’re allowed to show its content. Because of the obvious sensitivities of showing any element of any real corporate network, displaying screenshots of AD elements, even with blurred or masked content may not be advisable. Besides, the concealment of the syntax and result sets reduces the value of the exhibits.
I did the next best thing by setting up a home lab on my laptop. This post won’t go into the nitty-gritty of setting up a home lab, but there’s a lot of content on the web demonstrating setting up a hypervisor and a VM configured as a domain controller.
Here’s my quick lab setup along with a link for setting up the domain controller.
- Hardware: ASUS ROG G751JT-DB73 (yes, it’s a gaming rig), with Intel Core i7 4720HQ (quad core, 2.60 GHz), 16 GB RAM, 256 GB SSD, Windows 8.1 64-Bit
- Hypervisor: Oracle VM Virtual Box Manager
- Domain Controller VM: DC01, running Windows Server 2016 Standard (Eval), 2 GB vRAM, 1 vCore and 50 GB vDisk
- I used Jacktastik Tech’s video tutorial on setting up the home lab DC.
- SQL VM: SQL01, running Windows Server 2016 Standard (Eval), 4 GB vRAM, 4 vCore, 50 GB vDisk, and SQL Server 2019 Developer
- Network: utilized the Host-Only Ethernet Adapter
Active Directory Domain in a Nutshell
I opted to go with a minimalist domain setup at the onset with just enough elements to demonstrate AD querying capabilities.
- Domain Name: mattyzdba.local
- Organizational Unit: Domain Users
- Security Groups (with Members)
- SQLDBAs
- Matt.Zuberko
- SQLEndUsers
- Hiley.A.Noid
- SQLServiceAccounts
- Agent.SQLSvc
- DBEngine.SQLSvc
- SQLDBAs
- Security Groups (with Members)
- Organizational Unit: Domain Users
Linked Server Setup
To setup a linked server to submit AD queries, you’ll use the sp_addlinkedserver stored procedure to establish a profile using the Active Directory Services Interfaces (ADSI) provider.
|
1 |
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource' |
Next, you’ll setup default login mapping between the instance and the AD service when utilizing the linked server, using sp_addlinkedsrvlogin. The following syntax indicates that the context of the calling session will be passed to AD for authentication.
|
1 |
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL |
By the nature of the linked server login, logins on the SQL Server instance that are eligible to use the linked server will only be Windows authentication logins that are authorized to connect to and poll the domain. As long as the login meets this base criteria, It can be an ad-hoc session with SSMS or you can supply it to a credential and matching proxy in SQL Server Agent for use in executing job steps like for SSIS packages.
Linked Server Query
Querying AD group memberships via T-SQL and the linked server can be done with OPENQUERY pass-through queries.
|
1 2 3 4 5 6 7 8 |
SELECT givenName,sn,samAccountName FROM OPENQUERY(ADSI, 'SELECT givenName,sn,samAccountName FROM ''LDAP://DC=mattyzdba,DC=local'' WHERE objectCategory=''person'' AND objectClass=''user'' AND memberOf=''CN=SQLDBAs,OU=Domain Users,DC=mattyzdba,DC=local''') |
In the above syntax, ADSI is the name of my linked server created at top, mattyzdba and local represent the domain components (DCs) of the two-part name of the domain (mattyzdba.local), SQLDBAs is the common name (CN) of my security group for my sysadmins, which will be found in the Domain Users organizational unit (OU).
The above query would return the First Name (givenName), SurName/LastName (sn), and login name (samAccountName) for all members of the indicated security group. This query can used in a view, CTE, or other derived table for comparisons with the application’s username list to determine which members in the AD group should be added or deleted.
Below is a screen shot of the results of querying the domain group for our sysadmins.
To zero in on the membership of a particular group, you would utilize the common name, organizational unit, and domain components in specifying the LDAP filter. However, getting the syntax correct can be tricky if you’re not familiar with navigating the AD hierarchy, especially in a large corporate AD forest. Fortunately, there are many tricks to obtain it.
One way is with PowerShell, specifying the domain group name with the Get-ADGroup cmdlet and piping out the distinguishedname property. This was run in Administrator mode.
|
1 |
Get-ADGroup SQLDBAs | Select distinguishedname |
Error Messages
Attempts to run ADSI queries with a SQL authentication session will result in a 7399 error (see below), even if the SQL Server service is running under an AD login.
|
1 2 |
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "ADSDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation. |
Tracking Group Membership with Temporal Tables
Once we have the syntax of the ADSI query figured out, we can work on constructing the tracking mechanism.
Running the ADSI query by itself will give us the current membership of the security group we indicate. However, if we want to check on the group membership over time, we’ll want to poll the group at regular intervals and compare current membership against a previous baseline.
One method to achieve this is the use of a temporal table. Available from SQL Server 2016 onward, as well as in Azure SQL DB, these system-versioned table structures incorporate effective and expiration date attributes and a history table to maintain seamless change tracking for a given user table.
T-SQL syntax for INSERT, UPDATE and DELETE operations on a temporal table look identical to their non-temporal cousins. The key difference is that original versions of records impacted by such DML queries are stored in the history table defined when the temporal table is first created.
There is no need for additional coding of stored procedures or triggers to implement the temporal capabilities. Furthermore, querying the state of records in the table at a point in time is performed with the addition of a FOR SYSTEM_TIME clause as part of the predicate for the query.
Below is the syntax for a basic AD group tracking table (ADGroupName can be normalized, but we’ll maintain the flat structure for demo purposes).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DATABASE ADGroupAudit GO USE ADGroupAudit GO CREATE TABLE dbo.ADGroupMembership ( [ADGroupMemberID] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED , [ADGroupName] nvarchar(100) NOT NULL , [ADGroupMember] varchar(100) NOT NULL , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ADGroupMembership_History)) GO CREATE UNIQUE INDEX UIX_ADGroupMembership ON dbo.ADGroupMembership (ADGroupName,ADGroupMember) GO |
Below, I created a stored procedure to handle DML operations on the table, passing the name of the security group I’m querying for its membership via the linked server, storing its results in a temp table and then using a MERGE statement to synchronize the listing with the temporal table.
OPENQUERY requires a string literal for the query being passed to the linked server, so we need to use some dynamic SQL to construct the query (be sure to use safeguards from SQL injection or use a vetted source of security group names like from sys.server_principals as a parameter source depending on your access level).
Note the use of the CTE as the MERGE target to ensure when indicating the temporal table I filter on the one group. This will allow the table to track membership of multiple security groups. Also, I make an assumption that the specified security group is in the Domain Users OU. This procedure can be modified to pass in the entire distinguishedName property from the security group for improved accuracy.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
USE ADGroupAudit GO CREATE OR ALTER PROCEDURE dbo.usp_SynchADGroup (@ADGroupName NVARCHAR(100) = 'SQLDBAs' ) AS BEGIN SET NOCOUNT ON IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL DROP TABLE #tmp SET @ADGroupName=REPLACE(@ADGroupName,';','--;') --replace any semicolon terminators with comment tags to thwart SQL injection attempts CREATE TABLE #tmp ( ADGroupName NVARCHAR(100) ,ADGroupMember NVARCHAR(100) ) DECLARE @SQL NVARCHAR(512) = 'SELECT ' + QUOTENAME( @ADGroupName, '''') + 'ADGroupName,samAccountName FROM OPENQUERY(ADSI,''SELECT samAccountName From ''''LDAP://DC=mattyzdba,DC=local'''' WHERE objectCategory=''''person'''' AND objectClass=''''user'''' AND memberOf=''''CN=' + @ADGroupName + ',OU=Domain Users,DC=mattyzdba,DC=local'''''')' INSERT INTO #tmp EXEC (@SQL); WITH CurrentADGroupMembers ( [ADGroupMemberID] ,[ADGroupName] ,[ADGroupMember] ) AS ( SELECT ADGroupMemberID ,ADGroupName ,ADGroupMember FROM [dbo].[ADGroupMembership] WHERE ADGroupName = @ADGroupName ) MERGE INTO CurrentADGroupMembers AS Target USING ( SELECT ADGroupName ,ADGroupMember FROM #tmp ) AS Source(ADGroupName, ADGroupMember) ON (target.ADGroupName = Source.ADGroupName) WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED BY TARGET THEN INSERT ( ADGroupName ,ADGroupMember ) VALUES ( source.ADGroupName ,source.ADGroupMember ); DROP TABLE #tmp SET NOCOUNT OFF END GO |
The above procedure could be scheduled to run at regular intervals, updating its current listing in the ADGroupMembership table while migrating expired rows to the ADGroupMembership_History table.
To query the temporal table, T-SQL offers specialized syntax in seamlessly querying the main and history table.
For a particular moment in time.
|
1 2 3 4 5 6 7 8 |
SELECT [ADGroupName] ,[ADGroupMember] ,[ValidFrom] ,[ValidTo] FROM [ADGroupAudit].[dbo].[ADGroupMembership] FOR SYSTEM_TIME AS OF '2020-01-27' WHERE ADGroupName='SQLDBAs' |
For all current and expired records between two points in time.
|
1 2 3 4 5 6 7 8 |
SELECT [ADGroupName] ,[ADGroupMember] ,[ValidFrom] ,[ValidTo] FROM [ADGroupAudit].[dbo].[ADGroupMembership] FOR SYSTEM_TIME CONTAINED IN ('2020-01-26','2020-01-28') WHERE ADGroupName='SQLDBAs' |
For all current and historical data.
|
1 2 3 4 5 6 7 8 |
SELECT [ADGroupName] ,[ADGroupMember] ,[ValidFrom] ,[ValidTo] FROM [ADGroupAudit].[dbo].[ADGroupMembership] FOR SYSTEM_TIME ALL WHERE ADGroupName='SQLDBAs' |
Summary
Performing administrator-level queries with non-administrator privileges can be daunting especially with regards to access control and other sensitive topics. However, with reasonable permissions granted in partnership with your DBA team and leveraging OPENQUERY, other T-SQL commands, and PowerShell, you can accomplish essential tasks while adhering to your organization’s InfoSec requirements.
Temporal tables offer opportunities to introduce data versioning to various applications, whether corporate or IT, providing alternatives to DML triggers with powerful querying capabilities.
Please be sure to to visit my blog at www.mattyzdba.net for additional SQL Server developer and administrator techniques.
Leave A Comment