TodoSQL Blog de SQL Server en español

Hola gente!, les voy a comentar un inconveniente que nos sucedió con el extended stored procedure ‘xp_cmdshell’ en SQL Server 2005 al tratar de borrar unos archivos en el file system utilizados por un proceso de carga de datos.
A continuación les describo el entorno:

Productos instalados

Microsoft Windows 2003 standart edition SP1
MSSQL2005 SP2 standart edition
Framework 2.0
Aplicación financiera v 5.0

Líneas código ejecutadas (TSQL via JOB agendados en SQL Server):

declare @CommandSQL varchar(255)
set @CommandSQL = 'del ' + 'd:\\micarpeta\\subcarpeta \miarchivo.txt' + ' /F /Q'
set @CommandSQL = replace (@CommandSQL, '\\', '\')
exec master..xp_cmdshell @CommandSQL

Seguridad

Se invoca el proceso de carga de datos vía job agendados en SQL Server con la cuenta de la aplicación denominada ‘MTS_MiAplic’ perteneciente a Active Directory, con permisos de db_owner sobre la base de aplicación y perteneciente al rol de base de datos SQLAgentOperatorRole para poder disparar los jobs pertenecientes a la citada aplicación financiera.
La cuenta ‘MTS_MIAPLIC’ posee permisos de full control sobre la carpeta ‘\OLD’ donde reciden los archivos impactados por el proceso y que deben borrarse; en tanto la cuenta ‘dominio\svc_sqlserver’ que corren los servicios de SQL Server, solo posee permisos de ‘read’.

Síntomas:

Se dispara el job sin inconvenientes, pero, al tratar de borrar los archivos utilizados para la carga de datos no puede, y nos devuelve el siguiente error:

Job 'REFRESHDEL' : Step 1, 'BORRA' : Began Executing 2007-07-06 18:06:42

output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
d:\micarpeta\OLD\miarchivo.txt

Access is denied.

(null)

(3 rows(s) affected

Pudimos observar, que al tratarse de un job en batch, al ejecutar el extended stored procedure ‘xp_cmdshell’, se impersona con la cuenta que corre el servicio y está cuenta (en este caso ‘midominio\svc_sqlserver’) no tiene permisos para borrar archivos.

¿De que manera lo solucionamos?

Dando permisos de ‘MODIFY’ sobre la carpeta ‘\OLD’ a la cuenta ‘midominio\svc_sqlserver’ que impersona el servicio de scheduler de SQL Server y la salida fue la siguiente:

Job 'REFRESHDEL' : Step 1, 'BORRA' : Began Executing 2007-07-06 18:07:42

output
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)

(1 rows(s) affected

Si bien podemos solucionarlo dando permisos de ‘execute’ al ‘xp_cmdshell’ y crear una credencial y asociarlo a la cuenta windows de la aplicación 'MTS_MIAPLIC', NO SE PUEDE ejecutar a través de un job batch agendado en SQL Server, pues, siempre impersona la cuenta que ejecuta el servicio de scheduler de SQL Server y no funciona.

Para los que quieran crear la credencial para la cuenta aplicación y llamarlo de otra manera y no por batch job, es decir SIN EJECUTAR por ej: "exec sp_start_job ‘mijobaplicacion’"; deben correr el siguiente stored procedure con una cuenta de autenticación trusted sobre el SQL Server 2005, para que cree la credencial necesaria y de esta manera salir al OS y borrar los archivos sobre el directorio citado con anterioridad:

EXEC sp_xp_cmdshell_proxy_account 'MTS_MIAPLIC', '12345678'

Generara una credencial con el nombre ##xp_cmdshell_proxy_account##

Luego asignarle permisos de ‘execute’ sobre el extended stored procedure ‘xp_cmdshell’ a la cuenta 'MTS_MIAPLIC'.

GRANT EXECUTE ON xp_cmdshell TO MTS_MIAPLIC

Una última aclaración, las credenciales pueden estar asignadas a varios login´s pero los login´s solo a una credencial.

Espero les sirva y una abrazo virtual para todos !