Home Services Partners Company
Websphere Liberty Profile JDBC NTLM Windows Authentication from Unix/Linux to SQL Server

Websphere Liberty Profile JDBC NTLM Windows Authentication from Unix/Linux to SQL Server

May 16, 2013 0 Comments

In introducing Websphere Liberty Profile (WLP), Dr. Ian Robinson, IBM Distinguished Engineer asks these three questions when choosing a tool for any kind of job:

  1. Is this going to be able to do the job?
  2. Is this the easiest way to do it?
  3. Do I like using it?

The answers for WLP were all yes; and you should ask these same questions when choosing your JDBC driver for connectivity.  Here are my answers after following the WLP tutorial and these apply to Websphere Application Server, as well as Tomcat or JBoss. Note: If you're looking for the Websphere Upgrade to SQL Server tutorial, check here.

Is this going to be able to do the job?

The DataDirect SQL Server JDBC driver will get your job done without any road blocks thanks to:

  • Support for NTLM Windows Authentication from Unix/Linux servers to keep your DBAs and auditors happy.
  • Includes our patented SQL Server JTA library built by DataDirect without any locking or rollback issues under heavy load.
  • High performance and scalability both in terms of increased throughput and decreased memory/CPU/Garbage Collection.  Our daily performance runs against a competing driver show increased throughput up to 91% and up to 2815% memory savings and up to 221% CPU savings across different tests.  Note: Our lab results are not intended to substitute testing in a production like environment.

Is this the easiest way to do it?

  • It just works.  Not by coincidence, but because we run 80 million tests across multiple JRE flavors from IBM, HP, Oracle, etc.
  • Single JAR supports multiple JREs and SQL Server versions (Microsoft SQL Server 2012, 2008 R1, R2, 2005, 2000 Desktop Engine (MSDE 2000), 2000 and 7.0.
  • DataDirect has award winning 24/7 support including member to tsanet.org giving us access to Microsoft and IBM on your behalf.

Do I like using it?

We've heard from developers that they are 2x as productive with DataDirect; and can save many dev months with advanced features such as:

  • Bulk load (codeless and API) capabilities are built-in and can improve JDBC batch insert jobs from 7 hours down to under 30 minutes.
  • High availability features where driver can transparently failover to mirrored server during connection failure in the middle of a result set.

Getting Started with Websphere Liberty Profile JPA project with DataDirect JDBC drivers

1 . Install Progress DataDirect SQL Server Connect for JDBC driver using the instructions in the Progress DataDirect Connect for JDBC installation Guide.  You can download a free, 15-day trial of the Progress DataDirect SQL Server JDBC driver (including DB2, Oracle, Sybase, MySQL and Salesforce).

2. Install JTA support using DataDirect's patented sqljdbc.dll library that Websphere shops have been using for years for reliable transaction processing.

3. Follow tutorial for getting started with WAS Liberty Profile from IBM.  Note: I used SQL Server Express 2012 and noted the exceptions for each module below to configure the DataDirect Connect for JDBC SQL Server driver.

4. Create table for ReportServer$SQLEXPRESS database in SQL Server instead of DB2 per module Module 1.2 : Create the database and Liberty server.

CREATE TABLE [dbo].[ACCOUNTS]( [C_NUM] [smallint] NOT NULL, [C_NAME] [varchar](50) NULL, [C_MONEY] [bigint] NOT NULL, CONSTRAINT [PK_ACCOUNT] PRIMARY KEY CLUSTERED ( [C_NUM] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

5. Configure DataDirect SQL Server JDBC driver instead of DB2 in, Module 2.2 : Configure the ser`ver for DB2

Set DataDirect SQL Server JDBC Driver in WLP

Base directory is the DataDirect JDBC installation folder. In this case, I installed to C:\jdbc\Connect_for_JDBC_51.

Set DataDirect SQL Server JDBC Shared Library in WLP

id is ddsqlserverconnection and JNDI name*: jdbc/SQLServerConnection

Set DataDirect SQL Server JDBC Data Source in WLP

Database name is ReportServer$SQLEXPRESS; Server name is locahost\SQLEXPRESS nad URL is jdbc:datadirect:sqlserver://localhost\SQLEXPRESS;AuthenticationMethod=ntlm

Set DataDirect SQL Server JDBC Driver Connection URL in WLP

6. After creating new JPA Entities from Tables and select Generic JDBC for DataDirect.

Select Generic JDBC driver from Websphere Connection Profile

Enter URL as jdbc:datadirect:sqlserver://localhost\SQLEXPRESS;AuthenticationMethod=ntlm;DatabaseName=ReportServer$SQLEXPRESS

Note: If using Windows Authentication from Unix/Linux (for example AMERICAS\SUMITS), enter your Windows Authentication user "SUMITS" without the domain and password; and then add the following properties to your deployment profile to Linux, AIX, Solaris or HP-UX:

;AuthenticationMethod=ntlmjava;Domain=AMERICAS;

Set Authentication to ntlm or ntlm java for windows authentication from Unix/Linux

7. Modify DBInteractions.java with the the following line for SQL Server instead of DB2:

@Resource(name = "jdbc/SQLServerConnection", type = javax.sql.DataSource.class, shareable =

8. Run the index.html per Module 6.2 : Complete and test the JPA application with DB2

wlp7

wlp8

Finally, if you run into any issues, here is a link to generate WLP tracing.

So, did I like using it?

I had a good time!  Call us at N. America: (800)876-3101 | World: +44 (0) 1-344-386-367 to learn more about DataDirect SQL Server JDBC drivers for use in the world of Websphere.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Comments
Comments are disabled in preview mode.