5 Steps to Secure Passwordless Azure SQL Connections Using Node.js

Passwordless Authentication for Azure SQL Using Microsoft Entra ID

Introduction:
In today’s digital landscape, traditional password-based security systems are increasingly vulnerable to cyberattacks and data breaches. Passwords often serve as the weakest link in cybersecurity, exposing users to risks such as phishing, credential stuffing, and brute force attacks. To address these vulnerabilities, organizations are adopting passwordless authentication. This modern approach eliminates the need for passwords, significantly enhancing security by removing common password-related threats. Additionally, it simplifies the user experience by removing the need to remember complex passwords, thereby reducing the administrative burden of password management and improving overall system efficiency.

Azure SQL Authentication Options:

Traditional Methods:
Typically, we connect to Azure SQL using usernames and passwords. While these can be stored securely, they are still vulnerable to phishing, hacking, and other cyberattacks.
Passwordless Authentication:
Azure SQL allows passwordless connections through Microsoft Entra ID (formerly Azure Active Directory), reducing the reliance on passwords and leveraging secure tokens and identities instead.

Benefits of Passwordless Authentication:

1. Enhanced Security: Reduces the risk of phishing, brute force attacks, and credential stuffing by eliminating passwords.
2. Improved Compliance: Meets the requirements of various security regulations, such as GDPR and CCPA, which emphasize reducing password-related vulnerabilities.
3. Streamlined User Experience: Simplifies the login process, making it easier for users to access resources without managing complex passwords.

Setting Up Microsoft Entra ID for Passwordless Access:

Why Microsoft Entra ID?: It is Microsoft's cloud-based identity and access management service. It controls access to resources like Azure SQL, ensuring that only authorized users can connect.
How to Set It Up:
1. Ensure your Azure account is linked with Microsoft Entra ID.
2. Set up the necessary roles and permissions in Microsoft Entra ID to allow access to your Azure SQL databases.
3. Enable passwordless authentication methods in Microsoft Entra ID, such as multi-factor authentication (MFA).

Prerequisites:

1. Before setting up passwordless authentication, ensure you have:
2. An Azure subscription with the appropriate permissions.
3. A linked Microsoft Entra ID account.
4. Managed identities enabled for your Azure services (if required).
5. Visual Studio Code and the necessary Azure extensions installed.

High-level steps are used to connect to Azure SQL Database using passwordless connections

Step 1: Create an Azure Group

1. Add Members
   

2. Add Owners
   

Step 2:Configured SQL Server for authentication with Microsoft Entra ID

Enable the option: Allow Azure services and resources to access this selected server.

Step 3: Configure System Identity for the Backend App Service

Step 4: Create a SQL database User for the Identity and Assign Roles

CREATE USER "system-assigned-identity-name" FROM EXTERNAL PROVIDER; 
 
ALTER ROLE db_owner ADD MEMBER "system-assigned-identity-name"; 
---OR
ALTER ROLE db_datareader ADD MEMBER [system-assigned-identity-name];
ALTER ROLE db_datawriter ADD MEMBER [system-assigned-identity-name];
ALTER ROLE db_ddladmin ADD MEMBER [system-assigned-identity-name];

Step 5: Configure Your Database Connection for Passwordless Authentication
In Visual Studio Code, create a config.js file. Add the following mssql configuration code to enable passwordless authentication for your connection to Azure SQL Database.

Note: Set DB_AUTH_TYPE to 'azure-active-directory-default' for passwordless authentication.

require("dotenv").config();// Load environment variables from .env file
module.exports = {
    development: {
        dbConnectionString: {
            dialect: 'mssql',
            host: process.env.DB_HOST, //Update   
            database: process.env.DB_NAME, //Update
            dialectOptions: {
                authentication: {
                    type: process.env.DB_AUTH_TYPE, //Update
                },
                options: {
                    encrypt: true,
                    trustServerCertificate: true,
                    //Update 
                    "requestTimeout": Number(process.env.SEQUELIZE_TIMEOUT)
                },
            },
            pool: {
                max: 5,
                min: 0,
                acquire: Number(process.env.SEQUELIZE_TIMEOUT),
                idle: 10000
            },
        }
    }
};

Add the code to connect to Azure SQL Database using sequelize in Node.js

'use strict';
const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config')[env];
const db: {
    [key: string]: any
} = {};

module.exports = (async function sequelizeConnect() {

                const dbConnectionString = config.dbConnectionString;

                let sequelize: typeof Sequelize;
                //passing the dbConnectionConfig to the sequelize instance
                sequelize = new Sequelize(dbConnectionString);
                const files = fs
                    .readdirSync(__dirname)
                    .filter((file: string) => {
                        return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.ts' || file.slice(-3) === '.js');
                    });

                for (const file of files) {
                    const model = require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);
                    db[model.name] = model;
                }

                Object.keys(db).forEach(modelName => {
                    if (db[modelName].associate) {
                        db[modelName].associate(db);
                    }
                });

                db.sequelize = sequelize;
                db.Sequelize = Sequelize;

                return db;
})

The tedious package, which is used for connecting to SQL Server from Node.js, supports passwordless authentication starting from version 13.0.0. This version introduced support for Azure Active Directory (AAD) authentication, which includes passwordless authentication methods.

To use passwordless authentication with tedious, make sure you have at least version 13.0.0 or later installed. You can update your tedious package by running:

npm install tedious@latest

Running Locally: Setup with Visual Studio Code
Install Azure CLI on Windows

Install Azure Account Extension

Run the Command to log In

az login

Security Best Practices:

Conditional Access Policies: Implement conditional access policies in Microsoft Entra ID to add another layer of security, ensuring that only trusted devices and users can access your Azure SQL databases.
Enable Logging and Monitoring: Regularly monitor authentication events in Microsoft Entra ID to detect unusual activity and respond promptly to potential security threats. Additionally, enable auditing in Azure SQL Database to track access and changes effectively.

Use Cases:

High Compliance Industries:
Passwordless authentication is particularly beneficial in industries with strict compliance requirements, such as finance, healthcare, and government, where data protection is paramount. In these sectors, reducing the reliance on passwords helps minimize risks associated with data breaches and ensures compliance with regulatory standards.
Large User Bases:
Applications with large user bases can benefit from the streamlined login process. For instance, if an employee leaves the organization, the need to change and update passwords across multiple systems is eliminated, reducing administrative overhead and minimizing disruptions. This reduces support requests related to password resetting and enhances overall user satisfaction and security.

Troubleshooting Tips:

Common Issues: If you encounter connection failures, ensure that the correct roles and permissions are set in Microsoft Entra ID. Also, verify that the managed identity is properly configured and that the SQL Server settings allow Azure services to connect.
Diagnostic Tools: Utilize Azure's diagnostic tools, such as SQL Server logs and Azure Monitor, to troubleshoot and resolve issues quickly.

Further Reading and Resources:

Microsoft Entra ID Documentation
Azure SQL Database Security Best Practices
Using Managed Identities with Azure SQL
Passwordless connections for Azure services

Conclusion:

Passwordless authentication with Microsoft Entra ID offers a robust, secure, and user-friendly way to connect to Azure SQL databases. By following the steps outlined above and adhering to best practices, you can significantly reduce security risks and improve the efficiency of your system.

Start implementing passwordless authentication today to enhance your system’s security and streamline user access.

Comments

[Rutika Khaire]

Very informative and helpful. Thanks for sharing!