SQL Server 2000 Database Connection

SQL Server 2000 Database Connection

table of Contents

1 SQL Server 2000 service and instance ... 1

2 SQL Server 2000 database application structure ... 2

3 SQL Server 2000 Safety Management ... 2

3.1 Launch account for service components .... 2

3.2 Client Connection User Management ... 2

4 SQL Server 2000 Database Connection Common Faults and Handling Methods ... 3

4.1 SQL Server does not exist or visited ... 3

4.2 Users land in failure ... 6

4.3 timeout has expired ... 6

4.4 version compatibility problem ... 6

5 SQL Server 2000 Database Connection Fault and Ultimate Application Error Analysis ... 7

6 summary ... 10

1 SQL Server 2000 Service and Instance

SQL Server2000 service components are running in Windows Services. SQL Server2000 usually contains four Windows services (regardless of OLAP):

● MSSQLSERVER- provides general database features such as file management, query processing, data storage, etc.

● DTC (Distributed Transaction Coordinator) - Distributed Transaction Coordinator Supported two

Update of multiple servers to ensure the integrity of transactions

● SQLServerAgent- is responsible for SQL Server automation, if you need SQL Server at the specified

This service needs to be used between a stored procedure.

● Search Service-Full-text query service, responsible for the full-text search work.

In practice, we may install all service components, or only part of this, this group of SQL Server 2000 services is called SQL Server 2000 instances. Installing the SQL Server service component is to create a new SQL Server instance or increase or decrease the service components in the original instance.

SQL Server 2000 allows multiple instances to be created in the same operating system.

● If you only install only a SQL Server instance, you don't need to specify the instance name when SQL Server installation is required to automatically use the default name. Then the name of the computer in the Windows Domain is the name of the SQL Server instance; use the TCP / IP protocol to connect to the SQL Server instance, you can use the IP address to represent the SQL Server 2000 instance.

● If multiple SQL Server 2000 instances are installed in an operating system, you need to specify an instance name when SQL Server installation. The SQL Server 2000 instance can be used in the Windows Domain Forms; Use the TCP / IP protocol to connect to the SQL Server instance, you can use the "IP Address / Instance Name" to represent the SQL Server 2000 instance.

2 SQL Server 2000 database application structure

SQL Server 2000 database applications are generally a C / S structure. SQL Server 2000 instances are servers, providing engines, and user operating interface tools are client, both complement. The client's database connection is actually a connection to the client application to access the SQL Server 2000 instance.

3 SQL Server 2000 Safety Management

3.1 Launch Account of Service Components

Take the SQL Server server component as a Windows service program, mainly because the Windows service program can be used in the case where Windows users are not logged in. So since it is a service mode, the Windows boot account is required to set up a Windows system according to the requirements of the Windows system. The installer will require the user to set the startup account for the service, as follows:

If there is no correct configuration when installing, or later needs to modify, you can modify the corresponding configuration of the corresponding service via the Windows service manager.

3.2 Client Connection User Management

SQL Server users are divided into different user groups, and users of different user groups can have different database access operations permissions. SQL Server defaults to super-user group System Administrators for administrative authorization for SQL Server users, SQL Server Super User SA is the intrinsic user of this group, and is also the user's maximum user. This group has / administrator users and / administrators users by default. Users can create new user groups and users in SQL Server according to application requirements, and assign appropriate permissions.

SQL Server 2000 has two modes of security management of clients to clients:

● Windows user authentication mode

● Mixed mode (Windows User Authentication Mode and SQL Server Authentication)

The former is actually established a corresponding Windows system user in the user library of SQL Server. When the client initiates a connection, the client logs in to the client's legality to the client to check the permissions of the client connection user.

The latter also supports SQL Server custom user security policies, and the SQL Server username and login password must be provided when the client initiates a connection.

If you want to manage the client to manage the user to use SQL Server authentication, you must specify its login mode as the second mode when installing. The interface is as follows:

After the installation is complete, the system corresponds to the registry registration item hkey_local_machine / software / mssqlserver / mssqlserver / mssqlserver / loginmode determines what authentication mode will be taken by SQL Server:

● 1 means using "Windows Authentication" mode

● 2 Indicates the use of mixed mode (Windows Authentication and SQL Server Authentication)

If you need to modify the authentication mode in the later period, you can modify and save the corresponding value to restart the SQL Server service.

4 SQL Server 2000 Database Connection Common Fault and Processing Method

There are four most common errors in the SQL Server connection:

● SQL Server does not exist or accessed rejected

● User login failed, unable to connect to the server

● Connection timeout

● Version compatibility problem

4.1 SQL Server does not exist or access is rejected

The problem that SQL Server does not exist or access is the most common, and is usually the most complex, and there are many reasons for error, and there is more ways to check.

When this problem is solved, we must first check the network configuration of the server and client separately.

The client is connected to the server to support the communication library, and the SQL Server 2000 Communication Net-Library Network Protocol Communication Library supports a variety of network protocols, but it is generally selected to select TCP / IP or Name Pipeline Protocol Communications.

For simple networks, servers and clients use the same communication protocol, and for complex networks means that the server must support TCP / IP or named pipes on the server; the client usually chooses one of them.

Check if the network configuration of the server is enabled, whether the TCP / IP protocol is enabled. We can use the Server network using the SQL Server to check. Click: Program -> Microsoft SQL Server -> Server Network Using Tools, the screen you see after opening the tool is shown below:

From here we can see which protocols have been enabled. In general, we enable naming pipes and TCP / IP protocols.

Further check the settings for the default port of the SQK Server service, we can click the TCP / IP protocol, select "Properties", as shown below:

In general, we use SQL Server default 1433 ports. For the Hidden Server option, just restrict the client to enumerate this server by enumerating the server, just the protection function, does not affect the connection.

The client connection server is also implemented through the Net-library network protocol communication library, Microsoft's MDAC components provide NET-Library network protocol communication libraries, such as dbnmpntw.dll - Named pipeline protocols and DBMSSOCN.DLL - TCP / IP protocol The upper database accesses the basic components ADO, OLEDB, ODBC, DB-Library, etc. are all implemented on the server's data access communication.

After completing the MDAC installation (if the SQL Server 2000 server component or its client tool component, the Net-library network protocol communication library is built into the installation of SQL Server 2000, no need to install independently), through the system registry Registration item:

HKEY_LOCAL_MACHINE / SOFTWARE / Microsoft / MSSQLSERVER / Client / Connectto

View and modifications of the following configuration items can implement configurations for client network usage properties.

The configuration item under this configuration node actually on the server alias list can be connected. The aliasing of the server is the false name of the client application to connect to the server, and the server in the connection attribute parameter is a real server name, both of which may be the same or different. This is very useful for client application programming so that developers can define database connection parameters with fixed pseudo names (server alias) without having to rely on the name of the actual server, so that software has versatility. Specifically, only the service that needs to be accessed can be mapped to a real server. The definition rules for server alias are as follows:

= ,

Network Protocol Communication Library Defines the communication protocol used when connecting to the server, indicating attribute information of the aligned SQL Server service, such as server name, instance name, connection port, etc. if the alias is true.

Assuming that we have a SQL Server 2000 server SQL_SERVER, with an instance TestData, a IP address is 10.10.10.10, which defines the named pipe protocol and TCP / IP protocol on the server, and the default port of TCP / IP is 1433 ( See the contents of the server to the above-related content).

For named pipe protocols, the alias is configured as follows:

DataServer = dbnmpntw, SQL_SERVER / TESTDATA

or

DataServer = dbnmpntw, 10.10.10.10 / testdata

Note that the server name SQL_SERVER and server IP addresses here are equivalent. Sometimes the client and server are not in the same local area network. This time it is likely to use the server name directly to identify the server. At this time we can use the IP address to specify directly; this is related to the Host files that will be mentioned below. Place. For TCP / IP protocol, the alias is configured as follows:

DataServer = dbmsoCN, 10.10.10.10 / TestData, 1433

Note that the IP address 10.10.10.10 of the server is used here, and the specified 1433 port hold and the server's port settings are consistent.

After obtaining or correcting the network using the parameters, we will first ensure that the server and client connects from the physical network.

Use the ping command to check the physical network connection

PING or Ping

If ping fails, there is a problem with physical connection, this time you want to check hardware devices, such as NIC, HUB, router, etc. There is also a possibility that the client and the server have a firewall software, such as ISA Server. Firewall software may shield a response to ping, telnet, etc., so when checking the connection problem, we must first turn the firewall software to close, or open all closed ports.

If the Ping success and ping If the name parsing is problematic, you have to check if the DNS service is normal. Sometimes the client and server are not in the same local area network, which is likely to use the server name directly to identify the server. At this time we can use the HOSTS file to perform name resolution, the specific method is:

1. Use Notepad to open the HOSTS file (in general in C: / Winnt / System32 / Drivers / etc). 2, add a corresponding record of the IP address and server name, such as: 172.168.10.24 MyServer

You can also configure the client network using the parameters, specifically, see the alias configuration section of the named pipe protocol.

All in all, make sure the physical network connection.

Make sure that the physical network connection is in good condition, make sure that the server SQL Server 2000 instance is in a normal service state, and finally performs the corresponding check items for the use of the communication protocol library.

For clients using the TCP / IP Protocol Communications library, you need to use the Telnet command to check the SQL Server server working status:

Telnet 1433

If the command is executed successfully, you can see the screen after flashing, the cursor does not flash in the upper left corner, which means that the SQL Server server is working properly and is listening to the TCP / IP connection of the 1433 port; if the command returns "Unable to open the connection" error Information, the server-side does not enable the TCP / IP protocol, or the server side does not listen in the default port 1433 of SQL Server. In this way, the corresponding correction needs to be used for the server's network usage.

For clients using the TCP / IP protocol communication library, it is also necessary to ensure that the client has access to server resources, and can connect directly to SQL Server. The simplest test is to share access to the server. If the rejection requires the corresponding authorization to the client and client.

At this point, through the above aspects, we can basically solve most of the SQL Server does not exist or access the rejected issues.

4.2 User login failed User login failed problem is generally due to username or password is not legal. If you confirm that the username and password meet the connection requirements, the problem generally appears on the configuration issue of the SQL Server 2000 security management mode. You can refer to the configuration method in the SQL Server 2000 Safety Management Mode section.

4.3 timeout has expired

This error has occurred, in general, the client has found this server and can be connected, but the time is greater than the time allowed to cause an error.

To resolve such an error, you can modify the connection timeout setting of the client application process. For specific modifications, you can refer to the software usage instructions for the corresponding client application process or the contact software vendor resolved.

4.4 version compatibility issues

This problem has the probability of probability, but SQL Server itself has a great development, from SQL Server 6.5 to SQL Server 2000 or even SQL Server 2003, there is no minor change. If there is a problem with the interface version compatibility of the client and server, the SQL Server database connection is faulty. This needs to be reminded that you don't have to neglect the basic problem of version compatibility.

5 SQL Server 2000 Database Connection Fault and Ultimate Application Error Analysis

In general, the reporting database connection failure will not be a warning of SQL Server 2000 database services, usually a fault reported on the client application process, and there is a problem with fault location, because the trigger of the fault is not necessarily SQL Server itself. The setting problem, the upper application is also possible to fail.

In fact, this discernible work is still easy. That is to throw an application process and build a client connection through the test function in the client side through the ODBC configuration. If the test passes the pass, the problem is in the application process, otherwise it is. For details, please refer to the following interface:

At this time, if there is a problem with the setting of the SQL Server itself, the following fault alarm box is popped up:

Otherwise, the SQL Server connection is successful, there is no problem with its own settings.

6 summary

SQL Server 2000 database connection fault issues is a very complex issue that is associated with many nodes and knowledge points. Not one or a few pieces of tricks can be solved. During the processing, requirement must have the ability to maintain calm analysis and meticulous lookup. First confirm the category of the fault, the program is planned one by one, check and confirm the node, until the chasing roots, ultimately confirmed and solved the problem.

In addition, in addition to the concepts and content of some instances and services, SQL Server 2000 is different from SQL Server 6.5, most of this article also applies to SQL Server 6.5.