July 5, 2010 at 11:24 pm
Good day to ALL! My situation: there is stand-alone computer (no network cable) with name COMP. On COMP was installed WinServer 2008 and then SQL Server 2008R2 in two instances: default and with name MSSQLSERVER2. Configure of both instance the absolutely same. And on COMP there is Window's user XXXuser, the member of group Users. On both instances there is login COMP\XXXuser for this user with, again, identical configure.
Now, I am enter on COMP(in operating system) as COMP's administrator and open console (runas) as XXXuser. In this console I type:
[font="Courier New"]sqlcmd -S COMP[/font]
Not a problem - connect! Back to the command line and type:
[font="Courier New"]sqlcmd -S COMP\MSSQLSERVER2[/font]
But this time the console respond:
[font="Courier New"]Msg 18456, Level 14, State 1, Server COMP\MSSQLSERVER2, Line 1
Login failed for user COMP\XXXuser.[/font]
If we now go (as admin) to Management Studio and look to Errors Log for MSSQLSERVER2 for the massages concerning failed login attempts we can see:
[font="Courier New"]Message Login failed for user 'COMP\XXXuser'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
Message Error: 18456, Severity: 14, State: 11.[/font]
Search for "previous errors" and find:
[font="Courier New"]Message
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.[/font]
Want to note that default instance has absolutely the same message about 'not register SPN' and there is no problem with it.
So, as admin I can connect to both instances, as XXXuser - to default instance only. But why?? Where is the problem?
July 6, 2010 at 3:52 am
TRy using sa and specify the port number to which named instance is listening.
"More Green More Oxygen !! Plant a tree today"
July 6, 2010 at 6:51 am
And? What this give to me? I already CAN connect to named instance as admin! But I want to connect as non-privileged user...
July 6, 2010 at 6:54 am
do you have the browser service running? If not you can set up an alias with the native client tools that specified the port.
The probability of survival is inversely proportional to the angle of arrival.
July 6, 2010 at 9:45 am
sturner (7/6/2010)
do you have the browser service running? If not you can set up an alias with the native client tools that specified the port.
Already try. 🙂 Alias ZZZ was made and port 1166 was pointed. And...
[font="Courier New"]C:\Windows\system32>sqlcmd -S ZZZ -E
Msg 18456, Level 14, State 1, Server COMP\MSSQLSERVER2, Line 1
Login failed for user 'COMP\XXXuser'.[/font]
Admin connect to ZZZ with no problem, of course... Starting(or stopping) browser service don't change the situation.
July 6, 2010 at 10:25 am
if admin can connect but user COMP\XXXuser can't then I would look in the SQL Server log and find out the reason why this user cannot connect.
Edit: duh... the reason is in the error message you posted. The user can't authenticate to SQl Server. Make sure the user is a valid user on the server and that the user has a valid associated SQl Server login.
The probability of survival is inversely proportional to the angle of arrival.
July 6, 2010 at 11:09 am
The problem solved! As it turn out the events happen in this sequence:
1. I create XXXuser on COMP
2. I create COMP\XXXuser login on MSSQLSERVER2
3. I remove XXXuser from COMP
4. I re-create XXXuser on COMP
5. I create COMP\XXXuser login on default instance
Do you see? At the step 4 I had the Window's user with the same name, but with different SID (security identifier)!!! And SQL Server bind to the user by it's SID, not by it's name! 😀 So, to fix the situation and let XXXuser connect to MSSQLSERVER2 I was needed not more, but simple drop&re-create login on MSSQLSERVER2, that's all!:-)
July 6, 2010 at 9:55 pm
Shcherbunov Neil (7/6/2010)
The problem solved! As it turn out the events happen in this sequence:1. I create XXXuser on COMP
2. I create COMP\XXXuser login on MSSQLSERVER2
3. I remove XXXuser from COMP
4. I re-create XXXuser on COMP
5. I create COMP\XXXuser login on default instance
Do you see? At the step 4 I had the Window's user with the same name, but with different SID (security identifier)!!! And SQL Server bind to the user by it's SID, not by it's name! 😀 So, to fix the situation and let XXXuser connect to MSSQLSERVER2 I was needed not more, but simple drop&re-create login on MSSQLSERVER2, that's all!:-)
As posted above it seems this user had missed the SID, instead of recreating it you could also use sp_change_users_login with AUTOFIX option.
"More Green More Oxygen !! Plant a tree today"
July 7, 2010 at 4:21 am
Minaz Amin (7/6/2010)you could also use sp_change_users_login with AUTOFIX option.
Hmmm... Don't shure about sp_change_users_login. Agree to it description (http://msdn.microsoft.com/en-us/library/ms174378.aspx) this SP works with database user, whereas I had problem with server login. As I understand this SP fix the link DB user->Server login, but I was needed to fix the link Server login->Window's account.
July 8, 2010 at 7:17 am
If you are trying to connect over Kerberos, you need to have your Domain Admin to do a "SETSPN" to register the Service Principal Name.
July 8, 2010 at 7:38 am
Twinsoft SME (7/8/2010)
If you are trying to connect over Kerberos, you need to have your Domain Admin to do a "SETSPN" to register the Service Principal Name.
I don't have goal "connect over and only Kerberos". I want to let XXXuser connect to MSSQLSERVER2. By any means. Over any protocol.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply