Эта ошибка возникает для следующих учетных записей YourDomaninName ServerName $, когда вы переименовываете НОВЫЙ построенный сервер со старым именем сервера, то есть при обновлении сервера. Чтобы решить эту проблему, вам нужно будет удалить пользователя во всех сопоставленных базах данных, отказаться от входа в систему, создать имя входа и, наконец, снова предоставить разрешение базам данных.
Шаг 1. Создайте сценарий «Создать логин» (вы должны использовать сценарий, поскольку такие пользователи недоступны для поиска)
CREATE LOGIN [YourDomaninNameServerName$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
Шаг 2. Запишите базы данных, к которым у пользователя есть доступ, и предоставленные разрешения, затем удалите пользователя из каждой базы данных.
USE MyDatabase;
IF EXISTS (SELECT * FROM sys.DATABASE_principals WHERE name = N'YourDomaninNameServerName$')
DROP USER [YourDomaninNameServerName$];
Шаг 3. Сбросьте логин с сервера
USE [Master];
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'YourDomaninNameServerName$')
DROP LOGIN [YourDomaninNameServerName$]
Шаг 4. Создайте логин с помощью созданного ранее скрипта.
CREATE LOGIN [YourDomaninNameServerName$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
Шаг 5. Предоставьте разрешение каждой базе данных из примечаний, сделанных на шаге 2. Этот шаг можно выполнить вручную через SSMS.
- Remove From My Forums
-
Question
-
Hi
I’m having a problem with SQL Agent. I have W2008 x64 and SQL Server Std 2008. I have two instances installed in the server. In the second instance I decided to restore all databases from another and totally different server. Before the retores SQL Agent
was working fine. After the restore it didnt worked giving error message: «Login failed for user ‘SERVERNAMEdbsaccount’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.»The same account is used to start SQL server and works without problem…
I google and found that I have to change @@SERVERNAME , I did it and same issue…
another thing, in the original server I used Database Mail , I configured it in the restored server and same issue…
another thing , when I’m trying to change credentials in Confi Manager in SQL Agent , i receive «The process terminated unexpectedly. [0x8007042b]»
so… any other suggestion and help will be appreciated…
bto
Answers
-
-
Marked as answer by
Sunday, May 1, 2011 11:22 AM
-
Marked as answer by
- Remove From My Forums
-
Question
-
Hi
I’m having a problem with SQL Agent. I have W2008 x64 and SQL Server Std 2008. I have two instances installed in the server. In the second instance I decided to restore all databases from another and totally different server. Before the retores SQL Agent
was working fine. After the restore it didnt worked giving error message: «Login failed for user ‘SERVERNAMEdbsaccount’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.»The same account is used to start SQL server and works without problem…
I google and found that I have to change @@SERVERNAME , I did it and same issue…
another thing, in the original server I used Database Mail , I configured it in the restored server and same issue…
another thing , when I’m trying to change credentials in Confi Manager in SQL Agent , i receive «The process terminated unexpectedly. [0x8007042b]»
so… any other suggestion and help will be appreciated…
bto
Answers
-
-
Marked as answer by
Sunday, May 1, 2011 11:22 AM
-
Marked as answer by
All,
I have recently brought up a Windows 2008, SQL 2008 cluster and am seeing sporadic occurrences of this error: «Token-based server access validation failed with an infrastructure error». Thus far, I have seen it in three different scenarios:
1) When a user was attempting to use TSM (Tivoli Storage Manager) locally on the machine
2) When a service was attempting to connect from a 2008 server
3) When a user was attempting to connect from an XP desktop
#1 and #2, were both part of NT groups which had the proper access to the SQL Server (#1 had sysadmin). #3, i’m not as concerned about simply because the two users (out of 10) that are failing, are actually not part of the group, so obviously they shouldn’t be able to connect. What I find odd about this though is that (supposedly) they were able to connect to the old server … what’s even more odd though is that, why am I seeing this particular error when a user doesn’t have access vs. seeing the standard «login failed for user X»? I just find that rather mysterious. I am getting the users into the group now to hopefully resolve #3. More than anything I’m just curious to as why this particular error is raised.
Regardless, where I’m at a loss is that from what I’ve read online thus far about this error is that it is possibly one of three issues:
— The group is not sysadmin
— The user/group is part of to many other AD groups
— User Access Control
So I attempted to recreate the scenario by removing my groups sysadmin rights. I was not able to recreate the error as I was able to successfully connect without error. To debunk the second scenario, the user who was trying to use TSM that had the proper rights (had sysadmin actually), is only part of 3 NT groups. This leaves possibly user access control, which somewhat makes sense. My first two scenarios where failure has been experienced both had a source of running from Windows 2008. If this is the case, what is the resolution? How can a service do a «run as administrator» to resolve the UAC issue? Either way, I don’t know If I can buy that this is the problem … why is it that some groups work and some do not? Is it based on something with their NT group and it’s attributes, or the calling operating system/application?
Either way, I feel no closer to finding resolution to this problem. I am curious to as if anyone has additional insight to this matter.
Thanks in advance
-
Edited by
Monday, September 21, 2009 7:23 PM
enhanced clarification
All,
I have recently brought up a Windows 2008, SQL 2008 cluster and am seeing sporadic occurrences of this error: «Token-based server access validation failed with an infrastructure error». Thus far, I have seen it in three different scenarios:
1) When a user was attempting to use TSM (Tivoli Storage Manager) locally on the machine
2) When a service was attempting to connect from a 2008 server
3) When a user was attempting to connect from an XP desktop
#1 and #2, were both part of NT groups which had the proper access to the SQL Server (#1 had sysadmin). #3, i’m not as concerned about simply because the two users (out of 10) that are failing, are actually not part of the group, so obviously they shouldn’t be able to connect. What I find odd about this though is that (supposedly) they were able to connect to the old server … what’s even more odd though is that, why am I seeing this particular error when a user doesn’t have access vs. seeing the standard «login failed for user X»? I just find that rather mysterious. I am getting the users into the group now to hopefully resolve #3. More than anything I’m just curious to as why this particular error is raised.
Regardless, where I’m at a loss is that from what I’ve read online thus far about this error is that it is possibly one of three issues:
— The group is not sysadmin
— The user/group is part of to many other AD groups
— User Access Control
So I attempted to recreate the scenario by removing my groups sysadmin rights. I was not able to recreate the error as I was able to successfully connect without error. To debunk the second scenario, the user who was trying to use TSM that had the proper rights (had sysadmin actually), is only part of 3 NT groups. This leaves possibly user access control, which somewhat makes sense. My first two scenarios where failure has been experienced both had a source of running from Windows 2008. If this is the case, what is the resolution? How can a service do a «run as administrator» to resolve the UAC issue? Either way, I don’t know If I can buy that this is the problem … why is it that some groups work and some do not? Is it based on something with their NT group and it’s attributes, or the calling operating system/application?
Either way, I feel no closer to finding resolution to this problem. I am curious to as if anyone has additional insight to this matter.
Thanks in advance
-
Edited by
Monday, September 21, 2009 7:23 PM
enhanced clarification
Вопрос:
У меня есть новый экземпляр SQL Server 2008 RS Standard Edition. Я включил Mixed-Mode Authentication, но я не могу заставить Windows Authentication работать. Я получаю сообщение об ошибке: 18456, Login Ошибка для пользователя “MyDomainjreddy”. Я могу войти в систему с учетной записью sa, чтобы я мог вносить изменения, но я не уверен, что изменить. Любая помощь будет принята с благодарностью.
Я проверил журнал событий и обнаружил эту ошибку, связанную с неудачей входа:
Причина: проверка доступа к серверу на основе токена завершилась неудачей с ошибкой инфраструктуры.
Лучший ответ:
Очевидно, проблема была в UAC на сервере Windows 2008. Наши сетевые ребята меняли некоторые групповые политики и включили UAC для всех наших серверов. Таким образом, это не имело ничего общего с переключением выпуска SQL Server (Ent → Standard). Отключение UAC исправило проблему, с которой я столкнулся.
Ответ №1
При включении проверки подлинности Windows для Sql Server он не будет просто автоматически разрешать запросы аутентификации в Active Directory. Вам по-прежнему приходится настраивать учетную запись для каждого пользователя и назначать разрешения сервера Sql для этой учетной записи для отдельных баз данных (часто это делается через роли). Хорошей новостью является то, что вы можете часто делать это для всей группы Active Directory за раз; например, необычно разрешающая база данных может разрешить всем MyDomainUsers входить в систему и использовать определенную базу данных.
- Remove From My Forums
-
Общие обсуждения
-
Сервер с SQL выведен из домена. Службы SQL перенастроены на запуск под локальными учетками.
При попытке подключения через SQL SMS компонент Database engine не видит локального сервера и не может подключится. (Ошибка входа пользователя SERVERАдминистратор (Microsoft SQL server, ошибка 18456)
SQL авторизация насколько я помню не настраивалась
-
Изменен тип
Иван ПродановMicrosoft contingent staff, Moderator
15 марта 2013 г. 9:49
Отсутствие активности на протяжении длительного периода времени.
-
Изменен тип
Все ответы
-
А у вас случайно экземпляр SQL Server-а (SQL Server instance) не именованный? Проверьте на всякий случай (это хорошо видно по названию самой службы). Так же имя экземпляра, насколько я помню, можно подсмотреть в ветке реестра «HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft
SQL ServerInstance Names».-
Изменено
Evgenii Alekseev
18 декабря 2012 г. 19:24
-
Изменено
-
нет, обычное название службы SQL Server (MSSQLSERVER), и в HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names ничего не видно
-
Сервер с SQL выведен из домена. Службы SQL перенастроены на запуск под локальными учетками.
При попытке подключения через SQL SMS компонент Database engine не видит локального сервера и не может подключится. (Ошибка входа пользователя SERVERАдминистратор (Microsoft SQL server, ошибка 18456)
SQL авторизация насколько я помню не настраивалась
Имея администраторские права на сервере, где установлен сиквел, вы можете включить себя в серверную роль сисадминов…
для этого достаточно запустить sql server в однопользовательском режиме и уже тут вы спокойно можете управлять сиквелом…в том числе и добавить смешанную авторизацию и создать сиквельную УЗ
http://www.t-sql.ru
-
Запускаю SQL Server в однопользовательском режиме командой
C:UsersIvan.SERVER>»C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLS
ERVERMSSQLBinnsqlservr.exe» /c /m»SQLCMD»
2012-12-25 00:29:41.08 Server Microsoft SQL Server 2008 R2 (RTM) — 10.50.16
17.0 (X64)
Apr 22 2011 19:23:43
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service P
ack 1)2012-12-25 00:29:41.09 Server (c) Microsoft Corporation.
2012-12-25 00:29:41.09 Server All rights reserved.
2012-12-25 00:29:41.09 Server Server process ID is 5304.
2012-12-25 00:29:41.09 Server System Manufacturer: ‘DEPO Computers’, System
Model: ‘X6DH8’.
2012-12-25 00:29:41.09 Server Authentication mode is MIXED.
2012-12-25 00:29:41.09 Server Logging SQL Server messages in file ‘C:Progr
am FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG’.
2012-12-25 00:29:41.09 Server This instance of SQL Server last reported usi
ng a process ID of 6540 at 25.12.2012 0:29:08 (local) 24.12.2012 20:29:08 (UTC).
This is an informational message only; no user action is required.
2012-12-25 00:29:41.09 Server Registry startup parameters:
-d C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLD
ATAmaster.mdf
-e C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLL
ogERRORLOG
-l C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLD
ATAmastlog.ldf
2012-12-25 00:29:41.09 Server Command Line Startup Parameters:
/c
/m SQLCMD
2012-12-25 00:29:41.12 Сервер SQL Server is starting at normal priority bas
e (=7). This is an informational message only. No user action is required.
2012-12-25 00:29:41.12 Сервер Detected 4 CPUs. This is an informational mes
sage; no user action is required.
2012-12-25 00:29:41.22 Сервер Perfmon counters for resource governor pools
and groups failed to initialize and are disabled.
2012-12-25 00:29:41.23 Сервер Using dynamic lock allocation. Initial alloc
ation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an infor
mational message only. No user action is required.
2012-12-25 00:29:41.26 Сервер Node configuration: node 0: CPU mask: 0x00000
0000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a des
cription of the NUMA configuration for this computer. This is an informational m
essage only. No user action is required.
2012-12-25 00:29:41.28 Сервер Database Mirroring Transport is disabled in t
he endpoint configuration.
2012-12-25 00:29:41.29 spid6s Warning ******************
2012-12-25 00:29:41.29 spid6s SQL Server started in single-user mode. This
an informational message only. No user action is required.
2012-12-25 00:29:41.29 spid6s Starting up database ‘master’.
2012-12-25 00:29:41.37 spid6s 1 transactions rolled forward in database ‘ma
ster’ (1). This is an informational message only. No user action is required.
2012-12-25 00:29:41.38 spid6s 0 transactions rolled back in database ‘maste
r’ (1). This is an informational message only. No user action is required.
2012-12-25 00:29:41.38 spid6s Recovery is writing a checkpoint in database
‘master’ (1). This is an informational message only. No user action is required.2012-12-25 00:29:41.48 spid6s FILESTREAM: effective level = 0, configured l
evel = 0, file system access share name = ‘MSSQLSERVER’.
2012-12-25 00:29:41.56 spid6s SQL Trace ID 1 was started by login «sa».
2012-12-25 00:29:41.58 spid6s Starting up database ‘mssqlsystemresource’.
2012-12-25 00:29:41.60 spid6s The resource database build version is 10.50.
1617. This is an informational message only. No user action is required.
2012-12-25 00:29:41.84 spid6s Server name is ‘SERVER’. This is an inform
ational message only. No user action is required.
2012-12-25 00:29:41.84 spid9s Starting up database ‘model’.
2012-12-25 00:29:41.95 spid9s Clearing tempdb database.
2012-12-25 00:29:42.01 spid11s A new instance of the full-text filter daemon
host process has been successfully started.
2012-12-25 00:29:42.04 spid12s Starting up database ‘msdb’.
2012-12-25 00:29:42.04 spid11s Starting up database ‘ReportServer’.
2012-12-25 00:29:42.04 spid13s Starting up database ‘ReportServerTempDB’.
2012-12-25 00:29:42.04 spid14s Starting up database ‘TEST_M’.
2012-12-25 00:29:42.04 spid15s Starting up database ‘2TEST5’.
2012-12-25 00:29:42.04 spid16s Starting up database ‘SISOPR’.
2012-12-25 00:29:42.07 Сервер A self-generated certificate was successfully
loaded for encryption.
2012-12-25 00:29:42.08 Сервер Server is listening on [ ‘any’ <ipv6> 1433].
2012-12-25 00:29:42.08 Сервер Server is listening on [ ‘any’ <ipv4> 1433].
2012-12-25 00:29:42.08 Сервер Server local connection provider is ready to
accept connection on [ \.pipeSQLLocalMSSQLSERVER ].
2012-12-25 00:29:42.08 Сервер Server local connection provider is ready to
accept connection on [ \.pipesqlquery ].
2012-12-25 00:29:42.08 Сервер Server is listening on [ ::1 <ipv6> 1434].
2012-12-25 00:29:42.08 Сервер Server is listening on [ 127.0.0.1 <ipv4> 143
4].
2012-12-25 00:29:42.08 Сервер Dedicated admin connection support was establ
ished for listening locally on port 1434.
2012-12-25 00:29:42.09 Сервер The SQL Server Network Interface library coul
d not register the Service Principal Name (SPN) for the SQL Server service. Erro
r: 0x54b, state: 3. Failure to register an SPN may cause integrated authenticati
on to fall back to NTLM instead of Kerberos. This is an informational message. F
urther action is only required if Kerberos authentication is required by authent
ication policies.
2012-12-25 00:29:42.09 Сервер SQL Server is now ready for client connection
s. This is an informational message; no user action is required.
2012-12-25 00:29:42.11 spid17s Starting up database ‘datas’.
2012-12-25 00:29:42.11 spid18s Starting up database ‘datas1’.
2012-12-25 00:29:44.67 spid9s Starting up database ‘tempdb’.
2012-12-25 00:29:44.94 spid6s Recovery is complete. This is an informationa
l message only. No user action is required.Далее при попытке подключится через Management Studio:
2012-12-25 00:30:03.87 Вход Ошибка: 18456, серьезность: 14, состояние: 11
.
2012-12-25 00:30:03.87 Вход Login failed for user ‘SERVERsqlrun’. При
чина: проверка доступа к серверу на основе токена завершилась ошибкой инфраструк
туры. Проверьте на наличие предыдущих ошибок. [КЛИЕНТ: <local machine>]sqlrun — это учетка под которой стартуют службы SQL. При запуске management studio видимо служба пытается запуститься
-
То есть служба SQL стартует успешно?
Подключаетесь Вы локально ? local server или 127.0.0.1
Конфигурацию TCP/IP смотрели ?
LEXX