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:

1. Add the SQL Server driver JAR files to the Classpath.
2. Start the SQL Server database.
3. 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

1. 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 FilesMicrosoft SQL Server 2000 Driver for JDBClib directory.
2. 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.

• Next, open a command prompt window cd to the MSDE directory and run the command:

  setup SAPWD= SECURITYMODE=SQL

  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

1. 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.

• 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 20005 Express Edition and launch the file SQLEXPR.EXE to install the application.
• 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).

? Figure 1. Installing prerequisites.

? Figure 2. System configuration check.

? Figure 3. Selecting features to install.

• Click the Next button to launch the SQL Server Installation Wizard. The wizard will walk you through a System Configuration Check (see Figure 2) and ask you to supply registration information.
• Next, in the Feature Selection dialog, the Database Services node is selected by default as shown in Figure 3. Click on Next.
• In the Authentication Mode dialog, select Mixed Mode, as shown in Figure 4, specify a sa login password, and click Next.

Figure 4. Selecting authentication mode.

?

Figure 5. Configuring components.

• 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.

Figure 6. Enabling the TCP/IP protocol.

?

Figure 7. Specifying the TCP/IP port.

• 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:

• Configuring JDeveloper with SQL Server
• Configuring JBoss with SQL Server
• Configuring WebSphere with SQL Server
• Configuring WebLogic with SQL Server

Configuring JDeveloper with SQL Server

1. As I mentioned in the previous section, the first step is to add the JAR file for the JDBC driver class to the classpath by creating a new library for the SQL Server JAR files. Open JDeveloper and Select Tools>Default Project Properties. In the Default Project Properties dialog select the Libraries node and click the Add Library button (see Figure 8).

? Figure 8. Adding a library.

? Figure 9. Adding a user library.

? Figure 10. Creating a library.

1. In the Add Library dialog, select the User node, and click on the New button (see Figure 9).
2. In the Create Library dialog specify a Library Name, such as “SQLServer.” Select the Class Path node and click the Add Entry button. For SQL Server 2000, add the msbase.jar, mssqlserver.jar, and msutil.jar JAR files to this dialog. For SQL Server 2005 Express, add sqljdbc.jar (see Figure 10) and click OK. Click OK several more times until you?ve exited the Libraries dialog (see Figure 11).
   

   ? Figure 11. A new library.

   ? Figure 12. Specifying a connection type.

   ? Figure 13. Registering a JDBC driver.

3. Next, configure a JDBC connection with the SQL Server database. In the Connections Navigator right-click on the Database node and select New Database Connection to start the Create Database Connection wizard.
4. In the Type dialog specify a Connection Name and select ‘Third Party JDBC Driver’ in the Connection Type field (see Figure 12). Click on the Next button.
5. In the Authentication dialog specify sa as the Username and the Password as the password used to install the SQL Server database.
6. In the Connection dialog, locate the Driver Class field and specify the driver class, com.microsoft.jdbc.sqlserver.SQLServerDriver (2000), or com.microsoft.sqlserver.jdbc.SQLServerDriver (2005 Express). Click on New to add a driver class. In the Register JDBC Driver dialog, specify driver class in Driver Class field (see Figure 13). In the Library field select the SQLServer library that you configured in the last section (I called mine “SQLServer”). Click OK to add it.
7. In the URL field specify the connection URL as shown in Figure 14. This portion of the process is the same regardless of which application server you are using and the specific URL and port values required are explained in the sidebar “Obtaining the Port Values.”)

8. ? Figure 14. Specifying connection parameters.

   ? Figure 15. Testing the connection.

   ? Figure 16. A new database connection.

9. Now you can test the connection using the Test Connection button (see Figure 15). If you’ve done everything correctly, a JDBC connection is established with the database. Click on the Finish button.

You?ve added a node for this connection to the Database node in the Connections Navigator as shown in Figure 16. This connection is also available as jdbc/SQLServerConnectionDSdatasource in a JSP or a servlet. To obtain a connection from the datasource in a JSP, add to the web.xml of the JSP application:

  jdbc/SQLServerConnectionDS  javax.sql.DataSource  Container

You can obtain a JDBC connection in a JSP or a servlet using this code:

InitialContext initialContext = new InitialContext(); javax.sql.DataSource ds = (javax.sql.DataSource) initialContext.lookup("java:comp/env/jdbc/SQLServerConnectionDS");   java.sql.Connection conn = ds.getConnection();

Configuring JBoss with SQL Server

1. Assuming you’ve already downloaded and unzipped JBoss 4.0, the first step is to add the SQL Server JDBC driver JAR files to the classpath of the server. For SQL Server 2000, copy msbase.jar, mssqlserver.jar, and msutil.jar to the /server/default/lib directory. For SQL Server 2005 Express, copy the sqljdbc.jar file to the lib directory. is the directory in which the JBoss server is installed.
2. Copy the MS SQL Server datasource file docsexamplesjcamssql-ds.xml to the serverdefaultdeploy directory.
3. In the mssql-ds.xml datasource file specify the JDBC driver class in the element as com.microsoft.jdbc.sqlserver.SQLServerDriver for SQL Server 2000, and com.microsoft.sqlserver.jdbc.SQLServerDriver for SQL Server 2005 Express.
4. Specify the connection URL in the element using the URL specified in the sidebar “Obtaining the Port Values.”
5. Specify the username as sa and password as the SQL Server password. In the field specify a JNDI name for the datasource.
6. Next, modify the serverdefaultconflogin-config.xml configuration file by adding the following element to login-config.xml:

                    sa             jboss.jca:service=LocalTxCM,name=MSSQLDS        

   This will configure JBoss to work with SQL Server.

   If you want to develop an entity EJB, you also have to modify the serverdefaultconfstandardjbosscmp-jdbc.xml configuration file. To configure the standardjbosscmp-jdbc.xml with the SQL Server database modify the and elements as shown:

          java:/ MSSQLDS        MS SQLSERVER2000

Configuring WebSphere with SQL Server

1. Once installed you’ll need to launch WebSphere Admin server and the administration console as the JDBC connection is configured in the WebSphere administration console.
2. Access the administration console with the URL http://localhost:9060/ibm/console. Specify user ID as websphere and click on Log In. Select the Resources>JDBC Providers node in the administration console. Select the server scope as Server=server1 and click on the New button to configure a new JDBC provider (see Figure 17).

? Figure 17. Creating a new JDBC provider.

? Figure 18. Specifying the database type.

? Figure 19. Settings for a new JDBC provider.

1. In the Configuration dialog select SQL Server as the database type as shown in Figure 18. Select the provider type Websphere embedded ConnectJDBC driver for MS SQL Server. Next select the Implementation type Connection pool data source and click Next.
2. In the row for the Implementation class name com.ibm.websphere.jdbcx.sqlserver.SQLServerDataSource gets specified, as shown in Figure 19; this is the SQL Server 2000 and SQL Server 2005 data source class. Click on Finish.

   This will configure the JDBC Provider and add it to the JDBC Providers table as shown in Figure 20. Save the settings in the Master configuration with the Save link.

? Figure 20. A new JDBC provider.

? Figure 21. Adding a J2C Authentication data entry.

? Figure 22. Adding a new datasource.

1. The next step is to configure a JDBC datasource to use with SQL Server. This requires a J2EE Connection (J2C) Authentication data entry, which specifies the login credentials for SQL Server. Select the Security>Secure administration, applications, and Infrastructure node in the administration console. Select the Authentication>JASS Configuration>J2C Authentication data link (see Figure 21), and click on the New button to add a data entry.
2. On the next page specify an alias for the data entry and provide the userid (sa) and password to login to the SQL Server database. Click on the Apply button. Click on the OK button to create the entry then be sure to hit the Save link.
3. Now that you’ve created a JDBC Provider, you need create a WebSphere data source in order to enable it for use. Select Resources>JDBC Providers and select the link for the JDBC Provider you created above. Select the Additional Properties>Data Sources link and in the table of data sources click on the New button to configure a new data source as shown in Figure 22.
4. In the window that follows specify a data source name and a JNDI name for the data source. In the Component-managed authentication alias field select the J2C authentication data entry you already configured. Click on Next.
5. Finally, in the Create a data source frame, specify the database name as NA, the Server name as localhost and the Port number as 1433 or the port number for SQL Server 2005 (see the sidebar “Obtaining the Port Values“) and click Next to add the data source (see Figure 23).

