What are the settings to enable any memeber of an AD group to logon to SQL2005
I have a group login created on the SQL server like so:
CREATE LOGIN [MyDomain\MyGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
I have then created a user in the db MyDB for the login above and gave it db_owner role:
use MyDB
go
CREATE USER myDBUser FOR LOGIN [MyDomain\MyGroup]
go
sp_addrolemember db_owner, MyDomain\MyGroup
When I try to connect to this server using a user member of the group MyDomain\MyGroup, from a reporting services report I get the error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot impersonate user for data source 'MyDB'. (rsErrorImpersonatingUser)
Logon failed. (rsLogonFailed)
Logon failure: the user has not been granted the requested logon type at this computer. (Exception from HRESULT: 0x80070569)
Any ideas of what is going wrong?
Thanks.
Here it is:
Date 10/3/2005 4:01:16 PM
Log SQL Server (Current - 10/4/2005 5:30:00 PM)
Source Logon
Message
Login failed for user 'GLOBEINT\securityreport'. [CLIENT: 141.122.131.40]
--
Date 10/3/2005 4:01:16 PM
Log SQL Server (Current - 10/4/2005 5:30:00 PM)
Source Logon
Message
Error: 18456, Severity: 14, State: 11.
State 11 means that the login doesn't have access to the server. Was the user added recently to the Windows group? It looks like the group membership does not take effect.
You could try to add explicit connection permission to that user (using CREATE LOGIN), then dump its token from sys.login_token and see if the group is listed there. You can then use DROP LOGIN to remove the explicit login.
Thanks
Laurentiu
Hi Laurentiu,
I tried what you suggested and I can see the user in SQL WB and manage it correctly (grants, roles, etc.) but the sys.login_token does not contain any entry for that user. sys.server_principals does however.
This user is a non-interactive log-on user (we created this account on the domain to be able to test some custom security settings before granting the rights to actual interactive users).
This is my question basically. In the Local policies or in the AD, what should the user rights be? Isn't access from the network enough. Do we need to grant to these users log-in locally or what? We don't want the users to be able to log-on to the SQL server directly, however little actual rights they then might have...
Thanks.
