Slack and SQL Server Integration

We have recently embraced Slack for our internal communications here at Powerupcloud. Most of our customers use slack, invite us to their internal channels and we internally found it very useful especially with the range of integrations it allows.

All the production SQL server database servers that we manage (which are many!) have a standard set of alerts configured. The regular SQL Agent alerts through 16 to 25 severity, blocking alerts with query text, deadlock alerts with deadlock graph, long running query notifications, job failures etc. Now that we spend a lot of time on Slack, we thought it would be better to teach SQL Server how to speak Slack :)

SQLServerSlackAPI is a SQLCLR assembly for direct communication with Slack using good old TSQL. The github page does a pretty good job of explaining how to use it but we did run into a problem or two trying to get it working on our systems. Here is a step by step on how to configure it. Hope this helps someone.

What do you need?

Build the Assembly

For SQL Server 2012 or later -
Open SqlServerSlackAPI-master\src\SqlServerSlackAPI.sln

Signing is by default disabled in the project but enabled in the build script with the signing key as SqlServerSlackAPI.pfx and it's password SqlServerSlackAPI. So we need to change it.
Right click the project and click Properties.

Go to CLR option and click Signing

Check Sign the Assembly

In the key name file dropdown, choose new

Type your key file name and enter a strong password.

Now build the project

The assembly should be created now. Make a note of the path

Lets Integrate with Slack

Go to slack's api webpage. https://api.slack.com/tokens

In the Authentication option click tokens for testing and create a new token.

Maybe you can now create a new database to hold this CLR function or use an existing one. In my case, I created new for this demo purpose

Open Deploy.sql in Microsoft SQL Management Studio and replace
SlackTestDb with the database you want to install the function in. Also replace C:\temp\dev\github\SqlServerSlackAPI\src\SqlServerSlackAPI\bin\debug\SqlServerSlackAPI.dll with the path on server where you copied the dll. Execute the script and you’ll get the below result.

Testing Time

Open New Query Window.
Type the following query on the SlackTestDb, and execute

SELECT Ok,  
        Channel,
        TimeStamp,
        Error
    FROM dbo.SlackChatPostMessage(
        '<your slack token>',
        '#yourslackchannel',
        'Hello from SQL Server',
        null,
        null
    )

And bingo - you should see SQL Server and slack talking:

OK, SQL Server 2012 works fine. How about SQL Server 2008 which has CLR version 3.5?We ran into some issues and finally solved it. Here is how you set it up.

While building the assembly, make a small change. Choose relevant target platform

Change the target framework to .NET 3.5

It'll ask to close and re-open the project. Click OK for that
Open SlackChatAPI.cs and remove the following lines.

messageParams = null;  
            if (string.IsNullOrWhiteSpace(token))
            {
                error = string.Format("Invalid Message Token specified ({0})", (token ?? "NULL"));
                return false;
            }

            if (string.IsNullOrWhiteSpace(channel))
            {
                error = string.Format("Invalid Message Channel specified ({0})", (channel ?? "NULL"));
                return false;
            }

            if (string.IsNullOrWhiteSpace(text))
            {
                error = string.Format("Invalid Message Text specified ({0})", (text ?? "NULL"));
                return false;
            }

Now build the project and follow the rest of steps.

In the next couple of posts, we will show how to deliver all critical SQL server notifications to slack directly, including blocking and deadlock notifications. Stay tuned.

comments powered by Disqus