“Login failed for user” error message when you log on to SQL Server

Published by Jacob Martin on April 2, 2015

MS SQL server is very easy to install but sometime an error makes our task difficult, after the installation of Microsoft SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005, or SQL Server 2000 when user tries to connect to the server that is running SQL Server, you gets unable to connect and may receive one of the following error messages:

• Login failed for user ‘%.*ls’. The login is a SQL Server login and cannot be used with Windows Authentication.%.*ls
• Login failed for user ‘‘. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
• Login failed for user ‘‘. (Microsoft SQL Server, Error: 18456)

The above given error occurs if the user attempts to log in with credentials which is can’t be validated. This error may appear because of the following given scenarios:

Scenario 1: The login may be a SQL Server login but the server only recognizes Windows verification

To fix such issue you have to configure the SQL Server in Mixed Verification Mode. Apply the bellow given steps to do this

Steps for the user of Server 2014, SQL Server 2012, SQL Server 2008, and SQL Server 2005

1. Open the SQL Server Management Studio and click on the Start, and go to the All Programs->Microsoft SQL Server 20xx (where xx is the version of SQL), and after it click on the SQL Server Management Studio.
2. Right-click the server, and then select Properties.
3. On the Security page, under Server authentication, click on the SQL Server and Windows Authentication mode option button, and then click OK.
4. From the SQL Server Management Studio dialog box, click on OK to restart SQL Server.

Steps for SQL Server 2000

1. To do this in SQL server 2000 you have to open the Enterprise Manager first. Click on the Start and go to the All Programs-> Microsoft SQL Server 2000, and after it click SQL Server Enterprise Manager.
2. Expand the server group.
3. Right-click on the server and then click Properties.
4. Click the Security tab.
5. Below Authentication, click on the SQL Server and Windows option button.
6. After all you have to restart the SQL Server for the change to take effect.

Note:  When user install the SQL Server is through Windows Authentication mode and is afterward distorted to SQL Server and Windows Authentication mode, the sa login is originally disabled. This causes the state 7 error: “Login failed for user ‘sa’.” To allow the sa login, it is necessary to change the Server Authentication Mode.

Scenario 2: User tries to connect through SQL Server Authentication but the login used doesn’t exist on SQL Server

To fix this problem, confirm that the SQL Server login be present. To know more about this you have to learn how to create a login in SQL Server Books Online.

Scenario 3: The login may use Windows verification but the login is an unknown Windows principal

The meaning of an unrecognized Windows principal is Windows can’t confirm the login. This may be as the Windows login is from an un-trusted domain. To fix this matter, confirm that you are logged in to the right domain.

How to deal with SQL Database Corruption:

Once SQL database gets damaged then it is necessary to fix it to get the proper service or task, restore the database with the updated backup to remove any types of error. Apply this method only when you have the updated backup because restoring database with outdated database may harm your data.

In case you don’t have the backup file then use third party database repair tool, this is the much easiest way to get rid of any database error.      

Steps to fix MS SQL “Login failed for user” error

Step 1: Stop the running MS SQL server. Perform the repair task on the copy of the corrupt database, click on the ‘OK’ button to continue.

Step 2: Click on the ‘Select database’ button and select the path of corrupt MDF file. You can also search your corrupt database file by using ‘Look in’ and ‘File Type’ button. click on the ‘Scan file‘ button to start the repairing process.

Step 3: The recoverable objects of database are listed in a tree view on the left side of the window. You can see the preview by clicking on the object.

Step 4: You can also search for a particular object by using ‘Find item’ option. Write the object name or a part of the object name in the given text box, check on ‘Match case‘ or ‘Match whole word‘ and then click find next button.

Step 5: Click on the ‘Start Repair’ icon. A dialog box will appear fill the SQL server instance name. To save the repaired file in the desired location click on the brows button and give the path, else the repaired file will be saved in the ‘Default SQL Location’. Click on the ‘OK’ button.

404 Total Views 1 Views Today