Sửa lỗi auth in main server

This article helps you resolve various connectivity issues khổng lồ SQL hệ thống.

Bạn đang xem: Sửa lỗi auth in main server


Note

For a guided walk through experience of this article, see Solving Connectivity errors to lớn Squốc lộ Server.


Original product version: sydneyowenson.com SQL ServerOriginal KB number: 4009936

Prerequisite

To effectively use this troubleshooter, you may want to gather the following information.

The complete text of the error message along with the error codes and whether the error is intermittent (happens only sometimes) or consistent (happens all the time).

Errorlogs from SQL Server from which you can note the following:

Fully qualified domain name (FQDN) of the SQL Server machine or in case of clustered installations, the FQDN virtual name. If you are using a named instance, note down the instance name.


Note

You can search for "Server name is" string to lớn get this info in the errorlog.


Network libraries & ports the Squốc lộ instance is listening on. Example messages:

Named pipes: Server local connection provider is ready to accept connection on <\.pipesqlquery >. TCP/IP & port number: Server is listening on < ::1 1433>.

Application & System event logs from Squốc lộ Server và Client systems.

If the connections are failing from an application, the connection string from the phầm mềm. These are typically found in Web.config files for ASP.NET applications.

Checklist

Ensure Squốc lộ server is started và you see the following message in the Squốc lộ Server ErrorLog:

SQL Server is now ready for client connections. This is an informational message; no user action is required.

Verify basic connectivity over IP.. address & check for any abnormalities: ping -a , ping -a . If you notice any issues, work with your network administrator khổng lồ fix the same.

Cheông chồng if Squốc lộ is listening on appropriate protocols by reviewing the ErrorLog.

Check if you are able to lớn connect to Squốc lộ Server from other client systems & different user logins. If you are able to, then the issue could be specific to the client or login that is experiencing the issue. Cheông chồng the Windows event logs on problematic client for additional pointers. Also kiểm tra if network drivers are up lớn date.

If you are experiencing login failures, ensure the user has a login at the hệ thống cấp độ & appropriate permissions to lớn connect to the database that the user is trying khổng lồ connect to lớn.

A network-related or instance-specific error occurred while establishing a connection khổng lồ SQL Server

For more information about the relevant error, see the following Verify connection errors section.

Verify connection errors

The A network-related or instance-specific error occurred while establishing a connection to lớn SQL Server error represents one or more of the following error messages:

A network-related or instance-specific error occurred while establishing a connection khổng lồ Squốc lộ Server. The VPS was not found or was not accessible. Verify that the instance name is correct and that Squốc lộ Server is configured to allow remote connections.

provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance SpecifiedSQL Server Native sầu Client Data Link Error

: Login timeout expired: A network-related or instance-specific error has occurred while establishing a connection to Squốc lộ Server. Server is not found or not accessible. Chechồng if instance name is correct & if Squốc lộ Server is configured to allow remote connections. For more information see Squốc lộ Server Books Online.: Squốc lộ Server Network Interfaces: Error Locating Server/Instance Specified .A network-related or instance-specific error occurred while establishing a connection khổng lồ Squốc lộ Server. The hệ thống was not found or was not accessible. Verify that the instance name is correct và that Squốc lộ Server is configured khổng lồ allow remote connections.

provider: TCPhường Provider, error: 0A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed lớn respond.sydneyowenson.com SQL Server, Error: 10060A network-related or instance-specific error occurred while establishing a connection lớn SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that Squốc lộ Server is configured to lớn allow remote connections.

provider: Named Pipes Provider, error:40 - Could not open a connection khổng lồ SQL Serversydneyowenson.com Squốc lộ Server, Error:53The network path was not found TCP Provider: No connection could be made because the target machine actively refused it. Login timeout expired A network-related or instance-specific error has occurred while establishing a connection to lớn SQL Server. Server is not found or not accessible. Check if instance name is correct and if Squốc lộ Server is configured to allow remote connections. For more information see SQL Server Books Online.You can start troubleshooting from this section: Comtháng causes of various connection issues.

Comtháng causes of various connection issues

Go through each of the causes that is applicable khổng lồ your instance below and for each of the applicable causes, try the corresponding resolutions.

Cause 1: Incorrect VPS name being specified in the connection string or in the server name dialog box

To confirm:

Ensure the hệ thống name you are specifying the application matches what you have sầu in the error log.

Cause 2: Incorrect alias on the client machine

Aliases are typically used in environments when you need to lớn connect lớn Squốc lộ Server with an alternate name or when there are name resolution issues in the network. An incorrect alias on the client machine can cause the connections from your applications lớn go khổng lồ the wrong server resulting in failure.

Things lớn try:

Open SQL Server Client Network Utility by typing cliconfg.exe in your Run comm&.

Cheông xã if there are any aliases defined for the VPS you are trying khổng lồ connect lớn.

If present, vị the following:

Click on Edit & rename the Server alias. (for example, if your VPS name is MySQL, rename it as MySQL_test) and retry the connection. If the connection works, it is an indication that you had an incorrect alias, probably from an old configuration that is no longer needed. If you continue to experience the error, rename the alias bachồng khổng lồ its original name & proceed khổng lồ the next step.

Check the Connection parameters for the alias & ensure they are correct. Some of the common scenargame ios that can cause problems with connectivity are:

Incorrect IP. address for the Server name parameter. Ensure this IPhường address matches the entry in the SQL ErrorLog file.

Incorrect Server name in the Server name parameter. For example though your Server alias points the correct server name, if the Server name parameter has incorrect value, connections will fail.

If you are using a named pipes alias, ensure the pipe name has correct format.

For connecting to lớn mặc định instance named Mydefaultinstance, the pipe name should be \MydefaultinstancepipesqlqueryFor connecting to lớn a named instance MySQLNamed, the pipe name should be \MySQLpipeMSSQL$NamedsqlqueryCause 3(Default instance): Firewall(s) between the client & VPS blocking the port Squốc lộ Server instance is listening on

Default instance: A mặc định instance typically runs on port 1433. Some installations also use a non-standard port (other than 1433) for running SQL instances. The firewall may be blocking either of them.

Things khổng lồ try:

Try appending the port number of Squốc lộ Server khổng lồ the server name using the format , portnumber và see if that works. For example, if your Squốc lộ instance name is MySQLDefaultinstance & it is running on port 2000, specify the server name as MySQLServer, 2000 and see if that works. If it does work it is an indication the firewall is blocking the port.

Cause 4(Named instance): SQL Browser is not started

Client applications connecting to a named instance of Squốc lộ Server use the Squốc lộ Browser service on the system where SQL is running khổng lồ enumerate the port that Squốc lộ is listening on. If the browser service is not started, the connections would fail.

Things lớn try:

On the system running your SQL Server instance, either use the SQL Server Configuration manager or the Services applet in Control panel và start the Squốc lộ Browser Service if it is not already started. For more information, see How to: Start & Stop the Squốc lộ Server Browser Service

Cause 5(Named instance): UDP Port 1434 used by SQL browser is blocked on the network

If your SQL instance is a named instance, it may have sầu either been configured lớn use dynamic ports or a static port. In either case, the underlying network libraries query SQL Browser service running on your Squốc lộ Server machine via UDPhường. port 1434 to lớn enumerate the port number for the named instance. If a firewall between the client và the server blocks this UDP port, the client library cannot determine the port (a requirement for connection) và the connection fails.

To Confirm:

Method 1:

cảnh báo down the port your SQL instance is listening on from the SQL Server Errorlog.Try connecting to the named instance using the port number appended lớn the hệ thống name using the format , portnumber và see if that works. If it does work it is an indication the firewall is blocking the port UDP. port 1434. For example, if your SQL instance name is MySQLNamedinstance & it is running on port 3000, specify the VPS name as MySQLNamedinstance, 3000 & see if that works. If it does work it could either mean that UDP. port 1434 is blocked or the static port is blocked or both. To confirm if it is the UDPhường port or the static port using Portqry from Method 2 below.

Method 2:

Things to try:

First determine if the SQL Server instance is listening on dynamic vs static port và use the procedure that is relevant to lớn your scenario. How to tell if Squốc lộ is listening on Dynamic vs Static ports, move sầu to Tell if SQL is listening on Dynamic vs Static ports section.

If this does not resolve your issue, move to lớn Still having problems section.

Configuring firewalls

If you are using a custom firewall, work with your network administrator khổng lồ open up the necessary ports.

Below you can find some quiông chồng screenshots showing the required configuration of a Windows firewall for successful connections khổng lồ a default instance & a named instance.

A mặc định instance of SQL Server listening on the mặc định port 1433 on Windows 2012 R2 server. In this scenario, you need lớn ensure an exception is added khổng lồ TCPhường port 1433 in the Windows firewall.

xuất hiện Windows firewall on the system hosting SQL hệ thống mặc định instance & clichồng New Rule under Inbound Rules.

Select the Port option & cliông xã Next.

In the next screen:

Select TCP as the protocol.

Select Specific local ports & specify the value as 1433 & cliông chồng Next.

Xem thêm: Tải Game Đá Banh Hack - Tải Game Dream League Soccer Mod Apk 6

*

In the next screen, select Allow the connection & click Next.

In the next screen, select the option that best suits your environment and clichồng Next.

In the next screen, give sầu the name khổng lồ your rule & provide a clear description for future reference & clichồng Finish.

*

Once done, you should see that rule is created & is enabled by mặc định.

*

Adding an exception for UDP.. port 1434 to enable connections to a named instance of Squốc lộ server:

mở cửa Windows firewall on the system hosting SQL server mặc định instance & click New Rule under Inbound Rules.

Select the Port option và cliông chồng Next.

In the next screen:

Select UDP as the protocol.

Select Specific local ports and specify the value as 1434 và cliông xã Next.

*

In the next screen, select Allow the connection & cliông chồng Next.

In the next screen, select the option that best suits your environment and cliông xã Next.

In the next screen, give the name lớn your rule & provide a clear mô tả tìm kiếm for future reference và click Finish.

*

Once done, you should see that rule is created và is enabled by default.

*

If this does not resolve your issue, move sầu lớn Still having problems section.

Using PortqryUI tool with SQL Server

Download location: PortqryUI

Launch PortqryUI tool on your client machine. (the machine where you are having connection issues, for web apps, this could be the IIS server)Specify server name of the Squốc lộ Server instance or the SQL vitutal server name in the Destination IP or FQDN lớn query.Select Query predefined service & select Squốc lộ Service in the dropdown list.Click Query và examine the output và use the following table for additional pointers.Type of instanceđầu ra from PortqryPotential causes for connection issuesWhat khổng lồ try?
Default instanceTCPhường port 1433 (ms-sql-s service): NOT LISTENINGIndicates one of the following: SQL is not started. TCP/IPhường is not enabled on SQL hệ thống protocol danh sách. Squốc lộ is listening on a non-default port. (check errorlog) Firewall between the client and the hệ thống is blocking the port.Ensure SQL is started. Cheông chồng SQL error log for the port number & use that in your connection strings using the format , portnumber. Work with your network/windows administrator khổng lồ ensure the TCPhường. port 1433 is not blocked by a firewall on the network or by the Windows firewall on the SQL Server system. Note If you would lượt thích lớn resolve firewall issue, move lớn Configuring firewalls section.
Default instanceTCPhường. port 1433 (ms-sql-s service): LISTENING Client library is able to lớn connect fine to the Squốc lộ server machine but something else in the application layer could be causing the problem. Cheông xã if server name is correctly specified in the connection string. If the connection string is using the port number, it is correctly specified in the connection string. Any old aliases defined on the box.
Named instanceUDPhường. port 1434 (ms-sql-m service): FILTEREDIndicates one of the following: SQL named instance is not started. SQL browser not started on the system hosting your SQL instance. UDP port 1434 is blocked by a firewall on the Squốc lộ VPS or on the network between the client & the hệ thống. Service is started. Squốc lộ browser service is started.Work with your network/windows administrator to ensure the UDP.. port 1434 is not blocked by a firewall on the network or by the Windows firewall on the SQL Server system. Note If you would like lớn resolve sầu firewall issue, move sầu khổng lồ Configuring firewalls section.
Named instanceUDPhường port 1434 is LISTENINGClient library is able khổng lồ connect fine khổng lồ the Squốc lộ hệ thống machine but something else in the application layer could be causing the problem. Server name is correctly specified in the connection string. Port number is incorrectly specified in the connection string. Any old aliases defined on the box.

Examples outputs:

Default instance on default port: Working scenario

*

Default instance on default port: Non-working scenario

*

Named instance: Working scenario: (Instance name: SQL năm trước, hostname: SQLCONNVM)

*

Named instance: Non - Working scenario: (Instance name: SQL 2014, hostname: SQLCONNVM)

*

For more information, move to Configuring firewalls section.

If this does not resolve sầu your issue, move khổng lồ Still having problems section.

Tell if Squốc lộ is listening on Dynamic vs Static ports

In SQL Server Configuration Manager, in the console pane, exp& Squốc lộ Server Network Configuration, exp& Protocols for , & then double-cliông chồng TCP/IP.

In the TCP/IPhường. Properties dialog box, on the IPhường. Addresses tab, several IPhường. addresses appear in the format IP1, IP2, up to IPAll. One of these is for the IP address of the loopbaông xã adapter, 127.0.0.1. Additional IPhường. addresses appear for each IPhường. Address on the computer. (You will probably see both IP. version 4 and IP version 6 addresses.) Right-click each address, and then clichồng Properties to identify the IPhường address that you want to configure.

If the TCP. Dynamic Ports dialog box contains 0, it indicates that the Database Engine is listening on dynamic ports. If it contains a specific number, it means the database instance is listening on a static port.

*

For more information, see Configure a Server to lớn Listen on a Specific TCP Port.

If this does not resolve sầu your issue, move sầu to Still having problems section.

No connection could be made because the target machine actively refused it

For more information about the no connection error, move sầu to lớn The full error message section.

The full error message

You may get an error that is similar lớn the following:

TCP. Provider: No connection could be made because the target machine actively refused it.Login timeout expired.A network-related or instance-specific error has occurred while establishing a connection to lớn Squốc lộ Server. Server is not found or not accessible. Chechồng if instance name is correct and if SQL Server is configured khổng lồ allow remote connections. For more information see SQL Server Books Online.You can start troubleshooting from this section: Common causes of various connection issues.

SQL Server does not exist or access denied

You can start troubleshooting from this section: Common causes of various connection issues.

PivotTable Operation Failed: We cannot locate a server to load the workbook Data Model

You can start troubleshooting from this section: Comtháng causes of various connection issues.

Cannot Generate SSPI context error message

Security Support Provider Interface (SSPI) is a mix of Windows APIs that allows for delegation và mutual authentication over any generic data transport layer, such as TCP/IP.. sockets. Therefore, SSPI allows for a computer that is running a Windows operating system to lớn securely delegate a user security token from one computer to another over any transport layer that can transmit raw bytes of data.The Cannot generate SSPI context error is generated when SSPI uses Kerberos authentication lớn delegate over TCP/IP & Kerberos authentication cannot complete the necessary operations to successfully delegate the user security token lớn the destination computer that is running SQL Server.

For additional information as to lớn why the Kerberos operations cannot be completed, move sầu lớn Troubleshooting authentication failures due lớn Kerberos issues section lớn review and implement the steps.

Troubleshooting authentication failures due lớn Kerberos issues

Kerberos authentication failures can happen due khổng lồ a variety of reasons. The major causes & corresponding resolutions are highlighted below:

Issue typeSuggested resolutions
SPN Issues: Missing SPNs: SPN is not registered in the Active directory. Incorrect SPN entries: SPN exists, but the port number is incorrect or it exists on a different trương mục other than the SQL Service account.Duplicate SPNs: The same SPN exists on multiple accounts in the active directory.Cheông chồng Using Kerberos Configuration manager khổng lồ diagnose & fix SPN and delegation issues section khổng lồ diagnose & resolve sầu SPN issues.Note For in-depth understanding of SPNs, Kerberos, and other related concepts Reviews the information in the following KB article: How to troubleshoot the "Cannot generate SSPI context" error message
SQL Service accounts not trusted for delegation. If you are using Local System account, the middle server should be trusted for delegation in the active directory.UseKerberos configuration manager "s delegation tab lớn confirm và work with your Active directory administrator khổng lồ enable delegation for the account. Chechồng Using Kerberos Configuration manager to diagnose and fix SPN & delegation issues for details in the following paragraph.
Incorrect name resolution: Your server name may be resolving lớn a different IP address than that is registered by your network"s DNS hệ thống.ping -a (use -4 and -6 for IPv4 & IPv6 specifically) ping -a nslookup (type your local và remote machine name & IP.. address multiple times)Look for any discrepancies và mismatches on the returned results. The correctness of DNS configuration on the network is vital to lớn Squốc lộ connection. Wrong DNS entry could cause of all sorts of connectivity issue later.
Firewalls or other network devices preventing connections from the client lớn the domain name controller: SPNs are stored in active sầu directory & if the clients are unable to communicate with the AD, the connection cannot proceed further.Cheông chồng the following link for additional information: