javascriptsql-serverazureazure-functionstedious

How to connect to Microsoft SQL Server from Azure Functions using JavaScript?


We´re developing a function app in JavaScript with the Serverless Framework and deploying it to Azure.

We´re pulling data from a Microsoft SQL Server 2014 database, everything goes well when testing the functions in a local environment with the Serverless Offline plugin and a VPN. This is the code to establish the connection:

const mssql = require("mssql");

const sqlConfig = {
  user: process.env["DB_USER"],
  password: process.env["DB_PWD"],
  database: process.env["DB_NAME"],
  server: process.env["DB_HOST"],
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
  options: {
    encrypt: true, // for azure
    trustServerCertificate: false, // change to true for local dev / self-signed certs
  },
};

// pool-manager.js
const pools = new Map();

module.exports = {
  get: (name) => {
    if (!pools.has(name)) {
      const pool = new mssql.ConnectionPool(sqlConfig);
      // automatically remove the pool from the cache if `pool.close()` is called
      const close = pool.close.bind(pool);
      pool.close = (...args) => {
        pools.delete(name);
        return close(...args);
      };
      pools.set(name, pool.connect());
    }
    return pools.get(name);
  },

  closeAll: () =>
    Promise.all(
      Array.from(pools.values()).map((connect) => {
        return connect.then((pool) => pool.close());
      })
    ),
};

We´re also using Key Vault's references, these are working normally.

The problem is when the functions are deployed, the connections to the database is never established. This is the error I get:

{
  "code": "ESOCKET",
  "originalError": {
    "code": "ESOCKET"
  },
  "name": "ConnectionError"
}

So, my questions is: What has to be done in Azure or what has to be changed in our code to allow this connection?

Thanks


Solution

  • Is your MS SQL 2014 server hosted on-prem/on a VM behind a VNET?

    If so, you will need to integrate your Function App with that virtual network. See Microsoft guide.