Engwar

Pointing WSO2 Identity Server to use user data stored in an external database has never been this easy. Once you complete the following it just works without having to do any other configuration or editing config files. All this can be done in few seconds using the excellent, intuitive web based management interface. In this case I wanted to point the Identity Server to talk to a Microsoft SQL Server database. More specifically this is how the Identity Server is configured in Apache Stonehenge project so that the .Net client program can authenticate via the Identity Server passive STS against users stored in a Microsoft SQL Server database. If you’re talking via JDBC this can be any database.

1. You need to download the jTDS driver that can talk to Microsoft SQL. Get the latest driver here.
2. Copy the jTDS jar file to <Identity Server folder>\repository\components\lib and restart the server.
3. Now, login to Identity Server web based administration console by going to http://localhost:9443/carbon. Default username: admin and password: admin.
4. Goto User Management > Add External User Store and fill up with the following values

   Connection URL: jdbc:jtds:sqlserver://localhost:1433/stocktraderdb
   Connection user name: trade
   Connection password: yyy
   Connection password repeat: yyy
   Driver name: net.sourceforge.jtds.jdbc.Driver

   SQL for retrieving a given user’s password: select password from accountprofile where userid = ?
   SQL for listing users using “like”: select userid from accountprofile where userid like ?
   SQL for selecting a single user given the user name: select userid from accountprofile where userid = ?
   SQL for retrieving a user list: select userid from accountprofile

   and click Finish.

5. Now, click Test Connection to make sure the connection to the database is working fine.

That’s all you need to do. Now whenever your application point you to the Identity Server passive STS, you’ll be authenticated against users in the Microsoft SQL database.