SQL Server Performance Forum – Threads Archive

NT Groups and SQL Server Access Right

Hi, My question is related to DB Access Right and NT Group Security. At the end, I want that the user connect to the Database with the â€œWindows Authentificationâ€. Some NT Groups already exist on the Domain. I want to use directly these NT Groups to associate to a role. In that way, all the members of this group will have access to the DB. If I set the NT Group on the SQL Server computer and if I use it to give some right, everything works. But the problem comes when I want to use the NT Group (with active directory) from the domain, which is not on the same computer. I can give the access to the group, but when a member of this group tries to connect to the DB, we have a Login Error. It seams that SQL Server cannot identify the member of the Domain NT Group. All the operation that I’ve done was on SQL Server 7 and with the Enterprise Manager. Do you have already encounters this problem? I#%92ve looked in the Books Online, it just says use syntax like that â€œDomainNTGroupNameâ€ and nothing else. Is it due to a config on the domain? If you can help me, you are welcome. Regards, Jmackels

WHat was the error when trying to setup or access? _________
Satya SKJ

I can set up the access. the problem comes from when the member of this group try to connect to the DB. When I set up the access the Domain group doesn’t appear in the list. If I type a wrong Group Name, I receive an error. But If I type my Domain Group Name (which doesn’t appear in the list) access is set. After that I can give some access to the Group. As I said, the problem comes from when a member try to connect to the DB.

I would go with Sayta… What is the error that you recieve when you try to connect? Gaurav

Just to clarify your question: (1) You have a Win2K mixed mode AD domain – you mentioned Active Directory.
(2) You want to use "Windows Authentication"
(3) A group or a number of groups cannot be ‘seen’ by the computer you are using to configure the security.
(4) You ‘force’ SQL Server to see these group
(5) The group is accepted
(6) You grant this group access to the server
Is this correct ? When you say "But the problem comes when I want to use the NT Group (with active directory) from the domain, which is not on the same computer." – Is it possible that you are talking about a Local group ? And finally… Have you granted this group access to any other database – apart from ‘master’ – which is the default ?

The exact error that I have when a member of the group try to connect is: â€œUnable to Connect to server xxx Server: Msg 18456, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user â€˜DomainName#%92â€ Do you have put in place the same structure than me: Domain Group with NT Authentification on several computers in the same domain? I ask you this question because I can only test this problem on the office domain. I have no else structure like this. At this moment I haven#%92t meet or discuss with somebody who try exactly the same thing. I try to determine if it#%92s a SQL Server configuration problem, or domain configuration problem or policy problem. If somebody could try the same test than me, it will be great: - Normal user, SQL Server Computer, and domain computer are on the same domain but on 3 different computers. - On the domain (with active directory) we create a group of user with our normal user. - Normal user and Domain group has no windows access on the SQL Server computer - Domain Group has only a public access to a DB on SQL Server. The DB by default is the DB where the group has access <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> If everything is in place, the user tries with Windows Authentification to connect with SQL Query analyzer to the SQL Server. For the moment I have always the error list at the beginning of this mail. vbkenya questions (1)Yes (2)Yes (3)Yes. I see only the System Group like: Domain user, Domain Adminâ€¦ (4)Yes. I know the name of the group and I type in directly. If I type an inexistent group, I receive an error (5)Yes (6)Yes, and I set the DB default with the DB granted (different than master DB) When I speak about NT group, I have try different possibilities: -Firstly, I create a Local Group on the SQL Server Computer. The members of this Local group are all domain Users (â€˜DomainName#%92). I find it in the list of group on the SQL Server Computer. I put the right. And all the user of the Local Group could connect to the DB. In conclusion it works. -Secondly, I try to make the same thing but instead using the Local Group, I try to use a Domain Group. The conclusion in this case, is the problem describe before (no access).

Refer to KBA http://support.microsoft.com/defaul…rt/kb/articles/Q258/0/25.ASP&NoWebContent=1].
_________
Satya SKJ

Thanks for the link satya. But in my case, when I try to â€œAdd GlobalGroup to SQLLocalGroupâ€, he can#%92t find the GlobalGroup on the SQL Server Computer. I#%92ve already thought about this possibilities, but here, I can#%92t "force" the login.

Have you tried using SP_GRANTLOGIN procedure from QA with SA login?
Have you treid this from the SQL Server itself? _________
Satya SKJ

I have the same result if I use the Enterprise Manager or the SP: SP_GRANTLOGIN and SP_ GRANTDBACCESS with SA login. All the access rights for the Group are OK. When you grant a group, you have only a record in the sysmembers table. The problem comes only when a member try to use the Group Right. The group and his settings are OK The only thing which doesn#%92t work with the group is when you want add the Domain Group to the Local Group in the computer management. The Local Group doesn#%92t find the Domain Group and you can#%92t â€œforceâ€ it.

Take help of Network Admin to see why its not able to lookup domain group under local from OS side. _________
Satya SKJ

Now I#%92ll see with our network admin to install a new domain with the default parameters and try to see the domain group on the SQL Server computer. Anyway, many thanks to everybody to help me.

]]>