[IT] Connecting JSP with SQL Server 2008 R2 Express (JDBC)

Here are the problems that I got when I tried to connect JSP (JavaServer Pages) with SQL Server 2008 R2 Express through JDBC (Java Database Connectivity):

1. a. java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
   b. java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
2. Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
3. Login failed for user ...

The software and file that I used:

1. Tomcat 7.0 (Apache Software Foundation)
2. Java Runtime Environment (JRE) 1.7
3. JDBC Driver 4.0 for SQL Server (sqljdbc.jar and sqljdbc4.jar)

I will explain the problems in the 'REFER TO PROBLEMS' section in the steps I'll explain.
Pre-requirement:

1. Tomcat
2. Java Runtime Environment (JRE)
3. Microsoft SQL Server
4. Any web-editing softwares

THE STEPS

1. Download the Microsoft JDBC Driver for SQL Server to connect any Java programming-based applications with SQL Server. We can download it officially here (if the link is dead, search "Download Microsoft JDBC Driver for SQL Server").
2. Extract sqljdbc_4.0.2206.100_enu.exe or sqljdbc_4.0.2206.100_enu.tar.gz. We'll get two core files, sqljdbc.jar and sqljdbc4.jar, that we need in folder sqljdbc_4.0\enu.
3. Make sure that Tomcat's service status is 'stopped'.
4. Copy both sqljdbc.jar and sqljdbc4.jar to Tomcat's lib folder (commonly: C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib) 
5. Copy either sqljdbc.jar or sqljdbc4.jar to JRE's ext folder (commonly: C:\Program Files\Java\jre7\lib\ext). If we use JRE 1.5 below, copy just sqljdbc.jar. If we use JRE 1.6 above, copy just sqljdbc4.jar.
6. Start the Tomcat's service. We can officially connect to SQL Server 2008.

REFER TO PROBLEMS

• java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
• java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
  
  These problems could happen because:
  First, the Tomcat fails to 'recognize' the needed classes that are 'included' in sqljdbc.jar or sqljdbc4.jar. These problems can be solved if we accomplish step 3 and 4 above.
  
  Second, the wrong usage of Class.forName(). Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); is for SQL Server 2000 and Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); is for SQL Server 2005 and above. Don't interchange them, pay attention to their differences. We must pay attention to the differences of the connection string in DriverManager.getConnection().
  
  Before we start to focus on the last problem, please type this following syntax and save it as JSP (.jsp) and display the result to browser.
  
  <%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
  <%
  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
  Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;");
  Statement st = con.createStatement();
  %>
• Java Runtime Environment (JRE) version 1.7 is not supported by this driver. Use the sqljdbc4.jar class library, which provides support for JDBC 4.0.
  
  This error can be solved through step 5.
• Login failed for user ...
  
  If you encounter the same problem, do these following steps:

1. Open SQL Server Configuration Manager.
2. Make these following changes:

3. Open Server SQL Management Studio and connect to database engine.
4. Make new login.

5. Type the login name.

6. Select SQL Server authentication (you can type password--and remember it).
7. Select Server Roles.

8. Click OK.
9. Set the server properties:

10. Change server authentication:

11. Click OK.

Copy and paste these codes:

<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<%
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;user=evo");
Statement st = con.createStatement();
%> 

 Note: user=evo, evo is the login name you made.

If the result on the browser is blank page without error, you made it!