Download


Slack Integration with SQL Server

April 24 2020


In this blog I will show you how to integrate with Slack from SQL Server so you can read and write messages into a Channel (or as a direct message) using SQL commands with Enzo Server. Beyond reading and writing messages, the Slack adapter allows you to perform many other functions, such as listing users, creating new Channels and support for Slack Slash Commands. However to keep things simple, we will limit this blog to reading and writing messages.

To follow this blog you must first have downloaded and installed the latest version of Enzo Server, and ensure that you can connect to Enzo using SQL Server Management Studio (SSMS). For further instructions or to download Enzo Server, please visit the download page.

Download the Slack Adapter

To download the Slack adapter: .

  • Start Enzo Manager (with Administrative privileges)
  • If not already done, register your Enzo Instance (usually localhost,9550) and login using the Enzo 'sa' account
  • Select Marketplace -> Download/Update Adapters
  • Select Available on the left menu
  • Find the Slack adapter and click the Install button
  • A License screen will show up; select Generate free 30-day license
  • Select Close & Install/Update to begin installing the adapter


Configure the Slack Adapter

Next you will need to configure the Slack adapter. Slack requires that you register an application first at https://api.slack.com/apps. Let's call this app EnzoTest. This will allow you to obtain a ClientID, ClientSecret, and select the application permissions. The last step in the Slack application registration creates an OAuth Token that will be needed by Enzo. For a detailed set of instructions, please see the: Slack Configuration Documentation


Note: it is important to select the necessary permissions for Enzo Server to function with Slack, including Channels:History, Channels:Read, Channels:Write and Chat:Write at a minimum. Other permissions may be required.


Once you have registered EnzoTest as a Slack App, go back to Enzo Manager, select the Slack adapter, and create a new Configuration Setting; enter the ClientID, ClientSecret and the OAuth Token. You can leave the remaining settings empty for now. Mark this configuration as the default setting and click OK.


NOTE: Make sure to select the option so that this setting will be set as the default.


Connect to Enzo with SSMS

Now that your Slack adapter has been configured, start SQL Server Management Studio (SSMS).

When the login screen appears, enter the information to connect to Enzo directly. Because Enzo Server emulates the SQL Server protocol, you can connect to Enzo directly from SSMS. By default, Enzo Server listens on port 9550. If SSMS runs locally to Enzo, connect to localhost,9550.

See the Quick Start guide for instructions on how to connect to Enzo from SSMS
To verify you successfully connected to Enzo from SSMS, execute this command: 

EXEC Slack.Help

A list of available commands that can be executed on the Slack adapter will be returned.


Query Slack

Let’s first list all available channels in Slack by running this command (this command returns all public Channels, and the private Channels the EnzoTest App was invited to): 


SELECT * FROM Slack.Channels

We will need the id value of the Channel we want to interact with later, which is in the third column in this output. You can also see from this query which channels are private, and how many members each one has.

Invite EnzoTest to a Private Channel

At this time the EnzoTest App can read and write messages from public channels. You can skip this section if you just want to test access to public channels.

If you want Enzo to work with private channels, we need to first invite EnzoApp.

Let’s add the EnzoTest App to the private channel called testchannel (you can pick any channel in your Slack environment). Open the Slack application and select the Channel you would like EnzoTest to access, then click on the Details icon (top right) to access the Channel settings menu. Click on Add Apps and select the EnzoTest application you previously registered.

EnzoTest can now access this Channel.


Read/Write Messages

Let’s run this command in SSMS (the channel id was retrieved from the previous SQL command; so you will need to replace the channel Id with your value): 

SELECT * FROM Slack.Messages WHERE ChannelId = 'C011FGD81S4'

Since I have interacted with this channel previously there are a few entries already.

Last but not least, let’s post a message to this channel: 

INSERT INTO Slack.Messages (ChannelId, Message) VALUES ('C011FGD81S4', 'Test message')

This command instantly adds a message to the TestChannel Slack Channel.


Conclusion

This blog post shows you how to register and configure the Slack adapter to read and write messages to a Slack Channel using SQL commands from SQL Server Management Studio. This capability allows you easily integrate your applications using SQL commands, search for messages in Slack and more.