Using a SQL Server back end with a Java application server may sound like an unnatural proposition but there's no need to bow to such arbitrary limitations. In this article you'll get step-by-step instructions on making a JDBC connection between the four most popular Java application servers and Microsoft SQL Server.
by Deepak Vohra
Feb 6, 2007
Page 1 of 5
QL Server may be the domain of Microsoft, but that doesn't mean the database engine doesn't know how to play nice with Java. In fact, SQL Server is used in J2EE applications more often than you probably think. To develop an application using SQL Server as the backend with a Java application server, you'll have to create a JDBC connection with SQL Server.
In this tutorial I will show you how to configure a JDBC connection with SQL Server from four of the most popular Java application servers: Oracle's JDeveloper (with embedded OC4J server), Red Hat's JBoss, IBM WebSphere, and BEA WebLogic. I'll show the procedure using both SQL Server 2000 and SQL Server 2005 Express.
Here's the basic procedure:
Add the SQL Server driver JAR files to the Classpath.
Start the SQL Server database.
Establish a connection with the database.
Sounds easy enough, right? But before you get started making those critical JDBC connections with the four Java application servers, you have to do a little unpacking and installation, including finding and installing the right driver, based on the version of SQL Server that you're using.
Set Up and Installation for SQL Server 2000
Install the SQL Server 2000 Driver for JDBC Service Pack 3. The SQL Server JDBC driver JAR files msbase.jar, mssqlserver.jar, and msutil.jar get installed in the C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib directory.
Download SQL Server 2000 Service Pack 3a. Launch the file sql2kdesksp3.exe to install the SQL Server 2000 desktop engine, specifying a folder into which to extract the files. A directory MSDE will be created within this folder.
Author's Note: SQL Server 2000 supports Windows 2000, Windows 98, Windows ME, Windows NT, Windows Server 2003, and Windows XP.
Next, open a command prompt window cd to the MSDE directory and run the command:
setup SAPWD=<sapwd> SECURITYMODE=SQL
<sapwd> is the password for the sa user. The command SECURITYMODE=SQL installs SQL Server 2000 in mixed mode authentication. With mixed mode authentication the SQL Server may be accessed using a Windows user account or a SQL Server account. In contrast, if Windows authentication is used, a user may connect only with a Windows user account. As the sa user is a SQL Server account, mixed mode authentication is required. This command will install SQL Server 2000.
In the Administrative Tools>Services console start the MSSQLSERVER service if not already started. Next, select TCP/IP as a protocol supported by the SQL Server. To do this, start the SQL Server Network Utility (C:/Program Files/Microsoft SQL Server/80/Tools/Binn/SVRNETCN.exe); select the TCP/IP protocol and transfer the selection to the Enabled protocols text area using the > button. Hit Apply and OK to approve the change an then restart the MSSQLSERVER service in Administrative Tools>Services.
Set Up and Installation for SQL Server 2005 Express
Download and install Windows Installer 3.0, if not already installed. On Windows Server 2003 SP1 and Windows XP SP2, Windows Installer 3.0 is pre-installed.
Author's Note: SQL Server 2005 Express supports Windows 2000 SP4, Windows Server 2003 SP1, and Windows XP SP2.
Download and install Microsoft .NET Framework 2.0. The .NET Framework 2.0 version is different for 32-bit and 64-bit platforms.
Download SQL Server 2005 JDBC Driver. Double-click on sqljdbc_1.0.809.102_enu and specify a folder into which to extract the application files. (The default folder is Microsoft SQL Server 2005 JDBC Driver.) Click on the Unzip button to extract the files.
Double-click on SQLEXPR.EXE file. Accept the license agreement and click on Next. In the Installing Prerequisites dialog, click on Install to install Microsoft SQL Native Client and Microsoft SQL Server 2005 Setup Support Files (see Figure 1).
In the Error and Usage Report Settings dialog, select the checkboxes if you wish to enable automatic error and usage reports.
In the Ready To Install dialog click on the Install button. This will configure the SQL Server components as shown in Figure 5. Click on Next and Finish to complete the installation.
The next step is to enable TCP/IP. Select Microsoft SQL Server 2005>Configuration Tools>SQL Server Configuration Manager and select the node SQL Server 2005 Network Configuration>Protocols for SQLEXPRESS. Right-click on the TCP/IP node and select Enable (see Figure 6). Restart the SQL Server (SQLEXPRESS) service by right-clicking on it in Services and select Restart.
Now you have to specify the port values. Select the node SQL Server 2005 Network Configuration>Protocols for SQLEXPRESS. Right-click on TCP/IP node and select Properties. Select the IP Addresses tab. In IP ALL, the TCP Dynamic Ports (see Figure 7) area specifies the port number on which SQL Server 2005 may be accessed, and also the port value for the connection URL to SQL Server 2005.
Choosing Your App Server
The next steps, while similar, depend on which application server you're running. The links below detail the procedure for the four most popular application servers: