By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.
Althought if that user has relevant grants on database & server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server, the client will get the following error message:
Msg 18456, Level 14, State 1, Server <ServerName>, Line 1
Login failed for user '<Name>'
Ok now what, by looking at the error message you feel like this is non-descriptive to understand the Level & state. By default the Operating System error will show ‘State’ as 1 regardless of nature of the issues in authenticating the login. So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity & state of this error. You might look into a corresponding entry in log as:
2007-05-17 00:12:00.34 Logon Error: 18456, Severity: 14, State: 8.
or
2007-05-17 00:12:00.34 Logon Login failed for user '<user name>'.
As defined above the Severity & State columns on the error are key to find the accurate reflection for the source of the problem. On the above error number 8 for state indicates authentication failure due to password mismatch. Books online refers: By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.
Sung Lee, Program Manager in SQL Server Protocols (Dev.team) has outlined further information on Error state description:The common error states and their descriptions are provided in the following table:
ERROR STATE ERROR DESCRIPTION
------------------------------------------------------------------------------
2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required
Well I'm not finished yet, what would you do in case of error:
2007-05-17 00:12:00.34 Logon Login failed for user '<user name>'.
You can see there is no severity or state level defined from that SQL Server instance’s error log. So the next troubleshooting option is to look at the Event Viewer’s security log [edit because screen shot is missing but you get the
idea, look in the event log for interesting events].
| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords |
|---|---|---|---|---|---|---|---|---|---|
|
MSSQLSERVER_18456 |
A connection attempt is rejected due to a failure with a bad password or username in SQL Server. See an explanation of the error and possible resolutions. |
MashaMSFT |
mathoma |
jopilov, randolphwest |
01/16/2023 |
sql |
supportability |
reference |
18456 (Database Engine error) |
MSSQLSERVER_18456
[!INCLUDE SQL Server]
Details
| Attribute | Value |
|---|---|
| Product Name | SQL Server |
| Event ID | 18456 |
| Event Source | MSSQLSERVER |
| Component | SQLEngine |
| Symbolic Name | LOGON_FAILED |
| Message Text | Login failed for user ‘%.*ls’.%.*ls |
Explanation
You get this error message when a connection attempt is rejected because of an authentication failure. User logins can fail for many reasons, such as invalid credentials, password expiration, and enabling the wrong authentication mode. In many cases, error codes include descriptions.
User action
The following examples are some of the common login failures. Select the exact error that you’re experiencing to troubleshoot the issue:
-
Login failed for user ‘<username>’ or login failed for user ‘<domain><username>’
-
Login failed for user ‘NT AUTHORITYANONYMOUS’ LOGON
-
Login failed for user ’empty’
-
Login failed for user ‘(null)’
Login failed for user ‘<username>’ or login failed for user ‘<domain><username>’
If the domain name isn’t specified, the problem is a failing SQL Server login attempt. If the domain name is specified, the problem is a failing Windows user account login. For potential causes and suggested resolutions, see:
| Potential cause | Suggested resolution |
|---|---|
| You’re trying to use SQL Server Authentication, but the SQL server instance is configured for Windows Authentication mode. | Verify that SQL Server is configured to use SQL Server and Windows Authentication mode. You can review and change the authentication mode for your SQL Server instance on the Security page under Properties for the corresponding instance in SQL Server Management Studio (SSMS). For more information, see Change server authentication mode. Alternatively, you can change your application to use Windows Authentication mode to connect to SQL Server. Note: You can see a message like the following one in the SQL Server Error log for this scenario: Login failed for user '<UserName>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. |
| Login doesn’t exist on the SQL Server instance you’re trying to connect to. | Verify that the SQL Server login exists and that you’ve spelled it properly. If the login doesn’t exist, create it. If it’s present but misspelled, correct that in the application connection string. The SQL Server Errorlog will have one of the following messages: — Login failed for user 'username'. Reason: Could not find a login matching the name provided.— Login failed for user 'Domainusername'. Reason: Could not find a login matching the name provided.This can be a common issue if you deploy an application that uses a DEV or QA server into production and you fail to update the connection string. To resolve this issue, validate that you are connecting to the appropriate server. If not, correct the connection string. If it is, grant the login access to your SQL Server. Or if it’s a windows login grant access directly or add it to a local or domain group that is allowed to connect to the database server. For more information, see Create a Login. |
| You’re using SQL Server Authentication, but the password you specified for SQL Server login is incorrect. | Check the SQL error log for messages like «Reason: Password did not match that for the login provided» to confirm the cause. To fix the issue, use the correct password in your application or use a different account if you can’t remember the password. Alternatively, work with your SQL Server administrator to reset the password for the account. If the application is SQL Server Integration Services (SSIS), there may be multiple levels of a Configuration file for the job, which may override the Connection Manager settings for the package. If the application was written by your company and the connection string is programmatically generated, engage the development team to resolve the issue. As a temporary workaround, hard-code the connection string and test. Use a UDL file or a script to prove a connection is possible with a hard-coded connection string. |
| Server name is incorrect. | Ensure you’re connecting to the correct server. |
| You’re trying to connect using Windows authentication but are logged into an incorrect domain. | Verify that you’re properly logged into the correct domain. The error message usually displays the domain name. |
| You aren’t running your application (for example, SSMS) as an administrator. | If you’re trying to connect using your administrator credentials, start your application by using the Run as Administrator option. When connected, add your Windows user as an individual login. |
| Login is deleted after a migration to a contained database user. | If the Database Engine supports contained databases, confirm that the login wasn’t deleted after migration to a contained database user. For more information, see Contained Database Authentication: Introduction. |
| Login’s default database is offline or otherwise not available. | Check with your SQL Server administrator and resolve issues related to database availability. If the login has permissions to other databases on the server and you don’t need to access the currently configured default database in your application, use one of the following options: — Request the administrator to change the default database for the login using ALTER LOGIN statement or SSMS. — Explicitly specify a different database in your application connection string. Or if you’re using SSMS switch to the Connection Properties tab to specify a database that is currently available.Applications like SSMS may show an error message like the following one: Cannot open user default database. Login failed.Login failed for user <user name>. (Microsoft SQL Server, Error: 4064)SQL Server Errorlog will have an error message like the following one: Login failed for user '<user name>'. Reason: Failed to open the database '<dbname>' specified in the login properties [CLIENT: <ip address>]For more information, see MSSQLSERVER_4064. |
| The database explicitly specified in the connection string or in SSMS is incorrectly spelled, offline, or otherwise not available. | — Fix the database name in the connection string. Pay attention to case sensitivity if using a case sensitive collation on the server. — If the database name is correct, check with your SQL Server administrator and resolve issues related to database availability. Check if the database is offline, not recovered, and so on. — If the login has been mapped to users with permissions to other databases on the server and you don’t need to access the currently configured database in your application, then specify a different database in your connection string. Or if you’re connecting with SSMS, use the Connection Properties tab to specify a database that is currently available. SQL Server Errorlog will have an error message like the following one: Login failed for user <UserName>. Reason: Failed to open the explicitly specified database 'dbname'. [CLIENT: <ip address>]Note: If the login’s default database is available, SQL Server allows the connection to succeed. For more information, see MSSQLSERVER_4064. |
| The user doesn’t have permissions to the requested database. | — Try connecting as another user that has sysadmin rights to see if connectivity can be established. — Grant the login access to the database by creating the corresponding user (for example, CREATE USER [<UserName>] FOR LOGIN [UserName]) |
Also, check the extensive list of error codes at Troubleshooting Error 18456.
For more troubleshooting help, see Troubleshooting SQL Client / Server Connectivity Issues.
Login failed for user NT AUTHORITYANONYMOUS LOGON
There are at least four scenarios for this issue. In the following table, examine each applicable potential cause, and use the appropriate resolution:
See the note below the table for an explanation of the term double hop.
| Potential causes | Suggested resolutions |
|---|---|
| You’re trying to pass NT LAN Manager (NTLM) credentials from one service to another service on the same computer (for example: from IIS to SQL server), but a failure occurs in the process. | Add the DisableLoopbackCheck or BackConnectionHostNames registry entries. |
| There are double-hop (constraint delegation) scenarios across multiple computers. The error could occur if the Kerberos connection fails because of Service Principal Names (SPN) issues. | Run SQLCheck on each SQL Server and the web server. Use the troubleshooting guides: 0600 Credential Delegation Issue and 0650 SQL Server Linked Server Delegation Issues. |
| If no double-hop (constraint delegation) is involved, then likely duplicate SPNs exist, and the client is running as a LocalSystem or another machine account that gets NTLM credentials instead of Kerberos credentials. | Use SQLCheck or Setspn.exe to diagnose and fix any SPN-related issues. Also review Overview of the Kerberos Configuration Manager for SQL Server. |
| Windows Local Security policy may have been configured to prevent the use of the machine account for remote authentication requests. | Navigate to Local Security Policy > Local Policies > Security Options > Network security: Allow Local System to use computer identity for NTLM, select the Enabled option if the setting is disabled, and then select OK. Note: As detailed on the Explain tab, this policy is enabled in Windows 7 and later versions by default. |
| Intermittent occurrence of this issue when using constrained delegation can indicate presence of an expired ticket that can’t be renewed by middle tier. This is an expected behavior with either linked server scenario or any application that is holding a logon session for more than 10 hours. | Change delegation settings on your middle-tier server from Trust this computer for delegation to specified services only – Use Kerberos Only to Trust this computer for delegation to specified services only — Use any protocol. For more information review Intermittent ANONYMOUS LOGON of SQL Server linked server double hop. |
[!NOTE]
A double-hop typically involves delegation of user credentials across multiple remote computers. For example, assume you have a SQL Server instance named SQL1 where you created a linked server for a remote SQL Server named SQL2. In linked server security configuration, you selected the option Be made using the login’s current security context. When using this configuration, if you execute a linked server query on SQL1 from a remote client computer named Client1, the windows credentials will first have to hop from Client1 to SQL1 and then from SQL1 to SQL2 (hence, it’s called a double-hop). For more information, see Understanding Kerberos Double Hop and Kerberos Constrained Delegation Overview
Login failed for user (empty)
This error occurs when a user tries unsuccessfully to log in. This error might occur if your computer isn’t connected to the network. For example, you may receive an error message that resembles the following one:
Source: NETLOGON
Date: 8/12/2012 8:22:16 PM
Event ID: 5719
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: <computer name>
Description: This computer was not able to set up a secure session with a domain controller in domain due to the following: The remote procedure call was cancelled.
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.
An empty string means that SQL Server tried to hand off the credentials to the Local Security Authority Subsystem Service (LSASS) but couldn’t because of some problem. Either LSASS wasn’t available, or the domain controller couldn’t be contacted.
Check the event logs on the client and the server for any network-related or Active Directory-related messages that were logged around the time of the failure. If you find any, work with your domain administrator to fix the issues.
Login failed for user ‘(null)’
An indication of «null» could mean that LSASS can’t decrypt the security token by using the SQL Server service account credentials. The main reason for this condition is that the SPN is associated with the wrong account.
To fix the issue, follow these steps:
-
Use the SQLCheck or Setspn.exe to diagnose and fix SPN-related issues.
-
Use SQLCheck to check whether the SQL Service account is trusted for delegation. If the output indicates that the account isn’t trusted for delegation, work with your Active Directory administrator to enable delegation for the account.
-
Diagnose and fix Domain Name System (DNS) name resolution issues. For example:
-
Ping IP address by using PowerShell scripts:
ping -a <your_target_machine>(use-4for IPv4 and-6IPv6 specifically)ping -a <your_remote_IPAddress>
-
Use NSLookup to enter your local and remote computer name and IP address multiple times.
-
-
Look for any discrepancies and mismatches in the returned results. The accuracy of the DNS configuration on the network is important for a successful SQL Server connection. An incorrect DNS entry could cause numerous connectivity issues later.
-
Make sure that firewalls or other network devices don’t block a client from connecting to the domain controller. SPNs are stored in Active Directory. If the clients can’t communicate with the directory, the connection can’t succeed.
Additional error information
To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the [!INCLUDEssNoVersion] error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.
| State | Description |
|---|---|
| 1 | Error information isn’t available. This state usually means you don’t have permission to receive the error details. Contact your [!INCLUDEssNoVersion] administrator for more information. |
| 2 | User ID isn’t valid. |
| 5 | User ID isn’t valid. |
| 6 | An attempt was made to use a Windows login name with SQL Server Authentication. |
| 7 | Login is disabled, and the password is incorrect. |
| 8 | The password is incorrect. |
| 9 | Password isn’t valid. |
| 11 | Login is valid, but server access failed. One possible cause of this error is when the Windows user has access to [!INCLUDEssNoVersion] as a member of the local administrators’ group, but Windows isn’t providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to [!INCLUDEssNoVersion] as a specific login. |
| 12 | Login is valid login, but server access failed. |
| 18 | Password must be changed. |
| 38, 46 | Couldn’t find database requested by user. |
| 58 | When SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL authentication. Another cause is when SIDs don’t match. |
| 102 — 111 | Azure AD failure. |
| 122 — 124 | Failure due to empty user name or password. |
| 126 | Database requested by user doesn’t exist. |
| 132 — 133 | Azure AD failure. |
Other error states exist and signify an unexpected internal processing error.
More rare possible cause
The error reason An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. can be returned in the following situations.
-
When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection doesn’t explicitly specify that the connection should use a trusted connection.
-
When SQL server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection string doesn’t explicitly specify the use of a trusted authentication.
To resolve this issue, include TRUSTED_CONNECTION = TRUE in the connection string.
Examples
In this example, the authentication error state is 8. This indicates that the password is incorrect.
| Date | Source | Message |
|---|---|---|
| 2007-12-05 20:12:56.34 | Logon | Error: 18456, Severity: 14, State: 8. |
| 2007-12-05 20:12:56.34 | Logon | Login failed for user ‘<user_name>’. [CLIENT: <ip address>] |
[!NOTE]
When [!INCLUDEssNoVersion] is installed using Windows Authentication mode and is later changed to [!INCLUDEssNoVersion] and Windows Authentication mode, the sa login is initially disabled. This causes the state 7 error: «Login failed for user ‘sa’.» To enable the sa login, see Change Server Authentication Mode.
See also
- 0420 Reasons for Consistent Auth Issues
Вы можете столкнуться с ошибкой SQL Server 18456, если сервер не может аутентифицировать соединение, и это может быть вызвано недоступностью прав администратора для SQL-сервера или если протокол TCP / IP отключен в настройках SQL-сервера.
Проблема возникает, когда пользователь пытается подключиться к серверу SQL (локальному или удаленному), но обнаруживает ошибку 18456 (с разными состояниями).
Ошибка Microsoft SQL Server 18456
Вы можете исправить ошибку SQL-сервера 18456, попробовав приведенные ниже решения, но перед этим проверьте, решает ли проблему перезагрузка сервера, клиентского компьютера и сетевых компьютеров. Кроме того, убедитесь, что вы вводите правильное имя пользователя и пароль (а не копируете адрес).
Также проверьте, правильно ли вы вводите имя базы данных (без опечаток), и убедитесь, что вы соответствующим образом обновили файл конфигурации. Кроме того, проверьте, решает ли проблему разблокировка учетной записи (с помощью запроса ALTER LOGIN WITH PASSWORD = UNLOCK). Если вы видите ошибки в журнале ошибок SQL, убедитесь, что ваш SQL-сервер не атакован. И последнее, но не менее важное: убедитесь, что часы сервера и клиентского компьютера установлены правильно.
Вы можете столкнуться с ошибкой 18456, если SQL-сервер не имеет повышенных разрешений на выполнение своей операции, и запуск его от имени администратора (или отключение элементов управления UAC на сервере) может решить проблему.
Откройте SQL Server от имени администратора
- Щелкните Windows и введите SQL Server Management Studio.
- Теперь щелкните правой кнопкой мыши SMSS и выберите «Запуск от имени администратора».
Запустите Microsoft SQL Server Management Studio от имени администратора.
- Затем нажмите Да (если получено приглашение UAC) и проверьте, не содержит ли SQL-сервер ошибки 18456.
- Если нет, проверьте, решает ли проблему отключение UAC на сервере.
Запуск SQL Server в однопользовательском режиме
- Щелкните Windows, введите и откройте диспетчер конфигурации SQL Server.
- Теперь щелкните правой кнопкой мыши службу SQL Server (на вкладке «Службы SQL Server») и выберите «Свойства».
Откройте свойства SQL Server
- Затем перейдите на вкладку Параметры запуска и в поле Укажите параметр запуска введите: -m
- Теперь нажмите «Добавить» и примените изменения.
Добавьте параметр «-m» к параметрам запуска SQL Server.
- Затем щелкните правой кнопкой мыши службу SQL Server и выберите «Перезагрузить».
Перезапустите службу SQL Server.
- Теперь щелкните Windows, введите: SQL Server Management Studio, щелкните правой кнопкой мыши SMSS и выберите Запуск от имени администратора.
- Теперь проверьте, можете ли вы подключиться к SQL Server от имени администратора.
- Если это так, добавьте учетную запись домена на SQL-сервер и назначьте ей роль SysAdmin.
- Теперь вернитесь в окно диспетчера конфигурации SQL Server и удалите параметр -m на вкладке Параметры запуска.
- Затем перезапустите службу SQL Server (шаг 3) и проверьте, нормально ли работает SQL-сервер.
Если проблема не исчезнет, проверьте, правильно ли настроены параметры запуска или сведения о пути. Если проблема все еще существует, убедитесь, что ваша учетная запись пользователя имеет необходимые разрешения для служб базы данных / отчетов, а затем проверьте, решена ли проблема.
Включите протокол TCP / IP в диспетчере конфигурации сервера.
Код ошибки 18456 на сервере SQL означает, что серверу не удалось аутентифицировать соединение, и это может произойти, если протокол TCP / IP, необходимый для доступа к базе данных в сети, отключен в диспетчере конфигурации сервера. В этом контексте включение TCP / IP в диспетчере конфигурации SQL Server может решить проблему.
- Щелкните Windows и разверните Microsoft SQL Server, указав год, например, 2008 (вам может потребоваться немного прокрутить, чтобы найти параметр).
- Теперь откройте диспетчер конфигурации SQL Server и нажмите Да (если получено приглашение UAC).
- Затем разверните сетевую конфигурацию SQL Server и выберите Протоколы для (имя сервера / базы данных) на левой панели.
- Теперь на правой панели дважды щелкните TCP / IP и выберите Да в раскрывающемся списке Включено.
Откройте TCP / IP в протоколах конфигурации сети SQL Server
- Затем примените изменения и щелкните Windows.
Включить TCP / IP в SQL
- Теперь введите «Службы», щелкните правой кнопкой мыши результат «Службы» и выберите «Запуск от имени администратора».
Откройте службы в качестве администратора
- Затем щелкните правой кнопкой мыши SQL Server (с именем сервера) и выберите «Перезагрузить».
Перезапустите службу SQL в окне служб.
- Теперь проверьте, очищен ли SQL-сервер от ошибки 18456.
Если это не помогло, убедитесь, что вы подключаетесь к правильному порту SQL-сервера (особенно, если вы используете сервер в многосерверной среде).
Измените режим аутентификации SQL Server
Сервер SQL может отображать ошибку 18456, если метод аутентификации сервера SQL не настроен должным образом (например: вы пытаетесь войти в систему с использованием аутентификации сервера SQL, тогда как сервер настроен на использование аутентификации Windows). В этом случае изменение метода аутентификации SQL-сервера может решить проблему. Прежде чем двигаться дальше, убедитесь, что для текущего пользователя включен статус входа в систему (например, SA).
- В обозревателе объектов Microsoft SQL Server Management Studio щелкните правой кнопкой мыши свой сервер и выберите «Свойства».
- Теперь на левой панели выберите Безопасность, а на правой панели выберите SQL Server и проверку подлинности Windows (или наоборот).
Включить SQL Server и проверку подлинности Windows
- Затем примените изменения и в обозревателе объектов щелкните правой кнопкой мыши сервер.
- Теперь выберите «Перезагрузить» и после перезапуска проверьте, можете ли вы подключиться к базе данных без ошибки 18456.
Если вы не можете войти в SQL, вы можете установить MS Power Tools и выполнить следующую команду с повышенными привилегиями:
psexec.exe -i -s ssms.exe
После этого вы можете использовать учетную запись установки SQL, чтобы внести изменения, а также убедиться, что учетная запись SA не отключена:
Включите учетную запись SA и сбросьте пароль учетной записи
Если вы не можете подключиться к SQL Server, то включение учетной записи SA SQL-сервера и сброс его пароля может решить проблему.
- Запустите Microsoft SQL Server Management Studio (возможно, вам придется использовать учетную запись администратора домена) и разверните Безопасность.
- Затем дважды щелкните Logins и откройте SA.
Откройте учетную запись SA в Microsoft SQL Server Management Studio.
- Теперь введите новый пароль и подтвердите его (убедитесь, что вы используете надежный пароль).
- Затем перейдите на вкладку Server Roles и убедитесь, что выбраны следующие роли: Public Sysadmin
Включение ролей общедоступного сервера и сервера системного администратора для учетной записи SA
- Теперь перейдите на вкладку «Статус» и на правой панели выберите «Включено» (в разделе «Вход»).
Включение учетной записи SA в SQL
- Затем примените изменения и нажмите кнопку Windows.
- Теперь введите Services и щелкните его правой кнопкой мыши.
- Затем выберите «Запуск от имени администратора» и перейдите к службе SQL Server.
- Теперь щелкните его правой кнопкой мыши и выберите «Перезагрузить».
- После перезапуска службы проверьте, устранена ли ошибка 18456 SQL-сервера.
Создайте новый логин и перезапустите службы Reporting Services
Если вы не можете использовать какую-либо учетную запись для подключения к базе данных, то создание новой учетной записи и перезапуск служб отчетов может решить проблему.
- Запустите Microsoft SQL Server Management Studio и разверните вкладку «Безопасность».
- Затем разверните Логины и щелкните его правой кнопкой мыши.
- Теперь выберите «Новый вход» и введите учетные данные (в имени входа выберите учетную запись компьютера), если используется проверка подлинности SQL Server.
Создать новый логин в SQL Server
- Затем не забудьте снять флажок «Пользователь должен сменить пароль при следующем входе в систему» и выберите базу данных.
- Теперь перейдите на вкладку Server Roles и выберите роль Public.
- Затем на вкладке «Сопоставление пользователей» обязательно выберите базу данных и выберите db_owner.
Выберите db_owner для базы данных в SQL
- Теперь примените ваши изменения и щелкните Windows.
- Затем введите Services и щелкните правой кнопкой мыши результат Services. Затем выберите Запуск от имени администратора.
- Теперь щелкните правой кнопкой мыши службу отчетов SQL Server и выберите «Перезагрузить».
Перезапустите службу отчетов SQL Server.
- Затем повторно подключитесь к базе данных и проверьте, очищен ли сервер SQL от ошибки 18456.
Если это так, убедитесь, что вы создали пользователя в BUILTIN administrators, и затем вы можете использовать этого пользователя для управления SQL Server. Если вы восстановили базу данных из резервной копии, будет лучше удалить и повторно добавить пользователей, чтобы удалить все старые записи пользователей. Если вы хотите запустить SQL-сервер от имени другого пользователя, введите Microsoft SQL Server в поиске Windows, Shift + щелкните правой кнопкой мыши на SQL Server и выберите «Запуск от имени другого пользователя». И последнее, но не менее важное: проверьте, решает ли проблема использование Azure Data Studio с сервером SQL.
You may encounter the SQL Server Error 18456 if the server could not authenticate the connection and this can be caused by the non-availability of the administrator rights to the SQL server or if the TCP/IP protocol is disabled in the SQL server settings.
The issue arises when the user tries to connect to the SQL server (local or remote) but encounters the error 18456 (with different states).
You can fix the SQL server error 18456 by trying the solutions below but before that, check if restarting the server, client computer, and networking computers solves the issue. Moreover, make sure you are typing the correct username and password (not copy-pasting the address).
Also, check if you are entering the correct database name (no typo in it) and make sure you have updated the configuration file accordingly. Furthermore, check if unlocking the account (by using the query ALTER LOGIN WITH PASSWORD= UNLOCK) solves the issue. If you are seeing the errors in the SQL errors log, then make sure your SQL server is not under attack. Last but not least, make sure the server’s clock and client computer clock is correctly set.
Launch the SQL Server as Administrator and Disable UAC on the Server
You may encounter the error 18456 if the SQL server does not have the elevated permissions to execute its operation and launching it as administrator (or disabling the UAC controls on the server) may solve the problem.
Open the SQL Server as Administrator
- Click Windows and type SQL Server Management Studio.
- Now right-click on SMSS and select Run as Administrator.
Launch Microsoft SQL Server Management Studio as Administrator - Then click Yes (if UAC prompt received) and check if the SQL server is clear of the error 18456.
- If not, then check if disabling UAC on the server machine solves the issue.
Launch the SQL Server in a Single User Mode
- Click Windows, type, and open the SQL Server Configuration Manager.
- Now right-click on the SQL Server service (in the SQL Server Services tab) and select Properties.
Open Properties of the SQL Server - Then head to the Startup Parameters tab and in the Specify a Startup Parameter box, type:
-m
- Now click on Add and apply the changes.
Add the “-m” Parameter to the Startup Parameters of the SQL Server - Then right-click on the SQL Server service and select Restart.
Restart the SQL Server Service - Now click Windows, type: SQL Server Management Studio, right-click on SMSS, and select Run as Administrator.
- Now check if you can connect to the SQL Server as administrator.
- If so, then add the domain account to the SQL server and assign it the SysAdmin role.
- Now go back to the SQL Server Configuration Manager window and remove the -m parameter in the Startup Parameters tab.
- Then restart the SQL Server service (step 3) and check if the SQL server is working fine.
If the issue persists, check if the startup parameters or path details are properly configured. If the issue is still there, make sure your user account does have the required permissions to the database/ reporting services, and then check if the issue is resolved.
Enable the TCP/IP Protocol in the Server Configuration Manager
The error code 18456 in the SQL server means that the server could not authenticate the connection and this can happen if the TCP/IP protocol required to access the database on a network is disabled in the Server Configuration Manager. In this context, enabling the TCP/IP in the SQL Server Configuration Manager may solve the problem.
- Click Windows and expand Microsoft SQL Server with a year name like 2008 (you may need to scroll a bit to find the option).
- Now open SQL Server Configuration Manager and click Yes (if UAC prompt received).
- Then, expand SQL Server Network Configuration and select Protocols for (the server/database name) in the left pane.
- Now, in the right pane, double-click on TCP/IP and select Yes in the dropdown of Enabled.
Open TCP/IP in Protocols of SQL Server Network Configuration - Then apply your changes and click Windows.
Enable TCP/IP in SQL - Now type Services, right-click on the result of Services, and select Run as Administrator.
Open Services as Administrator - Then right-click on the SQL Server (with the server’s name) and select Restart.
Restart the SQL Service in the Services Window - Now check if the SQL server is clear of the error 18456.
If that did not do the trick, then make sure you are connecting to the right port of the SQL server (especially if you are using the server in a multi-server environment).
Change the Authentication Mode of the SQL Server
The SQL server might show the error 18456 if the authentication method of the SQL server is not properly configured (e.g: you are trying to login using SQL server authentication whereas the server is configured to use the Windows authentication). In this case, changing the authentication method of the SQL server may solve the problem. Before moving on make sure the status login for the present user (for example SA) is enabled.
- In the Object Explorer of Microsoft SQL Server Management Studio, right-click on your server and select Properties.
- Now, in the left pane, select Security, and in the right pane, select SQL Server and Windows Authentication (or vice versa).
Enable SQL Server and Windows Authentication - Then apply your changes and in the Object Explorer, right-click on the server.
- Now choose Restart and once restarted, check if you can connect to the database without error 18456.
If you cannot log into SQL, then you may install MS Power Tools and run the following in an elevated command:
psexec.exe -i -s ssms.exe
Afterward, you may use the installation account of SQL to make the changes and also make sure the SA account is not disabled:
Enable the SA Account and Reset the Account Password
If you cannot connect to the SQL Server, then enabling the SA account of the SQL server and resetting its password may solve the problem.
- Launch Microsoft SQL Server Management Studio (you may have to use the domain admin account) and expand Security.
- Then double-click on Logins and open SA.
Open the SA Account in the Microsoft SQL Server Management Studio - Now enter a new password and confirm the password (make sure you use a strong password).
- Then head to the Server Roles tab and make sure the following roles are selected:
Public Sysadmin
Enable the Public and Sysadmin Server Roles for the SA Account - Now head to the Status tab and in the right pane, select Enabled (under Login).
Enable the SA Account in SQL - Then apply your changes and click the Windows button.
- Now type Services and right-click on it.
- Then select Run as Administrator and steer to the SQL Server service.
- Now right-click on it and select Restart.
- Once the service is restarted, check if the error 18456 of the SQL server is cleared.
Create a New Login and Restart the Reporting Services
If you cannot use any account to connect to the database, then creating a new login and restarting the reporting services may solve the problem.
- Launch the Microsoft SQL Server Management Studio and expand its Security tab.
- Then expand Logins and right-click on it.
- Now select New Login and enter the credentials (in the login name select the computer account) if using the SQL Server Authentication.
Create a New Login in the SQL Server - Then make sure to uncheck “User Must Change Password at Next Login” and select the database.
- Now head to the Server Roles tab and select the Public role.
- Then, in the User Mapping tab, make sure to choose the database and select db_owner.
Select db_owner for the Database in SQL - Now apply your changes and click Windows.
- Then type Services and right-click on the result of Services. Then select Run as Administrator.
- Now right-click on the SQL Server Reporting Service and select Restart.
Restart the SQL Server Reporting Service - Then reconnect to the database and check if the SQL server is clear of the error 18456.
If so, make sure that you have created a user in BUILTINadministrators, and then you can use that user to manage the SQL Server. If you have restored the database from a backup, it will be better to remove and re-add the users to clear any old user entries. If you want to run the SQL server as a different user, then type Microsoft SQL Server in the Windows Search, Shift+Right-click on the SQL Server, and select Run as a Different User. Last but not least, check if using Azure Data Studio with the SQL server sorts out the issue.
Kevin Arrows
Kevin Arrows is a highly experienced and knowledgeable technology specialist with over a decade of industry experience. He holds a Microsoft Certified Technology Specialist (MCTS) certification and has a deep passion for staying up-to-date on the latest tech developments. Kevin has written extensively on a wide range of tech-related topics, showcasing his expertise and knowledge in areas such as software development, cybersecurity, and cloud computing. His contributions to the tech field have been widely recognized and respected by his peers, and he is highly regarded for his ability to explain complex technical concepts in a clear and concise manner.
Ошибка 18456 связана с отключенной SQL аутентификацией. Ее можно решить 2 путями. Либо включить SQL аутентификацию либо создать логин на основе пользователя Windows.
Для первого варианта сделаем следующее:
1) Зайдем под логином. Обратите внимание что бы у нас в поле 1 стоял Windows
2) Зайдем в свойства конфигурации сервера
3) Зайдем на закладку «Безопасность» и включим гибридную ацетификацию Windows и SQL:
После этого мы сможем зайти под SQL логином
Второй вариант — нам нужно создать логин на основе пользователя Windows
Для этого сделаем:
1) Зайдем в папку «Безопасность» — «Логины»:
2) После этого добавляем существующего пользователя Windows для работы в SQL:
Все должно заработать.
…
Теги:
#ms-sql