December 8, 2005 at 3:33 pm
I am running SQL server 2000 sp4. I have created a group (domian global) and given it read rights on a database. When the user tries to connect to the Server it gives him a login failure message. I check to make sure his account was a member of the group and it was. I check the rights to the group and it show read access. If I give his domain account rights to the database no problems. I want to use the group becuase I have about a 100 users. What can be the problem? what can I check?
December 8, 2005 at 5:16 pm
Do you have any groups that have been denied access ?
If do the deny overrides the grant and the login will not be able to connect.
You can confirm if the login is the member of a domain group defined to SQL Server by running under SQL Query Analyzer this:
exec master.dbo.xp_logininfo @acctname = 'DomainName\AccountName' , @option = 'all'
To get the members of a group, run
exec master.dbo.xp_logininfo @acctname = 'DomainName\GroupName' , @option = 'members'
If these all look fine, please use Enterprise Manger to generate the SQL for the database user and logins. This can be done by right click the database to get the menu.
1. Select "All Tasks"
2. Select "Generate SQL Script"
3. On the modal window, go to the "Options" tab
4. Select the check boxes for "Script database users and database roles" and for "Script SQL Server Logins"
5. Now select "General" tabe
6. Click the "Preview" button.
The script should look like:
if not exists (select * from master.dbo.syslogins where loginname = N'Dell400\Diana')
exec sp_grantlogin N'Dell400\Diana'
exec sp_defaultdb N'Dell400\Diana', N'master'
exec sp_defaultlanguage N'Dell400\Diana', N'us_english'
GO
USE NORTHWIND
go
if not exists (select * from dbo.sysusers where name = N'Diana' and uid < 16382)
EXEC sp_grantdbaccess N'Dell400\Diana', N'Diana'
GO
exec sp_addrolemember N'db_datareader', N'Diana'
GO
SQL = Scarcely Qualifies as a Language
December 9, 2005 at 7:57 am
Permissions should also be assigned directly to a Domain Local account. I think I recall an issue where SQL having trouble properly authenticating Global groups under some conditions.
The best practice would be to place the Global Group holding the user accounts inside the Local. Resource permissions, such as to a database, should only be assigned to Local groups. Thus giving you full flexibility of embedding other Domain Local up through Universal.
December 9, 2005 at 11:02 am
That probably should be server local groups. Domain local groups can only be seen within the domain and thus aren't typically as heavily used as domain global groups for obvious reasons. If you have to cross domains, the groups can't be used. That creates a big headache for administrators who have to worry about such things. The practice, back to the NT 4 days were:
User -> Domain Global Group -> Server Local Group -> SQL Server Login
However, with clustering, you tend not to be able to use Server Local Groups. Also, from a recovery perspective, it may be more advantageous to stick with domain groups, especially if there's a plan to restore the databases to a different server in the event of a server failure locally or if you're not to going to restore the servers from backup in the event of disaster recovery.
I've also not seen issues with SQL Server authenticating domain global groups. Can you cite some examples of where you've seen that? If it does have issues (except in cases where SQL Server is on the same server as a domain controller... there are known issues with security in that configuration) with that sort of thing, I know I can use that info.
Back to the original poster, has the user logged off and logged back on since the group membership change was made? What happens if you log on as the user (after having granted access to his/her user account) and run a SELECT IS_MEMBER('<Windows group>')? This is a similar sort of test to the xp_logininfo specified by Carl.
K. Brian Kelley
@kbriankelley
December 12, 2005 at 12:07 pm
How are logins being done?
Is your SQL Server set to use Windows Authentication only or is it set for Mixed Mode?
Is the user's login set for Windows Authentication or SQL Server Authentication?
Is the group set for Windows or SQL Server authentication?
-SQLBill
December 12, 2005 at 12:14 pm
A quick comment on the domain vs. local groups issue: Way back when, the SysAdmins told me that you'd want to use the global-to-local group methodology in Windows NT 4.0, because of <technobabble I couldn't understand back then--and probably not now, either> but whatever bugaboos required that were not present in the (then new) Windows 2000. When we upgraded I chucked the local groups, and never had any problems. (Well, never any problems because of the domain-group-only methodology...)
Philip
December 12, 2005 at 4:27 pm
Where would you check the following?
"Is the user's login set for Windows Authentication or SQL Server Authentication?
Is the group set for Windows or SQL Server authentication?"
I thought all Windows groups use Windows Authentication and all SQL based accounts use SQL Server Authentication.
I have noticed that if a add the user to the Security Login and not as a database user the user can authenticate. Is this required? I thought just adding the group to the Security Login and making the group a database user was all that was needed.
December 12, 2005 at 8:36 pm
A Windows user must have a login in order to connect to the SQL Server. Whether this is through the login itself or through a Windows group does not matter.
In order to access a database, after authentication the Windows login must be mapped to a database user. This can occur one of three ways:
a) Direct mapping of the user's Windows login
b) Mapping of a Windows group the user is a member of
c) The guest user is enabled on the database.
Have you attempted to run a Profiler trace (include failed logins) when you've mapped the group to see how the user is connecting? This would verify if the user is coming in using his/her Windows credentials. You can also turn on success/failure auditing in SQL Server to capture information that gets written to the SQL Server log (and the application event log for the OS). That might provide more insight.
K. Brian Kelley
@kbriankelley
December 13, 2005 at 12:45 pm
When I look at Enterprise Manager and check the Security Tab it says the Authentication is set to SQL Server and Windows. Am I confused or are these two different issues (Authentication and Integrated Security)?
I will be running a few test using Profiler, is their something that will prove something setup incorrectly. What should be looking for?
December 13, 2005 at 12:45 pm
When I look at Enterprise Manager and check the Security Tab it says the Authentication is set to SQL Server and Windows. Am I confused or are these two different issues (Authentication and Integrated Security)?
I will be running a few test using Profiler, is their something that will prove something setup incorrectly. What should be looking for?
December 13, 2005 at 4:26 pm
Integrated security is "Windows Authentication" - same thing.
You can run SQL using Windows only authentication, or you can run it in "mixed mode" authentication - in this case, authentication can be done on clients on the basis of windows accounts or via a set of usernames/passwords.
The suggestion on this thread at the moment is to see whether your users are trying to authenticate using their windows account or authenticate using SQL Server accounts. I have seen many developers make the mistake of trying to send the windows username+password to sql server on the connection string - if you do this then SQL will interpret them as a SQL login, not a windows login. The windows logins can only be done via trusted authentication setting the appropriate connection string param..
Run profiler, have it show good and failed logins, and you will be able to see if they are connecting with windows or sql auth.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply