9 votos

Llamar sp_start_job a partir de un procedimiento almacenado

Nuestros desarrolladores deben ser capaces de iniciar un trabajo del Agente SQL Server de sus .Net de código. Sé que puedo llamar a la base de datos msdb..sp_start_job para hacer precisamente eso, pero no quiero darle general de cuentas de usuario de acceso directo para ejecutar los trabajos.

Lo que me gustaría hacer es crear un procedimiento almacenado en la aplicación de la base de datos utilizando la cláusula EXECUTE as para suplantar la identidad de una cuenta de proxy. El procedimiento que tenemos es:

CREATE PROCEDURE dbo.StartAgentJob 
    WITH EXECUTE AS 'agentProxy'
AS
BEGIN
    EXEC msdb.dbo.sp_start_job N'RunThisJob';
END

Cuando ejecutamos este, sin embargo, podemos obtener el siguiente mensaje:

The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'.

Alguna idea? Es esta la mejor forma de hacer esto en SQL2005?

9voto

Remus Rusanu Puntos 7248

Me alegro de que te haya resuelto esto, pero el encadenamiento de propiedad no es la solución recomendada. Pues parece válidamente preocupados por la seguridad y el adecuado nivel de detalle de los derechos de que se trate, voy a agregar esta respuesta, aunque tarde, como una referencia a lo que está sucediendo y de cómo resolver este tipo de problemas.

EJECUTAR COMO ámbito de suplantación

El EJECUTAR COMO cláusulas vienen en dos sabores: EJECUTAR COMO inicio de SESIÓN y EJECUTAR COMO USUARIO. El EJECUTAR COMO usuario es autenticado por el servidor y es un contexto de suplantación de confianza por toda la instancia de SQL (ámbito en el servidor):

Cuando la suplantación de un director por mediante la EJECUCIÓN COMO inicio de SESIÓN de instrucción, o dentro de un ámbito en el servidor módulo de mediante la cláusula EXECUTE as, el ámbito de aplicación de la suplantación de identidad en todo el servidor. Esto significa que después de que el contexto interruptor, cualquier recurso dentro del servidor que el inicio de sesión ha suplantado los permisos se pueden acceder.

EJECUTAR COMO USUARIO es autenticado por la base de datos y es un contexto de suplantación de confianza sólo por que la base de datos (base de datos de ámbito):

Sin embargo, cuando la suplantación de un principal mediante el EJECUTAR COMO USUARIO declaración, o dentro de un ámbito de base de datos módulo mediante el uso de la cláusula EXECUTE as, el ámbito de la suplantación es restringido a la base de datos por defecto. Esto significa que las referencias a objetos fuera del ámbito de aplicación de la base de datos devolver un error.

Un procedimiento almacenado que tiene una cláusula EXECUTE as creará una base de datos de la suplantación de ámbito de contexto, y como tal será incapaz de hacer referencia a los objetos fuera de la base de datos, caso en el punto que usted no será capaz de referencia msdb.dbo.sp_start_job porque es en msdb. Hay muchos otros ejemplos, como tratando de acceder a un servidor ámbito del DMV, tratando de utilizar un servidor vinculado o tratando de entregar un mensaje de Service Broker en otra base de datos.

El habilitar una base de datos de ámbito de la suplantación de acceso a un recurso que no sería permitida normalmente el autenticador de la suplantación de la personalidad contexto tiene que ser de confianza. Para una base de datos de ámbito de la suplantación de la aplicación es la base de datos de la dbo. Esto puede lograrse por dos medios:

  • Girando en la CONFIANZA de la propiedad en la base de datos que autenticado el contexto de representación (es decir. la base de datos en la cláusula EXECUTE as fue emitido en).
  • Mediante el uso de firmas de código.

Estos detalles se describen en MSDN: la Ampliación de la Base de datos de la Suplantación de Utilizar EJECUTAR COMO.

Cuando se resuelve el problema mediante el encadenamiento de propiedad entre bases de datos que han permitido la cruz-db encadenamiento en la totalidad de nivel de servidor, que se considera un riesgo para la seguridad. La mayoría de las controladas, de grano fino para lograr el resultado deseado es el uso de firma de código:

  • En la base de datos de aplicación crear un certificado autofirmado
  • signo de la dbo.StartAgentJob con este certificado
  • colocar la clave privada del certificado
  • exportar el certificado a disco
  • importar el certificado en msdb
  • crear un derivado de usuario de la importación de certificado en msdb
  • conceder permiso de autenticación a la derivada de usuario en msdb

Estos pasos aseguran que el EJECUTAR COMO contexto de la dbo.StartAgentJob procedimiento es ahora de confianza en msdb, porque el contexto es firmado por un director que tiene permiso de autenticación en msdb. Esto resuelve la mitad del rompecabezas. La otra mitad es en realidad conceder el permiso de EJECUCIÓN en msdb.dbo.sp_start_job a la ahora de confianza contexto de suplantación. Hay varias maneras de cómo se puede hacer esto:

  1. mapa del usuario suplantado agentProxy usuario msdb y le conceda permiso de ejecución en msdb.dbo.sp_start_job
  2. conceder el permiso de ejecución a la msdb autenticador certificado derivados de usuario
  3. agregar una nueva firma para el procedimiento, se derivan de un usuario en msdb y conceder el permiso de ejecución para esta derivados de usuario

Opción 1. es simple, pero tiene una gran desventaja: la agentProxy de usuario ahora puede ejecutar la msdb.dbo.sp_start_job a su propia voluntad, él es verdaderamente concedido el acceso a la msdb y tiene el permiso de ejecución.

La opción 3 es positevely correcto, pero creo que es innecesario, excesivo.

Así que mi preferido es Opción 2: conceder el permiso de EJECUCIÓN en msdb.dbo.sp_start_job para el certificado derivados de usuario creado en msdb.

Aquí está el SQL correspondiente:

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

Mi blog tiene algunos artículos sobre este tema, escrito en el contexto de Service Broker activado procedimientos (ya que requieren de una cláusula EXECUTE as):

Por CIERTO, si usted está tratando de poner a prueba mi script y usted vive en el hemisferio oriental, o en el reino unido el tiempo de verano, definitivamente leer el último artículo que he enlazado antes de la prueba.

5voto

JohnMcG Puntos 5062

Has puesto el agentProxy de inicio de sesión en la base de datos msdb y dado que los derechos para ejecutar sp_start_job? Si no tendrás que habilitar la base de datos de permisos de encadenamiento para la base de datos msdb y su base de datos de usuario.

Lo mejor seria poner el inicio de sesión en la base de datos msdb y concederle los derechos adecuados.

0voto

KPWINC Puntos 8349

Ya que estamos tratando de iniciar el Agente SQL Server .NET de código, esto podría ser un mejor pregunta de StackOverflow?

http://www.stackoverflow.com

0voto

hil721 Puntos 11

La comprobación de un azar de la Instancia de SQL en la red SQLAgentOperatorRole no le da sp_start_job privledges directamente, hereda desde SQLAgentUserRole .

Doble comprobación de que el uso de :

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'

Ejecute esta en la base de datos MSDB y corroborar que aun no heredado explícita negación de acceso.

hth.

0voto

Wildster Puntos 1

Gran respuesta, sin embargo no se habla acerca de cómo el usuario agentProxy (el usuario que se ejecuta como en el SP) es el programa de instalación en la DB, donde el procedimiento almacenado se guarda (appdb).

Podría por favor explicar esto, ya que no sé cual de inicio de sesión que debo punto de la SP en la EJECUCIÓN COMO parte.

Si puedo crear el inicio de sesión agentProxy con el código: -

create user [agentProxy]  

luego me sale el error: -

Msg 15007, Nivel 16, Estado 1, Línea 1 'agentProxy no es un inicio de sesión válido o no tiene permiso.

Gracias

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: