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.
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.