Home / , / Manual and User Guide – DataSync (Auditing for SharePoint)

Manual and User Guide – DataSync (Auditing for SharePoint)

5165 views 7 min , 44 sec read

Applies to: SharePoint Online and On-Premises

Description

Crow Canyon DataSync Tool for SharePoint is a server-side tool that synchronizes any SharePoint custom list to any ODBC-compliant database. Users can then use standard reporting tools of their choice (e.g. Crystal Reports, SQL Server Reporting Services, etc.) to run comprehensive reports on the exported SharePoint list data. Most importantly, the DataSync Tool provides access to the Version History of SharePoint list items for audit and historical reports. Audit information includes details such as what SharePoint items were changed, which fields were modified, what the old and new values are, who made the change, and when it was made.

For SharePoint on-premises environment: The tool is deployed to the SharePoint application server.

For SharePoint Online environment: Tool is deployed in one of the on-premises servers that has access to internet and can communicate with both SQL database and SharePoint online site.

Through a few simple steps, users can quickly configure data synchronization from a SharePoint list to an external database. The tool provides support to configure any number of SharePoint lists. Users can start and stop the sync at any time for one or more lists. Users can specify the length of time between data syncs as per their reporting requirements.

This article describes the installation and configuration of Crow Canyon Data Sync and Auditing Tool for SharePoint.

Pre-requisites

  • Local Administrator rights on the installation server.
    • User installing the tool must have local administrator access to that machine
    • In case of SharePoint on-premises, installation server should be one of the SharePoint servers in the farm
  • Microsoft .Net Framework 4.7.2 on the installation server
  • Access to SQL server instance where this data will be synchronized.
    • This account requires permissions to create required tables in the database
  • SQL authenticated user account details (user name and password)
    • This account needs “Write” permissions on the target database. The account details will be used in our tool configurations
  • User account details (user name and password) for service account
    • This account will be used to install the windows service.
    • for SharePoint on-premises environment
      • This account should have at least read permissions on the site from where we would like to take the lists data for the sync.
      • This account should also have “SPShellAdmin” access on the SharePoint configuration database and content database in which the site present, refer instructions from this Article
  • SharePoint Online only
    • User login and password that has at least read permissions on the site and list that we would like to consider for the sync
    • If this user account has multi-factor authentication enabled then we need to use app password
    • Legacy authentication should be enabled in SharePoint Online tenancy
  • (Optional) FILESTREAM should be enabled on the instance of the SQL server database engine if you want to copy attachments from items.
    • To enable and configure FILESTREAM on the instance of the SQL server database engine, refer instructions from this Article
    • After enabling the FILESTREAM, we need to use FILESTREAM enabled database in the sync configuration settings. Refer instructions to create FILESTREAM enabled database from this Article

Installation Package

  1. CrowCanyon.SharePoint.DataSyncSetup.zip

Installation Steps

1. Login with the local administrator account to the installation server.

2. Download the package “CrowCanyon.SharePoint.DataSyncSetup.zip”, right click, properties and unblock the file if it is blocked.

3. Open Command Prompt (Run as Administrator) –> navigate to the Package location –> enter the below command to verify MD5 signature for the package.

4. In this example, package download link is like “https://…/MD5_a74ffb7338e67fcf78cf83d1d540da4a/CrowCanyon.SharePoint.DataSyncSetup.zip” and the command output is matching with GUID in the download link as expected. Please contact sharepointsupport@crowcanyon.com if this GUID is not matching.

5. After MD5 signature is validated, right click on zip file and extract the required package folder and paste in one of the local drives in installation server.

6. Unblock the CrowCanyon.SharePoint.DataSyncSetup.exe and other files present in the package folder.

Right-click on the CrowCanyon.SharePoint.DataSyncSetup.exe file, click properties, if “Unblock” button is visible under “General” tab at the bottom in the security column, click Unblock. See screenshot below.

7. Right-click on the CrowCanyon.SharePoint.DataSyncSetup.exe file and choose “Run as Administrator”

  • Accept the License Agreement and Click Next

8. Enter the qualified Domain username and password as shown in below picture and click next.

For SharePoint on-premises environment, Data Sync service runs in the context of these credentials so it must have access to read data from SharePoint list (from which data is to be taken) and write access to SQL Server database (in which list data is to be stored)

At the end of the installer, click “Finish” button and that will launch the Crow Canyon Data Sync Configuration tool and also it will add shortcut on the desktop as shown below.

Configuration of Data Sync Tool

Add list Data Synchronization

Note: Data from Lookup columns can be stored either as text or as a reference to items in another table or both. We can configure Data Sync on the lookup list(s) first to sync all items in lookup list(s) to a separate table(s). The ID column value of the lookup list items will be one of the columns in this table. After this, for the main list having this lookup column, we can sync the lookup ID which is same as the ID in the lookup list table (similar to foreign key relations in Db). You can then use joins in Db based on this common ID value between tables.

Steps for syncing a list:

Add List Data Sync

Open the Data Sync configuration tool, Click on ‘Add List DataSync’ to open Data Sync List Configuration Form

SharePoint Type
  • Select On-Prem/SPOnline as per the source SharePoint environment.
    • On-prem: This does not required additional input as service account will be used to read SharePoint lists data
    • SP Online: Enter user name and password. We can also use App context and that will need additional steps (not covered in this article).
SharePoint List Settings
  • These settings is to configure required list data to synchronize into database table
    • Site URL: Enter the Site URL of your SharePoint lists and Click Populate List
    • List Name: All of the SharePoint lists from that site will be loaded into the ‘List Name’ dropdown. Select the list from the drop down, which you want to synchronize.
    • List Sync Interval: Enter the interval time in minutes in List Sync Interval for synchronizing data in the list.
      • For example: if you enter 30, the synchronization will occur every 30 minutes.
Database Settings

These settings are used to define the location in the SQL Db where the data will be stored:

Build Connection

When we click on Build connection, it will open the Database Connection Settings window.

  • Enter the Database server name having the database to save synchronized data
  • Select the database from the populated items in the dropdown list
    • Note: If you want create a new database, first open SQL Server Management Studio. Then in Object explorer right click on databases and click ‘New Database’ to create a database
  • Click On Test Connection. You’ll see a message that the connection was successful. If it was not, please check the information you entered.

After Build Connection is successful, it will auto fill the Connection String

Database Tables

List data table name, Audit data table name, Attachments data table and Version Data table names are auto filled by the Data Sync tool. You can change the names as required.

Audit data table name and Copy List Versions are enabled when versioning is enabled in list (Advanced Settings page in the SharePoint List Settings). The audit table has details of changes made to the list columns: Column Name, previous value, new value, who made the change and when.

Version Data table name is enabled when Copy List Versions is checked. This table will have one record for every version of the item. If versioning is enabled on the SharePoint list then a new version is generated every time item is saved.

Attachments data table is enabled when Copy Attachments is checked. This table will have one record for every attachment for the item.

Create Data Table Now or Generate script to create database tables

You have two options for creating tables:

  1. When you select ‘Create Data Table now’ option, it will create the tables when the settings are saved
  2. When you select ‘Generate script to create database tables’ option, it will generate the SQL script and saves it in “C:\programming Files\Crow Canyon\SharePoint\DataSync\”.
    • By executing this script in the SQL Server Management Studio, you can create the Tables manually. Please run the script for the correct database

SharePoint List Settings

These settings are used to select the SharePoint list columns whose data will be stored in the database. Also, data type has to be selected.

  • Here we can edit the data types, if needed. Normally, you can leave the default value which is selected based on the type of the column.

Exclude Hidden Columns

By selecting this, you can exclude columns which are hidden by default in SharePoint.

Populate mapping Grid

It reads the list settings and populates the grid with one row for each List Column. Details of various columns in grid:

  • SharePoint List Fields
    • Name of the list column
  • Field Type
    • Type of the column
  • Do Audit
    • If we select this checkbox it saves the version history of that particular column in the audit table
  • Database Column Name
    • Name of the database table column name in which data for this SharePoint list column will be saved
    • Note: You can rename the columns
  • Column Data Type
    • The data type of particular column name in you database
  • Transform
    • By default lookup fields and person or group field in SharePoint will have the values format like {id; value}. If you check this, it will trim the ID from the value.
    • Note: Some of the field like Modified, Modified By, lookup and user type fields have “_sp_Id” as suffix and these are auto selected. These fields are used to store the ID of the user or the lookup item and can be used to join with corresponding user/lookup list tables

You have to click On Save Settings to start the Data Synchronization.

Note: Once created, sync configuration cannot be edited. Please delete and recreate for any changes.

Start/Stop a Data Sync Service

There are two ways to start the Service

1. From Services in your server computer

Open services and Right Click on Crow Canyon SharePoint DataSync and you will now start or stop service from drop down menu

2. From application of Data Sync Configuration Tool

Go to General Settings -> Click on to Start if service is not started

Enable/Disable List Data Sync

This option is used to enable or disable the list data synchronization.

Delete List Data Sync

Select the list that you want to delete and click on Delete List DataSync. This will open up a dialog box.

If checkbox is checked: All the Data in database tables related to this sync configuration will be deleted permanently

If checkbox is unchecked:  It deletes only the sync configuration and not the data

Enable/Disable Logs

Go to ‘general settings’ -> click on checkbox to Enable or Disable log

We can change the location of log file. By default it is saved in “C:\CrowCanyon Logs” and log file name is CCSSPListDataSyncLog.

  1. Information (log file stores the information of about synchronization)
    • Connection string
    • Check Db string
    • Column Mapping
    • Change data count of list items in Database
  2. Error (log file stores errors which are generated when list synchronization runs)

Configure Logon Account to start Service

  • Click WIN+R and Type ‘services.msc’, and then press Ok.
  • In the details pane, right-click the Crow Canyon SharePoint DataSync and then click Properties.
  • On the General tab, in Startup type, click Automatic, Manual, Disabled, or Automatic (Delayed Start).
  • To specify the user account that the service can use to log on, click the Log On tab, and then do one of the following:
    • To specify that the service uses the Local System account, click Local System account.
    • To specify that the service uses the Local Service account, click ‘This account’, and then type NT AUTHORITY\LocalService.
    • To specify that the service uses the Network Service account, click ‘This account’, and then type NT AUTHORITY\NetworkService.
    • To specify another account, click ‘This account’, click Browse, and then specify a user account in the Select User dialog box. When you are finished, click OK.
  • Type the password for the user account in Password and in Confirm password, and then click OK.

Note: If you select the Local Service account or Network Service account, do not type a password.

Configure User Information list

User Information list is available in the site collection. Configure this to Sync all user and groups from SharePoint to Database.

Related List Data Sync Example

Suppose if we want to sync “Tickets” list data and it is having some related lists like “Category” and “Issue Type”. Configure List Data Sync on Category, Issue Type and Tickets as Standalone configurations (i.e. three separate Data Sync configurations in this example). From the database, we can join the Tickets table with Category and Issue Type Tables after data sync.

Uninstall Steps for Crow Canyon Data Sync Service

  1. Stop the Crow Canyon SharePoint DataSync service (services.msc)
  2. Uninstall the service using below command
    • Open Command Prompt (Run as administrator)
    • Run the below command
      • C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe /u “setup file path\CCSSPDataSyncService.exe”
    • After this steps you should not see the Crow Canyon SharePoint DataSync service in services.msc any longer
  3. Clean up the folder where Crow Canyon SharePoint Data Sync settings file is stored
  4. Optional: Delete Data Sync Db and revoke any special permissions to any user account for service or Database

About supportTeam

View all posts by supportTeam →

Leave a Reply