Enables integration with the domain Active Directory.
SQL-statement to retrieve Active Directory requests (commands).
Possible value | Description |
---|---|
GETOBJECTS |
SQL-statement that the component executes whenever a request is partly finished.
Possible value | Description |
---|---|
GETOBJECTS |
Sql statement that the component executes when all requests are finished.
Deprecated. Use ADMIN_CancelMenuItem instead.
Id of the menuitem to execute if the user clicks Cancel in an ADMIN_FORCE dialog (the default being none). This value overrides cancelmenuitemid specified in the query string.
Alias of the menu group to show after execution (instead of former menu item). This value overrides any destination specified by the query string.
Deprecated. Use ADMIN_ForwardMenuGroup instead.
Id of the menu group to show after execution (instead of former menu item). This value overrides any destination specified by the query string.
Deprecated. Use ADMIN_ForwardMenuItem instead.
Id of the menu item to execute after execution (instead of former menu item). This value overrides any destination specified by the query string.
Displays a user friendly error message to the user.
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.
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'.
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