Ошибка odbc sqlstate 28000 номер ошибки 18456

Ошибка ODBC. SQLSTATE: 28000 Номер ошибки: 18456

Вы можете столкнуться с ошибкой 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 от имени администратора

1. Щелкните Windows и введите SQL Server Management Studio.
2. Теперь щелкните правой кнопкой мыши SMSS и выберите «Запуск от имени администратора».Запустите Microsoft SQL Server Management Studio от имени администратора.
3. Затем нажмите Да (если получено приглашение UAC) и проверьте, не содержит ли SQL-сервер ошибки 18456.
4. Если нет, проверьте, решает ли проблему отключение UAC на сервере.

Запуск SQL Server в однопользовательском режиме

1. Щелкните Windows, введите и откройте диспетчер конфигурации SQL Server.
2. Теперь щелкните правой кнопкой мыши службу SQL Server (на вкладке «Службы SQL Server») и выберите «Свойства».Откройте свойства SQL Server
3. Затем перейдите на вкладку Параметры запуска и в поле Укажите параметр запуска введите: -m
4. Теперь нажмите «Добавить» и примените изменения.Добавьте параметр «-m» к параметрам запуска SQL Server.
5. Затем щелкните правой кнопкой мыши службу SQL Server и выберите «Перезагрузить».Перезапустите службу SQL Server.
6. Теперь щелкните Windows, введите: SQL Server Management Studio, щелкните правой кнопкой мыши SMSS и выберите Запуск от имени администратора.
7. Теперь проверьте, можете ли вы подключиться к SQL Server от имени администратора.
8. Если это так, добавьте учетную запись домена на SQL-сервер и назначьте ей роль SysAdmin.
9. Теперь вернитесь в окно диспетчера конфигурации SQL Server и удалите параметр -m на вкладке Параметры запуска.
10. Затем перезапустите службу SQL Server (шаг 3) и проверьте, нормально ли работает SQL-сервер.

Если проблема не исчезнет, ​​проверьте, правильно ли настроены параметры запуска или сведения о пути. Если проблема все еще существует, убедитесь, что ваша учетная запись пользователя имеет необходимые разрешения для служб базы данных / отчетов, а затем проверьте, решена ли проблема.

Включите протокол TCP / IP в диспетчере конфигурации сервера.

Код ошибки 18456 на сервере SQL означает, что серверу не удалось аутентифицировать соединение, и это может произойти, если протокол TCP / IP, необходимый для доступа к базе данных в сети, отключен в диспетчере конфигурации сервера. В этом контексте включение TCP / IP в диспетчере конфигурации SQL Server может решить проблему.

1. Щелкните Windows и разверните Microsoft SQL Server, указав год, например, 2008 (вам может потребоваться немного прокрутить, чтобы найти параметр).
2. Теперь откройте диспетчер конфигурации SQL Server и нажмите Да (если получено приглашение UAC).
3. Затем разверните сетевую конфигурацию SQL Server и выберите Протоколы для (имя сервера / базы данных) на левой панели.
4. Теперь на правой панели дважды щелкните TCP / IP и выберите Да в раскрывающемся списке Включено.Откройте TCP / IP в протоколах конфигурации сети SQL Server
5. Затем примените изменения и щелкните Windows.Включить TCP / IP в SQL
6. Теперь введите «Службы», щелкните правой кнопкой мыши результат «Службы» и выберите «Запуск от имени администратора».Откройте службы в качестве администратора
7. Затем щелкните правой кнопкой мыши SQL Server (с именем сервера) и выберите «Перезагрузить».Перезапустите службу SQL в окне служб.
8. Теперь проверьте, очищен ли SQL-сервер от ошибки 18456.

Если это не помогло, убедитесь, что вы подключаетесь к правильному порту SQL-сервера (особенно, если вы используете сервер в многосерверной среде).

Измените режим аутентификации SQL Server

Сервер SQL может отображать ошибку 18456, если метод аутентификации сервера SQL не настроен должным образом (например: вы пытаетесь войти в систему с использованием аутентификации сервера SQL, тогда как сервер настроен на использование аутентификации Windows). В этом случае изменение метода аутентификации SQL-сервера может решить проблему. Прежде чем двигаться дальше, убедитесь, что для текущего пользователя включен статус входа в систему (например, SA).

1. В обозревателе объектов Microsoft SQL Server Management Studio щелкните правой кнопкой мыши свой сервер и выберите «Свойства».
2. Теперь на левой панели выберите Безопасность, а на правой панели выберите SQL Server и проверку подлинности Windows (или наоборот).Включить SQL Server и проверку подлинности Windows
3. Затем примените изменения и в обозревателе объектов щелкните правой кнопкой мыши сервер.
4. Теперь выберите «Перезагрузить» и после перезапуска проверьте, можете ли вы подключиться к базе данных без ошибки 18456.

Если вы не можете войти в SQL, вы можете установить MS Power Tools и выполнить следующую команду с повышенными привилегиями:

psexec.exe -i -s ssms.exe

После этого вы можете использовать учетную запись установки SQL, чтобы внести изменения, а также убедиться, что учетная запись SA не отключена:

Включите учетную запись SA и сбросьте пароль учетной записи

Если вы не можете подключиться к SQL Server, то включение учетной записи SA SQL-сервера и сброс его пароля может решить проблему.

1. Запустите Microsoft SQL Server Management Studio (возможно, вам придется использовать учетную запись администратора домена) и разверните Безопасность.
2. Затем дважды щелкните Logins и откройте SA.Откройте учетную запись SA в Microsoft SQL Server Management Studio.
3. Теперь введите новый пароль и подтвердите его (убедитесь, что вы используете надежный пароль).
4. Затем перейдите на вкладку Server Roles и убедитесь, что выбраны следующие роли: Public SysadminВключение ролей общедоступного сервера и сервера системного администратора для учетной записи SA
5. Теперь перейдите на вкладку «Статус» и на правой панели выберите «Включено» (в разделе «Вход»).Включение учетной записи SA в SQL
6. Затем примените изменения и нажмите кнопку Windows.
7. Теперь введите Services и щелкните его правой кнопкой мыши.
8. Затем выберите «Запуск от имени администратора» и перейдите к службе SQL Server.
9. Теперь щелкните его правой кнопкой мыши и выберите «Перезагрузить».
10. После перезапуска службы проверьте, устранена ли ошибка 18456 SQL-сервера.

Создайте новый логин и перезапустите службы Reporting Services

Если вы не можете использовать какую-либо учетную запись для подключения к базе данных, то создание новой учетной записи и перезапуск служб отчетов может решить проблему.

1. Запустите Microsoft SQL Server Management Studio и разверните вкладку «Безопасность».
2. Затем разверните Логины и щелкните его правой кнопкой мыши.
3. Теперь выберите «Новый вход» и введите учетные данные (в имени входа выберите учетную запись компьютера), если используется проверка подлинности SQL Server.Создать новый логин в SQL Server
4. Затем не забудьте снять флажок «Пользователь должен сменить пароль при следующем входе в систему» ​​и выберите базу данных.
5. Теперь перейдите на вкладку Server Roles и выберите роль Public.
6. Затем на вкладке «Сопоставление пользователей» обязательно выберите базу данных и выберите db_owner.Выберите db_owner для базы данных в SQL
7. Теперь примените ваши изменения и щелкните Windows.
8. Затем введите Services и щелкните правой кнопкой мыши результат Services. Затем выберите Запуск от имени администратора.
9. Теперь щелкните правой кнопкой мыши службу отчетов SQL Server и выберите «Перезагрузить».Перезапустите службу отчетов SQL Server.
10. Затем повторно подключитесь к базе данных и проверьте, очищен ли сервер SQL от ошибки 18456.

Если это так, убедитесь, что вы создали пользователя в BUILTIN administrators, и затем вы можете использовать этого пользователя для управления SQL Server. Если вы восстановили базу данных из резервной копии, будет лучше удалить и повторно добавить пользователей, чтобы удалить все старые записи пользователей. Если вы хотите запустить SQL-сервер от имени другого пользователя, введите Microsoft SQL Server в поиске Windows, Shift + щелкните правой кнопкой мыши на SQL Server и выберите «Запуск от имени другого пользователя». И последнее, но не менее важное: проверьте, решает ли проблема использование Azure Data Studio с сервером SQL.

Updated in July 2020 with a few new states

I think we’ve all dealt with error 18456, whether it be an application unable to access SQL Server, credentials changing over time, or a user who can’t type a password correctly. The trick to troubleshooting this error number is that the error message returned to the client or application trying to connect is intentionally vague (the error message is similar for most errors, and the state is always 1). In a few cases, some additional information is included, but for the most part several of these conditions appear the same to the end user. In order to figure out what is really going wrong, you need to have alternative access to the SQL Server and inspect the log for the true state in the error message. I helped our support team just today solve a client’s 18456 issues – once we tracked down the error log and saw that it was state 16, it was easy to determine that their login had been set up with a default database that had been detached long ago.

In SQL Server 2012, there is a new feature called «contained databases» – I’ve blogged about it here and here. With this feature comes a new layer of security that may creep onto your radar if you use this functionality: contained user authentication failures. There are a variety of things that can go wrong here. If you connect with a contained user but forget to specify a database name, SQL Server will attempt to authorize you as a SQL login, and you will fail with state 5 (if there is no SQL login with that name) or state 8 (if there is also a SQL login with the same name and the password doesn’t match). There is also a new state 65 which occurs if you have specified the correct username and contained database, but entered an incorrect password. The way that the authentication process works is, if SQL Server doesn’t find your user in the contained database you specified, it tries again at the server level, then gives up (it won’t go check all the other contained databases in case you match there – I hope you agree that this is a good thing). If you don’t specify a database in your connection string, then it won’t succeed unless – by coincidence – you have a contained user with the same username and password as a server-level login who also has access to your contained database. This is confusing and I strongly recommend against it.

When I see folks struggling with this problem, I almost always see the answer point to this old (and now horribly formatted) MSDN blog post (see this other version from MSDN), which has a very brief partial list and a lot of unanswered questions. A newer list appears here, with some useful info, but it is still incomplete.

So here is what I consider a more complete listing of all the various states for login failures. I included an instance of 18470 under state 1 for completeness.

State	Example / Description (note: the verbose message usually has [CLIENT: <IP>] suffix)
1	Error: 18470, Severity: 14, State: 1. Login failed for user ‘<x>’. Reason: The account is disabled.
State 1 now occurs when a login is disabled – but actually, the error in the log is 18470, not 18456 – because the login is disabled, it doesn’t get that far. See state 7.Prior to SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. 🙂
2	Error: 18456, Severity: 14, State: 2. Login failed for user ‘<x>’. Reason: Could not find a login matching the name provided.
The login (whether using SQL or Windows Authentication) does not exist. For Windows Auth, it likely means that the login hasn’t explicitly been given access to SQL Server – which may mean it is not a member of an appropriate domain group. It could also mean that you’ve created a server-level login, mapped a database user with a different name to that login, and are trying to connect using the user name, not the login name. This is the same as State 5, but State 2 indicates that the login attempt came from a remote machine.
5	Error: 18456, Severity: 14, State: 5. Login failed for user ‘<x>’. Reason: Could not find a login matching the name provided.
Like state 2, the login does not exist in SQL Server, but the login attempt came from the local machine. For both state 2 and 5, prior to SQL Server 2008, the reason was not included in the error log – just the login failed message. And starting in Denali, for both state 2 and 5, this error can happen if you specify the correct username and password for a contained database user, but the wrong (or no) database. Note that if you are trying to connect to a contained database using the connection dialog in SSMS, and you try to <Browse server…> for the database instead of typing the name explicitly, you will first receive a prompt «Browsing the available databases on the server requires connecting to the server. This may take a few moments. Would you like to continue?» If the SQL auth credentials do not also match a login at the server level, you will then receive an error message, because your contained user does not have access to master.sys.databases. The error message in the UI is, «Failed to connect to server <server>. (Microsoft.SqlServer.ConnectionInfo)Login failed for user ‘<x>’. (Microsoft SQL Server, Error: 18456).» The takeaway here: always specify the database name explicitly in the options tab of the connection dialog; do not use the browse feature.
6	Error: 18456, Severity: 14, State: 6. Login failed for user ‘<xy>’. Reason: Attempting to use an NT account name with SQL Server Authentication.
This means you tried to specify SQL authentication but entered a Windows-style login in the form of DomainUsername. Make sure you choose Windows Authentication (and you shouldn’t have to enter your domain / username when using Win Auth unless you are using runas /netonly to launch Management Studio). In SQL Server 2012 at least, you will only get state 6 if the domainusername format matches an actual domain and username that SQL Server recognizes. If the domain is invalid or if the username isn’t an actual Windows account in that domain, it will revert to state 5 (for local attempts) or state 2 (for remote attempts), since the login doesn’t exist.
7	Error: 18456, Severity: 14, State: 7. Login failed for user ‘<x>’. Reason: An error occurred while evaluating the password.
The login is disabled *and* the password is incorrect. This shows that password validation occurs first, since if the password is correct and the login is disabled, you get error 18470 (see state 1 above). It’s possible that your application is sending cached credentials and the password has been changed or reset in the meantime – you may try logging out and logging back in to refresh these credentials.
8	Error: 18456, Severity: 14, State: 8. Login failed for user ‘<x>’. Reason: Password did not match that for the login provided.
Probably the simplest of all: the password is incorrect (cASe sEnsiTiVitY catches a lot of folks here). Note that it will say «the login provided» even if you attempted to connect as a contained database user but forgot to specify a database, specified the wrong database, or typed the password incorrectly – unless it finds a match, SQL Server doesn’t have any idea you were attempting to use a contained database user. An interesting case here is Docker containers – docker run will allow you to spin up a container and specify an SA_PASSWORD with certain special characters, like $. However, you will never be able to connect to the container with that password. If you use non-alphanumerics, stick to slightly more benign characters like # and *.
9	Error: 18456, Severity: 14, State: 9. Login failed for user ‘<xy>’.
Like state 2, I have not seen this in the wild. It allegedly means that the password violated a password policy check, but I tried creating a login conforming to a weak password policy, strengthened the policy, and I could still log in fine. And obviously you can’t create a login with, or later set, a password that doesn’t meet the policy. Let me know if you’ve seen it.
10	Error: 18456, Severity: 14, State: 10. Login failed for user ‘<x>’.
This is a rather complicated variation on state 9; as KB #925744 states, this means that password checking could not be performed because the login is disabled or locked on the domain controller (note that if SQL Server does not start, it could be because the account that is locked or disabled is the SQL Server service account). No reason or additional information is provided in the «verbose» message in the error log.
11 12	Error: 18456, Severity: 14, State: 11. Login failed for user ‘<x>’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.  Error: 18456, Severity: 14, State: 12. Login failed for user ‘<x>’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
States 11 and 12 mean that SQL Server was able to authenticate you, but weren’t able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin). Finally, PSS has recently released more information about states 11 and 12; see this post for potential scenarios and solutions, and also see states 146-149 below for changes in SQL Server 2016.
13	Error: 18456, Severity: 14, State: 13. Login failed for user ‘<x>’. Reason: SQL Server service is paused. No new connections can be accepted at this time.
This state occurs when the SQL Server service has been paused (which you can do easily and even accidentally from the context menu in Object Explorer).
16	Error: 18456, Severity: 14, State: 16. Login failed for user ‘<x>’.  You may also see:  A connection was successfully established with the server, but then an error occurred during the pre-login handshake.
State 16, which only occurs prior to SQL Server 2008, means that the default database was inaccessible. This could be because the database has been removed, renamed, or is offline (it may be set to AutoClose). This state does not indicate a reason in the error log. In 2008 and beyond, this is reported as state 40 (see below), with a reason. In SQL Server 2005, this state may also be reported if the user’s default database is online but the database they explicitly requested is not available for the reasons stated above (also see state 27). If you get the pre-login handshake message, it may be because you’ve disabled SSL on the server.
18	Error: 18456, Severity: 14, State: 18. Login failed for user ‘<x>’.
Supposedly this indicates that the user needs to change their password. In SQL Server 2005, 2008 R2 and SQL Server 2012, I found this was raised as error 18488, not 18456; this is because for SQL logins the change password dialog just delays logging in, and is not actually a login failure. I suspect that, like state 16, this state will no longer appear in future versions of SQL Server.
23	Error: 18456, Severity: 14, State: 23. Login failed for user ‘<x>’. Reason: Access to server validation failed while revalidating the login on the connection.
There could be a few reasons for state 23. The most common one is that connections are being attempted while the service is being shut down. However if this error occurs and it is not surrounded in the log by messages about SQL Server shutting down, and there is no companion reason along with the message, I would look at KB #937745, which implies that this could be the result of an overloaded server that can’t service any additional logins because of connection pooling issues. Finally, if there *is* a companion reason, it may be the message indicated to the right, indicating that SQL Server was running as a valid domain account and, upon restarting, it can’t validate the account because the domain controller is offline or the account is locked or no longer valid. Try changing the service account to LocalSystem until you can sort out the domain issues.
27	Error: 18456, Severity: 14, State: 27. Login failed for user ‘<x>’.
State 27, like state 16, only occurs prior to SQL Server 2008. It means that the database specified in the connection string has been removed, renamed, or is offline (possibly due to AutoClose) – though in every case I tried, it was reported as state 16. This state does not indicate a reason in the error log. In 2008 and onward this is reported as state 38 (see below), with a reason.
28	Error: 18456, Severity: 14, State: 28. Login failed for user ‘<x>’.
I have not experienced this issue but I suspect it involves overloaded connection pooling and connection resets. I think you will only see state 28 prior to SQL Server 2008.
38	Error: 18456, Severity: 14, State: 38. Login failed for user ‘<x>’. Reason: Failed to open the database specified in the login properties.  or  Reason: Cannot open database «<database>» requested by the login. The login failed.
The database specified in the connection string, or selected in the Options > Connection Properties tab of the SSMS connection dialog, is no longer valid or online (it might be set to AutoClose or the user may simply not have permission). I came across this once when I typed <default> here instead of picking that option from the list. This is reported as state 27 or state 16 prior to SQL Server 2008.  Note that this could also be a symptom of an orphaned login. After establishing mirroring, Availability Groups, log shipping, etc. you may have created a new login or associated a user with a login on the primary database. The database-level user information gets replayed on the secondary servers, but the login information does not. Everything will work fine – until you have a failover. In this situation, you will need to synchronize the login and user information (for one example, see this script from the late Robert Davis).
40	Error: 18456, Severity: 14, State: 40. Login failed for user ‘<x>’. Reason: Failed to open the explicitly specified database.
Usually this means the login’s default database is offline (perhaps due to AutoClose) or no longer exists. Resolve by fixing the missing database, or changing the login’s default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated). This is reported as state 16 prior to SQL Server 2008.
46	Error: 18456, Severity: 14, State: 46. Login failed for user ‘<x>’. Reason: Failed to open the database configured in the login object while revalidating the login on the connection.
State 46 may occur when the login (or login mapping to the service account) does not have a valid database selected as their default database. (I am guessing here but I think this may occur when the login in question is attempting to perform log shipping. Again, just a guess based on the few conversations I discovered online.) It can also occur if the classifier function (Resource Governor) or a logon trigger refers to a database that is offline, no longer exists, or is set to AutoClose.
50	Error: 18456, Severity: 14, State: 50. Login failed for user ‘<x>’. Reason: Current collation did not match the database’s collation during connection reset.
As the message implies, this can occur if the default collation for the login is incompatible with the collation of their default database (or the database explicitly specified in the connection string). It can also happen if they are using a client tool like Management Studio which may, when they have been disconnected, try to connect to master upon reconnection instead of their default database.
51	Error: 18456, Severity: 14, State: 51. Login failed for user ‘<x>’. Reason: Failed to send an environment change notification to a log shipping partner node while revalidating the login.
Like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached, or that the domain account could not authenticate against the log shipping partner, or that the log shipping partner was down. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts, and validating that the partner instance is accessible, as well as the database that is being requested in the connection string and the default database of the login. Note that this could be trigged by the failover partner connection string attribute, and that the database may no longer exist or may be offline, single user, etc.
56	Error: 18456, Severity: 14, State: 56. Login failed for user ‘<x>’. Reason: Failed attempted retry of a process token validation.
State 56 is not very common – again, like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts.
58	Error: 18456, Severity: 14, State: 58. Login failed for user ‘<x>’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication. It can also occur when SIDs do not match (in which case the error text might be slightly different).
62	Error: 18456, Severity: 14, State: 62. Login failed for user ‘<x>’.
State 62 occurs when a Windows Authentication account tries to access a contained database, and the contained database exists, but the SIDs do not match.
65	Error: 18456, Severity: 14, State: 65. Login failed for user ‘<x>’. Reason: Password did not match that for the user provided. [Database: ‘<x>’]
Contained user exists, the database is correct, but the password is invalid. This can also happen if you use a SQL login to connect to a contained database that has a contained user with the same name but a different password (one of several reasons this is not recommended).
102 103 … 110 111	Error: 18456, Severity: 14, State: 102. Error: 18456, Severity: 14, State: 103. Error: 18456, Severity: 14, State: 104. Error: 18456, Severity: 14, State: 105. Error: 18456, Severity: 14, State: 106. Error: 18456, Severity: 14, State: 107. Error: 18456, Severity: 14, State: 108. Error: 18456, Severity: 14, State: 109. Error: 18456, Severity: 14, State: 110. Error: 18456, Severity: 14, State: 111.
Documented by Microsoft as Azure Active Directory login failures.
122 123 124	Error: 18456, Severity: 14, State: 122. Error: 18456, Severity: 14, State: 123. Error: 18456, Severity: 14, State: 124.
According to Microsoft, these indicate a blank or missing username and/or password.
126	Error: 18456, Severity: 14, State: 126.
The docs say «Database requested by user does not exist.» But it’s not clear why you would get 126 instead of, say, 38 or 40.
132 133	Error: 18456, Severity: 14, State: 132. Error: 18456, Severity: 14, State: 133.
Documented by paschott and by Microsoft as Azure Active Directory login failures.
146 147 148 149	Error: 18456, Severity: 14, State: 146. Login failed for user ‘<Windows auth login>’. Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.  Error: 18456, Severity: 14, State: 147. Login failed for user ‘<SQL auth login>’. Reason: Login-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.  Error: 18456, Severity: 14, State: 148. Login failed for user ‘<Windows auth login>’. Reason: Token-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission.  Error: 18456, Severity: 14, State: 149. Login failed for user ‘<SQL auth login>’. Reason: Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission.
These states replace states 11 and 12 above, but only in SQL Server 2016 or better. The goal was to make the actual underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving any further info to the user trying to log in). For more details, see the latter part of this post.

