Friday, 17 July 2015

Connecting AEM 6 with Microsoft SQL Server

Some old applications that you might want to migrate to AEM might still use SQL Server databases. Here are the steps to access an external SQL server from AEM 6:

Building the OSGI bundle for the JDBC driver:

1. Download the appropriate driver from here 
2. Unzip the downloaded driver.
3. Open eclipse indigo.
4. Click on File-> New -> Other -> Plugin from existing JAR archive 
5. Click on Add External and enter the path of the downloaded jar
6. Enter Plugin properties as shown here:
7. Observe the MANIFEST.MF, it shall look like this:
8. Save the project.
9. Right click the project --> Export --> Plug-in Development --> Deployable plug-ins and fragments
10. Choose a directory location --> Save.
11. The OSGI jar is available at the given-directory-location/plugins

Uploading the OSGI jar in felix:

1. Go to http://localhost:4502/system/console/bundles
2. Click on Install/Update
3. Upload the jar:
4. The jar should come in Active state. Look for errors in the error log if it still shows "Installed":
5. Error Log might show:
org.osgi.framework.BundleException: Unresolved constraint in bundle com.myproject.jdbc [398]: Unable to resolve 398.0: missing requirement [398.0] osgi.wiring.package; (&(>=1.3.0))
The bundle might also show unresolved dependencies:
6. Edit the MANIFEST.MF to remove the versions for resolving dependencies:
7. Re-install the bundle and it should be active now:

JDBC Connection Pool Factory config:

1. Go to http://localhost:4502/system/console/configMgr
2. click on "+" to add a configuration:
3. The configuration looks like this:

Test Connection:

1. An example code snippet for testing the connection:

Note: You might have to allow your Microsoft SQL server to accept remote connections! Please refer this

Hope this helps!!!