Início / dba / Perguntas / 80507
Accepted
Mansfield
Asked: 2014-10-18 09:45:39 +0800 CST

Problemas com a assinatura do módulo e os procedimentos internos do catálogo SSIS

  • 772

Estou tentando configurar um procedimento armazenado para executar um pacote SSIS. Eu quero permitir que um usuário execute apenas este procedimento armazenado, então preciso que ele seja executado como um usuário diferente.

Encontrei este artigo que detalha a criação de um certificado e a assinatura do módulo para que um login privilegiado possa executá-lo.

Quando tento executar qualquer comando representando o login, recebo o seguinte erro:

Não é possível executar como principal do servidor porque o principal "CrossDbCertLogin" não existe, esse tipo de principal não pode ser representado ou você não tem permissão.

Aqui está um script para reproduzir o problema:

create certificate CrossDatabaseCert
ENCRYPTION BY PASSWORD = 'passA'
WITH SUBJECT = 'Certificate for Cross-Database Database Impersonation'
GO


CREATE Login CrossDbCertLogin FROM CERTIFICATE CrossDatabaseCert
GO

backup certificate CrossDatabaseCErt to file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(
    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passA',
    ENCRYPTION BY PASSWORD = 'passB'
)
go

use msdb
create certificate CrossDatabaseCert from file='C:\temp\CrossDatabaseCert.CER'
WITH PRIVATE KEY
(    FILE = 'C:\temp\CrossDatabaseCert.PVK',
    DECRYPTION BY PASSWORD = 'passB',
    ENCRYPTION BY PASSWORD = 'passA'
)
Go

EXECUTE AS LOGIN=N'CrossDbCertLogin';
SELECT 1

Eventualmente, adiciono o certificado ao procedimento armazenado, mas parece que a representação nem está funcionando, então estou tentando fazer isso funcionar primeiro.

O que estou fazendo errado?

EDIT: Um pouco mais de informação sobre o meu procedimento.

Quando altero para "EXECUTE AS OWNER" (no mesmo formato do exemplo que segui), recebo os seguintes erros.

Msg 27123, Nível 16, Estado 1, Procedimento create_execution, Linha 39 A operação não pode ser iniciada por uma conta que usa Autenticação do SQL Server. Inicie a operação com uma conta que usa autenticação do Windows.

Msg 27123, Nível 16, Estado 1, Procedimento set_execution_parameter_value, Linha 34 A operação não pode ser iniciada por uma conta que usa Autenticação do SQL Server. Inicie a operação com uma conta que usa autenticação do Windows.

Msg 27123, nível 16, estado 1, procedimento start_execution, linha 32 A operação não pode ser iniciada por uma conta que usa a autenticação do SQL Server. Inicie a operação com uma conta que usa autenticação do Windows.

Dentro do meu procedimento, chamo três procedimentos internos de catálogo do SSIS: Create_Execution, Set_Execution_Parameter_Valuee Start_Execution.

Quando altero o procedimento para executar como a conta SQL Agent Service, recebo o seguinte erro:

Msg 15199, nível 16, estado 1, procedimento prepare_execution, linha 34 O contexto de segurança atual não pode ser revertido. Mude para o banco de dados original onde 'Execute As' foi chamado e tente novamente.

EDIT2: Acabei usando um SQLCLR SProc para chamar meu proc original, que resolveu todos os problemas.

sql-server stored-procedures

2 respostas

  1. No código de Vedran ao qual você faz referência, ele também assina o procedimento armazenado no outro banco de dados. Não vejo em seu código que você deu esse passo final.

    Erland Sommarskog tem uma extensa discussão sobre este problema em http://www.sommarskog.se/grantperm.html . Isso inclui uma discussão, em suas palavras, "o problemático EXECUTAR AS".

    No link a seguir, que é para iniciar os trabalhos do SQL Agent, você pode ver outra abordagem para delegar direitos a um login que não possui direitos específicos. Consulte: Permitir que não sysadmin, não proprietário de um trabalho do SQL Server Agent o execute

    Se isso lhe interessa, você pode criar seu próprio procedimento armazenado para permitir que seu usuário inicie o processo SSIS.

    20/10/2014 Editar: "Não é possível executar como principal do servidor porque ..."

    Em primeiro lugar, se você é um sysadmin, o problema não são seus direitos, mas o contexto que você está tentando executar. (Eu acredito que você é um sysadmin.) Então poderia ser algo como:

    1. Se o princípio do servidor que você deseja usar for um Windows Group, ele não poderá ser representado.
    2. Você restaurou recentemente um banco de dados do sistema (por exemplo msdb, ) de outro servidor? Então a conta que você vê msdbpode não corresponder a uma conta no servidor atual. (Você pode ver o mesmo nome em database_principals, mas sidpode ser diferente.) Nesse caso, tente usar exec sp_change_users_login 'AutoFix','principalname'.
    3. Se o proprietário de um banco de dados (em master.sys.databases) não for o mesmo sidque o usuário do banco de dados dbo, ele não poderá ser representado. (Isso é improvável, msdbpois saé o proprietário normal de msdb.)
    4. Se o login foi descartado, mesmo que o usuário permaneça no banco de dados, talvez você não consiga representar a conta.

    E provavelmente há outros sabores também.

    • 3
  2. Best Answer

    Dois pensamentos, ambos girando em torno da representação:

    1. A partir da descrição do problema, bem como das mensagens de erro, todas tratam de erros relacionados a tentativas de representar/EXECUTAR AS. Essa tentativa de mudar o contexto de execução pode ser todo o problema. Por que você está usando EXECUTE AS LOGIN = N'CrossDbCertLogin'? Esse login (e o certificado associado, etc.) existe como um proxy para obter permissões implícitas, não para executar nada diretamente. Se você observar mais de perto o exemplo que seguiu de sqlxdetails.com, notará que nunca há uma chamada para EXECUTE AS LOGIN = N'HighPrivCertLogin'.

      Assim, eu começaria por:

      1. Não há mais tentativas deEXECUTE AS LOGIN = N'CrossDbCertLogin'
      2. Seu procedimento armazenado que chama os três SSIS Catalog Procedures não deve ter nenhuma EXECUTE AS cláusula ou EXECUTE AS CALLER (que é o padrão se não for especificado)

      3. Conceda permissão de execução em cada procedimento armazenado do SSIS ao usuário baseado em certificado no banco de [SSISDB]dados. Estas são as permissões implícitas que seu login restrito obterá do link entre o proc sendo assinado com o certificado que mapeia para um usuário que possui as permissões necessárias:

        USE [SSISDB];
GRANT EXECUTE ON [Create_Execution] TO [CrossDbCertUser];
GRANT EXECUTE ON [Set_Execution_Parameter_Value] TO [CrossDbCertUser];
GRANT EXECUTE ON [Start_Execution] TO [CrossDbCertUser];

        Obviamente, seu código de exemplo não mostra a criação de um usuário de banco de dados local em [msdb]ou [SSISDB], mas presumo que esses usuários foram criados. Caso contrário, pelo menos você precisa criar o usuário, [SSISDB]pois é onde a permissão no nível do banco de dados é necessária (ou seja, para executar os 3 processos do SSIS). Mas provavelmente não faria mal criar também o usuário baseado em certificado [msdb].

    2. Se, por algum motivo (talvez o SSIS , sendo um processo externo, não possa usar um token de autenticação representado? ) não funcionará porque você está tentando:

      • representar logins que não podem ter um contexto de execução (ou seja, logins baseados em certificado e chave assimétrica)
      • representar por meio da EXECUTE AScláusula da CREATE PROCEDUREcláusula que não pode ser revertida (não sei por que alguém iria querer reverter isso, mas é uma mensagem de erro que você está recebendo ao tentar fazer isso).

      Então, aqui estão duas coisas para tentar (novamente, assumindo que a opção # 1 acima não funciona):

      1. Crie um procedimento armazenado SQLCLR que nada mais faz do que conectar por meio da cadeia de conexão "trusted_connection = true;" (que se conectará à instância padrão, presumivelmente a instância em que você está atualmente) e execute seu procedimento armazenado [msdb]que executa os três procedimentos SSIS. Isso funcionaria porque, a menos que você codifique explicitamente para usar a representação, por padrão, ele fará a conexão externa como a conta do Windows/Active Directory que está executando o processo do SQL Server (ou seja, a conta "Log On As" em Serviços) e essa conta deve ser capaz de EXEC esses procedimentos SSIS. Esta opção não exigiria nenhum certificado ou login porque vocêGRANT EXECUTEneste procedimento armazenado SQLCLR para esse logon restrito e esse procedimento armazenado faz uma conexão nova e independente como um logon privilegiado. Esse procedimento SQLCLR tem uma única finalidade, de modo que o logon como login privilegiado não represente uma ameaça à segurança. A outra peça chave é: devido à conexão ser feita por contexto não personificado, o token de autenticação pode ser passado para uma máquina externa. Mas sim, o Assembly precisará de um PERMISSION_SETde EXTERNAL_ACCESS, e isso deve ser feito assinando o Assembly e, em seguida, criando uma chave assimétrica do assembly e, em seguida, criando um login com base nessa chave assimétrica e, em seguida, concedendo a EXTERNAL_ACCESS ASSEMBLYpermissão para esse login.

        Se for necessária assistência com o lado do banco de dados das etapas de Assembly/Chave assimétrica, escrevi um artigo, Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (registro gratuito obrigatório), que contém um exemplo passo a passo de Fazendo isso; ele simplesmente não mostra a criação da chave privada no Visual Studio.

        ou

      2. Crie uma tabela de filas para a qual seu login restrito tenha permissão INSERT. Em seguida, crie um SQL Agent Job que verifique a tabela de filas e, se um registro "Novo" for encontrado, atualize o status para "Em processamento", chame o procedimento armazenado [msdb]que chama os três procedimentos do SSIS e, finalizado, atualize o registro da fila novamente para "Concluído". Nesse método, o SQL Agent está executando o procedimento armazenado por meio de uma nova conexão de um contexto não representado; portanto, o token de autenticação pode ser passado para uma máquina externa.

    • 2

relate perguntas