การเชื่อมต่อ JSP sqlServer ด้วย JDBC บน netbeans (3/3) Part III: Troubleshooting

There are 3 parts:

1. Part I : Create a connection
   This part which you’re reading shows about how to establish a connection between NetBeans and SQL Server. In this example, I use SQL Server 2000 SP4 and NetBeans IDE 5.5
2. Part II : Perform SQL Operations
   This part show how to perform some basic operations from NetBeans with SQL Server. For instance, send querys as SELECT, INSERT, UPDATE to the database.
3. Part III: Troubleshooting
   The last part is about problems and how to fix them.

Troubleshooting index

1. Missing the JDBC library
2. Mistype the connection string
3. The TCP/IP connection to the host has failed
4. Login failed for user ‘sa’
5. This driver is not configured for integrated authentication

1. Missing the JDBC library

Problem
You received this error message while compile code in part I.

Class Not Found Exception: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver


Cause
This problem may have many causes as the following:

1. The JDBC library file is not loaded properly. You may not have add the library file “sqljdbc.jar” to the project.
2. You haven’t import the needed library to the project

Solution

1. Add the appropriate library to the project. Refer to part I: create a connection.
   
2. You need to add following code at the top of thesource code:
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   
   Or you can
   import java.sql.*;

Back to top

Problem
You received this error message while compile code in part I.

SQL Exception: java.sql.SQLException: No suitable driver found for jdbc:sqlserver:bkksql2005:1433;databaseName=AdventureWorks;user=sa;password=password;

Cause
You may have typed your connection string incorrectly so the JDBC driver couldn’t understand.

Solution
Recheck your connection string format. The format for SQL authentication mode should be
“jdbc:sqlserver://serverName:portNumber;databaseName=DatabaseName;user=UserName;password=Password;” where

• serverName is the name of the SQL Server
• (Optional) portNumber is the TCP port of SQL Server. Default port is 1433.
• (Optional) DatabaseName is the database name on SQL Server.
• UserName and Password are username and password for login to SQL Server. This user must be SQL Server authentication mode.

The format for Windows authentication mode should be
“jdbc:sqlserver://serverName:portNumber;databaseName=DatabaseName;integratedSecurity=true;” where

• serverName is the name of the SQL Server
• (Optional) portNumber is the TCP port of SQL Server. Default port is 1433.
• (Optional) DatabaseName is the database name on SQL Server.

Note: Database name is an optional. For more complex connection string, refer to MSDN – Building the Connection URL at Microsoft.

In this example, I mistype “jdbc:sqlserver://…” to “jdbc:sqlserver:…“.

Back to top

Problem
You received this error message while compile code in part I.

SQL Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect

Cause
You try to connect to a SQL Server which has not SQL Server service running or the service refuse to accept remote connections.

Solution

1. Recheck your connection string that you have type server name and port correctly or not.
2. Check that SQL Server is running.
3. If SQL Server is on remote host, try to check that you have allow remote connections on the server. To enable remote connections on SQL Server, try visit Enable remote connection to SQL Server 2005 Express.
4. Check firewall which may block the connection from client to server.

Back to top

Problem
You received this error message while compile code in part I.

SQL Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘sa’.

Cause
This error message states clearly that you have provide wrong username or password that connect to SQL Server.

Solution
Recheck username and password again.

Back to top

Problem
You need to use Windows authentication mode by using ‘integratedSecurity=true’ instead of specify username and password in the connection string but receive this error message:

SQL Exception: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
Feb 8, 2008 11:28:55 AM com.microsoft.sqlserver.jdbc.AuthenticationJNI 
WARNING: Failed to load the sqljdbc_auth.dll

Cause
You need to add ‘sqljdbc_auth.dll’ to the system path. This file can be found in Microsoft SQL Server JDBC Driver. It resides in sqljdbc_1.1enuauthx86 depends on your JDBC driver and system platform (x86 or x64).

Solution
Copy ‘sqljdbc_auth.dll’ to C:WindowsSystem32
or
You can set the java.library.path to the directory where ‘sqljdbc_auth.dll’ is.

Note: If the path contains any spaces or special characters, you have to use double quotes “” around the value (ex. “your directory”).