Active Directory

SQL

Custom access control and logging

Examples

AD Syncronization

This is a basic example how data can be retrieved from Active Directory.
You cannot specify what AD you want to ask. Whatever Active Directory the servers runs in will be used.

The code runs in our dev-environments, if you create the table ADSync.ADSync as specified.

CREATE PROCEDURE ADSync.AD_Synchronization_example
    @request	bit				= 0,
    @requestid	varchar(300)	= null,
    @data		varchar(max)	= null,
    @finished	bit				= 0
AS
BEGIN
	---------------------------------------------------------------------
	 -- This is a layout of how a basic syncronisation stored procedure 
	 -- can look like. It can be improved by using batches, logging and
	 -- dynamic assigning of roles from AD-Groups and so on.
	 --------------------------------------------------------------------
	 SET NOCOUNT ON;
	--	This example expects the following table to be available to store the data retrieved from the AD.
	/*
		CREATE SCHEMA [ADSync]
		GO
		CREATE TABLE [ADSync].[ADSync](
			[ADSyncId] [INT] IDENTITY(1,1) NOT NULL,
			[Request] [VARCHAR](300) NULL,
			[SyncDateTime] [DATETIME2](2) NOT NULL,
			[SyncData] [VARCHAR](MAX) NULL	
		CONSTRAINT [PK_ADSync] PRIMARY KEY CLUSTERED 
		(
			[ADSyncId] ASC
		) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
		) 
	 */

	------------------------
	-- SQL Call: Commands --
	------------------------
	IF @request = 1
	BEGIN
		----------------------------
		-- Clear old data         --
		----------------------------
		DELETE ADSync.ADSync

		-----------------------------------------------------------------------------------------
		-- Return instructions to component:
		-- Get all objects idenitfied as objectCategory "users", that has an "sAMAccountName" 
		-- (but not "", in the "LDAP root" of "CN=Users,DC=Multisoft,DC=se".
		--
		-- Only ask for the elements that match FieldFilter.
		-- The first five are common fields to ask for, but any field can be requested. You may
		-- get more info than you ask for, depending on if Active Directory wants to or not.
		--
		-- You can ask for anything, but narrowing down the query will improve perfonmance.
		-- "AnySystemspecificField" is used to point out that anything can be retrieved.
		-----------------------------------------------------------------------------------------
		SELECT	
			'GETOBJECTS'																				AS Request,
			'CN=Users,DC=Multisoft,DC=se'																AS Root,
			'(&(sAMAccountName=*)(!sAMAccountName="")(!objectCategory=computer)(objectCategory=user))'	AS [Filter],
			'sAMAccountName¤mail¤givenName¤sn¤memberOf¤AnySystemspecificField'							AS FieldFilter;

		RETURN;
	END;

	------------------------------------------------------------
	-- SQL Call: Request finished (One row returned per call) --
	-- Fills the ADSync.ADSync-table with the information     --
	-- requested by call above. You have to create your own   --
	-- table "ADSync".                                        --
    ------------------------------------------------------------
	IF @data IS NOT NULL
	BEGIN
		INSERT INTO ADSync.ADSync
		(
			Request,
			SyncDateTime, 
			SyncData
		)
		SELECT
			'data',
			sysdatetime(),
			@Data;
 
		RETURN;
    END

	-----------------------------------------------------------------
	-- SQL Call: Final request finished (No more rows to retrieve) --
	-- This is where the users actually get updated                --
    -----------------------------------------------------------------
 	IF @finished = 1 
	BEGIN

		DECLARE
			@SyncData      VARCHAR(MAX),
			@ADSyncID      INT,
			@ADGroupRoleID INT,
			@ADMINRoleID   INT;

		DECLARE
			@DataPart  VARCHAR(MAX),
			@Parameter VARCHAR(MAX),
			@value     VARCHAR(MAX);

		DECLARE
			@Givenname               NVARCHAR(300),
			@Surname                 NVARCHAR(300),
			@Password                VARCHAR(50),
			@UserName                VARCHAR(300),
			@UserEmail               VARCHAR(300),
			@RoleID                  INT,
			@UserID                  INT,
			@Enabled                 BIT,
			@AnySystemspecificField	 VARCHAR(MAX)

		DECLARE @Memberships TABLE 
		(
			GroupPath VARCHAR(MAX)
		);

		--------------------------------
		-- Update all users in a loop --
		--------------------------------

		DECLARE ADSyncRows CURSOR FOR
		SELECT
			ADSyncID
		FROM
			ADSync.ADSync
		WHERE
			request = 'data'
		ORDER BY
			ADSyncID
 
		OPEN ADSyncRows
  
		FETCH NEXT FROM ADSyncRows INTO @ADSyncID
 
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT	
				@Data = SyncData
			FROM
				ADSync.ADSync
			WHERE	
				ADSyncID = @ADSyncID
 
			---------------------------------------
			-- Reset and fetch user information  --
			---------------------------------------
			SELECT	
				@Givenname				= NULL,
				@Surname				= NULL,
				@UserName				= NULL,
				@UserEmail				= NULL,
				@RoleID					= NULL,
				@UserID					= NULL,
				@AnySystemspecificField	= NULL
				
			DELETE	@Memberships
 
			WHILE @Data IS NOT NULL
			BEGIN
 
				EXEC ADMIN_GETFIRSTSTRING	
					@FullString		= @Data OUTPUT,
					@FirstString	= @DataPart OUTPUT,
					@Separator		= ';'
 
				EXEC ADMIN_GETFIRSTSTRING 
					@FullString		= @DataPart OUTPUT,
					@FirstString	= @Parameter OUTPUT,
					@Separator		= '¤'
					
				SELECT @Value = @DataPart
 
				IF @Parameter = 'memberOf'
					INSERT INTO 
						@Memberships 
					SELECT 
						@Value
				ELSE IF @Parameter = 'givenName'
					SELECT @Givenname = @Value
				ELSE IF @Parameter = 'sn'
					SELECT @Surname = @Value
				ELSE IF @Parameter = 'sAMAccountName'
					SELECT @UserName = @Value
				ELSE IF @Parameter = 'mail'
					SELECT @UserEmail = @Value
				ELSE IF @Parameter = 'AnySystemspecificField'
					SELECT @AnySystemspecificField = @Value;
			END

			-- Example: Determine Role from AD Group
			-- Here we use a crude mapping from AD-Group to ADMINRole. Expects RoleId=1 to be "user" and RoleId=2 to be "SysAdmin".
			SELECT TOP 1
				@RoleID = X.ADMINRoleId
			FROM
				@Memberships MX
				JOIN 
				(
					SELECT 
						*
					FROM
					(
						VALUES
							('CN=Applikationsdriftgruppen,OU=Roller,OU=Multisoft Dist Groups,DC=Multisoft,DC=se', 'Systemadministratörer', 2, 10),
							('CN=Konsultgrupp Pegasus,OU=Enheter,OU=Multisoft Dist Groups,DC=Multisoft,DC=se','Konsult', 1, 20),
							('CN=Konsultgrupp Orion,OU=Enheter,OU=Multisoft Dist Groups,DC=Multisoft,DC=se', 'Konsult', 1, 20),
							('CN=Konsultgrupp Lynx,OU=Enheter,OU=Multisoft Dist Groups,DC=Multisoft,DC=se', 'Konsult', 1, 20)
					) AS X(GroupPath, description, ADMINRoleId, SortOrder)
				) X ON 
					MX.GroupPath = X.GroupPath
			ORDER BY
				X.SortOrder ASC;
 
			SELECT		
				@UserID		= AU.UserID
			FROM
				SoftadminApi.[User] AU 
			WHERE
				AU.UserName = @UserName

			-------------------------------------------------------------------------
			-- Example: This implementation decides that any User that is a 
			-- member of an AD Group wiht a matching Role should be an active user.
			-- Any data from the AD can be used for this purpose.
			-------------------------------------------------------------------------
			SELECT @Enabled	= CASE WHEN @RoleID IS NULL THEN 0 ELSE 1 END

			-- If a user from AD does not exist in Softadmin, and should not exist, then just ignore it.
			IF @RoleID IS NULL AND @UserID IS NULL
			BEGIN
				FETCH NEXT FROM ADSyncRows INTO @ADSyncID
				CONTINUE;
			END

			----------------------------------------------------------
			-- Update the user
			----------------------------------------------------------
			EXEC SoftadminApi.User_InsertUpdate
				@Id = @UserId OUTPUT,
				@Username = @Username,
				@Password = NULL,
				@UsernameFirst = @Givenname,
				@UsernameLast = @Surname,
				@UserEmail = @UserEmail,
				@RoleId = @RoleId,
				@IsEnabled = @Enabled

			-----------------------------------------------------------------------------------------
			-- You want to store the last date the user was synced from the AD in order to 
			-- inactivate users that no longer show up in the AD Sync.
			-- Build your own UserExtraInfo-table to store this information, or use temp tables
			-- "AnySystemspecificField" that you want to store from AD is appropriate to store as well.
			-----------------------------------------------------------------------------------------
			DECLARE @LastADUpdate DATETIME2(2) = SYSDATETIME();
			/*
			EXEC UserExtraInfo_InsertUpdate
				@UserId = @UserId,
				@AnySystemspecificField = @AnySystemspecificField,
				@LastADUpdate = @LastADUpdate
			*/
		
			FETCH NEXT FROM ADSyncRows INTO @ADSyncID
		END
 
		CLOSE ADSyncRows
		DEALLOCATE ADSyncRows
 
		------------------------------------------------------
		-- Inactivate users that no longer show up in the   --
		-- AD directory.                                    --
		-- (Commented out becase of table dependencies)     --
		------------------------------------------------------
		/*
		UPDATE U SET
			IsEnabled = 0
		FROM
			SoftadminAPI.[User] U 
			JOIN UserExtraInfo UEI ON 
				U.UserId = WU.UserId
		WHERE
			COALESCE(UEI.LastADUpdate, '2010-01-01') < DATEADD(dd, -7, GETDATE()) AND
			U.IsEnabled = 1
		*/
	END
	
END