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:
- make sure the database application is installed, either locally or on a server
- Create the database that will hold the tables
- Load the jdbc driver in the repository /components /lib directory
- 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):
- Turn containment on :
sp_configure 'contained database authentication', 1;
Go
- Create the database
Create database wso2carbon containment = partial
Go
- Create a user
Create user root with password = 'root'
Go
- 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.
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 Tutorials, webinars or white papers for more technical information. Need support? We do deliver WSO2 Product Support, WSO2 Development Support, WSO2 Operational Support and WSO2 Training Programs.
Thanks to Thijs Volders for his contribution to this blogpost.