Llamar a sp_start_job desde un procedimiento almacenado

Posteriormente a mirar en diferentes repositorios y foros de internet al concluir hemos dado con la respuesta que te enseñamos pronto.

Solución:

Me alegro de que hayas resuelto esto, pero el encadenamiento de propiedad no es la solución recomendada. Dado que parece legítimamente preocupado por la seguridad y la granularidad adecuada de los derechos involucrados, agrego esta respuesta, aunque tarde, como una referencia a lo que está sucediendo y cómo resolver estos problemas.

EJECUTAR COMO ámbito de suplantación

Las cláusulas EXECUTE AS vienen en dos formas: EXECUTE AS LOGIN y EXECUTE AS USER. EXECUTE AS LOGIN es autenticado por el servidor y es un contexto de suplantación en el que confía toda la instancia de SQL (alcance del servidor):

Al suplantar a un principal mediante la instrucción EXECUTE AS LOGIN, o dentro de un módulo de ámbito de servidor mediante la cláusula EXECUTE AS, el ámbito de la suplantación es para todo el servidor. Esto significa que después del cambio de contexto, se puede acceder a cualquier recurso dentro del servidor en el que el inicio de sesión suplantado tenga permisos.

EXECUTE AS USER está autenticado por la base de datos y es un contexto de suplantación en el que solo confía esa base de datos (alcance de la base de datos):

Sin embargo, al suplantar a un principal mediante la instrucción EXECUTE AS USER, o dentro de un módulo de base de datos mediante la cláusula EXECUTE AS, el alcance de la suplantación se restringe a la base de datos de forma predeterminada. Esto significa que las referencias a objetos fuera del alcance de la base de datos devolverán un error.

Un procedimiento almacenado que tiene una cláusula EXECUTE AS creará un contexto de suplantación en el ámbito de la base de datos y, como tal, no podrá hacer referencia a objetos fuera de la base de datos, por ejemplo, no podrá hacer referencia a msdb.dbo.sp_start_job porque está en msdb. Hay muchos otros ejemplos disponibles, como intentar acceder a un DMV de ámbito de servidor, intentar utilizar un servidor vinculado o intentar enviar un mensaje de Service Broker a otra base de datos.

Habilitar una suplantación de ámbito de base de datos para acceder a un recurso al que normalmente no se le permitiría el autenticador Se debe confiar en el contexto de suplantación. Para una suplantación de ámbito de base de datos, el autenticador es el dbo de la base de datos. Esto se puede lograr por dos medios posibles:

• Activando la propiedad TRUSTWORTHY en la base de datos que autenticó el contexto de suplantación (es decir, la base de datos en la que se emitió la cláusula EXECUTE AS).
• Mediante el uso de firmas de código.

Estos detalles se describen en MSDN: Ampliación de la suplantación de la base de datos mediante el uso de EXECUTE AS.

Cuando resolvió el problema a través del encadenamiento de propiedad de bases de datos cruzadas, habilitó el encadenamiento de bases de datos cruzadas en todo el nivel del servidor, lo que se considera un riesgo de seguridad. La forma más controlada y detallada de lograr el resultado deseado es usar la firma de código:

• En la base de datos de la aplicación, cree un certificado autofirmado
• firma el dbo.StartAgentJob con este certificado
• dejar el privado key del certificado
• exportar el certificado al disco
• importar el certificado en msdb
• crear un usuario derivado del certificado importado en msdb
• otorgar permiso de AUTENTICACIÓN al usuario derivado en msdb

Estos pasos aseguran que el contexto EXECUTE AS del dbo.StartAgentJob ahora se confía en el procedimiento msdbporque el contexto está firmado por una entidad de seguridad que tiene el permiso AUTENTICAR en msdb. Esto resuelve la mitad del rompecabezas. La otra mitad es otorgar el permiso EXECUTE en msdb.dbo.sp_start_job al contexto de suplantación de confianza ahora. Hay varias maneras de cómo se puede hacer esto:

1. mapear el usuario suplantado agentProxy usuario en msdb y concédele permiso de ejecución en msdb.dbo.sp_start_job
2. conceder el permiso de ejecución a la msdb usuario derivado del certificado autenticador
3. agregar una nueva firma al procedimiento, derivar un usuario para él en msdb y otorgar el permiso de ejecución a este usuario derivado

La opción 1. es simple, pero tiene una gran desventaja: la agentProxy el usuario ahora puede ejecutar el msdb.dbo.sp_start_job por su propia voluntad, se le concede verdaderamente acceso a msdb y tiene el permiso de ejecución.

La opción 3 es positivamente correcta, pero creo que es una exageración innecesaria.

Así que mi preferida es la Opción 2: otorgar el permiso EJECUTAR en msdb.dbo.sp_start_job al usuario derivado del certificado creado en msdb.

Aquí está el SQL correspondiente:

use [];
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:tempagentProxy.cer';
go

use msdb
go

create certificate [agentProxy] 
  from file='c:tempagentProxy.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 [];
go

exec dbo.StartAgentJob;
go

Mi blog tiene algunos artículos que cubren este tema, escritos en el contexto de los procedimientos activados de Service Broker (ya que requieren una cláusula EXECUTE AS):

• Firmar un Procedimiento Activado
• Llamar a un procedimiento en otra base de datos desde un procedimiento activado
• ¿Por qué la función… no funciona con la activación?
• Certificado aún no válido

Por cierto, si está tratando de probar mi script y vive en el hemisferio este, o en el horario de verano del Reino Unido, definitivamente lea el último artículo que vinculé antes de la prueba.

¿Ha colocado el inicio de sesión de agentProxy en la base de datos msdb y le ha dado derechos para ejecutar sp_start_job? De lo contrario, deberá habilitar el encadenamiento de permisos de base de datos para la base de datos msdb y su base de datos de usuario.

Probablemente sea mejor poner el inicio de sesión en la base de datos msdb y otorgarle los derechos correctos.

Si sostienes alguna duda y forma de progresar nuestro crónica puedes realizar un exégesis y con mucho gusto lo observaremos.

---

---

Utiliza Nuestro Buscador