SQL Server 2000 database connection

server| Data | database | database connection
SQL Server 2000 database connection







Directory







1 SQL Server 2000 services and instances ... 1



2 SQL Server 2000 database application Architecture ... 2



3 SQL Server 2000 security Management ... 2



3.1 Startup account for service components .... 2



3.2 Client Connection User Management ... 2



4 SQL Server 2000 database connection common failures and handling methods ... 3



4.1 SQL Server does not exist or access is denied ... 3



4.2 User Login Failed ... 6



4.3 Timeout has expired ... 6



Version 4.4 compatibility issue ... 6



5 SQL Server 2000 database connectivity failure and upper application error discrimination ... 7



6 Summary ... 10






1 SQL Server 2000 services and instances
The service components of SQL Server2000 are run as Windows Services (Windows service). SQL Server2000 typically contains four Windows services (regardless of OLAP):







Mssqlserver-provides general database functions, such as file management, query processing, data storage, etc.







DTC (Distributed Transaction Coordinator)-Distributed Transaction Coordinator, support spanning two



Update operations on one or more servers to ensure transactional integrity







Sqlserveragent-is responsible for automating SQL Server, if SQL Server is required to specify



You need to use this service to execute a stored procedure between







Search service-Full-text query service, responsible for Full-text search work.







In practice, we may install all the service components, or only a subset of them, and this set of SQL Server 2000 services is called the SQL Server 2000 instance. Installing the SQL Server service component is to create a new instance of SQL Server or to add or subtract service components from an existing instance.



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







If you install only one instance of SQL Server, you do not need to specify the instance name at SQL Server installation, and the default name is used automatically. The name of the computer in the Windows domain is the name of the SQL Server instance, and you can use an IP address to represent a SQL Server 2000 instance when you connect to an instance of SQL Server using the TCP/IP protocol.







If you have more than one instance of SQL Server 2000 installed in an operating system, you need to specify the instance name when you install SQL Server. You can use the form "computer name \ Instance name" to flag SQL Server 2000 instances in a Windows domain, and you can use IP address \ Instance name to represent a SQL Server 2000 instance when you connect to an instance of SQL Server using the TCP/IP protocol.


2 SQL Server 2000 database application Architecture
SQL Server 2000 database application is generally c/s structure, SQL Server 2000 instance as a server, provide engine, user interface tool is client, the two complement each other. The client's database connection to the server is actually the connection that the client application accesses the SQL Server 2000 instance.


3 startup account for SQL Server 2000 Security Management 3.1 service components
The SQL Server server component is used as a Windows service program, primarily because the Windows service program is available to Windows users without logging on. Now that the service is running, the Windows startup account needs to be set up for the service as required by the Windows system.



Setup will require users to set up a startup account for the service, the interface is as follows:









If the installation is not configured properly, or if you need to modify it later, you can modify the corresponding configuration of the corresponding service through the Windows Service Manager.
3.2 Client Connection User Management
Users of SQL Server are divided into different user groups, and users of different user groups can have different permissions for database access operations. SQL Server provides the Superuser group system administrators,sql Server Superuser SA, which is used by default to administer authorization for SQL Server users, is an intrinsic user of this group and the user with the most permissions for database access operations. This group, in the case of normal installation, also defaults to the < local machine name >\administrator user and < Local machine name >\administrators user. Users can create new user groups and users in SQL Server based on application requirements and assign them the appropriate permissions.

SQL Server 2000 has two modes of security management for client-connected users:

Windows user authentication Mode

Mixed mode (Windows user authentication mode and SQL Server Authentication)

The former actually establishes a corresponding Windows system user in the user library of SQL Server, checking the permissions of the client connection user directly as the client initiates the connection with the legality of the user who is logged on to the Windows system.

The latter also supports the SQL Server custom user security policy, which must provide the SQL Server username and logon password when the client initiates the connection.

If you want to use SQL Server Authentication for client connection user management, you must specify that the logon mode is the second mode when you install. The interface is as follows:





After the installation is complete, the system corresponding registry registry key HKEY_LOCAL_MACHINE Oftware\microsoft \mssqlserver\mssqlserver\loginmode value determines the SQL What authentication mode server will take:



1 means using Windows authentication mode



2 means using mixed mode (Windows authentication and SQL Server authentication)



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


4 SQL Server 2000 database connection common failures and handling methods
There are four of the most common errors in SQL Server connections:

SQL Server does not exist or access is denied



User Login failed, unable to connect to server



Connection Timeout



Version compatibility issues


4.1 SQL Server does not exist or access is denied
SQL Server does not exist or access denied issues are the most common, often the most complex, the cause of the error occurred more, the need to check more aspects.



To solve this problem, we first check the network configuration of the server and the client separately.



Client-server connectivity requires communication library support, SQL Server 2000 Communication Library net-library Network Protocol Communication Library supports a variety of network protocols, but generally selects TCP/IP or named pipe protocol communication Libraries.

For a simple network, the server and the client use the same communication protocol, whereas for a complex network it means that TCP/IP or named pipes must be supported on the server at the same time, and the client usually chooses one of them.

Check that the server-side network configuration has named Pipes enabled, whether the TCP/IP protocol is enabled, and so on. We can use SQL Server's own server network using tools to check.



Click: Program-> Microsoft SQL Server-> Server network Usage tool, after opening the tool to see the screen as shown in the following image:







From here we can see what protocols are enabled by the server. Generally speaking, we enable named Pipes and TCP/IP protocols.



Further check the SQK Server service default port settings, we can point to the TCP/IP protocol, select "Properties", as shown in the following figure:







Generally, we use SQL Server's default 1433 port. For the Hide server option, only the client is restricted from enumerating the servers to enumerate the servers, only the protection function, without affecting the connection.



The Client Connection server is also implemented through the Net-library Network Protocol Communication Library, and Microsoft's MDAC component provides net-library network protocol communication libraries, such as dbnmpntw.dll--named pipe protocol and DBMSSOCN.DLL--TCP/IP Protocol , its upper-level database Access Basic components ADO, OLE DB, ODBC, db-library and so on on this basis to achieve the data access communication with the server.

After the installation of MDAC is complete (if the SQL Server 2000 Server component or its Client tool component is installed, the Net-library Network protocol Communication Library is built into the installation of SQL Server 2000 without having to provide an independent installation), by registering entries on the system registry:





HKEY_LOCAL_MACHINE Oftware \microsoft\mssqlserver\client\connectto







The following configuration items are viewed and modified to implement the configuration of the properties used for the client network.



The configuration item under this configuration node is actually a list of server aliases that the client can connect to. The alias of the server is the pseudo name of the server the client application is using to connect to, and the server in the connection property parameter is the true server name, which can be the same or different. This is useful for client application programming, so that developers can use a fixed pseudo name (server alias) without relying on the name of the onsite actual server to define database connection parameters, making the software versatile. A specific release requires that the services that need to be accessed can be mapped to a real server by definition of the above server alias on the client. The server alias definition rules are as follows:







< server alias >=< network protocol Communication Library >,< connection Properties >







The Network protocol Communication Library defines the communication protocol used when connecting to the server, and the connection properties indicate the property information for the End-to-end SQL Server service that the alias is actually connected to, such as the server name, instance name, connection port, and so on.



Suppose we have a SQL Server 2000 Server sql_server that has an instance testdata,ip address of 10.10.10.10, defines its alias as DataServer, and supports both the named pipe protocol and the TCP/IP protocol on the server, and tcp/ The default port for IP is 1433 (server detail configuration method see above).



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 machine name Sql_server and the server IP address 10.10.10.10 are equivalent. Sometimes the client and the server are not in the same LAN, it is very likely that the server name could not be used directly to identify the server, at which point we can use the IP address to specify directly; This is similar to the host file method mentioned below.







For the TCP/IP protocol, the alias is configured as follows:







DataServer = dbmssocn,10.10.10.10\testdata,1433



Note that the server's IP address is 10.10.10.10, and the specified 1433 port remains consistent with the server's port settings.







After obtaining or properly configuring the network usage parameters for both the server and the client, we will first ensure that the server and client are connected from the physical network.



To check physical network connections by using the ping command







Ping server IP address or ping server name







If the ping server IP address failed, indicating that there is a problem with physical connectivity, this time to check hardware devices, such as network cards, HUB, routers and so on. Another possibility is that the firewall software is installed between the client and the server, such as ISA server. Firewall software may block the response to Ping, Telnet, and so on, so when checking connectivity problems, we have to temporarily shut down the firewall software or open all the blocked ports.



If the ping server IP address is successful and ping the server name fails, there is a problem with name resolution, so check to see if the DNS service is normal. Sometimes the client and the server are not in the same LAN, it is very likely that the server name can not be used directly to identify the server, at this time we may use the Hosts file for name resolution, the specific method is:



1, use Notepad to open the Hosts file (usually located in C:\WINNT ystem32\drivers\etc). 2, add an IP address and server name of the corresponding records, such as: 172.168.10.24 myserver



You can also configure the client network using parameters, as described earlier in the alias configuration section using the Named pipe protocol.



In summary, make sure that the physical network is connected.



Make sure that the physical network connection is intact, and also make sure that the server SQL Server 2000 instance is in a normal service state, and then check the project again for the difference between using the Communication Protocol library.



For clients using the TCP/IP protocol communication Library, you need to use the Telnet command to check the working status of the SQL Server server:







Telnet Server IP Address > 1433







If the command succeeds, you can see the cursor flashing in the upper-left corner after the screen flashes, indicating that the SQL Server server is working properly and listening for TCP/IP connections on port 1433, and if the command returns an error message "Cannot open connection", the TCP/IP protocol is not enabled on the server side. Or the server side is not listening on SQL Server's default port 1433. This requires that the server's network usage configuration be modified accordingly.



For clients that use the TCP/IP protocol communication Library, it is also important to ensure that the client already has access to the server resources and can directly connect to SQL Server. The simplest test is shared access to the server, and if it is denied, the server and the client are authorized accordingly.



At this point, with the above checks, we can basically solve the problem that most SQL Server does not exist or access is denied.
4.2 User Login Failed
The problem with user logon failures is generally due to an illegal user name or password. If you confirm that the user name and password meet the connection requirements, the problem is generally in the configuration of SQL Server 2000 security management mode. You can refer to the configuration method in the SQL Server 2000 Security management Mode section above for modifications and settings.
4.3 Timeout has expired
This error, in general, indicates that the client has found the server and can connect, but that an error occurs because the connection time is greater than the allowed time.

To resolve such errors, you can modify the connection timeout settings for the client application process. The specific modification method, may refer to the corresponding client application process software use instruction or the contact software supplier solves.
4.4 Version compatibility issues
The probability of this problem occurring is low, but SQL Server itself has developed significantly, from SQL Server 6.5 to SQL Server 2000 or even SQL Server 2003. If there is a problem with the interface version compatibility between the client and the server, it can also cause a SQL Server database connection failure. This should be a reminder to avoid ignoring the basic issue of version compatibility.








5 SQL Server 2000 database connection failure and upper application error differentiation
In general, reporting a database connection failure is not an alarm for the SQL Server 2000 database service, usually a fault reported by the client application process, where there is an discrimination problem with fault location, because failure is not necessarily a setup problem for SQL Server itself. There may also be a problem with the upper application.

In fact, this kind of discrimination work is very easy to carry out. That is to put aside the application process, on the client through the ODBC configuration of the test function to build a new client connection, if the test pass the problem in the application process, or vice versa. Concrete operation method, please refer to the following interface operation:

















At this point, if there is a setup problem with SQL Server itself, a failure warning box similar to the following will pop up:



Otherwise, the SQL Server connection succeeds and there is no setup problem of its own.
6 Summary
The problem of SQL Server 2000 database connectivity failure is a very complex issue that is associated with many nodes and knowledge points. Not one or a few one-sided trick can be solved. In the process of processing, the requirements must have the ability to maintain a calm analysis and meticulous search. First identify the breakdown of the category, the system has a planned level by layer by node for verification and validation, until traced back to the final confirmation and resolution of the problem.

In addition, the vast majority of this article applies to SQL Server 6.5 as well, in addition to the concepts and content of some instances and services, where SQL Server 2000 is different from SQL Server 6.5.