Active Directory

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