IBM BPM and Microsoft SQL Server 2012 - What Fun

I've written about this MANY MANY times before, over the years and releases of IBM BPM.

This time around, I'm working with BPM 8.6 and Microsoft SQL Server 2012.

This is the second time in two weeks where I've worked with customers running their BPM workload on a Windows stack, including Windows Server 2012, Internet Information Server, Active Directory AND SQL Server.

The use of SQL Server 2012 brings two new wrinkles to the thus far smooth fabric of my BPM installations : -

1. SQL Server supports so-called Integrated Authentication, where the bind from WebSphere Application Server to SQL Server via JDBC is made without sending credentials over the wire
2. Secure JDBC connections are made using TLS 1.2, use of which is enforced

With regard to (1), this means that the JDBC connection is secured by use of a "trusted" user ID, known as a Service Account.

There is some analogy to the Single Sign-On (SSO) model that is offered by Kerberos / SPNEGO - in both cases, the target server trusts that the connecting user has been authenticated, by the Windows Domain Controller, rather than having credentials sent over the wire.

This is enforced by use of an additional JDBC connection string parameter: -

integratedSecurity=true

From a WAS perspective, this means that we need to run the JVMs, ALL of them, as the Service Account.

In the Windows world, this can be achieved using the runas command, similar to the way that the Unix sudo command works: -

runas /user:<DOMAIN>\<USER> cmd.exe

If successful, this brings up another CMD window, running as the Service Account.

From this, we can run commands such as startManager.bat and startServer.bat and bootstrapProcessServerData.bat.

To facilitate this, it's a good idea to run the JVMs, especially the Deployment Manager and the Node Agent(s), as Windows Services, using wasservice.exe and WASServiceHelper.bat : -

Using the WASServiceHelper utility to create Windows services for application servers

ensuring that one uses the Service Account to run the WAS binaries.

Speaking personally, I chose to set the Windows Services to auto-start on boot-up but NOT to restart after failure ( allowing one to perform PD when a problem occurs ).

Also, I chose NOT to include the actual JVMs ( AppClusterMember, SupClusterMember, MEClusterMember etc. ) as Services, allowing these to be manually started using WSAdmin or the Integrated Solutions Console.

With regard to (2), we need to ensure that all JDBC connections use TLS 1.2 …

This caused me some fun last week ……

I assumed (!) that this was a simple matter of configuring WAS to use TLS 1.2, via SSL Configurations, pulling the SQL Server signer certificate into the Cell-Default Trust Store etc. ….

Nope, it's not like that !

If the TLS 1.2 connection isn't enforced, we see this: -

Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "SQL Server did not return a response. The connection has been closed. ClientConnectionId:42f55367-538e-4f70-b007-023af79435f4". DSRA0010E: SQL State = 08S01, Error Code = 0

TLS 1.2 can be enforced, via use of a JVM property: -

com.ibm.jsse2.overrideDefaultTLS

This can either be set as  a Generic JVM Argument

-Dcom.ibm.jsse2.overrideDefaultTLS=true 

or as a JVM Custom Property 

Name - com.ibm.jsse2.overrideDefaultTLS
Value - true

This ALSO needs to be enabled for other Java processes, such as the Bootstrap …..

This requires one to edit the bootstrapProcessServerData.bat script and inserting: -

-javaoption "-Dcom.ibm.jsse2.overrideDefaultTLS=true"

into the script.

So….

Having said all of the above, I hit an issue yesterday where the Bootstrap failed, with: -

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select propvalue from lsw_system where propkey=?]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'lsw_system'.

Given that I had confirmed that I was running this using the Service Account ( using the runas command as mentioned above ), I wasn't too sure what was going wrong ( previously I'd seen the same issue when running the bootstrap as another user - the DBA was able to see this in a SQL Server trace ).

Given that I wasn't seeing any SSL exceptions, I knew that the TLS 1.2 configuration was A-OK.

I then asked the DBA to check the COLLATION sequence as per this: -

Creating SQL Server databases

which was also A-OK.

At this point, I was hitting a blocker ….

Finally, the DBA had the inspiration to check the Default Schema on the BPMDB and PDWDB ( and CMNDB ) databases.

This was set to dbo which is a SQL Server default RATHER than to the Service Account.

Once he changed it …. ALL WORKED !!!

So, in short, SQL Server is fun, just get your security right !

References

Building the Connection URL

Why do I get database connection error, in BPM 8.5.7 using MS SQL Database that uses SSL TLSv1.2 protocol ?