Security Model Problems

From: Brian Cidern (bcidern@gillespie.com)
Date: 11/01/02 

From: "Brian Cidern" <bcidern@gillespie.com>
Date: Fri, 1 Nov 2002 07:25:27 -0800

Some of the documents I've referenced (from MSDN) for
setting up security within SQL Server made the following
suggestions:

1. Security Mode: Windows Authentication
2. Create global domain groups and add appropriate users.
3. Create local domain group and add the appropriate
global domain groups.
4. Grant login to the local domain group.

Scenario.
-- Win2k AD groups --
Global Group: APP_read
Global Group: APP_write
Local Group: SQL_Logins

SQL_Logins (members)
  APP_read
  APP_write

(domain users added to the appropriate Global Domain
groups)
 
-- SQL Server --
USE master
sp_grantlogin 'DOMAIN\SQL_Logins'

USE APP_db
sp_grantdbaccess 'DOMAIN\APP_read'
sp_grantdbaccess 'DOMAIN\APP_write'
sp_addrolemember 'db_datareader', 'DOMAIN\APP_read'
sp_addrolemember 'db_datawriter', 'DOMAIN\APP_write'

------------
Now, when I try to connect one of the users (who has been
made a member of either of the Global Domain groups, I get
a Login Failed while trying to create/configure a User or
File DSN.

The only way I've been able to gain access from the
clients was to explicitly grant login to the specific
Global Domain group.

Details:
  SQL Server 2000
  Version: 8.00.679
  Patch Level: SP2+Q316333
  The NetLib is set to TCP/IP.
  MDAC 2.62.7400.1 installed on client.

Relevant Pages

  • Re: Security Model Problems
    ... Step 3 Create two new local groups on the server hosting SQL Server (one for ... Create global domain groups and add appropriate users. ...
    (microsoft.public.sqlserver.security)
  • RE: migrating file permissions
    ... It is not necessary for you to move all of your NT Local Domain groups to ... If you use upgrade method to upgrade your child domain to 2k3, ... Domain groups to Global Domain groups, ...
    (microsoft.public.windows.server.migration)
  • Re: Sql server 2005 clustered service group
    ... For each clustered service in the instance of SQL Server that you are ... administrator for the names of existing domain groups, ... Note that SQL Server accounts will not be removed from the groups if SQL ... I see with installing SS2005 cluster if forces me to use domain accounts ...
    (microsoft.public.sqlserver.setup)
  • Replacing Workgroup Security with Domain Groups/Acounts - is it possible
    ... We have Database with back end on SQL server and Front end as Access MDB ... with workgroup security setup to restrict editing the forms etc ... Is it possible to do Workgroup type security, but assigned to Domain groups ...
    (microsoft.public.access.security)
  • Re: Should you use local machine groups?
    ... No. Do not map domain groups to local accounts and then map those local ... Grant SQL Server login permission directly to the Domain accounts, ...
    (microsoft.public.sqlserver.security)