Add the Microsoft SQL Server Connection

How the Database, Active Directory, and Security Settings Work
Add the SQL Server Connection
Specify the Connection Information
- How to Configure Active Directory
Specify the SQL Security Settings

How the Database, Active Directory, and Security Settings Work

The settings that you specify in the following topics work together to create the connection to the SQL Connector:

Add the SQL Server Connection:
- See how to add the standard connection to the MS SQL server databases.
Specify the Connection Info:
- See the connection details that you require to access the source system Your Source System is the repository where your data is stored (data to be indexed). This repository is managed by applications such as: - SharePoint O365 - SharePoint 2013/16/19 - Documentum - File Share - OpenText - Lotus Notes - etc. Your Source System repository can also be a database such as SQL or Oracle. database.

Note: You must specify the settings on this page before you specify the settings on either the AD A directory service for Windows domain networks. A hierarchical structure that stores information about objects on the network. Used to manage permissions and control access to critical network resources. Settings page or the Security SQL page.

Configure the Active Directory Settings:
- Specify the connection information for your AD and the configuration information that determines how user groups are mapped into your AD system.
- This configuration ensures that the native security in the source system can be maintained for the content when this content is stored in SharePoint.
- BA Insight recommends that you work with your BA Insight consultant to perform these tasks.
- For more information, contact BA Insight Support.
Configure the Active Directory Settings:
- Specify the queries that are used to identify users and user groups from the source system, along with how those groups are populated.

Tip: You are only required to save your settings after you have specified your selections for all of the tabs on this page.

Add the SQL Server Connection

To add the SQL connection, follow these steps:

Launch the Connectivity Hub administration site.
Select Connections.
Click New > SQL connection.

Specify the Connection Information

To specify the connection information, follow these steps:

In the Connection Info tab, in the Title field, enter the title for your SQL Connector such as SQL Server Connector.:
Select General Settings tab.
Database Server Address:
1. This is the connection string to your MS SQL server instance.
2. If you are using named instances, this account should include the instance name such as MyServerName.
Database Name:
1. This is the name of the database to connect to.
Connection Timeout:
- Enter a valid value
Database Login:
1. Authentication Mode: Select one of the following:
  1. Service Account: it is not necessary to enter an SQL Account or a Password.
    1. This specification identifies whether the service account is used or if you must specify a username and password.
    2. The Service Account mode uses the:
      - SharePoint Search Service Account to access the database when crawling
      - SharePoint Timer Service Account when Connectivity Hub tasks are running
      - SharePoint Central Admin application pool account when performing tasks (such as setup and configuration) in the Connectivity Hub user interface
  2. Specify User Account: Enter your SQL server credentials including Login and Password.
Click Save. Optional.

Hint: You are only required to save your settings after you specify the settings for all of the tabs on this page.

How to Configure Active Directory

Active Directory (AD) configuration is required.

This configuration ensures that the native security in the source system is honored.

To specify the Active Directory (AD) settings, follow these steps:

From the top navigation menu select Tools>Configuration.
Select the Target A Target is a "pointer" to a specific instance of a search application, such as Elasticsearch. - (A Search application instance has one or more indexes) Directory tab.

Configuration

Configure the Active Directory using the fields on the page.

Active Directory Server
1. Enter the name of an existing Connection to enable the Connections to share the input Connection’s mapping.
2. If an existing Connection is entered into this field:
  - The connection's mapped users and groups are used
  - The rest of the settings on this page are replicated from your current Connection
Authentication mode
1. Use Service Account
Root Domain
1. Enter the Domain name that is used to map users from your source system into Active Directory.
  1. Your entry must be the fully qualified domain name format.
  2. For example: subdomain.mydomain.local.
Content Owner
1. This is the domain user such as MyDomain\contentowner.
2. This user will have access to all search results.
3. This must be a domain user with full read access to the information in SharePoint that is crawled by this content source Content Sources do the following: Receive data from the Source System via the Connection, Filter the data it receives, Provide the results to the Target, Define the specific search index that contains the content you wish to index (and later search).
4. Typically, this is the SP Search Service account, or similar administrative account.

Tip: If a dynamic group is created in Active Directory, this group is perpetuated in Connectivity Hub and the Connector Framework manages this group.

Note: Make sure this location exists and that the Active Directory Login account information has group management privileges.

Specify the SQL Security Settings

The information provided on the Security SQL tab determines how the SQL Connector identifies users, groups, and group members.

This information includes SQL statements that query the source system to gather the information.
Optionally, you can write VB.NET scripts to process the information in order to provide more options for user mapping.
For information, see VB.NET scripts.

To specify the security SQL settings, follow these steps:

Use the Security:Scripts tab to enter SQL statements to load the listings of groups and users.
In Connectivity Hub, select the Connections tab.
On your SQL connection select the Edit from the drop-down menu.
Select the Security tab. Assign a Master security connection, if desired.
Select the Security: Directory tab and select options as desired.
Select the Security: Scripts tab and enter the following information as appropriate for your environment:

Users load query:

This is an SQL statement that identifies all of the users in the source system.
The query must return a unique identifier for the user and the user name in the source system.
Optionally, fields are returned for the user’s Active Directory ID along with a field that reports whether the group is active.

All of the fields in the query should be identified by their column names as described in the table below:

Table 1: User Load SQL Fields

Field	Description
SPW_USERID	Required The unique identifier that is used by the source system to identify users.
SPW_USERNAME	Required The username for the user that is used by the source system (might be the same as the user ID.)
SWP_ADID	Optional The active directory ID of the user.
SPW_ACTIVE	Optional Identifies whether the user is active or not.

Groups load query:

This is an SQL statement that identifies all of the security groups or roles in the source system.
This query must return a unique identifier for each group along with the group name from the source system.
Optionally, fields are returned for the group’s Active Directory ID.
In addition, or alternatively, a field that identifies whether the group is active is returned.

All of the fields should be identified by their column names in the query as described in the table below:

Table 2: Group Loading SQL Fields

Field	Description
SPW_ GROUPNAME	Required The unique identifier that is used by the source system to identify groups.
SPW_ GROUPNAME	Required The username for the group that is used by the source system (might be the same as the group ID.)
SWP_ADID	Optional The active directory ID of the group. If used, this will override the group creation settings.
SPW_ ACTIVE	Optional Identifies whether the group is active or not.

Group users load query:

This is an SQL statement that identifies the users that belong to a specific group.
A group ID is passed to this query and the query returns the user IDs for all of the users in this group.

For more information, see the table below:

Table 3: Group Users SQL Fields

Field	Description
SPW_USERID	Required This is the column that is returned by this query. The user IDs of the users that to this group are included in the column information.
[SPW_GROUPID]	Required This is a parameter that is used in the where clause. See the square brackets [ ] This parameter is replaced by a group ID that Connectivity Hub uses to retrieve users. Note: If the group ID is a string, encase this string with single quotation marks (' '). See example below: `select dbo.UserID as SPW_USERID from dbo.group_users` `where dbo.groupid = '[SPW_GROUPID]'`

Group Hierarchy load query:

This is an SQL statement that identifies the groups that are the children of a parent group.
A group ID is passed to this query and the query returns the group IDs for all of the children to the specified parent group.

For more information, see the table below:

Table 4: Group Hierarchy SQL Fields

Field	Description
SPW_GROUPID (required)	This is the column that is returned by this query. The group ID’s of the children belonging to the group are returned.
[SPW_GROUPID] (required)	This is a parameter that is used in the where clause -see the square brackets ( [ ] ). This parameter is replaced by a group ID that Connectivity Hub uses to retrieve users. Note: If the group ID is a string, encase this string with single quotation marks (' '). See example below: `select dbo.childgroupid as SPW_GROUPID from dbo.group_` `groups where dbo.parentgroupid = '[SPW_GROUPID]'`

Users load script:
- Enter VB.NET script to filter the users or custom map to AD Account.
- VB.Net script that is executed by the Security Sync Loads User and Group tables and executes the mapping based on the connector configuration. task, once per group.
- Script must return a user received from any of the TargetDirectory.GetUserBy... methods, which are documented for each supported target directory type
- Return boolean true=add, false=skip
- Example:
  Copy
```
'CN=Uma Thurman/O=jgdomino,Uma Thurman
dim sysn as string = HOST.GetSystemName()
HOST.SetADID(HOST.GetDefaultDomain() & "\" & sysn.split(",") (1))
return true
```
- For more information and samples, see VB.Net Scripting.
- You must run the Security Purge task after updating SQL script used to load users and groups.
- Changes to the users and group SQL scripts or running the purge task requires running a full crawl to ensure the index accurately reflects the change.
- See Run a Security Synch/Purge Operation.
Group load script
- VB.Net script that is executed by the Security Sync task, once per group.
- Script must return a group received from any of the TargetDirectory.GetUserBy... methods, which are documented for each supported target directory type
- Can be used to return the mapped group from target directory.
  Copy
```
dim sysn as string = HOST.GetSystemName()
return not sysn.startswith("SYS")
```
- This rejects any groups starting with SYS.
- Parameters needed for the mapping are coming from the connector and can be retrieved by Host.Get... methods.
- Groups found in target directory are static, while others will be native.
- Note: Use only if your system supports group hierarchies.
INFO: User load Script and Group load Script fields are the same for all connectors.
The fields are explained here: How to Override User and Group Loading
Click Save.