? Figure 23. Specifying the server name and port number.

? Figure 24. Setting the database name.

? Figure 25. Testing the datasource connection.

1. Save the data source configuration to the Master configuration with the Save link. Select the datasource link in the Data sources page. Previously, the database name was specified as NA; now, set the Database Name field to empty and click on Apply (see Figure 24).

   To test the data source select its checkbox and click on the Test connection button as shown in Figure 25.

   If the JDBC connection is properly established you will see the following message:

   Test connection for data source WebSphere embedded ConnectJDBC for SQL Server DataSource on server server1
    at node d207-6-39-2Node01 was successful.

Configuring WebLogic with SQL Server

1. Create a WebLogic Server domain using the Configuration Wizard. The default domain that gets created is base_domain.
2. Start the WebLogic Admin Server for the base_domain by double-clicking on C:BEAuser_projectsdomainsase_domainstartWebLogic script.
3. Access the WebLogic server administration console with the URL http://localhost:7001/consoleor by clicking Administration Console link in the Welcome page. Specify the username as weblogic and Password as weblogic to login to the Administration Console.
4. To configure a JDBC Connection pool, select the Services>JDBC>Data Sources link (see Figure 26). Click on the Lock & Edit button to add a datasource.
   

   ? Figure 26. JDBC data sources.

   ? Figure 27. Adding a new data source.

   ? Figure 28. Specifying the data source properties.

5. On the Data Sources page, click the New button to configure a new data source (see Figure 27).
6. In the dialog that follows, specify a datasource name and a JNDI name. In the Database Type, select MS SQL Server (see Figure 28). You’ll also be asked to specify the database driver. BEA provides its own MS SQL Server drivers, which are pre-configured to work with WebLogic server; therefore, you don’t need to add a JDBC driver JAR file to the WebLogic server classpath. However, if you prefer to use Microsoft’s SQL Server driver JAR files, then you should select them at this time. Click on the Next button.
7. At this point the Transaction Options page is displayed. If you want to use global transactions select an XA-driver, which supports global transactions by default, in the previous dialog. Global transactions are transactions that span over multiple databases. If a non-XA driver is selected, you must configure the data source to support global transactions by selecting the Supports Global Transactions checkbox and a transaction protocol. A discussion on transactions is outside the scope of this article. Please refer the WebLogic Server JDBC documentation. Click on the Next button.
8. In the Connection Properties dialog specify the database name where prompted. If you plan to use the default database name, specify NA in the Database Name field. Specify the host name as localhost (see Figure 29). Specify the ports as explained in the sidebar “Obtaining the Port Values.” The username is sa and the password is whatever you use to login to the database. Click on the Next button.

9. Figure 29. Specifying connection properties.

   ?

   Figure 30. Testing the connection.

10. On the Test Database Connection page the driver class name is specified as weblogic.jdbc.sqlserver.SQLServerDriver. The URL, port values, and username/password are the same as in the earlier section. The URL is specified as jdbc:bea:sqlserver://localhost:1155. However, if the default database is used, delete property databaseName=NA.

    Now you can click on the Test Configuration button to test the JDBC data source. If the JDBC connection is properly established, a message “Connection test succeeded” is displayed (see Figure 30). Click Next and in the Select Targets dialog, select the AdminServer (see Figure 31), and click on the Finish button.

    Figure 31. Selecting the target server.

    ?

    Figure 32. A new data source.

    This creates a data source for SQL Server and adds it to the Data Sources page as shown in Figure 32. Click on the Activate Changes button to activate the datasource configuration.

Get Started
If you want an open source application server, use the JBoss application server. JBoss is also the easiest to configure with SQL Server. Otherwise, go with whichever application server fits best in your environment. By configuring an application server with SQL Server, J2EE applications with a JDBC component may be developed on your SQL Server back end.