Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Server Error 18456: A Solution

DZone 's Guide to

SQL Server Error 18456: A Solution

In this article, we will explain how to fix the SQL Server Error 18456 and describe the error and show different ways to solve this error.

· Database Zone ·
Free Resource

Introduction

In this article, we will explain how to fix the SQL Server Error 18456. We will first describe the error and then we will show different ways to solve this SQL Server error using SQL Server commands, backups or external software.

The SQL Server Error 18456

A typical error message when you try to connect is the following:

TITLE: Connect to Server

Cannot connect to SERVERNAME

ADDITIONAL INFORMATION:

Login failed for user "username." (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?%20SQL%20Server&&&

The error 18456, is a typical login error. There are multiple reasons you might see this message. Here, you have some possible reasons:

  • The login does not exist or was not typed correctly
  • The user forgot the password or login
  • The password is incorrect
  • The Windows Authentication is not in Mixed mode
  • A malicious hacker reset the password
  • A virus resets all the passwords
  • The database was migrated, but the logins were not migrated
  • The administrator modified the passwords by mistake
  • The logins were damaged or the master database is damaged

Solutions

There are many different types of solutions for these scenarios. Here, we will show the most popular solutions.

Reset the Password

If you forget your password, you can ask your DBA to reset your account. The easiest way to reset the password is by using SQL Server Management Studio (SSMS). Go to security and Logins:

Image title

  • Select the login and you can change the password:Image title

  • If you do not like to use SSMS, you can use T-SQL to create users and change the password:

USE [master]

GO

ALTER LOGIN [pedro] WITH 'newpassword'

GO

Change Windows Authentication

Another typical problem for the error is that you are trying to log in with a SQL login, but the SQL login authentication is not enabled. In SQL Server there are two types of authentication:

Image title

  • Windows Authentication that uses a Windows local or domain account to login. This option is secure and recommended.
  • Mixed mode is the second option that supports Windows and SQL Authentication

If you are trying to log in with a SQL login, but the Authentication is set to Windows, you will receive the error message.

You can change the authentication mode using SSMS. Right click the SQL Server and select properties. In the properties go to Security and change the Windows Authentication to SQL Server:

This option will require to restart the SQL Server Service.

If you do not want to use SSMS, there are other options to change the SQL Server authentication.

You can use the registry to modify the authentication mode. Use the regedit to change the registry:

Image title

machine\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQLServer

  • Change the login mode value.

  • 2 is mixed mode.
  • 1 is Windows Authentication.

Stellar SQL Database Toolkit

Another possible solution is to use the Stellar SQL Database Toolkit. This software contains 3 components. We will use the SQL Password Recovery to recover the password from a specific login. The other tools are used to recover the database and the backups. We will not cover them in this article.

Once downloaded, open the software and open the SQL Password Recovery:

Image title

  • This software is compatible with SQL Server 2012, 2008 and 2005:

Image title

  • This software looks for the master database. This database stores the logins and passwords. You can retrieve the logins information by querying the following view:

USE [master]

GO

select * from sys.sql_logins

The software will look at the master database data file (master.mdf) and get the password. As you can see, it is a straightforward process.

Conclusion

In this article, we learned the different reasons you might receive the SQL Server Error 18456. One of the reasons is that the login or the passwords are wrong. Another reason to receive this error is that the SQL Authentication is not enabled. We show how to solve this problem and learn how to retrieve the passwords using the Stellar SQL Database Toolkit.

If you have questions or suggestions regarding this article, please feel free to comment.

References

Topics:
tutorial ,sql server error ,database ,ssms ,sql server

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}