CodeIgniter 4 – How to connect to Microsoft Azure SQL server

To connect CodeIgniter 4 to a Microsoft Azure SQL database, you need to configure your CodeIgniter application by setting up the database configuration file appropriately. Follow these steps:

Step 1: Install CodeIgniter 4

If you haven’t already installed CodeIgniter 4, you can do so via Composer

composer create-project codeigniter4/appstarter projectName

Replace projectName with the desired name for your CodeIgniter project.

Step 2: Set Up Database Configuration

Navigate to app/Config/Database.php in your CodeIgniter project and configure the database settings for the Azure SQL Server

<?php

namespace Config;

use CodeIgniter\Database\Config;

class Database extends Config
{
    public $defaultGroup = 'default';

    public $default = [
        'DSN'      => '',
        'hostname' => 'your-server.database.windows.net',
        'username' => 'your-username',
        'password' => 'your-password',
        'database' => 'your-database',
        'DBDriver' => 'SQLSRV', // Use SQL Server driver
        'DBPrefix' => '',
        'pConnect' => false,
        'DBDebug'  => (ENVIRONMENT !== 'production'),
        'cacheOn'  => false,
        'cacheDir' => '',
        'charset'  => 'UTF-8',
        'DBCollat' => 'utf8_general_ci',
        'swapPre'  => '',
        'encrypt'  => true, // Azure SQL Server usually requires encryption
        'compress' => false,
        'strictOn' => false,
        'failover' => [],
        'port'     => 1433, // Azure SQL Server port
    ];
    
    // Other database connections, if needed...
}

Replace 'your-server.database.windows.net', 'your-username', 'your-password', 'your-database' with your Azure SQL Server credentials and database information.

Step 3: Enable SQLSRV Driver

Ensure that the PHP SQLSRV driver is installed and enabled on your server. You might need to install it via PECL or enable it in your PHP configuration file.

Step 4: Verify Connection

Create a controller or a simple test script to check the database connection

<?php

namespace App\Controllers;

class TestController extends BaseController
{
    public function index()
    {
        $db = \Config\Database::connect();
        
        if ($db->connID->connect_error) {
            die("Connection failed: " . $db->connID->connect_error);
        }
        
        echo "Connected successfully";
    }
}

Then, visit the corresponding URL in your browser to check if the connection to the Azure SQL Server database is successful.

Step 5: Error Handling and Security

Ensure proper error handling and security measures in your CodeIgniter application. Use environment-specific configuration files and avoid hardcoding sensitive information.

Additional Considerations

Ensure your Azure SQL Server firewall rules allow access from your application server’s IP address.
Azure SQL Server might require additional setup or configurations, such as setting up the firewall rules and enabling the appropriate features for external access.

Always remember to handle database credentials and sensitive information securely to prevent any unauthorized access to your Azure SQL Server