Common Errors in SQLServer connection

Source: Internet
Author: User
Common Errors in SQL Server connection: 1. SQL Server does not exist or access is denied. This is the most complicated issue. There are many causes of errors and there are many aspects to check. generally, there are several possibilities: 1. Incorrect SQLServer name or IP address spelling 2. Incorrect server network configuration 3. Incorrect Client Network Configuration

Common Errors in SQL Server connection: 1. "SQL Server does not exist or access is denied" is the most complicated. There are many causes of errors and there are many issues to check. generally, there are several possibilities: 1. the SQL Server name or IP address is misspelled. 2. The Server network configuration is incorrect. 3. The client network configuration is incorrect.

SQL ServerConnectionInCommonError:

1. "SQL Server does not exist or access is denied"

This is the most complex,ErrorThere are many causes, and there are also many issues to check.

Generally, there are several possibilities:

1. incorrect spelling of the SQL Server name or IP address

2. The server network configuration is incorrect.

3. The client network configuration is incorrect.

To solve this problem, we generally need to follow the steps below to find the cause step by step.Error.

============== First, check the network physicalConnection==================

Ping <服务器ip地址 服务器名称>

If ping <服务器ip地址> Unsuccessful, physical ConnectionIf there is a problem, Check hardware devices, such as NICs, hubs, and routers.

Another possibility is that a firewall software is installed between the client and the Server. For example, the ISA Server. firewall software may block the ping and telnet responses.

Therefore ConnectionWhen the problem arises, we must temporarily disable the firewall software or open all closed ports.

If ping <服务器ip地址> Ping <服务器名称> Failed

It indicates that there is a problem with name resolution. At this time, check whether the DNS service is normal.

Sometimes the client and server are not in the same LAN. At this time, the server name may not be used to identify the server. At this time, we can use the HOSTS file for name resolution,

The specific method is:

1. Use notepad to open the HOSTS file (usually in C: \ WINNT \ system32 \ drivers \ etc ).

Add a record corresponding to the IP address and server name, for example:

172.1610.24 myserver

2. You can also configure it in the SQL Server Client Network utility, which will be described in detail later.

============ Second, use the telnet command to check the running status of the SQL Server ==============

Telnet <服务器ip地址> 1433

If the command is successfully executed, you can see that the cursor does not stop flashing in the upper left corner after the screen flashes. This indicates that the SQL Server is working normally and is listening to the TCP/IP of port 1433. Connection

If the command returns "cannot be opened ", Connection" ErrorThe SQL Server service is not started on the Server,

The TCP/IP protocol may not be enabled on the Server, or the Server does not listen on the default port 1433 of SQL Server.

Then, we need to check the network configuration on the server and whether the named pipe is enabled. whether or not the TCP/IP protocol is enabled ================

SQL Server's built-in Server network tools can be used for inspection.

Click: program -- Microsoft SQL Server -- Server network usage Tool

After you open the tool, you can see which protocols are enabled on the server in "General.

In general, we enable Named Pipes and TCP/IP protocols.

Click "properties" in the TCP/IP protocol to check the default port settings of the SQK Server service.

In general, we use the default port 1433 of SQL Server. If "Hide Server" is selected, it means that the client cannot see this Server by enumerating the Server, which protects the Server but does not affect it. Connection.

============== Next we will go to the client to check the network configuration of the client ================

We can also use the client network tools provided by SQL Server to perform checks,

The difference is that this tool is run on the client this time.

Click: program -- Microsoft SQL Server -- client network usage Tool

After the tool is enabled, You can see which protocols are enabled on the client in the "General" item.

In general, we also need to enable the named pipe and TCP/IP protocol.

Click TCP/IP protocol and select "properties" to check the default value of the client. ConnectionThe port must be the same as the server.

Click the "alias" tab and configure the alias for the server. The server alias is used ConnectionName,

   ConnectionThe server in the parameter is the real server name. The two can be the same or different. The alias settings are similar to the HOSTS file.

Through the above checks, we can basically exclude the first Error.

-----------------------------------------------------------------------------

2. "No ConnectionFailed to log on to the server as xxx user"

The ErrorThe cause is that SQL Server uses the "Windows only" authentication method,

Therefore, you cannot use the Logon account of SQL Server (such as sa ). Connection. The solution is as follows:

1. Use the enterprise manager on the server side and select "use Windows Authentication" ConnectionSQL Server

2. Expand "SQL Server group", right-click the name of the SQL 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 preceding solution, if you use "use Windows Authentication" in step 1" ConnectionSQL Server failed,

Modify the Registry to solve this problem:

1. Click "start"-"run", enter regedit, and press enter to enter the Registry Editor.

2. Expand the registry key in sequence and browse to the following registry key:

[HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer]

3. Find the name "LoginMode" on the right 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. Disable Registry Editor

6. Restart the SQL Server service.

In this case, you can successfully use sa to create an SQL Server registration in the Enterprise Manager,

However, Windows Authentication mode is still unavailable. ConnectionSQL Server.

This is because there are two default logon accounts in SQL Server:

BUILTIN \ Administrators

   <机器名> \ Administrator deleted.

To restore these two accounts, you can use the following methods:

1. Open the Enterprise Manager, expand the server group, and then expand the server

2. Expand security, right-click logon, and click New logon"

3. In the Name box, enter BUILTIN \ Administrators

4. On the "server role" tab, select "System Administrators"

5. Click "OK" to exit

6. Use the same method to add <机器名> \ Administrator login.

Note:

The following registry key:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ LoginMode

The value determines the Authentication mode that SQL Server will adopt.

1. indicates that the "Windows Authentication" mode is used.

2. indicates that the hybrid mode is used (Windows Authentication and SQL Server Authentication ).

-----------------------------------------------------------------------------

3. Prompt ConnectionTimeout

If the third ErrorIn general, it indicates that the client has found this server and can perform Connection,

However, because ConnectionAn error occurs because the time is later than the allowed time.

This usually occurs when you run the enterprise manager on the Internet to register another server that is also on the Internet,



And is slow ConnectionThe above timeout may occur. ErrorIn some cases, due to lan problems Error.

To solve this problem Error, You can modify ConnectionTimeout settings.

By default, the timeout setting for registering another SQL Server through the Enterprise Manager is 4 seconds,

The query analyzer is 15 seconds (which is why it occurs in the Enterprise Manager ). Error).

The procedure is as follows:

Settings in Enterprise Manager:

1. In Enterprise Manager, select "Tools" from the menu, and then select "options"

2. In the displayed "SQL Server Enterprise Manager properties" window, click the "advanced" tab.

3" ConnectionEnter a large number in the right box of "Logon timeout (seconds)", for example, 20.

Query settings in Analyzer:

Tool -- option -- Connection-- Set logon timeout to a large number

   ConnectionThe timeout value is changed to 0.

1. ping is guaranteed first.

2. No error will be reported when telnet ip 1433 is written in dos

3. Use an ip address to connect to the Enterprise Manager:

Enterprise Manager --> right-click SQlserver group --> Create sqlserver registration --> next --> Write Remote Instance name (IP address, machine name) --> next --> select Sqlserver login --> next --> write login name and password (sa, pass) --> next --> complete

4. If not:

Sqlserver server --> Start Menu --> SQLserver --> server network utility --> enable WinSock Proxy --> proxy address: (sqlserver Server IP address) --> proxy port --> 1433 --> OK

5. If not:

Sqlserver client --> Start Menu --> SQLserver --> client network utility --> alias --> Add --> write alias such as "" --> "network library" select TCP/IP --> The server name is written to the remote ip address or Instance name --> OK

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.