I would appreciate help with the following issue:
I have created a local group in our SQL 2008 server and added two Windows user accounts "DOMAIN\UserName" I then added the local group to the database and granted read only access.
The users are trying to link tables using MS Access using and ODBC connection and getting the following error. Users are not system administrators.
D开发者_如何学运维ate 6/30/2010 1:01:54 PM Log SQL Server (Current - 6/30/2010 1:10:00 PM) Source Logon Message Login failed for user 'DOMAIN\UserName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 999.99.9.99] Date 6/30/2010 1:01:54 PM Log SQL Server (Current - 7/1/2010 8:12:00 AM)
Source Logon
Message Error: 18456, Severity: 14, State: 11.
Database Server: windows Server 2008 R2 Enterprise System type: 64-bit Operating System SQL Server 2008
Thank you for your response.
I fount the cause of the problem. I just whished the MS error message in the logs could be more clear. The remote user with logging access problems was also part of a group that was denied access to our database. I completely overlooked this configuration. I then created a different group and granted access to the user. I also granted access explicitly and in both instances the users was denied access. Once I remove the group that denied access it all worked fine.
p.reinoso
It's possible there may be an SPN missing for the service account you're using to connect to the SQL server. If for example you're trying to connect to sqlsrv1 from websrv1 using account svcacct1 you could (using a domain admin account) add an SPN to ensure that AD allows authentication from that machine using that account.
setspn -A MSSQLSvc/websrv1.domain.local:1433 svcacct1
Now when you try to connect to sqlsrv1 (to authenticate with MS SQL) using the svcacct1 from websrv1, the credentials should pass through and allow authentication on the server without generating this error.
This sounds like an issue with Kerberos/windows authentication with your AD server(s). I would check with your AD admin to see if they can help you troubleshoot this one.
My issue was just a little different than Pedro's (p.reinoso). In my set up a Windows Domain Group was set up to manage DBA Admins' access to the SQL servers. An individual was a member of the MyDomain\SQL_Admin group but their personal SQL Login on one of the servers had been disabled. When we deleted the SQL Login the individual was able to connect based on the group credentials.
I had this issue and it was because the user was part of the parent domain and I had only given a group they were part of access (ex. PARENT\someGroup). So it turns out that group is a "Domain Local" group, as opposed to a global / universal group. My DB in my child domain couldn't read the tokens for the domain local group in the parent domain.
I had the same issue:
Login failed for user Token-based server access validation failed with an infrastructure error
I had simply forgotten to give Permissions to read/write the database to that user. We create a user for each website on our server, and that lower-permissions user needs read access to the web files, its own Application Pool, and reader/writer access to their particular db in Sql Server. I forgot that last step and got this obscure message in the logs. Adding those permissions solved it.
This error is coming up for the following kind of account/s YourDomaninName\ServerName$ when you rename a NEW built server with an old server name, i.e. server upgrade. To resolve the issue you will need to essentially drop the user in all mapped databases, drop the login, create the login and finally grant permission to the databases again.
Step 1. Script out Create Login (you must use the script as this kind of users are not searchable)
CREATE LOGIN [YourDomaninName\ServerName$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
Step 2. Take a note of the databases the user has access to and the granted permissions then, drop the user from each database.
USE MyDatabase;
IF EXISTS (SELECT * FROM sys.DATABASE_principals WHERE name = N'YourDomaninName\ServerName$')
DROP USER [YourDomaninName\ServerName$];
Step 3. Drop the login from the server
USE [Master];
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'YourDomaninName\ServerName$')
DROP LOGIN [YourDomaninName\ServerName$]
Step 4. Create the login with the script created earlier
CREATE LOGIN [YourDomaninName\ServerName$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];
Step 5. Grant permission to each databases from the notes taken on step 2. This step can be done manually thru SSMS.
精彩评论