SQL Hints Login failed for user ' sa ' wrong solution

Phenomenon:---------------------------------------------------

1, connect SQL Server database hint: Login failed for user ' sa '. Reason:not associated with a trusted SQL Server connection error

Question:-------------------------------------------------

Preliminary analysis should be related to user rights, consult the Help file, Microsoft Official reasons:

This problem occurs when SQL Server 2000 security authentication is set to Windows only and one of the following conditions is present:

You are attempting to connect to a SQL Server 2000 database by using SQL Server login.

You are attempting to connect to a SQL Server 2000 Desktop Engine (also known as MSDE 2000) database that is installed on a Microsoft Windows 98-based computer by using a trusted SQL Server connection.

You are attempting to connect to a SQL Server 2000 database from a Windows account that does not have sufficient permissions to connect to the server.

Solution:--------------------------------------------

The Official solution:

SQL Server Login

Connect to SQL Server using a valid Windows login. If you must continue to log on with SQL Server, you can change the security authentication mode in SQL Server 2000 to SQL Server and Windows. To do this, follow these steps:

Start Enterprise Manager.

Expand Microsoft SQL Servers, and then expand SQL Server Group.

Right-click the server you want to change to SQL Server and Windows authentication, and then click Properties.

In the SQL Server Properties dialog box, click the Security tab, click SQL Server and Windows, and then click OK.

When you are prompted to restart the SQL Server service, click Yes.

The SQL Server 2000 Desktop Engine (MSDE 2000) is installed on a Microsoft Windows 98-based computer

When the SQL Server 2000 Desktop Engine is installed on a Windows 98-based computer, you must connect to the database using a standard SQL Server login. Windows only authentication mode is not supported when you install the SQL Server 2000 Desktop Engine on a Windows 98-based computer.

Insufficient Windows account permissions

To resolve this issue, you must add the Windows account to SQL Server, and then grant the appropriate permissions to each database that the user needs to access. To do this, follow these steps:

Start Enterprise Manager.

Expand Microsoft SQL Server, and then expand SQL Server Group.

Expand your server, and then expand Security.

Right-click Login, and then click New Login.

In the SQL Server Logon Properties dialog box, enter the Windows account name in the Name box. In the domain list, select the domain to which the Windows account belongs.

Click the Database Access tab, set the appropriate permissions for the client, and then click OK

Note: After you add an account, you must still grant access to individual database objects that the user needs to access.

2, just loaded SQL Server 2008 Express, try to use the SA account login, but always appear login failed for user ' sa ' error. Think that the authentication mode for SQL Server is not set, SQL Server Express defaults to Windows Authentication mode, I must be set to mixed authentication.

So check the documentation, and Microsoft's Official document says that you can just enable the SA account, but try the same error later. Later, a script that intercepts SQL Server Management Studio discovers that a registry key value must be changed.

On my machine The key value is: Hkey_local_machinesoftwaremicrosoftmicrosoft SQL servermssql10.sqlexpressmssqlserverloginmode

1-windows Authentication Mode

2-mixed Authentication Mode

You can log in with the SA account after changing to 2.

* Add that you must restart the SQL Server service changes to be valid.

3. In SQL Server 2005, you receive a "Logon Error: 18456" error message when you try to log on to an instance of SQL Server 2005 and the following conditions are true:

• You are attempting to log on to the instance by using SQL Server authenticated logins.

• Use domain accounts with the startup account of the SQL Server Service Configuration service.

The SQL Authentication login receives a "Logon error: 18456" error message that is configured to use Windows domain password policy enforcement.

Note By default, the Windows domain password policy implementation that creates a given login to enable SQL authentication logons unless you explicitly set the CHECK_POLICY clause to create a login statement to OFF.

The service account for the SQL Server startup service is locked or disabled on a domain controller.

If you configure a logon audit failure logon event to write to an instance of SQL Server in the error log, the following message is written to the SQL Server error log file:

Error message 1

Date Time Login error: 18456, serious: 14th Status: 10.

Solution

You can obtain supported hot fixes from Microsoft. However, this hotfix can only be used to resolve the problem that is described in this article. This hotfix applies only to systems that are having this particular problem. This hotfix may be subject to further testing. Therefore, if this problem does not have a serious impact on you, we recommend that you wait for the next software update that contains this hotfix.

If this hotfix is available for download, the "Provide hotfix downloads" section at the top of this Knowledge Base article. If this section is not displayed, contact Microsoft customer service and support to obtain this hotfix.

Note If you have other problems or need any troubleshooting, you may need to create a separate service request. Other support issues and events that are not appropriate for this particular hotfix will charge a regular support fee. Microsoft customer Service and support phone numbers, or to create a complete list of individual service requests, visit the following Microsoft Web site:

Http://support.microsoft.com/contactus/?ws=support

() http://support.microsoft.com/contactus/?ws=support

Note The Available Hotfix downloads table shows the language that this hotfix applies to. If you do not see your language, this is because hot fixes are not available for that language.

Prerequisite

You must have the SQL server for the hotfix installation to apply the 1.