Movatterモバイル変換

Applies to:Azure SQL Database

This quickstart describes how to connect an application to a database in Azure SQL Database and perform queries using Node.js and mssql. This quickstart follows the recommended passwordless approach to connect to the database.

Passwordless connections for developers

Passwordless connections offer a more secure mechanism for accessing Azure resources. The following high-level steps are used to connect to Azure SQL Database using passwordless connections in this article:

Prepare your environment for password-free authentication.
- For a local environment: Your personal identity is used. This identity can be pulled from an IDE, CLI, or other local development tools.
- For a cloud environment: Amanaged identity is used.
Authenticate in the environment using theDefaultAzureCredential from the Azure Identity library to obtain a verified credential.
Use the verified credential to create Azure SDK client objects for resource access.

You can learn more about passwordless connections on thepasswordless hub.

Prerequisites

AnAzure subscription
A database in Azure SQL Database configured for authentication with Microsoft Entra ID (formerly Azure Active Directory). You can create one using theCreate database quickstart.
Bash-enabled shell
Node.js LTS
Visual Studio Code
Visual Studio Code App Service extension
The latest version of theAzure CLI

Configure the database server

Secure, passwordless connections to Azure SQL Database require certain database configurations. Verify the following settings on yourlogical server in Azure to properly connect to Azure SQL Database in both local and hosted environments:

For local development connections, make sure your logical server is configured to allow your local machine IP address and other Azure services to connect:
- Navigate to theNetworking page of your server.
- Toggle theSelected networks radio button to show additional configuration options.
- SelectAdd your client IPv4 address(xx.xx.xx.xx) to add a firewall rule that will enable connections from your local machine IPv4 address. Alternatively, you can also select+ Add a firewall rule to enter a specific IP address of your choice.
- Make sure theAllow Azure services and resources to access this server checkbox is selected.
  Warning
  Enabling theAllow Azure services and resources to access this server setting is not a recommended security practice for production scenarios. Real applications should implement more secure approaches, such as stronger firewall restrictions or virtual network configurations.
  You can read more about database security configurations on the following resources:
  - Configure Azure SQL Database firewall rules.
  - Configure a virtual network with private endpoints.
The server must also have Microsoft Entra authentication enabled and have a Microsoft Entra admin account assigned. For local development connections, the Microsoft Entra admin account should be an account you can also log into Visual Studio or the Azure CLI with locally. You can verify whether your server has Microsoft Entra authentication enabled on theMicrosoft Entra ID page of your logical server.
If you're using a personal Azure account, make sure you haveMicrosoft Entra setup and configured for Azure SQL Database in order to assign your account as a server admin. If you're using a corporate account, Microsoft Entra ID will most likely already be configured for you.

Create the project

The steps in this section create a Node.js REST API.

Create a new directory for the project and navigate into it.
Initialize the project by running the following command in the terminal:
```
npm init -y
```
Install the required packages used in the sample code in this article:
```
npm install mssql express swagger-ui-express yamljs dotenv
```
Open the project in Visual Studio Code.
```
code .
```
Open thepackage.json file and add the following property and value after thename property to configure the project for ESM modules.
```
"type": "module",
```

Create Express.js application code

To create the Express.js OpenAPI application, you'll create several files:

File	Description
`.env.development`	Local development-only environment file.
`index.js`	Main application file, which starts the Express.js app on port 3000.
`person.js`	Express.js/person route API file to handle CRUD operations.
`openapi.js`	Express.js/api-docs route for OpenAPI explorer UI. Root redirects to this route.
`openApiSchema.yml`	OpenAPI 3.0 schema file defining Person API.
`config.js`	Configuration file to read environment variables and construct appropriate mssql connection object.
`database.js`	Database class to handle Azure SQL CRUD operations using themssql npm package.
`./vscode/settings.json`	Ignore files by glob pattern during deployment.

Create anindex.js file and add the following code:

import express from 'express';// Import App routesimport person from './person.js';import openapi from './openapi.js';const port = process.env.PORT || 3000;const app = express();// Connect App routesapp.use('/api-docs', openapi);app.use('/persons', person);app.use('*', (_, res) => {  res.redirect('/api-docs');});// Start the serverapp.listen(port, () => {  console.log(`Server started on port ${port}`);});

Create aperson.js route file and add the following code:

import express from 'express';import {   passwordConfig as SQLAuthentication,   noPasswordConfig as PasswordlessConfig } from './config.js';import { createDatabaseConnection } from './database.js';const router = express.Router();router.use(express.json());const database = await createDatabaseConnection(SQLAuthentication);router.get('/', async (req, res) => {  try {    // Return a list of persons    const persons = await database.readAll();    console.log(`persons: ${JSON.stringify(persons)}`);    res.status(200).json(persons);  } catch (err) {    res.status(500).json({ error: err?.message });  }});router.post('/', async (req, res) => {  try {    // add a person    const person = req.body;    console.log(`person: ${JSON.stringify(person)}`);    const rowsAffected = await database.create(person);    res.status(201).json({ rowsAffected });  } catch (err) {    res.status(500).json({ error: err?.message });  }});router.get('/:id', async (req, res) => {  try {    // Get the person with the specified ID    const personId = req.params.id;    console.log(`personId: ${personId}`);    if (personId) {      const result = await database.read(personId);      console.log(`persons: ${JSON.stringify(result)}`);      res.status(200).json(result);    } else {      res.status(404);    }  } catch (err) {    res.status(500).json({ error: err?.message });  }});router.put('/:id', async (req, res) => {  try {    // Update the person with the specified ID    const personId = req.params.id;    console.log(`personId: ${personId}`);    const person = req.body;    if (personId && person) {      delete person.id;      console.log(`person: ${JSON.stringify(person)}`);      const rowsAffected = await database.update(personId, person);      res.status(200).json({ rowsAffected });    } else {      res.status(404);    }  } catch (err) {    res.status(500).json({ error: err?.message });  }});router.delete('/:id', async (req, res) => {  try {    // Delete the person with the specified ID    const personId = req.params.id;    console.log(`personId: ${personId}`);    if (!personId) {      res.status(404);    } else {      const rowsAffected = await database.delete(personId);      res.status(204).json({ rowsAffected });    }  } catch (err) {    res.status(500).json({ error: err?.message });  }});export default router;

For passwordless authentication, change the param passed intocreateDatabaseConnection fromSQLAuthentication toPasswordlessConfig.

const database = await createDatabaseConnection(PasswordlessConfig);

Create anopenapi.js route file and add the following code for the OpenAPI UI explorer:

import express from 'express';import { join, dirname } from 'path';import swaggerUi from 'swagger-ui-express';import yaml from 'yamljs';import { fileURLToPath } from 'url';const __dirname = dirname(fileURLToPath(import.meta.url));const router = express.Router();router.use(express.json());const pathToSpec = join(__dirname, './openApiSchema.yml');const openApiSpec = yaml.load(pathToSpec);router.use('/', swaggerUi.serve, swaggerUi.setup(openApiSpec));export default router;

Create anopenApiSchema.yml file and add the following code so the OpenAPI UI explorer knows what APIs and models to display:

openapi: 3.0.0info:  version: 1.0.0  title: Persons APIpaths:  /persons:    get:      summary: Get all persons      responses:        '200':          description: OK          content:            application/json:              schema:                type: array                items:                  $ref: '#/components/schemas/Person'    post:      summary: Create a new person      requestBody:        required: true        content:          application/json:            schema:              $ref: '#/components/schemas/Person'      responses:        '201':          description: Created          content:            application/json:              schema:                $ref: '#/components/schemas/Person'  /persons/{id}:    parameters:      - name: id        in: path        required: true        schema:          type: integer    get:      summary: Get a person by ID      responses:        '200':          description: OK          content:            application/json:              schema:                $ref: '#/components/schemas/Person'        '404':          description: Person not found    put:      summary: Update a person by ID      requestBody:        required: true        content:          application/json:            schema:              $ref: '#/components/schemas/Person'      responses:        '200':          description: OK          content:            application/json:              schema:                $ref: '#/components/schemas/Person'        '404':          description: Person not found    delete:      summary: Delete a person by ID      responses:        '204':          description: No Content        '404':          description: Person not foundcomponents:  schemas:    Person:      type: object      properties:        id:          type: integer          readOnly: true        firstName:          type: string        lastName:          type: string

Configure the mssql connection object

Themssql package implements the connection to Azure SQL Database by providing a configuration setting for an authentication type.

In Visual Studio Code, create aconfig.js file and add the following mssql configuration code to authenticate to Azure SQL Database.

import * as dotenv from 'dotenv';if(process.env.NODE_ENV === 'development') {  dotenv.config({ path: `.env.${process.env.NODE_ENV}`, debug: true });}// TIP: Port must be a number, not a string!const server = process.env.AZURE_SQL_SERVER;const database = process.env.AZURE_SQL_DATABASE;const port = +process.env.AZURE_SQL_PORT;const type = process.env.AZURE_SQL_AUTHENTICATIONTYPE;const user = process.env.AZURE_SQL_USER;const password = process.env.AZURE_SQL_PASSWORD;export const noPasswordConfig = {  server,  port,  database,  authentication: {    type  },  options: {    encrypt: true  }};export const passwordConfig = {  server,  port,  database,  user,  password,  options: {    encrypt: true  }};

Create a local environment variable file

Create a.env.development file for your local environment variables

Add the following text and update with your values for<YOURSERVERNAME> and<YOURDATABASENAME>.

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.netAZURE_SQL_DATABASE=<YOURDATABASENAME>AZURE_SQL_PORT=1433AZURE_SQL_AUTHENTICATIONTYPE=azure-active-directory-default

Note

Passwordless configuration objects are safe to commit to source control, since they do not contain any secrets such as usernames, passwords, or access keys.

Add the following text and update with your values for<YOURSERVERNAME>,<YOURDATABASENAME>,<YOURUSERNAME>, and<YOURPASSWORD>.

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.netAZURE_SQL_DATABASE=<YOURDATABASENAME>AZURE_SQL_PORT=1433AZURE_SQL_USER=<YOURUSERNAME>AZURE_SQL_PASSWORD=<YOURPASSWORD>

Warning

Add the code to connect to Azure SQL Database

Create adatabase.js file and add the following code:

import sql from 'mssql';let database = null;export default class Database {  config = {};  poolconnection = null;  connected = false;  constructor(config) {    this.config = config;  }  async connect() {    try {      this.poolconnection = await sql.connect(this.config);      this.connected = true;      console.log('Database connected successfully.');      return this.poolconnection;    } catch (error) {      console.error('Error connecting to the database:', error);      this.connected = false;    }  }  async disconnect() {    try {      if (this.connected) {        await this.poolconnection.close();        this.connected = false;        console.log('Database disconnected successfully.');      }    } catch (error) {      console.error('Error disconnecting from the database:', error);    }  }  async executeQuery(query) {    const request = this.poolconnection.request();    const result = await request.query(query);    return result.rowsAffected[0];  }  async create(data) {    const request = this.poolconnection.request();    request.input('firstName', sql.NVarChar(255), data.firstName);    request.input('lastName', sql.NVarChar(255), data.lastName);    const result = await request.query(      `INSERT INTO Person (firstName, lastName) VALUES (@firstName, @lastName)`    );    return result.rowsAffected[0];  }  async readAll() {    const request = this.poolconnection.request();    const result = await request.query(`SELECT * FROM Person`);    return result.recordsets[0];  }  async read(id) {    const request = this.poolconnection.request();    const result = await request      .input('id', sql.Int, +id)      .query(`SELECT * FROM Person WHERE id = @id`);    return result.recordset[0];  }  async update(id, data) {    const request = this.poolconnection.request();    request.input('id', sql.Int, +id);    request.input('firstName', sql.NVarChar(255), data.firstName);    request.input('lastName', sql.NVarChar(255), data.lastName);    const result = await request.query(      `UPDATE Person SET firstName=@firstName, lastName=@lastName WHERE id = @id`    );    return result.rowsAffected[0];  }  async delete(id) {    const idAsNumber = Number(id);    const request = this.poolconnection.request();    const result = await request      .input('id', sql.Int, idAsNumber)      .query(`DELETE FROM Person WHERE id = @id`);    return result.rowsAffected[0];  }  async createTable() {    if (process.env.NODE_ENV === 'development') {      this.executeQuery(        `IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Person')         BEGIN           CREATE TABLE Person (             id int NOT NULL IDENTITY,              firstName varchar(255),              lastName varchar(255)           );         END`      )        .then(() => {          console.log('Table created');        })        .catch((err) => {          // Table may already exist          console.error(`Error creating table: ${err}`);        });    }  }}export const createDatabaseConnection = async (passwordConfig) => {  database = new Database(passwordConfig);  await database.connect();  await database.createTable();  return database;};

Test the app locally

The app is ready to be tested locally. Make sure you're signed in to the Azure Cloud in Visual Studio Code with the same account you set as the admin for your database.

Run the application with the following command. The app starts on port 3000.
```
NODE_ENV=development node index.js
```
ThePerson table is created in the database when you run this application.
In a browser, navigate to the OpenAPI explorer athttp://localhost:3000.
On the Swagger UI page, expand the POST method and selectTry it.
Modify the sample JSON to include values for the properties. The ID property is ignored.
SelectExecute to add a new record to the database. The API returns a successful response.
Expand theGET method on the Swagger UI page and selectTry it. SelectExecute, and the person you just created is returned.

Configure project for zip deployment

Create a.vscode folder and create asettings.json file in the folder.
Add the following to ignore environment variables and dependencies during the zip deployment.
```
{    "appService.zipIgnorePattern": ["./.env*","node_modules{,/**}"]}
```

Deploy to Azure App Service

The app is ready to be deployed to Azure. Visual Studio Code can create an Azure App Service and deploy your application in a single workflow.

Make sure the app is stopped.
Sign in to Azure, if you haven't already, by selecting theAzure: Sign In to Azure Cloud command in the Command Palette (Ctrl +Shift +P)
In Visual Studio Code'sAzure Explorer window, right-click on theApp Services node and selectCreate New Web App (Advanced).

Use the following table to create the App Service:

Prompt	Value
Enter a globally unique name for the new web app.	Enter a prompt such as`azure-sql-passwordless`. Post-pend a unique string such as`123`.
Select a resource group for new resources.	Select+Create a new resource group then select the default name.
Select a runtime stack.	Select an LTS version of the Node.js stack.
Select an OS.	SelectLinux.
Select a location for new resources.	Select a location close to you.
Select a Linux App Service plan.	SelectCreate new App Service plan. then select the default name.
Select a pricing tier.	SelectFree (F1).
Select an Application Insights resource for your app.	SelectSkip for now.

Wait until the notification that your app was created before continuing.
In theAzure Explorer, expand theApp Services node and right-click your new app.
SelectDeploy to Web App.
Select the root folder of the JavaScript project.
When the Visual Studio Code pop-up appears, selectDeploy.

When the deployment finishes, the app doesn't work correctly on Azure. You still need to configure the secure connection between the App Service and the SQL database to retrieve your data.

Connect the App Service to Azure SQL Database

The following steps are required to connect the App Service instance to Azure SQL Database:

Create a managed identity for the App Service.
Create a SQL database user and associate it with the App Service managed identity.
Assign SQL roles to the database user that allow for read, write, and potentially other permissions.

There are multiple tools available to implement these steps:

Service Connector is a tool that streamlines authenticated connections between different services in Azure. Service Connector currently supports connecting an App Service to an Azure SQL database via the Azure CLI using theaz webapp connection create sql command. This single command completes the three steps mentioned above for you.

Create the managed identity with Service Connector

Run the following command in the Azure portal's Cloud Shell. The Cloud Shell has the latest version of the Azure CLI. Replace the variables in<> with your own values.

az webapp connection create sql \    -g <app-service-resource-group> \    -n <app-service-name> \    --tg <database-server-resource-group> \    --server <database-server-name> \    --database <database-name> \    --system-identity

Verify the App Service app settings

You can verify the changes made by Service Connector on the App Service settings.

In Visual Studio Code, in the Azure explorer, right-click your App Service and selectOpen in portal.
Navigate to theIdentity page for your App Service. Under theSystem assigned tab, theStatus should be set toOn. This value means that a system-assigned managed identity was enabled for your app.
Navigate to theConfiguration page for your App Service. Under theApplication Settings tab, you should see several environment variables, which were already in themssql configuration object.
- AZURE_SQL_SERVER
- AZURE_SQL_DATABASE
- AZURE_SQL_PORT
- AZURE_SQL_AUTHENTICATIONTYPE
Don't delete or change the property names or values.

The Azure portal allows you to work with managed identities and run queries against Azure SQL Database. Complete the following steps to create a passwordless connection from your App Service instance to Azure SQL Database:

Create the managed identity

In the Azure portal, navigate to your App Service and selectIdentity on the left navigation.
On the identity page, change theSystem-assigned status toon and selectSave.
When asked to enable the identity, selectYes.
When this setting is enabled, a system-assigned managed identity is created with the same name as your App Service. System-assigned identities are tied to the service instance and are destroyed with the app when it's deleted.

Create the database user and assign roles

In the Azure portal, browse to your SQL database and selectQuery editor (preview).
SelectContinue as<your-username> on the right side of the screen to sign into the database using your account.
On the query editor view, run the following T-SQL commands. Replace<your-app-service-name> with your App Service resource's name.
```
CREATE USER "<your-app-service-name>" FROM EXTERNAL PROVIDER;ALTER ROLE db_datareader ADD MEMBER "<your-app-service-name>";ALTER ROLE db_datawriter ADD MEMBER "<your-app-service-name>";ALTER ROLE db_ddladmin ADD MEMBER "<your-app-service-name>";GO
```
This SQL script creates a SQL database user that maps back to the managed identity of your App Service instance. It also assigns the necessary SQL roles to the user to allow your app to read, write, and modify the data and schema of your database. After this step is completed, your services are connected.

Important

Although this solution provides a simple approach for getting started, it's not a best practice for production-grade environments. In those scenarios, the app shouldn't perform all operations using a single, elevated identity. You should try to implement the principle of least privilege by configuring multiple identities with specific permissions for specific tasks.

You can read more about configuring database roles and security on the following resources:

Create the App Service app settings

In the Azure portal, navigate to your App Service and selectConfiguration on the left navigation.
Select+ New application setting for each environment variable below. Add your own appropriate value to create the required environment variables for your App Service instance to connect to your database.
```
AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.netAZURE_SQL_DATABASE=<YOURDATABASENAME>AZURE_SQL_PORT=1433AZURE_SQL_AUTHENTICATIONTYPE=azure-active-directory-default
```
When you're done adding settings, selectSave.

In Visual Studio Code, in the Azure explorer, right-click your App Service and selectOpen in portal.

Navigate to theConfiguration page for your App Service. Under theApplication Settings tab, create environment variables for each property in the following table, with your own values.

AZURE_SQL_SERVER=<YOURSERVERNAME>.database.windows.netAZURE_SQL_DATABASE=<YOURDATABASENAME>AZURE_SQL_PORT=1433AZURE_SQL_USER=<YOURUSERNAME>AZURE_SQL_PASSWORD=<PASSWORD>NODE_ENV=development

Warning

Use caution when managing connection objects that contain secrets such as usernames, passwords, or access keys. These secrets shouldn't be committed to source control or placed in unsecure locations where they might be accessed by unintended users. For a real application in a production-grade Azure environment, you can store connection information in a secure location such as App Service configuration settings or Azure Key Vault. During local development, you'll generally connect to a local database that doesn't require storing secrets or connecting directly to Azure.

Test the deployed application

Browse to the URL of the app to test that the connection to Azure SQL Database is working. You can locate the URL of your app on the App Service overview page.

The person you created locally should display in the browser. Congratulations! Your application is now connected to Azure SQL Database in both local and hosted environments.

Tip

If you receive a 500 Internal Server error while testing, it may be due to your database networking configurations. Verify that your logical server is configured with the settings outlined in theConfigure the database section.

Clean up the resources

When you are finished working with the Azure SQL Database, delete the resource to avoid unintended costs.

In the Azure portal search bar, search forAzure SQL and select the matching result.
Locate and select your database in the list of databases.
On theOverview page of your Azure SQL Database, selectDelete.
On theAzure you sure you want to delete... page that opens, type the name of your database to confirm, and then selectDelete.

Delete your database by using theaz sql db delete command. Replace the placeholder parameters with your own values.

az sql db delete --name <database-name> --resource-group <resource-group-name> --server <logical-server-name>

Sample code

The sample code for this application is available: