Azure-functions-host: Host crashes when using tedious to connect to a database.

[ahmelsayed]

Please provide a succinct description of the issue.

Repro steps

Provide the steps required to reproduce the problem

• Create a node http function
• index.js

var Connection = require('tedious').Connection;  
var config = {  
    userName: 'username',  
    password: 'PASSWORD',  
    server: 'server.database.windows.net',  
    options: {encrypt: true, database: 'AdventureWorks'}  
};  

module.exports = function(context, req, saasSql) {

    var connection = new Connection(config);  
    connection.on('connect', function(err) {  
        if(err) {
            context.log(err);
        } else {
            context.log("Connected");  

            context.res = {
                body: 'Connected'
            };
        }
     context.done();
    });  
};

• package.json

{
    "dependencies": {
        "tedious": "*"
    }
}

• function.json

{
  "bindings": [
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req"
    },
    {
      "type": "http",
      "direction": "out",
      "name": "res"
    }
  ],
  "disabled": false
}

• Hit run
  

  Expected behavior

Error maybe, I don't know

Actual behavior

w3wp.exe crashes

Known workarounds

none

Related information

• original issue https://github.com/projectkudu/AzureFunctionsPortal/issues/548

[mamaso]

Interesting, can't repro this locally but can repro on a running app. I'll try a site extension with the newest bits.

[mamaso]

Private site extension with our newest bits solves the crashing issue, resolved in 0.6.

[fabiocav]

@finvamp1 is still running into this issue. He'll make the code available in a repo so we can repro.

[FinVamp1]

Sample Code has my code and the Function was upgraded to 0.6. If I enable Packet Tracing and data tracing on the Tedious library I can see us connect and fail after the re-routing on
2016-09-27T19:26:41.143 DO State change: SentTLSSSLNegotiation -> SentLogin7WithStandardLogin.
We get the list of CA's back from SQL but then don't continue.

[mamaso]

@FinVamp1 that gist is not displaying properly, can you try another?

Tested the scenario in the original issue and connected without a problem in the released 0.6 bits.

[fabiocav]

@mamaso just remove the .js from the link

[fabiocav]

https://gist.github.com/FinVamp1/4b5f01ae4a3b5f70c0c92a4c4ac0a14f

[mamaso]

Ok, I was able to login successfully to my own sql server using that code, which leads me to suspect there's some kind of configuration issue. Do you have any firewall rules on the server or sql auditing enabled?

In addition, the code as you sent won't run successfully as dbConnection is undefined within executeStatement

[ghost]

@mamaso Did you try that with SQL Database v12 instance?
This issue is only reproduced when using v12.

[mamaso]

@mozlight Just checked, I am using a v12 instance. Are you using transparent data encryption?

It seems like I can repro with that setting enabled.

[FinVamp1]

I've looked at my repro again with SQL Auditing turned on. The Tedious debug tracing shows that we stop at the Login but the Auditing logs show we do login and we execute the SQL. (I updated my code to define dbConnection outside of the function loop)

Here's the order I see in the Audit Logs.

Login
PlainSQL
ParameterizedSQL

This is with Transparent Encryption enabled.

[FinVamp1]

1)      The Tedious library caused the Function Host to exit and this is resolved in 0.6.
2)      The second issue is that the Tedious Debug logging isn’t always complete in the Log Streaming output and it stops at the
State change: SentTLSSSLNegotiation -> SentLogin7WithStandardLogin state which could lead you to assume the Login isn’t complete. I had to check the Audit logs to see that SQL had logged the user on and that the SQL was executed.
Here’s the code that worked for me. (I NPM installed Tedious but you can reference it from the package.json.

// Please visit http://go.microsoft.com/fwlink/?LinkID=761099&clcid=0x409 for more information on settting up Github Webhooks
 
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
 
var option = {
userName : 'user@sqlservername',
password : 'password',
server : 'sqlservername.database.windows.net',
options : {
encrypt : true,
database : 'TestDB'
}
};
 
 
var dbConnection = new Connection(option);

module.exports = function (context, req) {
    context.log('Node.js HTTP trigger function processed a request. RequestUri=%s', req.originalUrl);
    dbConnection.on('connect', function(err) {
          if(err)
          {
            context.log(err);
          }
          else {
            request = new Request("select * from dbo.TheBand", function (err, rowCount) {
              if (err) {
                context.log(err);
              } else {
                context.log(rowCount + ' rows');
              }
            });
            request.on('row', function (columns) {
              columns.forEach(function (column) {
                if (column.value === null) {
                  context.log('NULL');
                } else {
                  context.log(column.value);
                }
              });
            });
            dbConnection.execSql(request);
          }
 
        });
 
    context.done();
};

[fabiocav]

This is confirmed resolved in 0.6

[SameerDoshi]

The syntax has changed for config section.
See [Query Azure SQL from Node](https://stackoverflow.com_
Config now looks like:

var config =
{
    authentication: {
        options: {
            userName: 'userName', // update me
            password: 'password' // update me
        },
        type: 'default'
    },
    server: 'your_server.database.windows.net', // update me
    options:
    {
        database: 'your_database', //update me
        encrypt: true
    }
}