戴周林的Blog

Connecting to SQL Server 2005 in Websphere Application Server v6.1 using Embedded Connect JDBC Driver

The Connect JDBC driver is a Type 4 JDBC driver for high-performance Java database connectivity to all of today’s major databases. This article describes how to configure Connect JDBC driver in WAS v6.1 to work with SQL Server 2005.

1. Before you start
Before you configure the data source, you need to have the following software installed:
* IBM Websphere Application Server v6.1
* Microsoft SQL Server 2005

If you do not install Websphere Application Server and SQL Server on the same machine, you need to configure SQL Server to access remote connections via TCP/IP.
* Go to Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration.
* Click Surface Area Configuration for Services and Connections
* Go to MSSQLSERVER->Database Engine->Remote Connections
* Select “Local and remote connections” and “Using TCP/IP only”
* Go to MSSQLSERVER->Database Engine->Service
* Stop and start SQL Server

2. Create Authentication Data for Database Connection
The authentication data contain the username and password for SQL Server 2005 connection. It will be used to connect to the database in the SQL Server 2005. Make sure the user id you specify here has appropriate permissions to perform the actions in your application.
* Open WAS admin console
* Go to Security->Secure administration, applications, and infrastructure
* Expend Java Authentication and Authorization Service and click J2C authentication data
* Click New button to create a new username password pair
* Set alias, username and password for this authentication, for example,
Alias: MSSQL2005_Auth
User ID: <sql_server_id>
Password: <password>
* Click OK and Save

3. Create JDBC Provider for SQL Server Connection
To connect to SQL Server 2005, you need to tell WAS where the JDBC driver is installed.
* Open WAS admin console
* Go to Resources->JDBC->JDBC Providers
* In the Node scope, click New
* In Step 1, set the values as following:
Database type: SQL Server
Provider type: Websphere embedded ConnectJDBC driver for MS SQL Server
Implementation type: Connection pool data source
* Click Next, the wizard goes directly to Step 3
* Verify the values, then click Finish and Save.

4. Create a Data Source for SQL Server Connection
Now you can tell WAS where the SQL Server is and which database to use.
* Open WAS admin console
* Go to Resources->JDBC->Data sources
* In the Node scope, click New
* In Step 1, set the data source name and the JNDI name
Data source name: MSSQL2005_DATASOURCE
JNDI name: jdbc/mssql2005
Authentication: MSSQL2005_Auth

* Click Next to Step 2
* In Step 2, select the JDBC provider you created, Websphere embedded ConnectJDBC driver for MS SQL Server
* Click Next to Step 3
* Set the database name and server name
Database name: <db_name>
Server name: <IP address or host name for the machine where SQL Server presents>
* Click Next to Step 4
* Verify the values, then click Finish and Save.

5. Verify the Connection to SQL Server 2005
Now you have everything ready, use Data sources-> MSSQL2005_DATASOURCE->Test connection to verify the configuration.