I am sure I missed some, but I hope that is a helpful summary of most of the 18456 errors you are likely to come across. Please let me know if you spot any inaccuracies or if you know of any states (or reasons) that I missed.

If you are using contained databases, there will be a little extra complication in solving login failures, especially if you try to create contained users with the same name as server-level logins. This is a ball of wax you just probably don’t want to get into…

Thanks to Jonathan Kehayias (blog | twitter), Bob Ward (CSS blog | twitter), and Rick Byham for input and sanity checking.

Ошибка SQL Server 18456 не удается подключиться-ошибка входа в систему SQL Server 2017 18456

Ошибка ODBC. SQLSTATE: 28000 Номер ошибки: 18456

При использовании внешнего источника данных в 1С 8.3 возникает сообщение с кодом 18456.

Чаще всего такое бывает при переходе на 8.3 — данный код говорит об ошибке авторизации в момент подключения.

Даже, если ранее этот код работал на 8.2, здесь он не работоспособен.

Способы решения проблемы:

1. Проверка написания пользователя, пароля
2. Явное указание при соединении имени и пароля, связано скорее всего с тем, что в платформе появилась возможность хранить настройки соединения для каждого пользователя

Верная инициализация соединения:

Настройки пользователя в управляемом приложении:

Все функции/ Стандартные/Управление внешними источниками данных

Далее можно задать общие настройки параметры соединения или индивидуальные для пользователя

Пример задания общих параметров:

Реклама — величайшее искусство XX века.

Источник

Login failed for [SQLSTATE 28000] (Error 18456) the step failed SQL Server

Table of Contents

Details of Error:

Executed as user: DomainLoginName. Login failed for user ‘DomainLoginName’. [SQLSTATE 28000] (Error 18456). The step failed.

or below ODBC Data Source SQL Server Connection – Login Failed For User

Connection failed:
SQLState: ‘28000’
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘UserName’.

Cause of Error:

This error is encountered when SQL Agent Service does not have permissions required to run the job. Or When we try ODBC Data Source SQL Server Connection Login Failed message when Login used for connection does not have permission needed.

Resolution:

First if this message is caused by SQL Agent Job connection then we will need to check the source SQL server agent service account name from Services MMC or Server manager or SQL Server Cofiguration Manager in Services-> Go to the SQL Server Agent Service –> properties –> Check the logon tab where we can find the account name.

Now check and add the login we found above or if the its caused by ODBC Data Source SQL Server Connection then add the Login used in ODBC to the SQL server instance by checking in Security tab –>logins with necessary permission on your target machine where its trying to connect or run the job on. This can be same instance or different instance on same or some other server.

Источник

‘SQL error 1045 sqlstate 28000’ – Here are the steps to fix it

Server errors are annoying, especially when they are cryptic like “sql error 1045 sqlstate 28000″.

The message contains some error codes.

But, what’s the real problem here?

At Bobcares, we help website owners resolve complex errors like “sql error 1045 sqlstate 28000“ as part of our Outsourced Hosting Support services.

Today, let’s discuss the top 5 reasons for this error and we fix them.

‘SQL error 1045 sqlstate 28000’ – What this means?

Before we move on to the reasons for this error, let’s first get an idea of this error.

Website owners face this error when querying data from the SQL server.

For instance, the complete error message looks like this:

This error shows that the MySQL server disallows the user to connect to it from localhost or 127.0.0.1.

‘SQL error 1045 sqlstate 28000’ – Causes & Fixes

In our experience managing servers, we’ll see the major causes of this error and how our Dedicated Support Engineers fix it.

1) Typo in username and password

This is the most common reason for the error “sql error 1045 sqlstate 28000″.

Users may type wrong username and password while connecting to the database.

Therefore, SQL server can’t identify the authenticity of the account.

Solution

In such cases, we help website owners reset the database user password.

For example, in cPanel servers, we reset the database user password from Databases > Mysql databases > Current Users.

Mysql databases option in cPanel

Also, for database driven websites like WordPress, Magento, etc., we update the new database username and password in the website configuration files.

For example, in the Magento application, we update the database name, username and password in the “app/etc/local.xml” file.

In some cases, website owners get errors like this:

This is because, the root session don’t know the password of mysql root user.

And, it can be probably a mis-typed password during the initial setup.

Here, our Hosting Engineers reset the admin/root password after starting the MySQL in safe mode.

For example, we use the below command to reset the root password in safe mode.

2) Accessing from wrong host

MySQL uses host based restrictions for user access to enhance security.

In other words, MySQL allows user access only from hosts defined in the MySQL user table.

So, any access from remote machines whose hostnames are not defined in this user table will bounce with the error “sql error 1045 sqlstate 28000”

Solution

First, our Hosting Engineers check whether the remote host is allowed in the MySQL user table.

If not, we add the hostname of the remote machine in the MySQL user table.

For instance, we use the below command to add a host in the MySQL user table.

Here, hostname is the hostname of the remote machine, and username is the MySQL user.

We’ve seen cases where server owners use wildcards(%) in host field which gives universal access to this user.

But, this is not a good practice as there is a security risk that user can access the database from any hosts.

In addition to that, due to security concerns, we always disable accessing root user from remote machines.

[You don’t have to be a MySQL expert to keep your websites online. We have experienced MySQL admins available 24/7.]

3) User doesn’t exist

Similarly, this error “sql error 1045 sqlstate 28000” occurs when the user trying to access the database doesn’t exist on the MySQL server.

For example, if you access MySQL using a testuser that doesn’t exist, you can see the following error.

Solution

In such cases, our Support Engineers first check whether the user exists in the MySQL user table.

If not, we check the user’s requirement to access the database and if it is valid, we create a user with that username.

4) Existence of Anonymous users

Website owners face this error when there are anonymous MySQL users like ”@localhost or ”@127.0.0.1.

That is, when a client tries to connect to the database, the MySQL server looks through the rows in the user table in a sorted order.

The server uses the first row that matches the most specific username and hostname.

So, here the anonymous user (‘ ‘@localhost) precedes any other users like ‘user’@localhost when connecting from localhost.

And, use the anonymous user password to connect to the server.

Finally, the result is “sql error 1045 sqlstate 28000“.

Solution

Our Hosting Engineers check the MySQL user table and remove the anonymous user account.

For example, we use the below command to remove the anonymous user from MySQL.

5) Insufficient privileges

Likewise, insufficient privileges for the user to access the database can also result in this error.

Solution

In such cases, we assign proper access rights for the user to access the database.

For example, in cPanel servers, we manage user privileges from:

cPanel > Mysql databases > Current databases > Privileged users > Click on the database user.

Granting user privileges in cPanel

[Struggling with database user permissions and privileges? Our MySQL Experts are here for your help.]

Conclusion

In short, “sql error 1045 sqlstate 28000” may occur due to insufficient database privileges, wrong username or password, etc. Today we’ve discussed the top 5 reasons for this error and how our Dedicated Support Engineers fix it.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

Excellent Article very exciting and well presented.

Источник

Sql error sqlstate 28000

Question

The common causes for login failed are wrong password or a bad database name. Check the SQL Server error log, which will provide more information about the specific cause. Details of authentication errors are not returned to the client for security reasons.

Also, don’t use a sysadmin rule member account for routine application access. Instead, use one with only the permissions needed by your app.

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

• Proposed as answer by Will_Kong Microsoft contingent staff Monday, July 10, 2017 5:11 AM

Sql server error log «Login failed for user «sa» Reason: Password did not match that for the login provided»

According to the error log, you may change the password of the user «sa» in the SQL Server. Or you could do as Dan said, create a new account with some permissions for your applications. Or it is probably the problem of string password, so you may refer to https://docs.microsoft.com/en-us/sql/relational-databases/security/strong-passwords for assistance.

H ope these are helpful to you.

MSDN Community Support
Please remember to click «Mark as Answer» the responses that resolved your issue, and to click «Unmark as Answer» if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Edited by Will_Kong Microsoft contingent staff Monday, July 10, 2017 5:12 AM

Sql server error log «Login failed for user «sa» Reason: Password did not match that for the login provided» i dont understant why it did not mach if i have another database on the same app working perfectly.

Maybe that database is on a different instance with a different password for sa?

In any case, you should absolutely not use sa in your application. That’s a complete no-no. You should use a login which has been granted the permissions needed, which at most is membership in db_datareader and db_datawriter. (But even better is that the application uses stored procedures, in which case permission to execute the stored procedures is all you need.)

Also, if this is a plain Windows client, the application should not use its own login, but the login would depend on the user, preferrably through Windows authenticationl.

Источник

Sql error sqlstate 28000

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

We have an application that connects to SQL 2005 thru ODBC with the following string:

When our administrators login on the workstation the application works well. But when ordinary users login they get the following error. (We only have 1 domain)

SQL Server Error: 18452

Login failed for user «. The user is not associated with a trusted SQL Server connection.

Then the standard SQL Server Login window pops up asking for the Login ID and Password. On the window the ‘Use Trusted Connection’ is checked and the name of the user on the workstation appears on the LoginID. What we do is uncheck the ‘Use Trusted Connection’ then login using the credentials above.

Need help on this one please. Thanks.

Answers

Try sp_adduser to make sure that the login is identified as trusted by SQL Server.

Checked the setting, it is on SQL Server ad Windows auth mode.

Try sp_adduser to make sure that the login is identified as trusted by SQL Server.

I am facing Same problem and it is as follows:-

There are two servers ‘A’ and ‘B’. At server ‘A’, there is installed Window server at server ‘B”, there

is installed Window Server2000. And both servers have SQL SERVER 2000. I have created link server from A to B. I have to insert data into table of server ‘B’ from server ‘A’. When I used directly insert command like

Insert into [server name (B)]. [Database]. [Owner] .[ table name]

Select * from [server name (A)]. [Database]. [Owner] .[ table name]

The data is inserted successfully.

But when I created trigger (for insertion) on table of server (A) (because I want as there is insertion in the table of server( A), rest

Records that are not in the table of server (B ) , are automatically inserted into the table of server(B)).

The Insertion is not complete and there is shown system message which is as follows:-

“ Server: Msg 7391, Level 16, State 1, Procedure rms_trn_con_details_insertion , Line 6

The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.

[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator . ] ”

I have searched in Google , I found that there are problems in heterogeneous system.

But till today, I have no solution for it. Temporary, I find solution through Job Scheduling.

Try to find out solution for this problem.

Regards,

Nikhil Chaturvedi

Источник

Details of Error:

Executed as user: DomainLoginName. Login failed for user 'DomainLoginName'. [SQLSTATE 28000] (Error 18456). The step failed.

or below ODBC Data Source SQL Server Connection – Login Failed For User

Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'UserName'.

Cause of Error:

This error is encountered when SQL Agent Service does not have permissions required to run the job. Or When we try ODBC Data Source SQL Server Connection Login Failed message when Login used for connection does not have permission needed.

Resolution:

First if this message is caused by SQL Agent Job connection then we will need to check the source SQL server agent service account name from Services MMC or Server manager or SQL Server Cofiguration Manager in Services-> Go to the SQL Server Agent Service –> properties –> Check the logon tab where we can find the account name.

Now check and add the login we found above or if the its caused by ODBC Data Source SQL Server Connection then add the Login used in ODBC to the SQL server instance by checking in Security tab –>logins with necessary permission on your target machine where its trying to connect or run the job on. This can be same instance or different instance on same or some other server.

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit «Cookie Settings» to provide a controlled consent.