Nossos desenvolvedores precisam ser capazes de iniciar um trabalho do SQL Server Agent a partir de seu código .Net. Eu sei que posso chamar msdb..sp_start_job para fazer exatamente isso, mas eu não quero dar às contas gerais de usuários acesso direto para executar trabalhos.
O que eu gostaria de fazer é criar um procedimento armazenado no banco de dados do aplicativo usando a cláusula WITH EXECUTE AS para representar uma conta de proxy. O procedimento como nós temos é:
CREATE PROCEDURE dbo.StartAgentJob
WITH EXECUTE AS 'agentProxy'
AS
BEGIN
EXEC msdb.dbo.sp_start_job N'RunThisJob';
END
Quando executamos isso, recebemos a seguinte mensagem:
The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.
Alguma ideia? Essa é mesmo a melhor maneira de fazer isso no SQL2005?
Você colocou o login proxyProxy no banco de dados msdb e deu a ele direitos para executar sp_start_job? Caso contrário, você precisará ativar o encadeamento de permissões do banco de dados para o banco de dados msdb e o banco de dados do usuário.
Provavelmente, é melhor colocar o login no banco de dados msdb e conceder a ele os direitos corretos.
Fico feliz por você ter resolvido isso, mas o encadeamento de propriedade não é a solução recomendada. Como você parece validamente preocupado com a segurança e a granularidade adequada dos direitos envolvidos, estou adicionando essa resposta, embora atrasada, como uma referência ao que está acontecendo e como resolver esses problemas.
EXECUTAR como escopo de representação
As cláusulas EXECUTE AS vêm em dois tipos: EXECUTE AS LOGIN e EXECUTE AS USER. O EXECUTE AS LOGIN é autenticado pelo servidor e é um contexto de representação confiável por toda a instância do SQL (com escopo no servidor):
When impersonating a principal by using the EXECUTE AS LOGIN statement, or within a server-scoped module by using the EXECUTE AS clause, the scope of the impersonation is server-wide. This means that after the context switch, any resource within the server that the impersonated login has permissions on can be accessed.
EXECUTAR COMO O USUÁRIO é autenticado pelo banco de dados e é um contexto de representação confiável somente por esse banco de dados (escopo do banco de dados):
However, when impersonating a principal by using the EXECUTE AS USER statement, or within a database-scoped module by using the EXECUTE AS clause, the scope of impersonation is restricted to the database by default. This means that references to objects outside the scope of the database will return an error.
Um procedimento armazenado que tenha uma cláusula EXECUTE AS criará um contexto de representação com escopo de banco de dados e, como tal, não poderá referenciar objetos fora do banco de dados. Caso contrário, você não poderá referenciar msdb.dbo.sp_start_job porque está em %código%. Há muitos outros exemplos disponíveis, como tentar acessar um DMV de escopo de servidor, tentar usar um servidor vinculado ou tentar entregar uma mensagem do Service Broker em outro banco de dados.
A habilitação de uma representação com escopo de banco de dados para acessar um recurso que normalmente não permitiria que o autenticador do contexto de representação fosse confiável. Para uma representação com escopo de banco de dados, o autenticador é o banco de dados dbo. Isto pode ser conseguido por dois meios possíveis:
Esses detalhes são descritos no MSDN: Estendendo a representação de banco de dados usando EXECUTE AS .
Quando você resolveu o problema por meio do encadeamento de propriedade entre bancos de dados, ativou o encadeamento entre bancos de dados em todo o nível do servidor, o que é considerado um risco de segurança. A maneira mais controlada e refinada para obter o resultado desejado é usar a assinatura de código:
msdb com este certificado dbo.StartAgentJob
msdb
msdb
Essas etapas garantem que o contexto EXECUTE AS do procedimento msdb seja agora confiável em dbo.StartAgentJob , porque o contexto é assinado por uma entidade com permissão AUTHENTICATE em msdb . Isso resolve metade do quebra-cabeça. A outra metade é conceder a permissão EXECUTE em msdb ao contexto de representação agora confiável. Existem várias maneiras de como isso pode ser feito:
msdb.dbo.sp_start_job usuário em agentProxy e conceda a ele permissão de execução em msdb
msdb.dbo.sp_start_job authenticator msdb e conceda a permissão de execução a esse usuário derivado A opção 1. é simples, mas tem uma grande desvantagem: o usuário msdb agora pode executar o agentProxy por sua própria vontade, ele realmente recebe acesso a msdb.dbo.sp_start_job e tem permissão de execução.
A opção 3 é positivamente correta, mas eu sinto que é um exagero desnecessário.
Portanto, minha opção preferida é a Opção 2: conceda a permissão EXECUTE em msdb ao usuário derivado do certificado criado em msdb.dbo.sp_start_job .
Aqui está o SQL correspondente:
use [<appdb>];
go
create certificate agentProxy
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
with subject = 'agentProxy'
, start_date='01/01/2009';
go
ADD SIGNATURE TO OBJECT::[StartAgentJob]
BY CERTIFICATE [agentProxy]
WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
go
alter certificate [agentProxy]
remove private key;
go
backup certificate [agentProxy]
to file='c:\temp\agentProxy.cer';
go
use msdb
go
create certificate [agentProxy]
from file='c:\temp\agentProxy.cer';
go
create user [agentProxyAuthenticator]
from certificate [agentProxy];
go
grant authenticate to [agentProxyAuthenticator];
grant execute on msdb.dbo.sp_start_job to [agentProxyAuthenticator];
go
use [<appdb>];
go
exec dbo.StartAgentJob;
go
Meu blog tem alguns artigos sobre esse tópico, escritos no contexto de procedimentos ativados pelo Service Broker (já que eles exigem uma cláusula EXECUTE AS):
BTW, se você estiver tentando testar meu roteiro e morar no hemisfério oriental, ou no horário de verão do Reino Unido, leia definitivamente esse último artigo que eu vinculei antes do teste.
Como você está tentando iniciar o SQL Server Agent a partir do código .NET, essa pode ser uma pergunta melhor para o StackOverflow?
Verificando uma Instância SQL aleatória na rede SQLAgentOperatorRole não fornece privilégios sp_start_job diretamente, ela os herda de SQLAgentUserRole.
Verifique novamente usando:
select dp.NAME AS principal_name,
dp.type_desc AS principal_type_desc,
o.NAME AS object_name,
p.permission_name,
p.state_desc AS permission_state_desc
from sys.database_permissions p
left OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID
inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id
where o.name = 'sp_start_job'
Execute isso no MSDB e verifique novamente se você não herdou nenhum acesso de negação explícito.
hth.
Uma maneira de conseguir isso sem conceder permissões adicionais: não deixe o procedimento armazenado iniciar o trabalho diretamente, mas apenas permita que o procedimento armazenado seja invertido em uma tabela (no banco de dados do aplicativo); então, deixe o trabalho rodar a cada minuto ou mais, verifique se o bit está invertido e, em caso afirmativo, execute o trabalho e inverta o bit novamente. Se o trabalho perceber que o bit não foi invertido, o trabalho simplesmente sairá.
Funciona como um encanto, se você não se importa com o atraso (e o trabalho é executado com muita frequência).
Tags sql sql-server sql-server-2005