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.
Do you have already encounters this problem? I’ve looked in the Books Online, it just says use syntax like that “Domain\NTGroupName” and nothing else. Is it due to a config on the domain?
If you can help me, you are welcome.
Regards,
Jmackels
-
Hi,
are you doing this via EM? Which SQL Version?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I use mostly SQL Server 2000. But it will be better if it works on SQL Server 7
And yes, I put the right with Enterprise Manager. Could you help me ? Is it better with SQL command ?
-
Hi,
if you add a new login via EM and click on the button right of name, you have the chance to browse the valid NT users/NT groups in the domain chosen by the combobox. Clicking on add -> ok will add the entry with the correct syntax. This is one thing that has definitely improved in SQL2k.
If all fails in EM, go for QA. Something things work in QA which don't work in EM. Whatever the reasons might be.
Unfortunately, I can't tell if this is an issue with active directory, because we don't use this feature right now.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
It's exactly what I've done.
If it's a NT Group Security on the SQL Server computer, it works.
If it's on the Domain, I don't find the NT Group Name in the list. But if I type a wrong NT Group Name, I've an error. And if I type the right name, SQL Server find it but it doesn't work when I try to connect to the DB.
-
There is a search on the new login form. Have you tried this?
I would go for QA, although I fear the results will be the same.
Is the computer running SQL Server a domain controller?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes I used the Search. the problem is not their. In fact, the login appears in the list. And I associate some right to the Group. The problem comes from when a member of this group try to connect to the DB, at this time I have an error.
It seems that SQL Server cannot find who is member of this group.
-
Maybe a stupid question, but do the users in this group have access rights to the computer running SQL?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Members of the Dommain Group haven't access to windows. The only access that they have is on SQL Server. And the only way where I try to connect is with SQL Query Analyser (directly on the port of SQL Server).
-
Sorry, I've been no help for you so far.
Maybe someone else knows better?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for your help a5xo3z1.
-
Probably of no help as I have not done this and I am probably barking up someone else's tree but two things spring to mind.
Are the NT Groups in the same domain as the server? If not have you set a trust.
Have you tried creating a local group that contains the domain groups and giving access to the local group.
Far away is close at hand in the images of elsewhere.
Anon. -
SQL Server is on the same domain that the Domain NT Group. Just for information how do you trust the other Domain? Is it at the Domain Side or SQL Server?
When I create a Local NT Group and then insert the Domain Group as member, it doesn’t find the Domain Group. The system only see Domain Group like : Domain User, Domain Admin… but not DomainTestGroup.
-
1) Check to make sure users are logging into the domain, not using an account on their local system. The problems you describe (so far as user logins) happen when this is the case. As far as what you're seeing on the server, that leads me to point 2.
2) Log in as the administrator account for the SQL Server. Try and re-add the SQL Server to the domain (this may mean dropping it to a workgroup and then readding it). The reason to log in as the administrator is because even if your domain account had admin rights, as soon as you remove it from the domain, it no longer does. Logging in as the administrator verifies you have the password and can log back into the system once it is in a workgroup and not on the domain. If a computer account is corrupted (usually saw this on NT 4 more), sometimes these types of behaviors show up.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Yes the user is really connected at the windows login on the Domain.
Question about your point 2
“Log in as the administrator account for the SQL Server”. What do you mean? Log in as administrator Account of the SQL Server (Admin on the Windows Side) and nothing related to the Security Admin on SQL Server.
Now this is the details concerning the operation that I’ve done:
Previously, I was connected as a member of the Domain. My Domain Login is also Admin of the computer (Windows) but not a Domain Admin.
1) I set up my computer in a Workgroup instead of the Domain.
2) I reboot the computer
3) I log in with my Local Admin Account
4) I set up my computer on the domain. (I use my Domain Login describe previously. Does-it changes something if I use a Domain Admin account instead of my Domain Login?)
5) I reboot the computer
6) I log in with my Domain Login
After that, I look to add a Domain Group in SQL Server. I don’t find it in the list but if I search it, it’s ok. But always the same problem when I try to connect to the DB with a member of the Domain, I’ve still a login error
I try to add the Domain Group to a Local Group; I don’t find the Domain group even if I search it.
Just for information: Start and Run SQL Server with the system account
In conclusion even after I’ve done that, I’ve still the same problems.
I’ve already try on SQL Server 2000 in the Server Properties -> Active Directory: I add the instance of SQL to Active Directory (What does-it change exactly? Just registering in the SQL Server’s server list available on the network?)
After that I check like before and still the same error even if I refresh the attributes of this instance of SQL Server in Active directory.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply