=================== Next, we have to go to the server to check the network configuration on the server side, check whether the named pipe is enabled, whether the TCP/IP protocol is enabled, etc. ===============
You can use the server network usage tool that comes with SQL Server to check.
Click: Program -- Microsoft SQL Server -- Server Network Usage Tool
After opening the tool, you can see in "General" which protocols the server has enabled.
Generally speaking, we enable named pipes and the TCP/IP protocol.
Click on the TCP/IP protocol and select "Properties". We can check the settings of the default port of the SQK Server service.
Generally speaking, we use the default 1433 port of SQL Server. If "Hide Server" is selected, it means that the client cannot see the server by enumerating the server, which plays a protection role, but does not affect the connection.
================ Next we will go to the client to check the client's network configuration ==============
We can also use the client network usage tool provided by SQL Server to check.
The difference is that this time we run this tool on the client side.
Click: Program -- Microsoft SQL Server -- Client Network Usage Tool
After opening the tool, in the "General" item, you can see which protocols the client has enabled.
Generally speaking, we also need to enable named pipelines and TCP/IP protocols.
Click the TCP/IP protocol and select "Properties" to check the settings of the client's default connection port, which must be consistent with the server.
Click the "Alias" tab and you can also configure alias for the server. The alias for the server is the name used to connect to.
The server in the connection parameter is the real server name, and the two can be the same or different. The settings of the alias are similar to using the HOSTS file.
Through the above inspections, the first error can be basically ruled out.
2. "Unable to connect to the server, user xxx login failed"
The reason for this error is that SQL Server uses the "Windows only" authentication method.
Therefore, users cannot use SQL Server's login account (such as sa) to connect. The solution is as follows:
1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server
2. Expand "SQL Server Group", right-click the name of the SQL Server server, select "Properties", and then select the "Security" tab
3. Under "Authentication", select "SQL Server and Windows".
4. Restart the SQL Server service.
In the above solution, if you use "Use Windows Authentication" to connect to SQL Server in step 1, fail,
Then solve this problem by modifying the registry:
1. Click "Start"-"Run", enter regedit, and enter to enter the registry editor
2. Expand the registry keys in turn and browse to the following registry keys:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer]
3. Find the name "LoginMode" on the right side of the screen, and double-click to edit the double-byte value
4. Change the original value from 1 to 2 and click "OK"
5. Close the Registry Editor
6. Restart the SQL Server service.
At this time, the user can successfully use sa to create a new SQL Server registration in the enterprise manager.
However, it is still impossible to use Windows authentication mode to connect to SQL Server.
This is because there are two default login accounts in SQL Server:
BUILTIN\Administrators
<Machine Name>\Administrator was deleted.
To restore these two accounts, you can use the following methods:
1. Open Enterprise Manager, expand Server Group, and then expand Server
2. Expand "Security", right-click "Login", and then click "New Login"
3. In the "Name" box, enter BUILTIN\Administrators
4. In the "Server Roles" tab, select "System Administrators"
5. Click "OK" to exit
6. Use the same method to add <machine name>\Administrator to log in.
illustrate:
The following registry keys:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\LoginMode
The value determines what authentication mode SQL Server will adopt.
1. Indicates the use of "Windows Authentication" mode
2. Indicates the use of hybrid mode (Windows Authentication and SQL Server Authentication).
Previous page123Next pageRead the full text