How do I connect to Azure SQL Database with Ballerina

Question

I am using this mssql Ballerina package to connect to my Azure SQL database.

Error:

error: Error in SQL connector configuration: Failed to initialize pool: Connection reset ClientConnectionId:5d5b4f8b-7e6e-43d0-bce3-5fe79e403088 Caused by :Connection reset ClientConnectionId:5d5b4f8b-7e6e-43d0-bce3-5fe79e403088 Caused by :Connection reset
        at ballerinax.mssql.1:createClient(Client.bal:173)
           ballerinax.mssql.1:init(Client.bal:49)
           viggnah.test_apis.0.$anonType$_1:$get$test(testAPI.bal:45)

Code:

import ballerina/sql;
import ballerina/http;
import ballerinax/mssql.driver as _;
import ballerinax/mssql;

configurable int servicePort = 9090;
configurable string user = "admin@my-server";
configurable string host = "my-server.database.windows.net";
configurable string database = "my-db";
configurable int dbPort = 1443;
configurable string password = "***";

service / on new http:Listener(servicePort) {
    
    resource function get test(string id) returns error? {
        mssql:Client|sql:Error dbClient = new(host=host, user=user, password=password, database=database, port=dbPort);
        if dbClient is error {
            return dbClient;
        }
    }
}

Answer 1

I created azure SQL database with following firewall rules:

Added clint IP to the firewall. I run the below code to connect Azure SQL database with Ballerina.

import  ballerina/sql; 
import  ballerinax/mssql.driver  as  _;
import  ballerinax/mssql;

string  clientStorePath = "/path/to/keystore.p12";
string  trustStorePath = "/path/to/truststore.p12"; 

mssql:Options  mssqlOptions = {

 secureSocket: {

    encrypt: true,

   -trustServerCertificate: false,

    key: {

      path: clientStorePath,

     password: "password"

   },

   cert: {

       path: trustStorePath,

       password: "password"

   }

 }

};

mssql:Client|sql:Error  dbClient = new(host="<serverName>.database.windows.net", user="<username>", password="<password>", database="<dbName>", port=1433); 

It connected successfully without any error. Image for reference:

I try this another way using below code:

    import  ballerina/sql; 
    import  ballerinax/mssql.driver  as  _;
    import  ballerinax/mssql;
    
    mssql:Client|sql:Error  dbClient = new(host="<serverName>.database.windows.net", user="<username>", password="<password>", database="<dbName>", port=1433);

By using above also my azure SQL database is connected successfully. Image for reference:

It worked for me. Once check from your end.