Cannot create login on cluster

Posted on

Question :

I have a SQL Server 2008 R2 cluster on Win2008 R2.

I am unable to add a login for the service account.

Any other login I add to this instance works fine and I have been able to add the same service account login to numerous other SQL instances.

Moving the instance to other node does not resolve the issue.

when I run

 CREATE LOGIN [DOMAINServiceAccount] FROM windows; 

I get the error

Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'DOMAINServiceAccount' not found. 
    Check the name again. 

When I run

exec xp_logininfo 'DOMAINServiceAccount', 'all'; 

I get the error

Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62
Could not obtain information about Windows NT group/user 
    'DOMAINServiceAccount', error code 0xffff0002. 

Any suggestions would be appreciated.

Answer :

Is the service account a domain account? Sounds to me like the service account may in fact be an account that is local to the machine, not a domain account.

Try:

CREATE LOGIN [WINDOWS_MACHINE_NAMEServiceAccount] FROM windows; 

If this works, the account is not a domain account.

I had a similar issue where the domain controller was in Asia and the SQL Server was in Los Angeles. See if this is relevant to you:

http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/1138616c-1395-43e9-994f-234e177ec032

Does this fail with any user you try to create or only specific ones? If it’s all then you should look at if you could contact the domain controller from the sql server and the authentication settings described in the bottom of that post.

Thanks for the suggestions but I have found the issue.

The service account was entered in SQL Server Configuration manager with a different case than AD. the instance is case sensitive so I guess when it was validating the name/SID of the login I was trying to create it saw that it did not match the name the engine already knew about with the same SID

Leave a Reply

Your email address will not be published. Required fields are marked *