none
Depurar MSSQL Server 2008 R2 RRS feed

  • Pregunta

  • Hola. Acudo a uds. porque llevo bastante tiempo intentando encontrar la solucion y no lo logro. Ojala puedan darme una mano con esto.

    El asunto es el siguiente:

    Tengo un server sql 2k8 r2 configurado de cero y con algunas bases de datos. Al hacer una nueva consulta sobre cualquiera de estas bases (print 'hola', por ejemplo) sale todo bien. Pruebo depurar (F10 debug) sobre la misma sentencia y funciona correctamente. Como veran,  todo funciona.

    El problema viene cuando intento reproducir la depuracion en un equipo que se conecta al servidor mencionado. La conexion con las bases de datos es satisfactoria. Dice:

    "No se puede iniciar la sesión de depuración de T-SQL. No se pudo conectar con el equipo XXXXX. Error de inicio de sesión: nombre de usuario desconocido o contraseña incorrecta"

    Me extraña muchisimo esto porque el usuario que uso en el servidor es el mismo que uso en el equipo que se conecta a este. Y en el servidor la depuracion funciona sin problemas. Repito: El usuario y contraseña es el mismo en ambos casos. Tanto en servidor como en equipo cliente.

    Espero haber sido claro con mi problema. Les agradezco mucho desde ya por su tiempo y ojala puedan darme una mano.

    Gracias de nuevo

    EDIT: Las conexiones remotas estan habilitadas en el server

    EDIT 2: El firewall esta deshabilitado de ambos lados. Tanto en el server como en el cliente

    • Editado EstebanR viernes, 20 de diciembre de 2013 12:28
    jueves, 19 de diciembre de 2013 20:21

Respuestas

Todas las respuestas

  • Podrias subir a algun servicio el error log o buscar y pegar la parte que hace mencion a esto?.  
    jueves, 19 de diciembre de 2013 23:38
  • Si te funciona en el servidor llo tienes bien configurado, supongo que sera el firewall,

    para depurar TSQL desde un equipo remoto debes configurar el Firewall de windows en el equipo y el servidor.

    Mira esto:

    http://msdn.microsoft.com/es-es/library/vstudio/s0fk6z6e%28v=vs.100%29.aspx

    Saludos

    viernes, 20 de diciembre de 2013 10:44
  • Buenos dias. Este deberia ser el log. Esta es la ruta de donde lo saque: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log"

    Resalto la parte donde creo que esta el problema.

    2013-12-19 07:04:45.85 Server      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) 

    Apr  2 2010 15:48:46 
    Copyright (c) Microsoft Corporation
    Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)

    2013-12-19 07:04:46.14 Server      (c) Microsoft Corporation.
    2013-12-19 07:04:46.14 Server      All rights reserved.
    2013-12-19 07:04:46.14 Server      Server process ID is 1088.
    2013-12-19 07:04:46.15 Server      System Manufacturer: 'Bochs', System Model: 'Bochs'.
    2013-12-19 07:04:46.15 Server      Authentication mode is MIXED.
    2013-12-19 07:04:46.17 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
    2013-12-19 07:04:46.23 Server      This instance of SQL Server last reported using a process ID of 1480 at 17/12/2013 05:58:56 p.m. (local) 17/12/2013 08:58:56 p.m. (UTC). This is an informational message only; no user action is required.
    2013-12-19 07:04:46.23 Server      Registry startup parameters: 
    -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
    -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG
    -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
    2013-12-19 07:04:46.39 Servidor    SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2013-12-19 07:04:46.40 Servidor    Detected 1 CPUs. This is an informational message; no user action is required.
    2013-12-19 07:04:47.34 Servidor    Using locked pages for buffer pool.
    2013-12-19 07:04:49.50 Servidor    Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
    2013-12-19 07:04:54.11 Servidor    Node configuration: node 0: CPU mask: 0x0000000000000001:0 Active CPU mask: 0x0000000000000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2013-12-19 07:04:56.17 spid7s      Starting up database 'master'.
    2013-12-19 07:04:57.31 spid7s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2013-12-19 07:04:58.76 spid7s      Resource governor reconfiguration succeeded.
    2013-12-19 07:04:58.84 spid7s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2013-12-19 07:04:59.03 spid7s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2013-12-19 07:04:59.16 spid7s      FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.
    2013-12-19 07:05:00.78 spid7s      SQL Trace ID 1 was started by login "sa".
    2013-12-19 07:05:01.07 spid7s      Starting up database 'mssqlsystemresource'.
    2013-12-19 07:05:01.20 spid7s      The resource database build version is 10.50.1600. This is an informational message only. No user action is required.
    2013-12-19 07:05:02.77 spid7s      Server name is 'WIN2K8TEST'. This is an informational message only. No user action is required.
    2013-12-19 07:05:02.80 spid11s     Starting up database 'model'.
    2013-12-19 07:05:04.30 spid11s     Clearing tempdb database.
    2013-12-19 07:05:05.69 Servidor    A self-generated certificate was successfully loaded for encryption.
    2013-12-19 07:05:05.81 Servidor    Server is listening on [ 'any' <ipv6> 1433].
    2013-12-19 07:05:05.81 Servidor    Server is listening on [ 'any' <ipv4> 1433].
    2013-12-19 07:05:05.82 Servidor    Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2013-12-19 07:05:05.82 Servidor    Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2013-12-19 07:05:05.94 Servidor    Server is listening on [ ::1 <ipv6> 1434].
    2013-12-19 07:05:05.94 Servidor    Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2013-12-19 07:05:05.94 Servidor    Dedicated admin connection support was established for listening locally on port 1434.
    2013-12-19 07:05:06.25 Servidor    The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2013-12-19 07:05:06.28 Servidor    SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2013-12-19 07:05:10.70 spid11s     Starting up database 'tempdb'.
    2013-12-19 07:05:13.07 spid10s     The Service Broker protocol transport is disabled or not configured.
    2013-12-19 07:05:13.12 spid10s     The Database Mirroring protocol transport is disabled or not configured.
    2013-12-19 07:05:14.52 spid10s     Service Broker manager has started.
    2013-12-19 07:05:20.96             Error: 18456, gravedad: 14, estado: 38.
    2013-12-19 07:05:20.96             Login failed for user 'NT AUTHORITY\SYSTEM'. Motivo: no se puede abrir la base de datos explícitamente especificada. [CLIENTE: <local machine>]
    2013-12-19 07:05:26.27             Error: 18456, gravedad: 14, estado: 38.
    2013-12-19 07:05:26.27             Login failed for user 'NT AUTHORITY\SYSTEM'. Motivo: no se puede abrir la base de datos explícitamente especificada. [CLIENTE: <local machine>]
    2013-12-19 07:05:29.96 spid14s     A new instance of the full-text filter daemon host process has been successfully started.
    2013-12-19 07:05:30.71 spid14s     Starting up database 'msdb'.
    2013-12-19 07:05:30.71 spid17s     Starting up database 'ReportServer'.
    2013-12-19 07:05:30.72 spid18s     Starting up database 'ReportServerTempDB'.
    2013-12-19 07:05:30.72 spid19s     Starting up database 'Prevencion2'.
    2013-12-19 07:05:32.74 spid18s     Starting up database 'Prevencion'.
    2013-12-19 07:05:34.88 spid7s      Recovery completed for database Prevencion (database ID 8) in 1 second(s) (analysis 113 ms, redo 0 ms, undo 176 ms.) This is an informational message only. No user action is required.
    2013-12-19 07:05:35.67 spid7s      Recovery is complete. This is an informational message only. No user action is required.
    2013-12-19 07:05:40.70 spid52      Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
    2013-12-19 07:05:40.71 spid52      Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    2013-12-19 07:05:41.39 spid52      Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
    2013-12-19 07:05:41.44 spid52      Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2013-12-19 07:05:41.67 spid52      Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.
    2013-12-19 07:05:41.71 spid52      Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    2013-12-19 12:24:59.27             Error: 18456, gravedad: 14, estado: 8.
    2013-12-19 12:24:59.27             Login failed for user 'sa'. Motivo: la contraseña no es válida para el inicio de sesión proporcionado. [CLIENTE: 172.16.10.103]
    2013-12-19 12:55:20.56 spid51      Starting up database 'Virt_Test_Medimas'.
    2013-12-19 12:55:20.84 spid51      Setting database option COMPATIBILITY_LEVEL to 100 for database Virt_Test_Medimas.
    2013-12-19 12:55:20.92 spid51      Setting database option ANSI_NULL_DEFAULT to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:20.93 spid51      Setting database option ANSI_NULLS to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:20.94 spid51      Setting database option ANSI_PADDING to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:20.95 spid51      Setting database option ANSI_WARNINGS to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:20.96 spid51      Setting database option ARITHABORT to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:20.97 spid51      Setting database option AUTO_CLOSE to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:20.98 spid51      Setting database option AUTO_CREATE_STATISTICS to ON for database Virt_Test_Medimas.
    2013-12-19 12:55:20.99 spid51      Setting database option AUTO_SHRINK to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.00 spid51      Setting database option AUTO_UPDATE_STATISTICS to ON for database Virt_Test_Medimas.
    2013-12-19 12:55:21.01 spid51      Setting database option CURSOR_CLOSE_ON_COMMIT to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.04 spid51      Setting database option CURSOR_DEFAULT to GLOBAL for database Virt_Test_Medimas.
    2013-12-19 12:55:21.05 spid51      Setting database option CONCAT_NULL_YIELDS_NULL to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.06 spid51      Setting database option NUMERIC_ROUNDABORT to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.07 spid51      Setting database option QUOTED_IDENTIFIER to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.09 spid51      Setting database option RECURSIVE_TRIGGERS to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.11 spid51      Setting database option DISABLE_BROKER to ON for database Virt_Test_Medimas.
    2013-12-19 12:55:21.14 spid51      Setting database option AUTO_UPDATE_STATISTICS_ASYNC to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.15 spid51      Setting database option DATE_CORRELATION_OPTIMIZATION to OFF for database Virt_Test_Medimas.
    2013-12-19 12:55:21.17 spid51      Setting database option PARAMETERIZATION to SIMPLE for database Virt_Test_Medimas.
    2013-12-19 12:55:21.18 spid51      Setting database option READ_WRITE to ON for database Virt_Test_Medimas.
    2013-12-19 12:55:21.18 spid51      Setting database option RECOVERY to FULL for database Virt_Test_Medimas.
    2013-12-19 12:55:21.19 spid51      Setting database option MULTI_USER to ON for database Virt_Test_Medimas.
    2013-12-19 12:55:21.19 spid51      Setting database option PAGE_VERIFY to CHECKSUM for database Virt_Test_Medimas.
    2013-12-19 12:56:10.63 spid52      Starting up database 'Virt_Test_Medimas'.
    2013-12-19 12:56:10.66 spid52      The database 'Virt_Test_Medimas' is marked RESTORING and is in a state that does not allow recovery to be run.
    2013-12-19 12:56:11.28 spid52      Starting up database 'Virt_Test_Medimas'.
    2013-12-19 12:56:12.89             Restore is complete on database 'Virt_Test_Medimas'.  The database is now available.
    2013-12-19 12:56:12.90             Database was restored: Database: Virt_Test_Medimas, creation date(time): 2011/12/15(11:57:17), first LSN: 3019:1111:108, last LSN: 3019:1155:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\BasesTest\Test_Medimas.bak'}). Informational message. No user action required.
    2013-12-19 12:56:43.97 spid52      Starting up database 'Virt_Test_Premedic'.
    2013-12-19 12:56:44.13 spid52      Setting database option COMPATIBILITY_LEVEL to 100 for database Virt_Test_Premedic.
    2013-12-19 12:56:44.16 spid52      Setting database option ANSI_NULL_DEFAULT to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.17 spid52      Setting database option ANSI_NULLS to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.20 spid52      Setting database option ANSI_PADDING to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.21 spid52      Setting database option ANSI_WARNINGS to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.23 spid52      Setting database option ARITHABORT to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.24 spid52      Setting database option AUTO_CLOSE to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.25 spid52      Setting database option AUTO_CREATE_STATISTICS to ON for database Virt_Test_Premedic.
    2013-12-19 12:56:44.26 spid52      Setting database option AUTO_SHRINK to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.27 spid52      Setting database option AUTO_UPDATE_STATISTICS to ON for database Virt_Test_Premedic.
    2013-12-19 12:56:44.28 spid52      Setting database option CURSOR_CLOSE_ON_COMMIT to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.30 spid52      Setting database option CURSOR_DEFAULT to GLOBAL for database Virt_Test_Premedic.
    2013-12-19 12:56:44.31 spid52      Setting database option CONCAT_NULL_YIELDS_NULL to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.36 spid52      Setting database option NUMERIC_ROUNDABORT to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.37 spid52      Setting database option QUOTED_IDENTIFIER to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.38 spid52      Setting database option RECURSIVE_TRIGGERS to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.39 spid52      Setting database option DISABLE_BROKER to ON for database Virt_Test_Premedic.
    2013-12-19 12:56:44.40 spid52      Setting database option AUTO_UPDATE_STATISTICS_ASYNC to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.41 spid52      Setting database option DATE_CORRELATION_OPTIMIZATION to OFF for database Virt_Test_Premedic.
    2013-12-19 12:56:44.42 spid52      Setting database option PARAMETERIZATION to SIMPLE for database Virt_Test_Premedic.
    2013-12-19 12:56:44.43 spid52      Setting database option READ_WRITE to ON for database Virt_Test_Premedic.
    2013-12-19 12:56:44.45 spid52      Setting database option RECOVERY to FULL for database Virt_Test_Premedic.
    2013-12-19 12:56:44.46 spid52      Setting database option MULTI_USER to ON for database Virt_Test_Premedic.
    2013-12-19 12:56:44.46 spid52      Setting database option PAGE_VERIFY to CHECKSUM for database Virt_Test_Premedic.
    2013-12-19 12:58:24.86 spid52      Starting up database 'Virt_Test_Premedic'.
    2013-12-19 12:58:24.88 spid52      The database 'Virt_Test_Premedic' is marked RESTORING and is in a state that does not allow recovery to be run.
    2013-12-19 12:58:25.81 spid52      Starting up database 'Virt_Test_Premedic'.
    2013-12-19 12:58:27.75             Restore is complete on database 'Virt_Test_Premedic'.  The database is now available.
    2013-12-19 12:58:27.76             Database was restored: Database: Virt_Test_Premedic, creation date(time): 2012/01/04(11:03:19), first LSN: 3634:1435:280, last LSN: 3634:1546:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\BasesTest\Test_Premedic.bak'}). Informational message. No user action required.
    2013-12-19 13:02:34.94             Error: 18456, gravedad: 14, estado: 5.
    2013-12-19 13:02:34.94             Login failed for user 'Administrador'. Motivo: no se encontró un inicio de sesión que coincida con el nombre proporcionado. [CLIENTE: 172.16.10.103]
    2013-12-20 00:00:58.49 spid22s     This instance of SQL Server has been using a process ID of 1088 since 19/12/2013 07:05:35 a.m. (local) 19/12/2013 10:05:35 a.m. (UTC). This is an informational message only; no user action is required.

    Sigo sin poder entender la razon del problema, porque me es posible conectarme al servidor con un cliente remoto usando el mismo usuario y contraseña que uso en el server.

    Espero puedan ayudarme. Muchas gracias de nuevo por su tiempo

    viernes, 20 de diciembre de 2013 13:50
  • Hola Antonio. Gracias por la respuesta.

    Los pasos de configuracion del firewall ya los hice antes y no obtuve resultados. Lo que sí no hice fue esto: 

    Para preparar los permisos SQL Server en la instancia de SQL Server.

    Como dice en el articulo que me pasaste.

    Disculpa mi ignorancia pero en donde tendria que ejecutar esos comandos que indica el articulo?

    Gracias por tu tiempo

    viernes, 20 de diciembre de 2013 14:02
  • Hola Esteban,

    los permisos a que se refiere son de la cuanta de dominio que utilizas en el equipo 'remoto' que tambien deberia estar como usuario en la instacia de Sql con el rol de Serversysadmin. De no estar tendrias que crear el Login y despues el usuario en las base de datos a las que vayas a acceder, agregandole este rol.

    Se supone que el equipo y el servidor estan en el mismo dominio y que no tendras problemas en agregar este usuario de windows a Sql y darle los permisos adecuados. No obstante hazlo como indica en el artículo con el comando:

        sp_addsrvrolemember 'Domain\Name', 'sysadmin'

    Para hacer esto tendras que hacerlo con un usuario que tambien sea administrador de Sql.

    Prueba a ver que tal te va.

    Saludos.

    • Propuesto como respuesta Lincoln_VS martes, 24 de diciembre de 2013 5:53
    lunes, 23 de diciembre de 2013 10:26