How to change default databases of WSO2 API Manager to databases in MSSQL Docker Container on Ubuntu

1. Install Docker Engine using this link.
2. Create and run MSSQL as a Docker container using following command.

sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Root@123" -p 1433:1433 --name mssql -d mcr.microsoft.com/mssql/server:2019-latest

3. [Optional] Start the mssql container.

sudo docker start mssql

4. View running Docker containers to get container-id of mssql container.

sudo docker ps -a

5. Add USE test; to start of the <API-M_HOME>/dbscripts/apimgt/mssql.sql file then copy it tomssql container.

sudo docker cp <API-M_HOME>/dbscripts/apimgt/mssql.sql <container-id>:/var/lib/

6. Login to mssql container.

sudo docker exec -i -t mssql /bin/bash

7. Login to MSSQL console as sa user.

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Root@123

8. Create a database named test.

1> create database test
2> go
1> use test
2> go

9. Exit to mssql container

1> exit

10. Run mssql.sql database script.

/opt/mssql-tools/bin/sqlcmd -S localhost -i /var/lib/mssql.sql -U sa -P Root@123

11. Exit the mysql container.

exit

12. Download and copy mssql-jdbc-<mssql-version>.jre<version>.jar from this link to <API-M_HOME>/repository/components/lib directory.

13. Change the configurations.

Below configurations show changes to WSO2AM_DB.
Follow the same pattern for other databases.

• If your WSO2 API-M version < 3.0.0 then change configurations in <API-M_HOME>/repository/conf/datasources/master-datasources.xml file as follows.

<datasource>
    <name>WSO2AM_DB</name>
    <description>The datasource used for API Manager database</description>
    <jndiConfig>
        <name>jdbc/WSO2AM_DB</name>
    </jndiConfig>
    <definition type="RDBMS">
        <configuration>
            <url>jdbc:sqlserver://localhost:1433;databaseName=test;SendStringParametersAsUnicode=false;trustServerCertificate=true</url>
            <username>sa</username>
            <password>Root@123</password>
            <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
            <maxActive>80</maxActive>
            <maxWait>360000</maxWait>
            <minIdle>5</minIdle>
            <testOnBorrow>true</testOnBorrow>
            <validationQuery>SELECT 1</validationQuery>
            <validationInterval>30000</validationInterval>
            <defaultAutoCommit>false</defaultAutoCommit>
        </configuration>
    </definition>
</datasource>

• If your WSO2 API-M version ≥ 3.0.0 then change configurations in <API-M_HOME>/repository/conf/deployment.toml file as follows.

[database.apim_db]
type = "mssql"
url = "jdbc:sqlserver://localhost:1433;databaseName=test;SendStringParametersAsUnicode=false;trustServerCertificate=true"
username = "sa"
password = "Root@123"
driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
validationQuery = "SELECT 1"

14. Now start/restart WSO2 API Manager.