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

  1. 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. 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

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.

<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/test</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>
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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Binod Karunanayake

Binod Karunanayake

BSc Engineering (Hons) Department of Computer Science and Engineering of University of Moratuwa | Software Engineer @WSO2