' SA ' Logon Failure solution Encyclopedia

' sa ' Logon Failure solution Encyclopedia

The "user ' sa" Login failed when a Web project was recently deployed. Reason: The error bit rate hint was not associated with a trusted SQL server connection, but verified that the connection database string is correct and that the database settings on the database server are correct. An application deployed on the same server can access the database server, indicating that the network should also be free of problems. But the online solution is all about database settings and network setup issues, which makes me difficult. But an accidental hint (presumably) "has successfully established a connection with the server, but an error occurred during the logon process." (Provider: Shared Memory provider, error:0-the server actively rejects the request.) "Let me solve the problem."
Below I will solve these problems on the network from the database and network settings methods and my new findings summarized as follows: (I strongly asked for extra points, last night for several hours, today only published)
One: The network spreads the general solution method:
Because SQL Server is not integrated with Windows authentication, the solution is:
1 Open SQL Server Enterprise Manager.
2 Select "Edit SQL Server Registration Properties" on the server name and select "Use Windows Authentication" in the dialog box.
3 Give it a try, no. Looking at security settings
4 the same Right button, select Properties, and then open the Security tab.
5 in the tab, select Authentication as SQL Server and Windows, and other unchanged

You can use this link to quote this article: http://hanwei1983.bokee.com/tb.b?diaryId=16792232
In the process of using SQL Server, the most common problem users encounter is the failure of the connection. Generally speaking, there are two ways to connect to SQL Server, one is to take advantage of SQL Server's own client tools, such as Enterprise Manager, Query Analyzer, transaction Profiler, and so on.
The second is to use the user's own development of client programs, such as ASP scripts, VB programs, and so on, the client program is to use ODBC or OLE DB and other connected SQL Server. Next, I'll talk a little more about how to troubleshoot connection failures in these two ways of connecting.

One, client tool connection failed

When you connect to SQL Server with SQL Server's own client tools (for example, in Enterprise Manager), the most common errors are as follows:

1. SQL Server does not exist or access is denied

ConnectionOpen (Connect ())

2, user ' sa ' login failed. Reason: not associated with a trusted SQL Server connection

3, timeout has expired

Here's how to solve the three most common connection errors in turn.

The first error "SQL Server does not exist or access is denied" is usually the most complex, the cause of the error is more, there is more to be examined. Generally speaking, there are several possibilities:

1, the SQL Server name or IP address spelling error;

2, server-side network configuration is incorrect;

3, the client network configuration is incorrect.

To solve this problem, we generally follow the steps below to find out the cause of the error step by step.

First, check the network physical connection:

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/system32/drivers/etc).

2, add an IP address and server name of the corresponding records, such as:

172.168.10.24 MyServer

It can also be configured in SQL Server's client Network Utility, which is described later.

Second, 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 that the server side does not start SQL if the command returns an error message "Cannot open connection" Server service, either 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.

Next, we check the server-side network configuration on the server, check whether named Pipes are 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.

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

Point in the TCP/IP protocol, select "Properties", we can check the SQK Server service default port settings

Generally, we use SQL Server's default 1433 port. If "Hide Server" is selected, it means that the client cannot see the server by enumerating the servers, and it has a protective effect, but does not affect the connection.

After checking the server-side network configuration, we will then go to the client to check the client's network configuration. We can also take advantage of the use of SQL Server's own client network tools to check, but this time it is on the client to run the tool.

Click: Program-> Microsoft SQL Server-> Client Network usage tool

From here we can see what protocols are enabled by the client. In general, we also need to enable named Pipes and TCP/IP protocols.

Click the TCP/IP protocol and select Properties to check the client default connection port settings, which must be consistent with the server.

You can also configure aliases for the server by clicking the Alias tab. The server alias is the name used for the connection, and the server in the connection parameter is the true server name, which can be the same or different. We can use MyServer instead of the real server name sql2kcn-02 and use the network library Named pipes. The alias settings are similar to those used in the Hosts file.

Through the above several aspects of the examination, error 1 The cause of the occurrence can basically be excluded. Let's describe in detail how to troubleshoot error 2.

The error message shown in Figure 2 is often encountered when a user attempts to connect to SQL Server using SA in Query Analyzer, or when using SA to create a new SQL Server registration in Enterprise Manager.

This error occurs because SQL Server uses Windows only authentication, so users cannot connect using SQL Server's logon account, such as SA. The workaround looks like this:

1. Use Enterprise Manager on the server side and select "Use Windows Authentication" to connect to SQL Server;

2, expand the "SQL Server group", the right mouse click on 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 workaround, if using Windows Authentication to connect to SQL Server fails in step 1th, then we are faced with a dilemma: First, the server allows only Windows authentication, and second, it still fails to connect even with Windows authentication On the server.

This situation is vividly referred to as "locking yourself out of the door" because, in any way, the user cannot use the connection. In fact, we can change the authentication mode to SQL Server and Windows mixed authentication by modifying a registry key, as shown in the following steps:

1, click "Start"-"Run", input regedit, enter the Registry Editor;

2, expand the registry keys in turn, and browse to the following registry key:

[Hkey_local_machine/software/microsoft/mssqlserver/mssqlserver]

3, on the right side of the screen to find the name "LoginMode", double-click Edit Double byte value;

4, change the original value from 1 to 2, click "OK";

5, close Registry Editor;

6. Restart the SQL Server service.

At this point, users can successfully use SA to create new SQL Server registrations in Enterprise Manager, but still cannot use Windows Authentication mode to connect to SQL Server. This is because there are two default login accounts in SQL Server: Builtin/administrators and the machine name >/administrator are deleted. To recover both accounts, you can use the following methods:

1, open Enterprise Manager, expand the server group, and then expand the server;

2, expand Security, right-click Login, and click New Login;

3, in the "Name" box, input builtin/administrators;

4. In the Server Roles tab, select System Administrators;

5, click "OK" exit;

6, use the same method to add the machine name >/administrator login.

The following registry key

Hkey_local_machine/software/microsoft/mssqlserver/mssqlserver/loginmode

Value determines what authentication mode SQL Server will take. The value is 1, which means that the Windows Authentication mode is used; The value is 2, which means mixed mode (Windows authentication and SQL Server authentication).

After watching how to solve the first two errors, let's take a look at the third error.

If you encounter a third error, it is generally stated 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. This situation typically occurs when a user runs Enterprise Manager on the Internet to register another server that is also on the Internet, and is a slow connection, which may cause the above timeout error. In some cases, this error can also be caused by network problems in the LAN.

To resolve such an error, you can modify the client's connection timeout setting. By default, the timeout setting for registering another SQL Server through Enterprise Manager is 4 seconds, and the Query Analyzer is 15 seconds (which is why there is a greater likelihood of errors occurring in Enterprise Manager). The specific steps are:

1, in Enterprise Manager, select the "Tools" on the menu, and then select "Options";

2, in the pop-up SQL Server Enterprise Manager Properties window, click on the "Advanced" tab;

3. Enter a larger number, such as 20, in the box to the right of login timeout (seconds) under Connection settings.

It can also be set in the same location in the Query Analyzer.

Second, application connection failed

All three of the above error messages occur in SQL Server's own client tools, and similar error messages are encountered in the application, such as:

Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB] [ConnectionOpen (Connect ()).] Specified SQL Server not found.

Microsoft OLE DB Provider for SQL Server (0x80004005)
User ' sa ' login failed. Reason: not associated with a trusted SQL Server connection.

Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '.
[Microsoft] The [ODBC SQL Server Driver] timeout has expired.

First, let's take a look at the diagram below to see what is different about using ODBC and using OLE DB to connect to SQL Server.

In practice, the application creates and uses various ADO objects, and the ADO object framework invokes the OLE DB provider that is enjoyed.

To access a SQL Server database, OLE DB provides two different methods: The OLE DB Provider for SQL Server and the OLE DB Provider for ODBC. These two different methods correspond to two different connection strings, and the standard connection string is written as follows:

1. Use OLE DB providers for SQL Server:

Using SQL Server authentication:

oConn.Open "Provider=sqloledb" & _
"Data source=myservername;" & _
"Initial catalog=mydatabasename;" & _
"User id=myusername;" & _
"Password=mypassword"

To use Windows authentication (trusted connection):

oConn.Open "Provider=sqloledb" & _
"Data source=myservername;" & _
"Initial catalog=mydatabasename;" & _
"Integrated SECURITY=SSPI"

2. Use an OLE DB provider for ODBC (without using an ODBC data source):

Using SQL Server authentication:

oConn.Open "Driver={sql Server};" & _
"Server=myservername;" & _
"Database=mydatabasename;" & _
"Uid=myusername;" & _
"Pwd=mypassword"

To use Windows authentication (trusted connection):

oConn.Open "Driver={sql Server};" & _
"Server=myservername;" & _
"Database=mydatabasename;" & _
"Trusted_connection=yes"

3. Use an OLE DB provider for ODBC (using an ODBC data source):

oConn.Open "Dsn=mysystemdsn" & _
"Uid=myusername;" & _
"Pwd=mypassword"

If you encounter a failure of the connection, we simply follow the method shown in one, combining the connection string in the program to check, basically can be solved. In addition, there are several places to note:

1, when configuring an ODBC data source, click on the "Client" configuration option allows us to specify the network library, port number and other properties used by the connection.

2. If you encounter a connection timeout error, we can modify the timeout setting of the Connection object in the program, and then open the connection. For example:

<%set Conn = Server.CreateObject
("ADODB.") Connection ") dsntest=" Driver={sql
Server}; Server=servername; Uid=user;
Pwd=password;database=mydatabase "Conn.
Properties ("Connect Timeout") = 15 '
Conn.Open dsntest%> in seconds

3. If you encounter a query timeout error, we can modify the Recordset object time-out settings in the program, and then open the result set. For example:

Dim cn as New ADODB. ConnectionDim rs as ADODB. Recordset ...
CMD1 = Txtquery.textset rs = New ADODB. Recordsetrs.properties
("Command Time Out") = 300 '
Also in seconds, if set to 0 indicates unrestricted
Rs. Open CMD1, CNRS. MoveFirst ...

Third, summary

Summarizing this article is a common failure of connectivity for most users using SQL Server, focusing on how to troubleshoot and troubleshoot connection failures in both the SQL Server client tools and the applications developed by the user.
After reading this article, I'm sure all the people I see will have a more comprehensive and in-depth connection to how SQL Server works, how to authenticate, and how to develop applications.

All of the tests or samples in this article are passed on Windows Advanced server + SQL Server 2000 Enterprise Edition.

When you reverse-engineer with Visio today, you suddenly fail to link to the SQL Server database with the following error code

An error occurred while establishing a connection to the server. When you connect to SQL Server 2005, this failure may be caused by SQL Server not allowing remote connections under the default settings. (Provider:sql network interface, error:26-error locating the specified server/instance) (Microsoft SQL Server, Error:-1)


So I started checking.

First go to "Start"-> "Microsoft SQL Server 2005"-> Select "Configuration Tool"-> Open SQL Server Configuration Manager

SQL Server (SQLEXPRESS) was found to be functioning normally, and SQL Server Browser was not opened, and the service was banned after a closer look, so

On the command line, enter services.msc to locate the SQL Server Browser service and turn it on.

Back to SQL Server Configuration Manager, turn SQL Server Browser on, link again, succeed, run normally.



The following is the information collected online, but did not solve my personal problems, but write a good collection, in this share with you

