Configuration for MS SQL integration operations
The base setup of the MS SQL integration enables successful data aggregation in accordance with the defined integration data schema. The collected and normalized MS SQL integration data is presented in two perspectives: the account, resource and entitlement catalogues in the MS SQL integration and the Analytics warehouse. Both allow users to perform access auditing and data analysis.
To support workflow automation, provisioning and reconciliation, additional integration configuration is required, including:
- Identity lifecycle management
- Access control and account administration
- Automated access provisioning for onboarding/reinstatement scenarios
- Automated access termination for offboarding scenarios
- Other custom integration operations
Identity lifecycle management
To correctly identify the identity owner of an account created directly on the SQL Server, and to determine the account type for subsequent access governance processes, at least the following rules must be configured:
- A Correlation Rule, which automatically assigns an account owner during the data aggregation process.
- A Customization Rule, which determines the account type based on the agreed account naming convention.
Correlation Rule Code Sample
var accountName = readOnlyAccess360DbContext.Accounts
.Where(x => x.IntegrationId == integrationId &&
x.Id == accountId &&
!x.Terminated)
.Select(x => x.Name)
.FirstOrDefault();
if (string.IsNullOrEmpty(accountName))
return null;
// Normalize
accountName = accountName.Split('_')[0];
// Find identity
var ownerIdentityId = readOnlyAccess360DbContext.Identities
.Where(x => !x.Terminated)
.Where(x => string.Equals(
x.Name.Replace(" ", ""),
accountName,
StringComparison.OrdinalIgnoreCase))
.Select(x => x.Id)
.FirstOrDefault();
return ownerIdentityId != default
? new(ownerIdentityId, OwnerType.Identity)
: null; Customization Rule Code Sample
var accountName = readOnlyAccess360DbContext.Accounts
.Where(x => x.IntegrationId == integrationId &&
x.Id == accountId &&
!x.Terminated)
.Select(x => x.Name)
.FirstOrDefault();
if (accountName?.StartsWith("adm_", StringComparison.OrdinalIgnoreCase) == true)
{
return (AccountType.Privileged, null, null, null);
}
if (accountName?.StartsWith("tmp_", StringComparison.OrdinalIgnoreCase) == true)
{
return (AccountType.Temporary, null, null, null);
}
return (AccountType.User, null, null, null); Access control and account administration
Configuration of MS SQL provisioning rules is required for account creation and for updating additional account properties. Each provisioning rule defines the value of a specific additional account property. Some properties are mandatory at account creation, while others are optional. Provisioning rules for mandatory properties must be configured to enable successful account creation. Provisioning rules for optional properties may be configured as needed.
For MS SQL account creation, the Name provisioning rule that defines the account name must be configured to enable the creation of native SQL logins. MS SQL does not impose strict naming conventions for login names; therefore, they can follow corporate standards and adopt the format such as johndoe_dev or [email protected].
const string postfix = "-dev";
var identityName = identity.Name;
// If identity name looks like an email, keep only the local part
var atIndex = identityName.IndexOf('@');
if (atIndex >= 0)
{
identityName = identityName.Substring(0, atIndex);
}
// Remove spaces
var name = identityName.Replace(" ", "");
// Add account-type prefix
if (accountType == AccountType.Temporary)
{
name = "tmp_" + name;
}
else if (accountType == AccountType.Privileged)
{
name = "adm_" + name;
}
else if (accountType == AccountType.Firecall)
{
name = "adm_s_" + name;
}
// Build base account name
var baseAccountName = $"{name}_{postfix}";
// Try base name, then base_1 ... base_19
for (var counter = 0; counter < 20; counter++)
{
var candidateName = counter == 0
? baseAccountName
: $"{baseAccountName}_{counter}";
var exists = readOnlyAccess360DbContext.Accounts.Any(x =>
!x.Terminated &&
x.IntegrationId == integrationId &&
x.Name == candidateName);
if (!exists)
{
return candidateName;
}
}
throw new InvalidOperationException(
$"The number of accounts with base name '{baseAccountName}' exceeded the limit of 20 checked variants.");Automated access provisioning for onboarding/reinstatement scenarios
To enable automated provisioning of MS SQL access during identity onboarding or reinstatement, corresponding birthright roles must be defined with the required MS SQL access assignments. This ensures that new identities are provisioned with accounts that include the specified MS SQL entitlements. For existing identities, any reconciliation of MS SQL access is performed through an Identity Update request, in full alignment with the applicable birthright role assignment rule, which can be coded in C# or configured with the filters.
Automated access termination for offboarding scenarios
The Leaver Rule of the MS SQL integration does not impose predefined constraints and allows administrators to define the approach for handling MS SQL accounts when an identity is offboarded. The following Account Termination Behavior options are available:
.LockAndRemoveAllEntitlements- removes assigned entitlements and then locks the MS SQL account..Delete- deletes the MS SQL account..Lock- locks the MS SQL account..TransferOwnership- transfers ownership of the MS SQL account to another identity..DoNothing- leaves the MS SQL account unchanged.
A sample implementation of the Leaver Rule is provided below. To switch between termination options, set the required value for the AccountTerminationBehavior. object.
return (AccountTerminationBehavior.Delete, null, null);Custom Integration Operations
Upon request, the MS SQL integration can be extended with custom operations implemented on an ad hoc basis and tailored to the specific needs of an organization. This enables the creation of customized functional capabilities designed to meet unique business requirements.