The ultimate solution to JSP connection failure to SQL Server

This is not something that can be solved simply by installing an SP patch. I recently used sqlserver as a database. I didn't expect that there will be so many problems just by connecting. below is my solution, for your reference:

First, check the error message. If the error message cannot establish a socket connection, install the patch, go to the MS website to install the SP3 or SP4 patch. The latest SP4 patch is over 60 m. You don't need to find the SP3 patch. SP4 includes it. Then download the JDBC driver for SP3 (because there is no for SP4 ...), Put the driver in classpath. If Tomcat is used, put the driver in the common/lib directory under the tomcat installation directory. If you do not put the driver, you still cannot connect to sqlserver, even if you set classpath.

Try to write a test JSP. Can you connect to it:

<%
Resultset rs = NULL;
Statement ST = NULL;
String drivername = "com. Microsoft. JDBC. sqlserver. sqlserverdriver ";
String dburl = "JDBC: Microsoft: sqlserver: // localhost: 1433; databasename = Replace with your database name ";
String username = "sa ";
String userpwd = "Replace with your password ";
Connection dbconn;
Try {
Class. forname (drivername );
Out. println ("driver loaded <br> ");
Dbconn = drivermanager. getconnection (dburl, username, userpwd );
Out. println ("connection successful! ");
St = dbconn. createstatement ();
Rs = st.exe cutequery ("replace select * From with a table in your database ");
While (Rs. Next ())
{
Out. println ("<br> query successful! ");
Out. println (Rs. getstring ("1 "));
}
}
Catch (exception e ){
E. printstacktrace ();
}
%>

First, write some records in your table and start Tomcat to run this program. If the program is connected at this time, you will not have to read it later, if you do not continue reading, the error message above indicates that your table is incorrect, but it is clearly the simplest SELECT statement, and the table name is not wrong, this is the focus of this study, because I have hardly mentioned this problem on the Internet. After my research, the solution is as follows, because the SP patch enhances the security of sqlserver, that is, the SA user with the highest permissions cannot access your database because he has no permissions (even if you use the database created by the SA user ), this is a silly task of Ms. Therefore, we need to add a user to the database and grant the user the permission to use the new user to connect to the database, the operation method is to start the Enterprise Manager, right-click the database you want to add and choose "New"> "Database User". Select "new" in the login name to add a new user, however, it is best to use an existing user. The new user may not be successful. for authentication, You must select sqlserver. for authentication, select the database name you want to connect to. Click OK, sqlserver will also ask you to change the password once, so that you can add an attribute button and click Permissions to grant select, update, and other permissions to the tables you want to access, OK.
Next, modify your code and set both the user name and password to your new user. Let's see if it can be connected.