Add support for Access Token Authentication for SQL Server Driver (mssql)

[funkydev]

Overview

When I was modifying mmsql package, I noticed that it accepts authentication parameters in the config object. See discussion under PR tediousjs/node-mssql#1208 and documentation I've updated https://github.com/tediousjs/node-mssql#tedious. It passes authentication configuration directly to the tedious library, which supports the following authentication types.

• DefaultAuthentication
• NtlmAuthentication
• AzureActiveDirectoryAccessTokenAuthentication
• AzureActiveDirectoryMsiAppServiceAuthentication
• AzureActiveDirectoryMsiVmAuthentication
• AzureActiveDirectoryPasswordAuthentication
• AzureActiveDirectoryServicePrincipalSecret

I copied authentication options interfaces directly from the tedious repository and modified TypeORM code to support passing authentication options for the sqlserver driver.

What does it change?

It changes a way of passing credentials to the SQLServer database. I modified current interfaces to deprecate domain property directly in the configuration model. Instead of this I proposed to pass authentication property which is compatible with the mssql and tedious one.

I'd recommend to remove options.domain in future, to give developers time for any adjustments. Then instead of passing:

createConnection({
  type: "mssql",
  database: "db-name",
  host: "db-server",
  username: "username",
  password: "password",
  domain: "domain"
}).then(connection => {
  // here you can start to work with your entities
}).catch(error => console.log(error));

developers should pass:

createConnection({
  type: "mssql",
  database: "db-name",
  host: "db-server",
  authentication: {
    type: "ntlm",
    options: {
        userName: "username",
        password: "password",
        domain: "domain"
    }
  }
}).then(connection => {
  // here you can start to work with your entities
}).catch(error => console.log(error));

Solves

• Access Azure MSSQL with AccessToken from Azure Managed Service Identity (MSI) #4510
• Establish a connection to MYSQL DB using Microsoft azure access token.  #7340

Usage

Default authentication type

createConnection({
  type: "mssql",
  database: "db-name",
  host: "db-server",
  authentication: {
    type: "default",
    options: {
      userName: "username",
      password: "password"
    }
  }
}).then(connection => {
  // here you can start to work with your entities
}).catch(error => console.log(error));

AAD Access Token

createConnection({
  type: "mssql",
  database: "db-name",
  host: "db-server",
  authentication: {
    type: "azure-active-directory-access-token",
    options: {
      token: "token"
    }
  }
}).then(connection => {
  // here you can start to work with your entities
}).catch(error => console.log(error));

AAD Service Principal (secret)

createConnection({
  type: "mssql",
  database: "db-name",
  host: "db-server",
  authentication: {
    type: "azure-active-directory-service-principal-secret",
    options: {
      tenantId: 'tenant-id',
      clientId: 'client-id',
      clientSecret: 'client-secret'
    }
  }
}).then(connection => {
  // here you can start to work with your entities
}).catch(error => console.log(error));

AAD MSI App Service Authentication

createConnection({
  type: "mssql",
  database: "db-name",
  host: "db-server",
  authentication: {
    type: "azure-active-directory-msi-app-service",
    options: {
      clientId: "optional clientId (it should be taken from app service environment)",
      msiEndpoint: "optional msiEndpoint (it should be taken from app service environment)",
      msiSecret: "optional msiSecret (it should be taken from app service environment)"
    }
  }
}).then(connection => {
  // here you can start to work with your entities
}).catch(error => console.log(error));

[pleerock]

we have username everywhere written in lowercase. Can we do same here?

[AlexMesser]

thank you for contribution!