Início / dba / Perguntas / 341326
Accepted
Vladimir Baranov
Asked: 2024-07-31 18:05:55 +0800 CST

O usuário não pode acessar um banco de dados porque pertence a vários grupos do AD

  • 772

Temos um SQL Server 2016 que possui 20 bancos de dados diferentes que são usados ​​por diferentes aplicativos e diferentes equipes da empresa. Existem grupos de usuários no Active Directory que reúnem funcionários que trabalham em determinados projetos. O problema é que um funcionário pode fazer parte de diversas equipes ao mesmo tempo. A mesma pessoa pode pertencer a vários grupos AD diferentes.

Um usuário reclamou que não conseguia acessar um determinado banco de dados. Quando ele estava tentando atualizar o relatório do Power BI que consulta esse banco de dados, ele recebeu uma mensagem de erro "Não foi possível autenticar com as credenciais fornecidas".

Aqui está uma imagem simplificada. Um servidor possui dois bancos de dados: DB1e DB2. Existem dois grupos AD App_Excel_Reportere DB2_Userdois logins correspondentes no SQL Server:

CREATE LOGIN [LegacyDomain\App_Excel_Reporter] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

CREATE LOGIN [Domain\DB2_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

Não tenho certeza se isso é importante, mas este servidor é um servidor legado que fazia parte de outra empresa adquirida pela empresa principal. Portanto, este computador SQL Server está no domínio LegacyDomain.

Dois grupos AD LegacyDomain\App_Excel_Reportertêm Domain\DB2_Userseus próprios conjuntos de usuários, mas um usuário Jack.Universalpertence a ambos os grupos AD.

Os usuários que fazem parte App_Excel_Reporterdevem ter acesso ao DB1. Os usuários que fazem parte DB2_Userdevem ter acesso ao DB2.

Nele DB1há um usuário mapeado para o login correspondente:

USE [DB1]
GO
CREATE USER [LegacyDomain\App_Excel_Reporter] FOR LOGIN [LegacyDomain\App_Excel_Reporter] WITH DEFAULT_SCHEMA=[dbo]
GO

Nele DB2há um usuário mapeado para o login correspondente:

USE [DB2]
GO
CREATE USER [Domain\DB2_User] FOR LOGIN [Domain\DB2_User] WITH DEFAULT_SCHEMA=[dbo]
GO

Quando um usuário Jack.Universaltenta atualizar o Power BI, ele está conectado ao Windows em seu laptop Domain\Jack.Universale vejo estas mensagens no log do SQL Server com o mesmo carimbo de data/hora:

Login succeeded for user 'Domain\Jack.Universal'. Connection made using Windows authentication, [CLIENT: <ip address>]
Error: 18456, Severity: 14, State: 38.
Login failed for user 'Domain\Jack.Universal'. Reason: Failed to open the explicitly specified database 'DB2'. [CLIENT: <ip address>]

Quando eu corro

xp_logininfo 'Domain\Jack.Universal', @option = 'all'

isto mostra

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User                 |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB3_User                 |
+-----------------------+------+-----------+-----------------------+---------------------------------+

(sim, existem mais de dois bancos de dados e este usuário pertence a três grupos AD)

Se eu correr

xp_logininfo 'Domain\Jack.Universal'

sem a opção "todos", apenas a primeira linha será retornada:

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------+------+-----------+-----------------------+---------------------------------+

Portanto, parece que quando um usuário do Windows Domain\Jack.Universalefetua login no SQL Server, o SQL Server escolhe o login LegacyDomain\App_Excel_Reporterpara permitir a entrada do usuário, mas quando tenta acessar um banco de dados, DB2essa tentativa falha porque o login LegacyDomain\App_Excel_Reporteré mapeado apenas para o usuário em DB1.

"o banco de dados especificado explicitamente" deve ser devido à cadeia de conexão do Power BI que especifica explicitamente DB2.

Como configuramos isso para que um usuário pertencente a ambos os grupos AD tenha acesso a ambos os bancos de dados? Os usuários que pertencem a apenas um grupo AD deverão ter acesso apenas ao banco de dados correspondente.

Não sou administrador de domínio, mas posso perguntar o que for necessário. A empresa não tem DBA, sou apenas um programador que sabe uma ou duas coisas sobre SQL Server.

Cavei um pouco mais fundo. Quando eu corro

EXEC xp_logininfo 'Domain\DB2_User', @option = 'members'

Recebo uma lista de usuários nesse grupo:

+-----------------------+------+-----------+-----------------------+-----------------+
|     account name      | type | privilege |   mapped login name   | permission path |
+-----------------------+------+-----------+-----------------------+-----------------+
| Domain\user1          | user | user      | Domain\user1          | Domain\DB2_User |
| Domain\user2          | user | user      | Domain\user2          | Domain\DB2_User |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User |
+-----------------------+------+-----------+-----------------------+-----------------+

E os usuários estão dentro Domaindo esperado.

Quando eu corro

EXEC xp_logininfo 'LegacyDomain\App_Excel_Reporter', @option = 'members'

Eu entendi isso:

+-----------------------------+------+-----------+-----------------------------+---------------------------------+
|        account name         | type | privilege |      mapped login name      |         permission path         |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\user3          | user | user      | LegacyDomain\user3          | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\user4          | user | user      | LegacyDomain\user4          | LegacyDomain\App_Excel_Reporter |
| LegacyDomain\Jack.Universal | user | user      | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+

Aqui o caminho da permissão está dentro LegacyDomaine o nome da conta também está dentro LegacyDomain, mas acima nos resultados do xp_logininfo 'Domain\Jack.Universal'nome da conta está dentro Domain, mas o caminho da permissão está dentro LegacyDomain.

Outra vez:

EXEC xp_logininfo 'Domain\Jack.Universal', @option = 'all'

+-----------------------+------+-----------+-----------------------+---------------------------------+
|     account name      | type | privilege |   mapped login name   |         permission path         |
+-----------------------+------+-----------+-----------------------+---------------------------------+
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB2_User                 |
| Domain\Jack.Universal | user | user      | Domain\Jack.Universal | Domain\DB3_User                 |
+-----------------------+------+-----------+-----------------------+---------------------------------+

EXEC xp_logininfo 'LegacyDomain\Jack.Universal', @option = 'all'

+-----------------------------+------+-----------+-----------------------------+---------------------------------+
|        account name         | type | privilege |      mapped login name      |         permission path         |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+
| LegacyDomain\Jack.Universal | user | user      | LegacyDomain\Jack.Universal | LegacyDomain\App_Excel_Reporter |
+-----------------------------+------+-----------+-----------------------------+---------------------------------+

Deve haver alguma mágica no Active Directory que mapeie os usuários do domínio legado para o domínio principal.

sql-server

2 respostas

  1. Best Answer

    Se você adicionar ou remover um usuário AD de um grupo AD e quiser aplicar essas alterações imediatamente sem que o usuário faça logoff, o usuário precisará executar o seguinte comando em uma cmdjanela do Powershell

    klist purge

    Se for uma máquina que você adicionou/removeu, você precisa

    klist -li 0x3e7 purge

    O que isso faz é limpar o cache de tíquetes Kerberos, de modo que, na próxima vez que um tíquete Kerberos for necessário, o controlador de domínio será contatado para obter um novo tíquete TGT, e o novo tíquete conterá a lista atualizada de grupos. Caso contrário, o cache manteria o ticket até o vencimento (geralmente 10 horas) ou logoff.

    Para confirmar as alterações, execute

    whoami /groups
    • 3

  2. Pelo que sei, se você tentou usar Grupos Universais no Active Directory para acessar seus SQL Servers, notou que os usuários membros desses grupos não conseguem acessar a Instância do SQL Server. A razão para isso tem mais a ver com o Active Directory do que com o SQL Server.

    Os grupos normais no Active Directory são armazenados em cache para que as solicitações de autenticação possam retornar grupos dos quais o usuário é membro como parte do token do Windows. Os grupos universais, no entanto, não estão incluídos no token do Windows, pois os grupos universais dos quais o usuário pode ser membro podem não estar no mesmo domínio pelo qual a solicitação é tratada.

    Como os Grupos Universais não estão no token de autenticação quando o SQL Server verifica se o usuário tem acesso, o token indica que o usuário não tem. A correção para isso é bastante fácil: use um tipo de grupo de domínio do Windows diferente dos grupos universais.

    Não sei se habilitar o cache de grupo universal pode resolver o problema: https://learn.microsoft.com/en-us/windows-server/identity/ad-ds/plan/planning-global-catalog-server-placement #enable-universal-group-membership-caching

    • 1

relate perguntas