TodoSQL Blog de SQL Server en español

A continuación les voy a detallar un problema que nos hizo luchar bastante, espero les sea de utilidad.

Problema: Al ejecutar la sentencia BULK INSERT para carga un archivo de texto en una tabla desde un file server da como resultado “Cannot bulk load because the file "\\SERVER01APPFS\SHARE\GRUPOS\ARCHIVOS\filiales.txt" could not be opened. Operating system error code 5(error not found). ”
Cabe aclarar, que no se puede realizar una inserción Bulk, para este caso, desde una estación remota con double hop.

A) Escenarios:

A.1) Software instalado, equipos y roles involucrados.

Escenario 1

Nombre: WKS20
ROL: WKS - Cliente con tools 2k - Cliente con tools 2k5
Active Directory (Dominio): ADP
Productos instalados:
Windows XP SP2
Cliente/Tools SQL2K SP 3a
Cliente/Tools SQL2K5 SP2

Nombre: SERVER02DB
ROL: Database server
Active Directory (Dominio): ADP
Productos instalados:
Windows 2003 R2 SP2
SQL Server 2005 SP 2

Nombre: SERVER01APPFS
ROL: Aplication Server/File Server
Active Directory (Dominio): ADP
Productos instalados:
Windows 2003 R2 SP2

Escenario 2

Nombre: WKS205
ROL: WKS - Cliente con tools 2k - Cliente con tools 2k5
Dominio: ADP
Productos instalados:
Windows XP SP2
Cliente/Tools SQL2K SP 3a
Cliente/Tools SQL2K5 SP2

Nombre: SERVER02DB
ROL: Database server
Dominio: ADP
Productos instalados:
Windows 2003 R2 SP2
SQL Server 2005 SP 2

Nombre: SXC01ADP0001
ROL: Aplication Server/File Server
Dominio: ADP
Productos instalados:
Windows 2003 SP2

A.2) Permisos.

Ante la necesidad de generar un análisis de entorno chequeamos los "permisos efectivos" sobre los directorios y archivos para los usuarios y grupos afectados a los mismos.
A continuación se detallan los usuarios y recursos afectados:

Dominio en AD = ADP

Usuarios

'ADP\U97865'
'ADP\U97867'
'ADP\U97855'
'ADP\U97833'
'ADP\U97811'
Cuenta de servicio SQL Server 2005 => 'ADP\SVC_SQLSERVER02DB'

Dominio = CENTRAL

Cuenta de servicio SQL Server 2000 => 'CENTRAL\SQLServer '

Directorios(PATH-UNC): Archivos

\\SXC01ADP0001\COM\ARCHIVOS Todos los ubicados en el mismo.
\\SERVER01APPFS\SHARE\GRUPOS\ARCHIVOS Todos los ubicados en el mismo.

Permisos efectivos:

\\SXC01ADP0001\COM\ARCHIVOS

todos tiene permisos de lectura excepto Cuenta de servicio SQL Server 2005'ADP\SVC_SQLSERVER02DB' que no posee permisos.

\\SERVER01APPFS\SHARE\GRUPOS\ARCHIVOS

Los usuarios tienen permisos de lectura/escritura. La cuenta de servicio tienen permisos de lectura pero no están definidos sobre algunas subcarpetas.

El usuario ADP\S80407 afectado a las pruebas, pertenece al rol ‘bulkadmin’ dentro de SQL Server 2005.

B) Casos:

B.1)

Desde la WKS el usuario de ADP\S80407, se conecta al server de base de datos SERVER02DB, ejecutando en este la sentencia BULK INSERT, tomando como origen de datos un archivo de texto en el servidor de aplicación SERVER01APPFS mediante un shared, para luego ser cargado en una tabla, mediante las tools de SQL2K5.

La sentencia completa es la siguiente:

BULK INSERT dbo.Sucursales
FROM '\\SERVER01APPFS\SHARE\GRUPOS\ARCHIVOS\filiales.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

B.1.2) Síntomas:

Arroja el siguiente error.

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\SERVER01APPFS\SHARE\GRUPOS\ARCHIVOS\filiales.txt" could not be opened. Operating system error code 5(error not found).

B.2)

Desde la WKS el usuario de ADP\S80407, se conecta al server de base de datos SERVER02DB, ejecutando en este la sentencia BULK INSERT, tomando como origen de datos un archivo de texto en el servidor de aplicación SXC01ADP0001 mediante un shared, para luego ser cargado en una tabla, mediante las tools de SQL2K5.

La sentencia completa es la siguiente:

BULK INSERT dbo.Sucursales
FROM '\\SXC01ADP0001\transCOM\ARCHIVOS\filiales.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

B.2.1) Síntomas:

Arroja el siguiente error.

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "\\SXC01ADP0001\TransCOM\ARCHIVOS\filiales.txt" could not be opened. Operating system error code 5(error not found).

C) Acciones tomadas:

Se procede a verificar en conjunto con otras áreas, los grupos y permisos al cual pertenece el usuario citado anteriormente, tanto a nivel sistema operativo como motor de base de datos.

Una vez verificado los permisos sobre el shared y archivos afectados, se realizan las pruebas que se detallan a continuación:

1) Se ejecuta la sentencia BULK INSERT mediante las tools de SQL2K5 en la WKS.

Resultado: (Operating system error code 5)
Continua con el mismo problema.

2) Se realiza una conexión desde la WKS y se intenta abrir y editar el archivo con los usuarios Windows afectados.

Resultado: Se edito y abrió el archivo en forma satisfactoria.

3) Se intenta abrir y editar el archivo con los usuarios Windows afectados, desde las tools de SQL2K5 en la WKS.

Resultado: Se edito y abrió el archivo en forma satisfactoria.

4) Se aísla la WKS.

Se ejecuta vía terminal services desde el server SERVER02DB con las tools de SQL 2K5 e impersonando al usuario en cuestión (ADP\U97865)

Resultado: Satisfactorio, el BULK INSERT funciona, si bien rechaza los registros por no estar en el formato indicado.

5) Se reinstala la WKS con todas las tools de SQL2k5 y se vuelve a ejecuta la sentencia BULK INSERT mediante las tools de SQL2K5.

Resultado: (Operating system error code 5)
Continua con el mismo problema.

Resolucion:

Se confirmo que el SPN estuviera configurado correctamente.
Para obligar la conexión a kerberos se verifico que SQL server bajo surface area configuration tuviera "using TCP/IP only".
Se pidió una captura de NETMON y PERFMON se noto que el problema era a nivel SHARE y no NTFS además que se estaba autentificando por medio de NTLM.

30056 55.109375 10.2.249.38 10.2.248.75 SMB Session Setup AndX Request
30062 55.125000 10.2.249.38 10.2.248.75 SMB Tree Connect AndX Request, Path: \\SXC01ADP0001\IPC$
30064 55.140625 10.2.249.38 10.2.248.75 SMB Trans2 Request, GET_DFS_REFERRAL, File: \SXC01ADP0001\xcom
30070 55.140625 10.2.249.38 10.2.248.75 SMB Session Setup AndX Request, NTLMSSP_NEGOTIATE
30080 55.140625 10.2.249.38 10.2.248.75 SMB Session Setup AndX Request, NTLMSSP_AUTH, User: 30083 55.140625 10.2.249.38 SXC01CC0001 SMB SMB: C; Tree Connect Andx, Path = \\SXC01ADP0001\COM, Service = ?????

Se estableció una sesión remota y se corrió una prueba con cuenta local de SQL la cual funciono adecuadamente.
Esto determino que podía ser un problema de cómo esta personificando SQL la cuenta con la que se ejecuta.
Se pidió conformar los siguientes permisos:

The Account is sensitive and cannot be delegated check box must not be selected for the user requesting delegation.
The Account is trusted for delegation check box must be selected for the service account of SQL Server.
The Computer is trusted for delegation check box must be selected for the server running an instance of Microsoft SQL Server.

Se detecto que la cuenta de SQL no contaba con la opción de delegar, se habilito y después del mismo, el Bulk Insert empezó a funcionar.

Abrazo virtual a todos.

Link's Relacionados:

Security Account Delegation
http://msdn.microsoft.com/en-us/library/aa905162(SQL.80).aspx

909801 How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;909801

929650 How to use SPNs when you configure Web applications that are hosted on IIS 6.0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;929650

PROCMON
http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx

NETMON
http://www.microsoft.com/downloads/details.aspx?FamilyID=18b1d59d-f4d8-4...

Bulk Insert Performance Parte 1
http://www.todosql.com/blog/200704/problemas-de-performance-con-utilidad-bcp-o-bulk-insert-(parte-1)

Bulk Insert Performance Parte 2
http://www.todosql.com/blog/200704/problemas-de-performance-con-utilidad...

Bulk Insert Performance Parte 3
http://www.todosql.com/blog/200704/problemas-de-performance-con-utilidad-bcp-o-bulk-insert-(parte-3)

Migrando Datos
http://www.todosql.com/blog/200709/migrando-datos-con-la-utilidad-bcp-de...