July 5, 2012 at 9:03 am
When attempting to create ANY domain level NT user in SQL server 2008 R2, I am getting the following error
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'DOMAIN\username' not found. Check the name again.
Query: EXEC('create login ['+@Login+'] from windows')
I am executing the create login command through SQL server management studio. The very strange part about this is that if I go into the object explorer > Server Instance > Security (right click) > New > Login > click the Search button. When I search for ANY domain level username, I correctly get a result back. If I then hit cancel from the search window and cancel from the new login window (such that no new login is created) I can now use the same query to create ANY NT login. It seems that the process of simply searching for any username is forging some connection to the DNS.
To complicate the situation, we have 2 separate DNS's and machines are split across both. However, a primary DNS is defined for this machine and I can ping both DNS's successfully from the SQL server host machine.
I have done some searching to no avail. I've checked the following:
1) Both the db and sql server are case insensitive. In any case, the case of the NT users are also correct.
2) Don't have duplicate SID's
3) The login definitely exists and I can authenticate through windows (checked by pinging) to both DNS's.
4) These are domain level accounts, not local accounts
Also note that the following query successfully returns the users of this NT group, so I am in some way able to grab usernames from the AD:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser='domain\username',@rmtpassword='pwd'
insert into @Users (Login)
select 'DOMAIN\' + sAMAccountName
from OpenQuery(ADSI,
'SELECT sAMAccountName
FROM ''LDAP://domain.net/CN=users,DC=domain,DC=net''
WHERE MemberOf=''cn=NTGroup,CN=users,DC=domain,DC=net''
ORDER BY cn'
)
Any suggestions would be very much appreciated!
Thanks,
Jason
July 5, 2012 at 10:42 am
Have tried loading your entire query into a variable first and doing EXEC(@MyVar)?
Can you run the create login statement by itself without the variable & dynamic SQL?
July 5, 2012 at 11:13 am
Hi Brandie,
Thanks for the response! Unfortunately running both without dynamic sql (and therefore no variable) or running the entire statement as one variable in the exec command failed with the same error.
July 5, 2012 at 11:16 am
It might be worth checking that your SQL Service account has the ability to read attributes of Active Directory and therefore find users.
July 5, 2012 at 11:40 am
Can you add the account via the SSMS GUI? Or does it error out when you hit "OK"?
July 5, 2012 at 12:24 pm
Thanks guys.
Nicholas - good call. I changed the login to be a domain user instead Local System, restarted the MSSQL Service and was able to add the user.
Brandie - I am only able to add the username through the SSMS GUI after I have used the Check Names function in the Search window of the New Login UI. After checking names, I can also add the logins through a query.
Unfortunately I need to be able to do this all through a query (well this would be ideal - we use VMs that are reset to a snapshot daily and these queries are run by users with limited access/knowledge). It seems like the user Local System is able to authenticate through to the DNS only after I Check Names in the SSMS GUI. Any ideas why?
July 5, 2012 at 12:34 pm
Giving a W.A.G. because it's been a long time since I learned my AD security... SQL Server is acting with your user credentials in SSMS rather than the service account credentials. Something in the pass-through is allowing you, as the sysadmin, to grab those credentials from AD. But when running as a query (I'm presuming as a job), Local can't reach outside the server.
If a domain account is working for you, is there a reason why you can't use a plain vanilla domain account for your service accounts?
July 5, 2012 at 12:56 pm
I don't think that that is the issue because
-I am running the query through SSMS
-I am logged into SSMS (both the query and the object explorer GUI) using sql credentials (the dbo) rather than NT credentials.
As a result, both the query and GUI are using the same credentials. That being said, it could be switching over to use my windows credentials of my logged in windows user after I use the Check Names functionality (matching your WAG with my own...). However, this also doesn't seem to be the case because if I connect to the sql server through SSMS with a completely different session from a different machine after performing check names once on the first session, I can still use the query successfully. It seems that the sql server instance itself is saving some connection to the AD after I use check names that isn't a part of SSMS. This is lost after the VM is reset to the snapshot. Very strange...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply