Build powerful process automated systems using the power of the SQL language, and leverage Enzo to eliminate the complex API layers that usually stand in the way without using ODBC drivers.

Let’s build a new Employee Onboarding process using SQL Server that will detect a new employee provisioning request in a SharePoint List, send a SMS message to your phone when a new employee has been added, and create a SQL Server login account for the user automatically. In order to follow along, you will need to download and install Enzo Server on a virtual machine in your environment (or hosted by a cloud provider such as an AWS EC2 or an Azure VM instance).

Conceptually the onboarding process works like this: a new employee added to a SharePoint list triggers a process that sends an SMS message, creates a new SQL Server login, and updates the record status in SharePoint. The process is essentially event-driven, using simple SQL coding logic that listens for changes in SharePoint, which can be easily modified.

This blog uses the following Enzo technologies:

• SharePoint adapter
• Message adapter
• SQLServer adapter
• Central Connection String

Implementing this process requires three things:

• Configuration settings for: SharePoint access, sending messages and accessing a SQL Server database
• A SharePoint List created with specific fields
• Write the process automation using T-SQL

Create Configuration Settings

Let’s first create the configuration settings for the SharePoint environment we want Enzo to point to. This can be an on-prem SharePoint server, or a SharePoint Online environment. In addition you will need to create a configuration setting for the Messaging adapter, a Central Connection String, and a SQL Server adapter configuration setting.

1. Open SQL Server Management Studio and connect to Enzo Server
   By default Enzo Server listens on port 9550; if you run SSMS on the same server where Enzo was installed, the server name for Enzo is localhost,9550.
   
   
   
2. Execute the SharePoint._createConfig command; it takes 6 parameters (replace YOURSITE, USERNAME and PASSWORD with your own values):
   
   

   EXEC SharePoint._configCreate 
   	'spconfig',   					-- config name
   	1,                  				-- set as default
   	'USERNAME@YOURSITE.onmicrosoft.com',  	-- username
   	'YOURPWD',   					-- password
   	'https://YOURSITE.sharepoint.com/teamsite/',	-- sharepoint site url
   	1     						-- user lookup IDs 
   

   
   
3. Create the Messaging configuration setting (which will be used to send a text message); the following example shows valid settings for Office365.
   
   

   EXEC Messaging._configCreate
   	'msgconfig',		-- config name
   	1,			-- set as default
   	'smtp',
   	'YOUR_NAME',		-- Your name
   	'FROM_EMAIL',		-- FROM email
   	'smtp.office365.com',	-- Email server 
   	587,			-- Server port
   	'USERID',		-- Email address used to authenticate
   	'PASSWORD',		-- Email server password
   	1			-- Specify 1 for SSL
   

   
   
4. Create a Central Connection String, which securely stores a database connection string in Enzo (replace the connection string with your own, so that Enzo can connect to a database server).
   
   

   EXEC sys.vault.ConnectionStringCreate 
   	'sqlconnection',
   	'Data Source=localhost;User ID=sa;Password=PASSWORD',
   	0,
   	''
   

   
   
5. Finally let’s create a SQLServer configuration setting; this setting uses the previously created connection string. This is the adapter that is used to create a login account in the destination database.
   
   

   EXEC SQLServer._configCreate
   	'sqlconfig',
   	1,
   	'sqlconnection',	-- Central Connection String to use
   	'master',		-- Database to connect to
   	'Enzo Server',
   	0,			-- SSL
   	1,			-- Retries
   	15			-- Connection timeout
   

   
   

At this point you have completed the configuration of Enzo Server; the connection settings are stored in Enzo, allowing you to program against a SharePoint site, a Messaging system and a SQL Server database.

Create SharePoint List

The first thing we need to do is to create a new SharePoint list that will host the new employee records. Three fields are needed in this example: Title, Status, UserId. The Title field will host the employee name; the Status field will indicate the status of the process, and the UserId will hold the user name of the employee that will be created in SQL Server.

The easy way to create the SharePoint list and the necessary fields is to use Enzo Server directly. Assuming you have provided credentials with enough permissions for SharePoint, you can run the following T-SQL command against Enzo Server to configure the list:

1. Open SQL Server Management Studio and connect to Enzo Server.
   By default Enzo Server listens on port 9550; if you run SSMS on the same server where Enzo was installed, the server name for Enzo is localhost,9550
2. Execute the following commands to create the SharePoint list and add the necessary fields.
   
   

   -- Create the list
   EXEC SharePoint.CreateList 'newemployees', 'GenericList', 'New employee automation'
   -- Add the Status field of type Text, and set a default value of NEW
   EXEC sharepoint.addfield 'newemployees', 'Status', 'Text', 'NEW'
   -- Add a UserId field of type Text without a default value
   EXEC sharepoint.addfield 'newemployees', 'UserId', 'Text'
   

   
   
3. Let’s make sure the following command works; no records should be returned at this point:
   
   

   SELECT ID, Title, Status, UserId, Created, Modified FROM SharePoint.[list@newemployees]
   

   
   
   

SQL Automation Process

At this point we are ready to write your automated process using pure T-SQL commands, without the need for any ODBC drivers, executing commands through a Linked Server connection to Enzo.

DECLARE @id int
DECLARE @userId nvarchar(255)

--
-- GET FIRST RECORD TO PROCESS
--
SELECT TOP 1 @id=ID, @userId=UserId FROM [localhost,9550].bsc.SharePoint.[List@newemployees] WHERE Status='new'

-- ANYTHING TO DO?
WHILE (ISNULL(@id, 0) > 0)
BEGIN

	SELECT @id, @userId   -- output the current record being worked on
	
	--
	-- CREATE SQL SERVER LOGIN
	--
	-- TODO: Add logic to create a strong password
	IF (@userId IS NOT NULL)
	BEGIN
		DECLARE @pwd nvarchar(50) = '123456'
		DECLARE @sql nvarchar(255) = 'IF (NOT EXISTS(SELECT * from master..syslogins WHERE name = ''' + @userId + ''')) CREATE LOGIN ' + @userId + ' WITH PASSWORD = ''' + @pwd + ''' '
		EXEC [localhost,9550].bsc.SQLServer.Run @sql
	END

	--
	-- UPDATE SHAREPOINT
	--
	-- Update the SharePoint list item's status using the UpdateListItemRaw method
	DECLARE @where nvarchar(50)
	SET @where = 'ID=' + CAST(@id as nvarchar(40))
	EXEC [localhost,9550].bsc.sharepoint.updatelistitemraw 'newemployees', '{ ''Status'': ''ready'' }', @where

	--
	-- SEND COMPLETION TEXT MESSAGE
	--
	-- send message to phone using Messaging adapter
	EXEC [localhost,9550].bsc.messaging.sendText 'tmobile', '5612362025', 'New user created in SQL Server!', @userId
   
	--
	-- MORE RECORDS?
	--
	-- Check for next item to process in sharepoint
	SET @id = NULL
	SET @userId = NULL
	SELECT TOP 1 @id=ID, @userId=UserId FROM [localhost,9550].bsc.SharePoint.[List@newemployees] WHERE Status='new'

END

This blog shows you how you can easily program against the Enzo platform, by leveraging the power of the T-SQL language, and implement business and IT process automation. The ability to encapsulate business processes as simple SQL commands makes the logic easy to review, test, and check-in as regular code.