fb
WSO2 Tutorial 7 min

Making Microsoft SQL Server the core database for your WSO2 product

Rob Blaauboer
Rob Blaauboer
Integration Consultant & WSO2 Trainer
Microsoft SQL Server
Scroll

Microsoft SQL Server.pngAs you might know, WSO2 products have an out of the box database included. This h2 database is suited for some light testing or trying out the product. For anything beyond that, moving to another database is really recommended.

This database can basically be any jdbc enabled database, however, there are some databases for which scripts are available. These scripts will create all the necessary tables.

For any database the steps are the same:

  1. make sure the database application is installed, either locally or on a server
  2. Create the database that will hold the tables
  3. Load the jdbc driver in the repository /components /lib directory
  4. Make changes to the configuration files like master-datasources.xml or custom-datasources.xml

With regards to the latter, depending on the product and or the type of deployment (e.g. clustered or distibuted) one of more data sources need to be changed.

In this example we will take the WSO2 ESB 5.0.0 and move from the h2 database to an MSSQL database running on a separate server.

The setup of MSSQL server is more complex than a MySql or MariaDB but only from the perspective of some extra steps pertaining to MSSQL.

Setup

We assume the MSSQL server is running on a server somewhere in the network and it is known by the hostname mssqltest.acme.com.

For the setup of the database we will use the SQLCMD tool. This commandline tool is not the easiest way to do things for people who appreciate a graphical interface but since the scripts that we are going to run to create the tables with a limited set of commands this approach makes sense.

Ask your DBA

If MSSQL is managed by a database administrator or DBA, ask him of her to create a user with sufficient rights to create and alter tables,triggers, etc and configure a password. It is best to do this in a contained database (there are no dependencies with MSSQL as far as users go, everything is inside the database). Create a password as well since we want to access the data with a userid / password combination for security reasons.

Install SQLCMD locally or on a server and start it. You might need a userid password to do so (depends on how it was installed). Pass these as parameters to SQLCMD like this:

SQLCMD –U [username] -P [password]

Where username and password are of course the values you were given or that you created. The interface is simple and just offers an incremental linenumber and a ‘>’ sign.

>1 'commands go here'

You enter a command and to effectuate it press enter, type in go on the next line and press enter again.

Setting it up

If you do have access to the MSSQL environment because you installed it locally these are the steps you need to take (using SQLCMD):

  1. Turn containment on :

sp_configure 'contained database authentication', 1;
Go

  1. Create the database

Create database wso2carbon containment = partial
Go

  1. Create a user

Create user  root with password = 'root'
Go

  1. Make the user db_owner

Exec sp_addrolemember 'db_owner', 'root'
Go

This is a quick and dirty approach which DBA’s will not like. The essence is that you have rights to create tables from the user as well as general read/write rights.

In order to be able to login, you also need to set the Server Authentication to SQL Server and Windows Authentication Mode and restart MSSQL in order to effectuate the change.

set the Server Authentication to SQL Server and Windows Authentication Mode and restart MSSQL.png

Run the script

We will use sqlcmd for this since we just need to run a SQL script.

We will start the command line interface using the following statement (from a command window or terminal  window)

sqlcmd -S mssqltest.acme.com -U root –P root –d wso2carbon -i [esbhome]/dbscripts/mssql.sql

If the user as sufficient rights the tables will be created.  We now have a database with WSO2 ESB required tables. Now we need to tell the ESB where it needs to look for these tables.

Making changes

In this simple case, a database that has both user and registry data we need to go to the master-datasources.xml file and edit the entry for wso2carbon.

It’s easiest to copy the existing entry, comment it out with <!– and –> and change the copied entry to the new situation. This allows you to keep the old-entry available as template. You need to change a number of XML tags to make it work:

  • Username
  • Password
  • Jdbc driver
  • URL

Other settings can be left unchanged  or changed to the sample setup described in the setting up the physical database in the online documentation.

<datasource>
            <name>WSO2_CARBON_DB</name>
            <description>The datasource used for registry and user manager</description>
            <jndiConfig>
                <name>jdbc/WSO2CarbonDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:sqlserver://mssqltest.acme.com:1433;databaseName=wso2carbon</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClassName>
                    <maxActive>80</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                    <defaultAutoCommit>false</defaultAutoCommit>
                </configuration>
            </definition>
        </datasource>

Please note that a separated user and registry database is also possible I refer you again  to the online online documentation for separating these databases.  It is a little bit more work since there are, in that case, two databases that need to be created.

JDBC MSSQL Driver

The driver(a jar file) that you put in the [esb-home]/repository/components/lib directory can be downloaded from Microsoft. In case of the most current MSSQL version (2016) use the 4.2 version of the driver or a newer general version (don’t use a beta version as that limits your support availability when you will run the ESB in a production environment).

After all the changes have been made you can start up the WSO2 ESB product and keep an eye on the logfile (in [ESB_HOME]repositorylogswso2carbon.log) while its starting.  Login to the WSO2 ESB admin console with an administrator account (i.e. admin/admin). Creating a new user through this admin-console is a good way to test if the DB-user has sufficient access-rights for the WSO2 ESB to work with the MSSQL database .

When you login to the admin-conolse it will show the overview screen which also shows the database and the driver details.

Integrated security

If you’re running a Windows centric environment and have the WSO2 ESB installation also on a Windows machine then you can use MSSQL Integrated security feature to login to the database.

In this case you don’t need to provide a username nor password in the master-datasources.xml for your datasource configuration. The WSO2 ESB process will run with a specific user account, Windows will use this account to connect to the MSSQL database server and access the database.

Make sure that the MSSQL database has Windows authentication configured for the database’s security setup and the WSO2 ESB user (that runs the ESB process) has accessrights to the database. You can configure this on a per database level. In the previous paragraph you see a screenshot of this security configuration where we earlier selected the SQL Server and Windows authentication mode. This mode should work fine when using integrated security. You can even choose “Windows authentication” mode only if preferred or required by your security policy.

The next thing to change is the JDBC URL in the master-datasources.xml of the ESB. You have to append the attribute “;integratedSecurity=true” the url-value.

The username and password-values will be ignored when the connection is made so you can fill in anything in those two XML-elements.

Example:

<configuration><url>jdbc:sqlserver://mssqltest.acme.com;databaseName=wso2esb_carbon;integratedSecurity=true</url><username>ACMEdatabaseuser</username>

Save the master-datasources.xml file and restart the ESB. Keep track of the wso2carbon.log file to see if the database connection is properly created and the WSO2 ESB instance starts without errors related to the database connectivity.

If you have any questions about this blogpost contact us via the comments section of this blog. View also our WSO2 Tutorialswebinars or white papers for more technical information. Need support? We do deliver WSO2 Product Support, WSO2 Development SupportWSO2 Operational Support and WSO2 Training Programs. 

Thanks to Thijs Volders for his contribution to this blogpost.

Full API lifecycle Management Selection Guide

WHITEPAPER

smartmockups l0qqucke