Setup chat bot (wrriten by MS Bot Builder NodeJS) with Nginx + HTTPS on Windows Server

Continuing with my series of articles on chat bot:

• Create a Luis Chat Bot on Azure Bot Service - Part 1
• Create a Luis Chat Bot on Azure Bot Service - Part 2

In this article, I will cover a solution to set up an production environment for your chat bot written by MS Bot Builder NodeJS with Nginx & HTTPS on Windows Server. In which, you can register your chat bot hosted on any server (not on Azure) with MS Bot Framework.

1. Install Nginx

Download the latest version for Windows from: http://nginx.org/en/download.html
Unpack it on a folder (e.g. C:\nginx ) and click nginx.exe to run nginx. Open your browser, run http://localhost/. If you see a screen like below, it means Nginx is running well.

2. Run your chat bot

Assuming that you've coded a wonderful chat bot, let run it.  For example, I have a super chat bot by echoing what user says, it is in app.js file as the following:

var restify = require('restify');
var builder = require('botbuilder');
// Create chat bot
var connector = new builder.ChatConnector({
    appId: 'app id of your bot on MS Bot Framework',
    appPassword: 'password of your bot on MS Bot Framework'
 });
// Receive messages from the user and respond
var bot = new builder.UniversalBot(connector, function(session) {
    session.send("You said: %s", session.message.text);
});
// Setup Restify Server
var server = restify.createServer();
server.listen(process.env.port || process.env.PORT || 5678, function() {
    console.log('%s listening to %s', server.name, server.url);
});
// Listen for messages from users
server.post('/api/messages', connector.listen());

Run app.js:

node app.js

Note that appId and appPassword will be replaced by your real values when you register your bot on MS Bot Framework.

3. Set up Nginx as proxy for your chat bot

Assuming that you want to run your chat bot on the link: yourdomain.com/bot. Open nginx.conf file (e.g. C:\nginx\conf\nginx.conf) and add a server configuration for your domain:

server {
        listen      *:80;
        server_name  yourdomain.com  www.yourdomain.com;

        location / {
            root your_web_site_root_folder;
            index  index.html index.htm;
        }

        location /bot {
            proxy_pass http://localhost:5678/api/messages;
            proxy_http_version 1.1;
            proxy_set_header Upgrade $http_upgrade;
            proxy_set_header Connection 'upgrade';
            proxy_set_header Host $host;
            proxy_cache_bypass $http_upgrade;
        }
}

Now you can open the chat emulator for testing with the link http://yourdomain.com/bot to see if it works (see section 3 of Create a Luis Chat Bot on Azure Bot Service - Part 1 for setting the emulator with ngrok):

4. Set up HTTPS

MS Bot Framework just allows to register a bot with HTTPS end point message. So that's why we need to set up HTTPS. Luckily, we can setup HTTPS with free SSL Certificate from Let's Encrypt. You can read my article WAMP 64 Bits + Free SSL (section 5) for how to create a free SSL certificate.

Below is the configuration on nginx for a server with SSL:

server {
        listen       443 ssl;
        server_name  yourdomain.com  www.yourdomain.com;

        ssl_certificate      C:/ProgramData/letsencrypt-win-simple/httpsacme-v01.api.letsencrypt.org/www.yourdomain.com-crt.pem;
        ssl_certificate_key  C:/ProgramData/letsencrypt-win-simple/httpsacme-v01.api.letsencrypt.org/www.yourdomain.com-key.pem;
        ssl_trusted_certificate C:/ProgramData/letsencrypt-win-simple/httpsacme-v01.api.letsencrypt.org/ca-<hex number>-crt.pem;

        ssl_session_cache    shared:SSL:50m;
        ssl_session_timeout  1d;
        ssl_ciphers EECDH+AESGCM:EECDH+AES;
        ssl_prefer_server_ciphers  on;

        location / {
            root your_web_site_root_folder;
            index  index.html index.htm;
        }

        location /bot {
            proxy_pass http://localhost:5678/api/messages;
            proxy_http_version 1.1;
            proxy_set_header Upgrade $http_upgrade;
            proxy_set_header Connection 'upgrade';
            proxy_set_header Host $host;
            proxy_cache_bypass $http_upgrade;
        }
}

Test again on the emulator with https://yourdomain.com/bot

5. Register your bot

Go to https://dev.botframework.com, sign in and click My bots menu for registering your bot. Remember to select Register an existing bot built using Bot Builder SDK when creating new bot.

It will open a page for you keying your bot info. Note that Messaging endpoint is https://yourdomain.com/bot.

Click Create Microsoft App ID and password to create app id & password for your bot. Paste your app ID to the required box then copy the app id & password into the source code of your bot (e.g. appId & appPassword in section 2). Save the setting.

Now you can click Test button on top right for testing your bot directly from the website https://dev.botframework.com. For example:

You can also connect your chat bot to other channel like Facebook (see Create a Luis Chat Bot on Azure Bot Service - Part 2 for how to connect with Facebook).

Alright, you have known how to set up a production environment for your chat bot which can be hosted any where.

Have fun! Any comment is welcome.

MS SQL SMS export large data to Excel without breaking format

If you often work on MS SQL, surely sometimes you want to export large data (result a query) to Excel file. After querying on MS SQL SMS (Microsoft SQL Server Management Studio), you can use right click on the cell top left then select "Copy with Headers" and copy to Excel file or "Save Result As" and save to a CSV file.

However 2 these functions have their problems. "Copy with Headers" cannot copy large data.  "Save Result As" often breaks CSV format if your data contains some special characters.

Fortunately, there is another function for exporting large data without breaking its format. Right click on your database and choose Tasks >> Export Data...

For Data Source, choose SQL Server Native >> select Server name >> select your Database, see the following for example:

Next, select Destination as Microsoft Excel & specify Excel file path.

Next, select Write a query to specify the data to transfer.

In next step, paste your query into or select a file containing your query. In the step Review Data Type Mapping, let review again columns have been converted data (Source Type vs. Destination Type). If you want to fix, click Back to select again Destination Type for converting.

If they are ok, click Next then Finish (don't worry for warning signs). Waiting a moment and you will have your Excel file with correct format you wanted.

Yeah! This is a small tip for you, hope it is useful. Share it to your friend for helping him or her out 😍. Any comment is welcome!

Happy Halloween!