2

I am trying to set up an SQL Server Login and User using Windows Authentication.

What I have done so far is as follows:

  1. Added a new non-administrator account to my PC called "User1". The PC is called "DESKTOP" so the full username is "DESKTOP\User1"

  2. Created an SQL Server login for this user using the "FROM WINDOWS" option to link the SQL Login to my PC login. The user is called e.g. "DESKTOP\User1."

  3. Created a database level user for the SQL Server login using the "FOR LOGIN" option to link the user to the SQL Server login.

  4. Verified that the new database user is not a member of the "SysAdmins" group and that the login is enabled and login access is allowed.

  5. Logged out of my administrator Windows account and logged in as "User1". I successfully logged in.

  6. Started SSMS, ensured the username set was "DESKTOP\User1" and that "Windows Authentication" was selected.

When I pressed "Connect" I was presented with an error which in the event log reads as follows:

Token based server access validation failed with an infrastructure error
Server Name: DESKTOP
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

Reading about this, there is a suggestion that this is a UAC error. So I ran SSMS as an administrator. On Windows 8.1 it then asks me to log in with the system adminstrators account - which works - but then I am logged into SQL Server as "DESKTOP\Administrator" and not "DESKTOP\User1".

Why can't Windows pass my non-administrator credentials to SQL Server and log me in? The point of this is so that (in the future "domain") users can access the SQL Server with non-administrator privileges and using Windows Authentication.

Thanks in advance.

Improve this question

2 Answers 2

Reset to default
3

The problem was that the BUILTIN\Users group had its login permission disabled.

This must override the individual user accounts. DESKTOP\User1 must be part of this group, and the group level permission overrode it.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

I am assuming you have the following message in your SQL Server event log:

Login failed for users ‘DESKTOP\User1’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]
Error: 18456, Severity: 14, State: 12

State 12 means that SQL Server was able to authenticate you, but weren’t able to validate with the underlying Windows permissions.

Try logging into the server as the admin and running the following:

GRANT CONNECT SQL TO [DESKTOP\User1];
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [DESKTOP\User1];
Improve this answer

3 Comments

I have updated the question with more detailed error information. It's state 1 for this error. Thanks though.
Check the SERVER event log in SQL Server. Client errors always show state of 1.
Got a bit more useful information from that: Error 18456, Severity 14, State 11.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.