MS SQL 18456 — решаем ошибку за 5 минут
Ошибка 18456 связана с отключенной SQL аутентификацией. Ее можно решить 2 путями. Либо включить SQL аутентификацию либо создать логин на основе пользователя Windows.
Для первого варианта сделаем следующее:
1) Зайдем под логином. Обратите внимание что бы у нас в поле 1 стоял Windows
2) Зайдем в свойства конфигурации сервера
3) Зайдем на закладку "Безопасность" и включим гибридную ацетификацию Windows и SQL:
После этого мы сможем зайти под SQL логином
Второй вариант — нам нужно создать логин на основе пользователя Windows
Для этого сделаем:
1) Зайдем в папку "Безопасность" — "Логины":
2) После этого добавляем существующего пользователя Windows для работы в SQL:
Your access to this site has been limited by the site owner
If you think you have been blocked in error, contact the owner of this site for assistance.
If you are a WordPress user with administrative privileges on this site, please enter your email address in the box below and click "Send". You will then receive an email that helps you regain access.
Block Technical Data
| Block Reason: | Access from your area has been temporarily limited for security reasons. |
|---|---|
| Time: | Wed, 24 May 2023 5:41:25 GMT |
About Wordfence
Wordfence is a security plugin installed on over 4 million WordPress sites. The owner of this site is using Wordfence to manage access to their site.
You can also read the documentation to learn about Wordfence's blocking tools, or visit wordfence.com to learn more about Wordfence.
Click here to learn more: Documentation
Generated by Wordfence at Wed, 24 May 2023 5:41:25 GMT.
Your computer's time: .
Login to Microsoft SQL Server Error: 18456
I am getting this error while trying to connect to the SQL Server.
Microsoft SQL Server Error: 18456
Can anybody tell me what the error code means?
25 Answers 25
If you’re trying to connect using «SQL Server Authentication» then you may want to modify your server authentication:
Within the Microsoft SQL Server Management Studio in the object explorer:
Right click on the server and click Properties
Go to the Security page
Under Server authentication choose the SQL Server and Windows Authentication mode radio button
Restart SQL Services
Check out this MSDN blog article from the data platform team.
You really need to look at the state part of the error message to find the root cause of the issue.
Afterwards, Google how to fix the issue.
Before opening, right-click and choose ‘Run as Administrator’. This solved the problem for me.
I have faced this issue.
Please look at the attached image,
Step 1: Go to server property
Step 2: Go to Security
Step 3: Change server authentication as SQL server and WindowsAuthenication mode
and restart your Sql server.
first see the details of the error if «state» is «1» Ensure the database is set for both SQL and Windows authentication under SQL server / Properties / Security.
for other state see the above answers .
Just an update, here is the solution if anyone else has there error with a properly configured login:
If you’re trying to connect using "SQL Server Authentication" then you may want to modify your server authentication:
Within the Microsoft SQL Server Management Studio in the object explorer:
Right click on the server and click Properties
Go to the Security page
Under Server authentication choose the SQL Server and Windows Authentication mode radio button
Restart SQL Services
- Check whether mixed mode authentication is enabled in you server->properties
- Then create a login in the server->security
- create an user for that login in your database
- Then restart your server by right clicking the instance and select restart
If you change a login user credential or add new login user then after you need to log in then you will have to restart the SQL Server Service. for that
GO to—> Services 
Then go to SQL Server(MSSQLSERVER) and stop and start again
Now try to log in, I hope You can.
Just happened to me, and turned out to be different than all other cases listed here.
I happen to have two virtual servers hosted in the same cluster, each with it own IP address. The host configured one of the servers to be the SQL Server, and the other to be the Web server. However, SQL Server is installed and running on both. The host forgot to mention which of the servers is the SQL and which is the Web, so I just assumed the first is Web, second is SQL.
When I connected to the (what I thought is) SQL Server and tried to connect via SSMS, choosing Windows Authentication, I got the error mentioned in this question. After pulling lots of hairs, I went over all the setting, including SQL Server Network Configuration, Protocols for MSSQLSERVER:
Double clicking the TCP/IP gave me this:
The IP address was of the other virtual server! This finally made me realize I simply confused between the servers, and all worked well on the second server.
Also you can just login with windows authentication and run the following query to enable it:
Right Click the User, go to properties, change the default database to master This is the screen print of the image which shows what you have to check if you have the error 19456. Sometimes it default to a database which the user doesn’t have permission
18456 Error State List
ERROR STATE ERROR DESCRIPTION
- State 2 and State 5 Invalid userid
- State 6 Attempt to use a Windows login name with SQL Authentication
- State 7 Login disabled and password mismatch
- State 8 Password mismatch
- State 9 Invalid password
- State 11 and State 12 Valid login but server access failure
- State 13 SQL Server service paused
- State 18 Change password required
Potential causes Below is a list of reasons and some brief explanation what to do:
SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because server might be set in Windows Authentication mode (only).
Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server)
Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers.
less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know.
18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states and for more information about retrieving accurate states visit Understanding «login failed» (Error 18456) error messages in SQL Server 2005
(Solved) Login Failed for User (Microsoft SQL Server, Error: 18456)
As you know well, we can login-in at MS SQL Server 3 types:
1. A local Windows user account or trustworthy domain account —
2. Windows group — Granting access to a Windows group grants
3. SQL Server login. SQL Server stores each the username and a hash of the password within the master database, by victimisation internal authentication methods to verify login tries
If you got this error for “SA” then 100% sure the issue is about Password (not inserting correct password / Incorrect password).
If you don’t know the password of user “SA” then do the below steps to reset the password —
Login in MS SQL server with Windows Authentication.
Open the Object Explorer → expand connected SQL instance → select Security. expand it → expand login → select “sa” user name and open the property then change the password.
Also, make sure below 2 things as well which is very important-
- “SA” user status should be (permission to connect to database engine = Grant and Login also Enabled.)
2. Open the MS SQL server Instance Property — server authentication has to selected as “SQL Server and Windows Authentication Mode”