Database Setup

Configure MSSQL Server database connection for Portal Self-Service Backend

Overview

The Portal Self-Service Backend uses Microsoft SQL Server (MSSQL) as its database, with connections managed through the mssql package. The application establishes a connection pool for efficient database operations.

Database Requirements

MSSQL Server

Microsoft SQL Server instance (2016 or later recommended)

Network Access

Server must be accessible from your application host

Authentication

Valid database credentials (user/password)

Database Created

Target database must exist before connecting

Required Environment Variables

The following environment variables must be configured for database connectivity:

Variable	Description	Example
DB_USER	Database username	sa or app_user
DB_PASSWORD	Database password	YourSecurePassword123!
DB_SERVER	Database server hostname or IP	localhost or 192.168.1.100
DB_DATABASE	Database name	PortalSelfService
DB_PORT	SQL Server port	1433 (default)

Connection Configuration

1. Review Database Configuration

   The database configuration is defined in src/config/db.config.js. Here’s the connection setup:

   src/config/db.config.js

   import sql from 'mssql';
   
   const config = {
       user: process.env.DB_USER,
       password: process.env.DB_PASSWORD,
       server: process.env.DB_SERVER, 
       database: process.env.DB_DATABASE,
       port: parseInt(process.env.DB_PORT, 10),
       options: {
           trustServerCertificate: true, 
           enableArithAbort: true
       },
       pool: {
           max: 10,
           min: 0,
           idleTimeoutMillis: 30000
       }
   };
   
2. Connection Pool Settings

   The application uses a connection pool with the following configuration:

   • Maximum connections: 10 concurrent connections
   • Minimum connections: 0 (connections created on-demand)
   • Idle timeout: 30 seconds before releasing idle connections
3. SSL/TLS Configuration

   The connection uses trustServerCertificate: true to allow self-signed certificates.

   In production environments, configure proper SSL certificates and set trustServerCertificate: false for enhanced security.

Connection Pool Initialization

The application establishes the database connection pool at startup:

src/config/db.config.js

const poolPromise = new sql.ConnectionPool(config)
    .connect()
    .then(pool => {
        console.log('Conexión a SQL Server establecida.');
        return pool;
    })
    .catch(err => {
        console.error('Falló la conexión a SQL Server:', err.message);
        throw err; 
    });

export { sql, poolPromise, dbConfig };

The connection is established asynchronously and verified during server startup in server.js.

Server Startup Verification

The application waits for the database connection before starting the HTTP server:

server.js

async function startServer() {
    try {
        await poolPromise; // Wait for database connection
        
        server.listen(PORT, () => {
            console.log(`Servidor escuchando en http://localhost:${PORT}`);
        });
    } catch (error) {
        console.error('Error fatal: La aplicación no pudo iniciar.', error.message);
        process.exit(1); 
    }
}

Troubleshooting

Connection Refused

Check Network Connectivity

Ensure the database server is reachable:

# Test connectivity to SQL Server
telnet your-db-server 1433

Authentication Failed

Verify Credentials

• Double-check DB_USER and DB_PASSWORD
• Ensure the user has access to the specified database
• Check for special characters in password (may need escaping)

SQL Server Authentication

• Verify SQL Server authentication mode is enabled (not Windows-only)
• Confirm the user account is not locked or disabled

Connection Timeout

1. Check Firewall

   Ensure port 1433 (or your custom port) is open on the database server firewall.

2. Verify SQL Server Configuration

   Ensure TCP/IP protocol is enabled in SQL Server Configuration Manager.

3. Check Connection String

   Verify DB_SERVER and DB_PORT are correct in your .env file.

Database Does Not Exist

The database specified in DB_DATABASE must be created manually before running the application. The application does not auto-create databases.

Create the database using SQL Server Management Studio (SSMS) or via T-SQL:

CREATE DATABASE PortalSelfService;
GO

Common Issues

Issue	Possible Cause	Solution
ConnectionError: Failed to connect	Server unreachable	Verify DB_SERVER and network connectivity
Login failed for user	Invalid credentials	Check DB_USER and DB_PASSWORD
Cannot open database	Database doesn’t exist	Create database or verify DB_DATABASE name
Connection timeout	Firewall blocking	Enable port 1433 in firewall rules
Self signed certificate	SSL validation	Already handled with trustServerCertificate: true

Testing the Connection

To verify your database configuration:

1. Set Environment Variables

   Configure all required variables in your .env file.

2. Start the Application

   npm start
   
3. Check Console Output

   Look for the success message:

   Conexión a SQL Server establecida.
   Servidor escuchando en http://localhost:3000
   
4. Test Health Endpoint

   curl http://localhost:3000/health
   

   Expected response:

   {
     "status": "UP",
     "message": "Express Server Running"
   }
   

Next Steps

Environment Configuration

Configure all environment variables including Azure AD

Deployment

Deploy your application to production