An error occurred while establishing a connection to the server. When you connect to SQL Server 2005, this failure may be caused by SQL Server not allowing remote connections under the default settings. (provider: Named pipe provider, error:40-cannot open a connection to SQL Server)
Workaround:
The database you may be connecting to is SQL Server Edition Express, and from the Start menu, bring up the SQL Server perimeter application configurator in configuration tools. Then click the Services and connections perimeter configuration, and select the database instance (default is " SQLEXPRESS, select local and remote connections (with TCP/IP) in remote connection in database engine to open remote connections to SQL Server 2005 (not allowed by default).
Actually, you can.
Configuration file:
<connectionStrings>
<add name= "testdbconnectionstring" connectionstring= "Data source=./sqlexpress; Attachdbfilename=d:/wwwroot/ssztest1/app_data/testdb.mdf;integrated security=true; Connect timeout=30; User instance=true "providername=" System.Data.SqlClient "/>
</connectionStrings>
When the program is called:
SqlConnection sqlcon = new SqlConnection (system.configuration.configurationsettings.appsettings[" Testdbconnectionstring "]. ToString ());//testdbconnectionstring
or both directly generate connection:
SqlConnection sqlcon = new SqlConnection ("Data source=.//sqlexpress; Attachdbfilename=d://wwwroot//ssztest1//app_data//testdb.mdf;integrated security=true; Connect timeout=30; User instance=true ")
As long as the connection is successful, it should be no problem if you generate DataSource.

An error occurred while establishing a connection to the server. When you connect to SQL Server 2005, this failure may be caused by SQL Server not allowing remote connections under the default settings. (Provider:sql network interface, error:26-error locating the specified server/instance)
Preferred please confirm that SQL Server Exprss is installed on the local computer and that the SQL Server (SQLEXPRESS) service has started successfully. If the problem persists, try opening the SQL Server perimeter Application configurator, selecting the perimeter configuration for services and connections, and then configuring remote connections to use both TCP/IP and Named pipes

The user instance of SQL Server could not be generated because of an error in the process that started the user instance. The connection will be closed.
Modify the "User Instance=true" in the connection string to "user Instance=false".
Refer to the non-Admin user instance for related reference articles

The file ' X:/website/app_data/database.mdf ' cannot be used as a database '. Additional. A critical error has occurred in the current command. Any possible results should be discarded.
You need to assign Read permissions to the Users group for the X partition and for the subfolders and files in the X partition.

An attempt was x:/website/app_data/database.mdf to attach an automatically named database to a file, but failed. A database with the same name already exists, or the specified file cannot be opened or located in a UNC share directory.
The preferred option is to confirm that the Users group's read permissions have been assigned to the X partition and to the X partition's subfolders and files. If the problem persists, use SQL Server Management Studio to connect to the SQLExpress database instance and check to see if there is a database with the name "DB". If so, detach the database with the same name.

Unable to open user default database. Login failed.
User ' NT authority/network SERVICE ' login failed.
The read and write permissions of the database file Database.mdf are set to be available only network service. The workaround is to stop the SQL Server (SQLEXPRESS) service and then start the SQL Server (SQLEXPRESS) service again by integrating permissions from the parent.

I now want to learn asp.net, I read the code of others, found the following sentence
FormsAuthentication.RedirectFromLoginPage (UserId, false);
Can redirect to another page, but there's no designation to turn to that page.
I faint, originally in Web.config have configuration. [Code]<authentication mode= "Forms" >
<forms loginurl= "Login.aspx" ></forms>
</authentication>
<authorization>
<deny users= "?" />
</authorization> [/code] Oh, this thing is very cool, when using the form certification, if you do not login, no matter which page you open, will enter the designated login page, login completed, and can return to the page you specified ...


Authentication mode= "Forms" >
<forms loginurl= "Login.aspx" ></forms>
</authentication>
-----------------------------is authenticated above.
-----------------is authorized below.
<authorization>
<deny users= "?" />
</authorization>

REDIRECT the authenticated user back to the originally requested URL

Two: My solution, if the data settings and network settings are not a problem, you can connect strings from the Ado.net, set the above to start.
I have successfully established a connection from the server, but an error occurred during the logon process. (Provider: Shared Memory provider, error:0-the server actively rejects the request.) ) ”。 I found that the connection string was not modified in the last few days of the modification. And the same configuration file on its own machine deployment (Win 2003server) can work properly, and the server is Win2000. What causes the denial of access? You can only find a solution from the database resource, and finally I change the maximum pool of database data (max Pool Size) to 20 o'clock, Win2000 the application Web and access the database. (The default connection pool in Ado.net is 100).

My SQL Server browser is not open, look carefully, the service is banned, turn it on OK,