Component: Active Directory

 Show all Hide all

Enables integration with the domain Active Directory.

SQL

SQL Call: Commands (mandatory)

SQL-statement to retrieve Active Directory requests (commands).
Supports custom errors: No
May modify database: No

Parameters:

@request mandatory bit
Set to '1' by Softadmin® when the component asks for active directory requests.

Resultset: AD commands

Resultset with active directory requests (commands).
Repeat mode: repeated exactly once
Row count: zero or more rows

Columns:

Data optional string
Additional information to be used by request. Leave out this column for GETOBJECTS.
FieldFilter optional string
Fields to extract from each directory entry in the query. If left out, all properties are returned for each entry. Common properties are dc (domain component),ou (organizational unit),cn (common name), givenName (first name),sn (surname), sAMAccountName (login),memberOf (group belongings).
Filter optional string
Filter for request (operators & and | can be used), example: Accounts with login "xxx" or persons with surname beginning with "A" (|(&(objectCategory=users)(SAMAccountName=xxx))(&(objectCategory=person)(sn=A*)))
Request mandatory string
Type of request to Active Directory. Supported requests are: GETOBJECTS.
Possible value Description
GETOBJECTS
RequestId mandatory string
Id value of the active directory request.
Root optional string
Root node to perform request in, example: ou=XX,dc=multisoft,dc=se.

SQL Call: Request finished

SQL-statement that the component executes whenever a request is partly finished.
Supports custom errors: No
May modify database: Yes

Parameters:

@data mandatory string
Data associated to the request with requestid. For GETOBJECTS @data is "path¤pathvalue;¤property1¤value1;property2¤value2..." where path is the entry's location in the directory hierarchy and propery is mail,sn,givenname,etc...
@requestid mandatory string
Id of the request.

Resultset: Follow-up requests

Resultset with Active Directory requests (commands) for follow-up queries.
Repeat mode:
Row count:

Columns:

Data optional string
Additional information to be used by request. Leave out this column for GETOBJECTS.
FieldFilter optional string
Fields to extract from each directory entry in the query. If left out, all properties are returned for each entry. Common properties are dc (domain component),ou (organizational unit),cn (common name), givenName (first name),sn (surname), sAMAccountName (login),memberOf (group belongings).
Filter optional string
Filter for request (operators & and | can be used), example: Accounts with login "xxx" or persons with surname beginning with "A" (|(&(objectCategory=users)(SAMAccountName=xxx))(&(objectCategory=person)(sn=A*)))
Request mandatory string
Type of request to Active Directory. Supported requests are: GETOBJECTS.
Possible value Description
GETOBJECTS
RequestId mandatory string
Id value of the active directory request.
Root optional string
Root node to perform request in, example: ou=XX,dc=multisoft,dc=se.

SQL Call: Final request finished (mandatory)

Sql statement that the component executes when all requests are finished.
Supports custom errors: No
May modify database: Yes

Parameters:

@finished mandatory bit
Set to '1' by Softadmin® when the component has processed all commands. Including follow-up requests.

Resultset: Forwarding definitions

Repeat mode:
Row count:

Columns:

<querystring_variable> optional string
Any column with no other specific meaning will pe passed along on the querystring to the menuitem you are forwarding to.
admin_cancelcidstepsback optional int
Number of steps in the page history to jump back if the user clicks OK in an ADMIN_MESSAGE dialog or Cancel in an ADMIN_FORCE dialog (the default being none). This value overrides cancelcidstepsback specified in the query string.
admin_cancelmenuitemid optional int
Id of the menuitem to execute if the user clicks OK in an ADMIN_MESSAGE dialog or Cancel in an ADMIN_FORCE dialog (the default being none). This value overrides cancelmenuitemid specified in the query string.
admin_cidstepsback optional int
Number of steps in the page history to jump back after execution (the default being one step back). This value overrides any destination specified by the query string.
admin_force optional string
Prompts the user with the specified text and the user may answer OK or cancel. If the user chooses OK the sql call will be rerun with the parameter @force set to 1.
admin_forward optional string
Displays a user friendly message and then forwards to the next menu item.
admin_forwardmenugroupid optional int
Id of the menu group to show after execution (instead of former menu item). This value overrides any destination specified by the query string.
admin_forwardmenuitemid optional int
Id of the menuitem to execute after execution (instead of former menu item). This value overrides any destination specified by the query string.
admin_message optional string
Displays a user friendly message to the user.
admin_pastehtmlfrompopup optional string
Pastes HTML. See admin_setfieldvaluefrompopup
admin_setfieldvaluefrompopup optional string
Sets the value of field specified in the menuitempopup call. Only select this column if menuitem is opened in popup.
admin_closepopup optional bit
If this column is anything but NULL the popup will be closed. Only select this column if the menuitem is opened in popup.
The default behavior is to step back inside the popup window and close it if there is nothing to step back to.
admin_unselect optional bit
Alias for ADMIN_UnselectAll.
Cache optional string
Cache key to be cleared. Supports wildcards.
CacheUserId optional string
Either a user id or '%'.

Clears all caches (e.g. access permissions) related to the specified user id.

Use '%' to clear caches for all users.
cancelbuttontext optional string
Changes the text of the Cancel button when used with ADMIN_Force.
okbuttontext optional string
Changes the text of the OK button when used with ADMIN_Message, ADMIN_Force, or ADMIN_Forward.

SQL Call: Validate parameters

Allows you to validate the parameters supplied by the user before any other SQL is run in the component. This call is only made if the component has visible parameters, the SQL is a stored procedure, and 'Validate parameters' is checked.
Supports custom errors: No
May modify database: No

Parameters:

@force optional bit
@validateparams mandatory bit
This parameter is set to 1 by Softadmin® when this call is made.

Custom access control and logging

SQL Call: Custom access control and logging

Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.

Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.

The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.

If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.
Supports custom errors: No
May modify database: Yes

Resultset: Access permissions

Return whether the user is allowed to visit the menu item with the current parameters.
Repeat mode: repeated exactly once
Row count: exactly one row

Columns:

GrantAccess mandatory bit
1 if the user is allowed to view the menu item, 0 if the user should not be allowed to view the menu item.

If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.

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 sepcified.

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               VARCHAR(300),
			@Surname                 VARCHAR(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