MS SQL Schema of Integration Data

The integration data schema defines the structure of the data imported by the MS SQL integration. The primary data model entities include Accounts, with mandatory and optional attributes; Credentials Types and Policies applicable at account creation and credentials rotation; Resources, categorized by resource type; and Entitlements, categorized by permission type.

Accounts

Native SQL logins being the server-level principals stored in the master database and used in SQL Server Authentication are retrieved from the MS SQL server as the source system during the integration data aggregation process. These logins are then normalized and mapped to Account objects within the MS SQL integration data model.

Within the integration interface, the aggregated accounts can be accessed at: ObserveID > Identity Automation > Integrations > {specific MS SQL integration} > Accounts

The schema of account additional properties is presented in the table below. Properties that are required when creating an account are marked as Required in the On Creation column. Properties that are optional at account creation, or that can be modified later, are marked as Allowed in the On Creation column or in the On Update column, respectively.

Provisioning Rules are used to define the value of a property during account creation or update when the property is required at creation or allowed to be updated. N/A indicates that no provisioning rule is defined for the property.

Custom properties are not listed below and can be added on an ad hoc basis together with the corresponding provisioning rule.

Account Property	Type	Description	Provisioning Rule	On Creation	On Update
Name	String	The SQL Server Login name.	Required	Required	Required
CheckExpiration	Boolean	Applies only to SQL Server logins. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.	Available	Allowed	Allowed
CheckPolicy	Boolean	Applies only to SQL Server logins. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.	Available	Allowed	Allowed
DefaultDatabase	String	Name of the default database of the user when a connection is established.	Available	Allowed	Allowed
DefaultLanguage	String	Default language of the user.	Available	Allowed	Allowed
IsNtGroup	Boolean	It is a True\False parameter. If true, it indicates that the Login is a Windows group.	N/A	N/A	N/A
IsNtUser	Boolean	It is a True\False parameter. If true, it indicates that the Login is a Windows user.	N/A	N/A	N/A

Credentials Type and Policies

A password is used as the primary credential type for the native SQL logins created and managed with the MS SQL integration. The available password policies are listed in the table below. A default password policy can be configured for integration operations such as account creation, credential updates, and password rotation. For Password Change requests, a password policy different from the default can be selected.

Password Policy	Description
Auto Generate Permanent Credentials	Automatically generates a permanent password.
Auto Generate One Time Credentials	Automatically generates a one time password required to be changed by the user on the first login to the MS SQL server,
Set Specific Permanent Credentials	A user can establish a permanent password in alignment with the MS SQL credentials policy requirements.
Set Specfific One Time Credentials	A user can establish a one time password in alignment with the MS SQL credentials policy requirements. The password must be changed by the user on the first login to the MS SQL server.

Resources

The resource objects are imported from MS SQL and displayed as the resources in ObserveID. The attributes of each resource type that a specific resource belongs to are displayed as Additional Properties objects of the Resource.

Resource Property	Type	Description
Server resource type
CreatedDateTime	String	Timestamp of when the server was created.
Database resource type
CreatedDateTime	String	Timestamp of when the database was created.
Table resource type
CreatedDateTime	String	Timestamp of when the table was created.

Entitlements

The permission objects are imported from MS SQL and displayed as the entitlements in ObserveID. The attributes of each permission type that a specific entitlement belongs to are displayed as Additional Properties objects of the Entitlement.

Permission Property	Type	Description
ServerFixedRole permission type
CreatedDateTime	DateTime	Timestamp of when the ServerFixedRole was created.
ServerGrant permission type
CreatedDateTime	DateTime	Timestamp of when the ServerGrant was created.
ServerUserDefinedRole permission type
CreatedDateTime	DateTime	Timestamp of when the ServerUserDefinedRole was created.
DatabaseFixedRole permission type
CreatedDateTime	DateTime	Timestamp of when the DatabaseFixedRole was created.
DatabaseGrant permission type
CreatedDateTime	DateTime	Timestamp of when the DatabaseGrant was created.
DatabaseUserDefinedRole permission type
CreatedDateTime	DateTime	Timestamp of when the DatabaseUserDefinedRole was created.
DatabaseObjectGrant permission type
CreatedDateTime	DateTime	Timestamp of when the DatabaseObjectGrant was created.