Infra

Sep

2024

PBIRS vs Power BI Service

Cuando hablamos de soluciones de Business Intelligence (BI) dentro del ecosistema de Microsoft, la primera opción que viene a la mente es Power BI. Sin embargo, dentro de esta herramienta tan robusta existen dos variantes principales que, a menudo, generan dudas sobre cuál elegir: Power BI Report Server (PBIRS) y el servicio de Power BI en la nube. En este artículo, exploraremos en profundidad las características, ventajas y limitaciones de cada opción, ayudándonos a discernir cuál se adapta mejor a nuestras necesidades organizativas.

¿Qué es Power BI?

Power BI es una suite de herramientas de análisis empresarial (BI) desarrollada por Microsoft que nos permite convertir datos en información útil mediante informes interactivos y visualizaciones impactantes. Dentro de Power BI tenemos integración de manera efectiva con una amplia gama de fuentes de datos, permitiéndonos analizar y compartir insights con facilidad prácticamente sea cual sea su origen. Además de poder combinar en un solo modelo de datos e informes datos de varios orígenes.

Hemos hablado de modelo de datos e informes y es que, Power BI Desktop (la aplicación que se instala en el ordenador y nos permite a los usuarios crear informes complejos y dashboards interactivos) consta de dos partes principales. Por un lado la integración de datos de los informes y su adaptación (lo que tradicionalmente se conoce como ETL) a cargo de Power Query y la propia aplicación para diseñar los informes que además permite cálculos avanzados con DAX.

Así, a grandes rasgos, con Power BI Desktop, podemos conectar, transformar y modelar datos antes de visualizarlos en gráficos y tablas que ayuden a tomar decisiones informadas. La interfaz es intuitiva y, a pesar de su poder, está diseñada para ser accesible tanto a analistas de datos experimentados como a usuarios menos técnicos. Una vez que los informes están listos, los podemos publicar y compartir a través de Power BI Service (en la nube) o mediante Power BI Report Server (PBIRS), según la infraestructura y las necesidades de la organización.

Entendiendo PBIRS y Power BI en la Nube

Antes de entrar en sus diferencias, es importante entender qué son PBIRS y el servicio de Power BI en la nube. PBIRS es una plataforma local de informes basada en SSRS que nos permite mantener los datos y reportes dentro de nuestra infraestructura local, sin necesidad de depender de servicios externos. Esta opción es especialmente útil para aquellas empresas con estrictos requisitos de seguridad o que operan en sectores altamente regulados. Por otro lado, Power BI en la nube ofrece un servicio completamente gestionado por Microsoft, con actualizaciones frecuentes, escalabilidad casi ilimitada y acceso desde cualquier lugar.

Seguridad y Cumplimiento. Punto para PBIRS

Uno de los principales argumentos a favor de PBIRS es la seguridad. Muchas organizaciones tienen normativas estrictas sobre dónde deben residir los datos, lo que hace que la opción de mantener todo «en casa» sea atractiva. Con PBIRS, el control total sobre los servidores, bases de datos y la red es una realidad. Esto es crucial en sectores como el financiero, sanitario o gubernamental, donde el cumplimiento de normativas es ineludible.

Además, PBIRS permite la integración directa con las políticas de seguridad corporativas existentes, como Active Directory, lo que facilita la implementación de controles de acceso granulares y personalizados. En contraste, Power BI en la nube, aunque seguro y conforme a muchas normativas internacionales, deja el control de la infraestructura en manos de Microsoft, lo que puede no ser ideal para todas las organizaciones.

Flexibilidad y Personalización. Otro punto para PBIRS

PBIRS nos ofrece una mayor flexibilidad en términos de personalización y control de la infraestructura. Podemos ajustar los servidores a las necesidades específicas de nuestros informes y modelos de datos, lo que es fundamental cuando trabajamos con grandes volúmenes de información o requerimos configuraciones especializadas. Además, PBIRS permite utilizar Reporting Services, Power BI y Excel, lo que proporciona una solución integral para la gestión de informes en una única plataforma.

En contraste, Power BI en la nube se enfoca más en la simplicidad y la facilidad de uso. Aunque ofrece un entorno muy completo, su flexibilidad en cuanto a personalización es menor, ya que estamos limitados a las opciones y configuraciones que Microsoft ha diseñado para el servicio. Sin embargo, esta «limitación» viene acompañada de una gestión simplificada y la eliminación de la carga de mantenimiento y actualizaciones de la infraestructura.

Licenciamiento. Punto para el Servicio

Un aspecto clave en la decisión de optar por PBIRS o Power BI en la nube, es el modelo de licenciamiento. En PBIRS, los usuarios pueden consultar informes sin necesidad de adquirir licencias adicionales. Una vez que el servidor está configurado y licenciado, cualquier usuario de la organización con acceso al servidor puede visualizar los informes sin coste adicional. Sin embargo, las opciones de licenciamiento de PBIRS son escasas y caras, muy caras. Realmente no podemos licenciar exclusivamente PBIRS y, si lo queremos usar debemos adquirir una licencia de otro producto que incluya este. Estas licencias de otros productos que incluyen PBIRS son SQL Server Enterprise con Software Assurance o una capacidad Premium de Power BI (mínimo una F64 de instancia reservada y no pago por uso).

Este modelo contrasta con el de Power BI en la nube, donde cada usuario que quiera acceder a los informes debe contar con una licencia, ya sea Power BI Pro o Premium. Aunque este modelo de suscripción tiene sus ventajas en términos de escalabilidad y simplicidad de gestión, puede resultar costoso para organizaciones grandes o aquellas con muchos usuarios ocasionales.

Esta diferencia en el licenciamiento hace que PBIRS sea poco atractivo ya que muchas empresas no pueden permitirse el desembolso de dinero del que estamos hablando. Una licencia de SQL Server Enterprise cuesta unos 14.000€ por cada dos cores del servidor (y que menos que 8 cores para un servidor decente, lo que suman ya más 55.000€ ) más luego la suscripción del Software Assurance y, para el otro modo de licenciamiento, una instancia reservada con capacidad F64 tiene un coste de suscripción de unos 8000€ al mes.

Escalabilidad y Mantenimiento. Otro punto para el servicio

La escalabilidad es otro aspecto donde las diferencias entre PBIRS y Power BI en la nube se hacen evidentes. Power BI en la nube ofrece una escalabilidad casi ilimitada, ya que la infraestructura de Microsoft Azure se encarga de todo. Esto significa que podemos empezar con un pequeño proyecto piloto y escalar sin problemas a nivel empresarial sin necesidad de preocuparnos por la capacidad del servidor o el rendimiento, solo por el coste.

Por otro lado, con PBIRS, la escalabilidad depende completamente de nuestra infraestructura local. Si nuestras necesidades crecen, deberemos estar preparados para invertir en más hardware, espacio y, seguramente, más personal para gestionar y mantener el entorno. Esto puede ser una barrera para organizaciones en rápido crecimiento o que experimentan picos estacionales en la demanda de informes.

El mantenimiento es otro punto clave. Power BI en la nube se actualiza automáticamente, con nuevas características y mejoras implementadas por Microsoft de manera constante. Esto garantiza que siempre tengamos acceso a la última tecnología sin necesidad de realizar cambios manuales en nuestra infraestructura. En cambio, con PBIRS, somos responsables de aplicar las actualizaciones y parches, lo que requiere un equipo dedicado y una planificación cuidadosa para evitar interrupciones en el servicio.

Costes y Retorno de la Inversión. ¿Empate?

A la hora de evaluar PBIRS frente a Power BI en la nube, los costes son un factor determinante. PBIRS suele requerir una inversión inicial significativa en hardware, licencias y recursos humanos. Además, los costes de mantenimiento y actualización deben considerarse a largo plazo. Sin embargo, para organizaciones que ya disponen de una infraestructura robusta, este coste puede ser amortizado más fácilmente.

Por otro lado, Power BI en la nube sigue un modelo de suscripción, lo que permite empezar con costes más bajos y escalarlos según el uso y las necesidades. Aunque a largo plazo, las suscripciones pueden acumularse, ofrecen la ventaja de no requerir una inversión inicial significativa y permiten a las organizaciones ajustar sus gastos según la evolución de sus requerimientos.

El retorno de la inversión (ROI) en ambos casos depende en gran medida de la naturaleza de la organización y de cómo se utilice la herramienta. PBIRS puede ofrecer un ROI más alto en entornos donde la seguridad y el control son primordiales, mientras que Power BI en la nube podría ofrecer un mejor ROI para organizaciones que valoran la flexibilidad y la capacidad de escalar rápidamente.

Facilidad de Implementación y Adopción. El cloud gana esta batalla

La facilidad de implementación es otra área donde Power BI en la nube sobresale. Al ser un servicio gestionado, la configuración inicial es mínima y la adopción por parte de los usuarios finales suele ser más rápida. Los informes pueden compartirse fácilmente, y el acceso a los mismos está garantizado desde cualquier lugar y dispositivo, lo que fomenta una cultura de datos más abierta y colaborativa.

Por otro lado, PBIRS puede requerir un proceso de implementación más complejo, especialmente si no contamos con una infraestructura avanzada o experiencia en la gestión de servidores de informes.

¿PBIRS o Power BI en la Nube?

La elección entre PBIRS y Power BI en la nube no es sencilla y depende en gran medida de las necesidades específicas de cada organización. Si la seguridad, el cumplimiento normativo y el control absoluto sobre la infraestructura son prioridades, PBIRS es la opción ideal. Si ya contamos en nuestra organización con una licencia de SQL Server Enterprise con SA ese problema de costes de licenciamiento se diluye y PBIRS pasa a ser una opción muy atractiva. Además, el hecho de que no se necesitan licencias adicionales para que los usuarios visualicen informes puede representar un ahorro significativo, en entornos con un gran número de usuarios.

Sin embargo, si valoramos la escalabilidad, la facilidad de uso y la reducción de la carga de mantenimiento, Power BI en la nube se posiciona como la opción más adecuada. Aunque implica un coste por usuario, la flexibilidad y el acceso global que ofrece son difíciles de igualar.

Conclusión

En resumen, ambas herramientas son complementarias y podríamos combinar un servicio en la nube con uno local. La clave está en evaluar cuidadosamente las necesidades de nuestra organización, los recursos disponibles y los objetivos a largo plazo antes de tomar una decisión. Al hacerlo, garantizamos que estamos invirtiendo en la solución que mejor se alinea con nuestra estrategia de BI.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Antes de cerrar este artículo me gustaría agradecer la inestimable ayuda de mi amigo Ricardo Rincón, experto MVP en Power BI que me ha asesorado y ayudado, sobre todo a poner algo de luz en el tema del licenciamiento.

Sep

2024

Transacciones Distribuidas y DTC

Cuando hablamos de sistemas de bases de datos, uno de los retos más grandes es garantizar la consistencia de los datos en entornos distribuidos. No suele ser lo común pero, a medida que las arquitecturas de aplicaciones se vuelven más complejas, surge la necesidad de coordinar múltiples transacciones que puedan involucrar diferentes bases de datos o incluso diferentes servidores. Aquí es donde entra en juego el concepto de transacciones distribuidas, y en SQL Server, el Distributed Transaction Coordinator (DTC) juega un papel crucial.

¿Qué son las Transacciones Distribuidas?

Una transacción distribuida es aquella que abarca más de un recurso de red, como bases de datos o sistemas de archivos ubicados en diferentes servidores. A diferencia de una transacción local que afecta a una sola base de datos, las transacciones distribuidas tienen la capacidad de coordinar cambios en varias bases de datos, asegurando que todos los participantes de la transacción lleguen a un estado de compromiso o vuelvan a un estado previo en caso de fallo.

El desafío en las transacciones distribuidas es garantizar que todos los nodos involucrados lleguen a un consenso sobre el resultado de la transacción. Esto es fundamental para mantener la integridad de los datos y evitar inconsistencias que podrían llevar a resultados inesperados o, en el peor de los casos, a la corrupción de los datos.

Distributed Transaction Coordinator (DTC)

En SQL Server, el Distributed Transaction Coordinator (DTC) es el componente encargado de gestionar las transacciones distribuidas. Su función principal es asegurar que todas las partes de una transacción distribuida, que pueden involucrar múltiples bases de datos y servidores, se comprometan correctamente o se deshagan en caso de error.

DTC utiliza el protocolo de dos fases (2PC, por sus siglas en inglés) para coordinar las transacciones. Este protocolo se divide en dos fases: la fase de preparación y la fase de compromiso. En la primera fase, DTC pregunta a todos los participantes si están listos para comprometer la transacción. Si todos responden afirmativamente, se procede a la segunda fase, donde se envía la orden de compromiso. Si alguno de los participantes no puede comprometerse, se inicia el proceso de deshacer la transacción en todos los participantes, asegurando que el sistema vuelva a un estado coherente.

Configuración y uso de Transacciones Distribuidas en SQL Server

Para aprovechar las transacciones distribuidas en SQL Server, primero necesitamos asegurarnos de que DTC esté configurado y funcionando correctamente en todos los servidores involucrados. Esto implica la configuración tanto a nivel de sistema operativo como en SQL Server.

Configurar DTC a nivel sistema operativo y red

En cuanto a la configuración del sistema operativo, es crucial que DTC esté habilitado y configurado para permitir transacciones remotas, ya que, por defecto, estas suelen estar desactivadas por razones de seguridad. Para habilitar DTC, desde el panel de control tendremos que acceder a “Agregar o Quitar Componentes de Windows” y activar la opción “Habilitar el acceso DTC de red”. Una vez hecho esto, y reiniciado el servidor si se nos requiere, el equipo estará listo para admitir transacciones distribuidas.

Sin embargo, esto no es todo, es importante asegurarse de que las reglas del firewall permitan la comunicación entre los servicios DTC de los diferentes servidores. DTC usa llamadas al procedimiento remoto RPC por lo que los puertos necesarios son, en primer lugar el puerto 135 TCP y UDP para establecer la comunicación y después un puerto TCP dinámico del rango 49152-65535. Este rango se puede configurar cambiando configuraciones del registro de windows si lo deseamos pero lo importante es que, nuestro firewall admita conexiones tanto por el puerto 135 como por todos los del rango dinámico seleccionado.

Usar DTC en SQL Server

Una vez que DTC esté operativo, podremos comenzar a utilizar transacciones distribuidas en SQL Server. Esto se hace a través de la instrucción BEGIN DISTRIBUTED TRANSACTION, que inicia una transacción distribuida que abarca múltiples servidores. Es importante tener en cuenta que, aunque la sintaxis es similar a la de una transacción local, el alcance y la complejidad son considerablemente mayores.

Un ejemplo sencillo podría involucrar dos servidores SQL Server diferentes. Comenzamos la transacción distribuida en el primer servidor y realizamos las operaciones necesarias. Luego, nos conectamos al segundo servidor y realizamos más operaciones dentro de la misma transacción. Finalmente, decidimos si se comprometen los cambios (commit) o si se deshacen (rollback).

Consideraciones en el Uso de Transacciones Distribuidas

Aunque el uso de transacciones distribuidas y DTC ofrece grandes ventajas en términos de consistencia y fiabilidad, también presenta una serie de retos que debemos considerar.

En primer lugar, las transacciones distribuidas suelen ser más lentas que las locales debido a la sobrecarga de la coordinación entre múltiples nodos. Esto puede afectar el rendimiento de las aplicaciones, especialmente en sistemas con alta concurrencia.

Además, la complejidad de la configuración y la gestión de DTC puede ser un obstáculo en muchas organizaciones donde, también será común involucrar a varias personas de varios departamentos para el cambio. Es vital asegurarse de que todos los servidores involucrados estén correctamente configurados y que la comunicación entre ellos sea fluida. Cualquier problema en la configuración de DTC puede resultar en errores difíciles de diagnosticar, que pueden ser muy costosos de resolver en producción.

Otro aspecto a tener en cuenta es la fiabilidad del sistema. Aunque DTC está diseñado para manejar fallos, es esencial contar con mecanismos adicionales de recuperación y monitorización para minimizar el impacto de posibles fallos de red o de los servidores.

Por último, es fundamental considerar la seguridad en la configuración de DTC. Dado que las transacciones distribuidas pueden involucrar la transferencia de datos sensibles entre servidores, es necesario implementar medidas de seguridad robustas para proteger esta información. Esto incluye el uso de comunicaciones seguras, así como la correcta configuración de permisos y autenticaciones.

Buenas prácticas para la gestión de Transacciones Distribuidas

Para gestionar eficazmente las transacciones distribuidas en SQL Server, es importante seguir una serie de buenas prácticas que nos permitirán minimizar riesgos y maximizar el rendimiento.

En primer lugar, debemos evitar utilizar transacciones distribuidas a menos que sean absolutamente necesarias. Si es posible, debemos buscar alternativas, como la replicación o el uso de servicios distribuidos que manejen la consistencia eventual. Las transacciones distribuidas deben reservarse para casos en los que la consistencia estricta sea un requisito ineludible. Si, el primer consejo es no lo hagas, es lo que hay.

Cuando sea necesario utilizar transacciones distribuidas, es fundamental optimizar el diseño de las mismas para reducir al mínimo el tiempo que la transacción está abierta. Esto incluye realizar todas las operaciones preparatorias fuera de la transacción y asegurarse de que el código dentro de la transacción sea lo más eficiente posible.

Además, es recomendable implementar una monitorización continua del rendimiento y de los posibles errores de DTC. Existen herramientas en SQL Server que nos permiten rastrear y analizar el rendimiento de las transacciones distribuidas, así como diagnosticar problemas en tiempo real. Por ejemplo, SQL Server Profiler, xEvents o DMVs.

Conclusión

Las transacciones distribuidas y el uso de DTC en SQL Server son herramientas poderosas que permiten garantizar la consistencia de los datos en entornos complejos y distribuidos. Sin embargo, su uso requiere una planificación cuidadosa y una gestión rigurosa para evitar problemas de rendimiento y fiabilidad.

Es importante recordar que no todas las aplicaciones necesitan transacciones distribuidas. En muchos casos, existen soluciones alternativas que pueden ofrecer la consistencia y fiabilidad necesarias sin la complejidad adicional. Cuando se opte por utilizar transacciones distribuidas, debemos asegurarnos de seguir las buenas prácticas y mantener una supervisión constante para garantizar el éxito a largo plazo.

Si abordamos las transacciones distribuidas con una comprensión clara de sus beneficios y limitaciones, y si estamos dispuestos a invertir en su correcta implementación, podemos lograr una gestión eficiente y segura de nuestros sistemas distribuidos en SQL Server.

Sep

2024

Kerberos SPN para SQL Server

En el pasado artículo os hablé de la autenticación en SQL Server y profundizamos en los protocolos NTLM y Kerberos que se usan para la autenticación con usuarios de Directorio Activo. Cuando hablamos de Kerberos, os comenté que pese a ser un sistema más rápido y seguro que NTLM necesita de más configuraciones. En concreto comentamos los Service Principal Names (SPN) que es en lo que vamos a profundizar hoy. Un SPN mal configurado puede desencadenar una serie de problemas, desde forzar el uso de NTLM hasta errores de autenticación. Por ello, comprender en detalle cómo funciona Kerberos en combinación con SQL Server, y más específicamente, cómo configurar adecuadamente los SPN, es fundamental para cualquier DBA.

¿Qué es un SPN y por qué es importante en SQL Server?

Los Service Principal Names, son identificadores únicos que permiten a los ordenadores localizar un servicio específico dentro de una red. Si llevamos esto a SQL Server, el SPN actúa como un enlace entre una instancia de SQL Server y su identidad en la red, permitiendo a los clientes autenticarse correctamente mediante Kerberos. En escenarios de autenticación Kerberos, el SPN se registra en el Active Directory y se asocia a la cuenta de servicio que ejecuta SQL Server. Esta configuración asegura que Kerberos pueda autenticar correctamente a los usuarios que intentan conectarse a la base de datos. De lo contrario, los usuarios podrían enfrentar errores de autenticación que, en el mejor de los casos, causan inconvenientes, y en el peor, generan brechas de seguridad. Si el SPN no está configurado correctamente, el proceso de autenticación podría revertir al protocolo NTLM, que es menos seguro y menos eficiente que Kerberos.

Configuración de SPN

Configurar correctamente los Service Principal Names es esencial para garantizar la autenticación Kerberos en SQL Server. En primer lugar, es necesario identificar bajo qué cuenta se ejecuta el servicio SQL Server, ya que el SPN debe registrarse en el Active Directory bajo esa cuenta específica.

Un detalle muy importante que no podemos pasar por alto es que un SPN debe ser único en todo el dominio. Si dos servicios diferentes compartieran el mismo SPN, tendríamos fallos en la autenticación que además, por experiencia propia os lo digo, son muy difíciles de diagnosticar y solucionar. Para evitar estos conflictos, se recomienda utilizar el comando setspn -L para listar los SPN actuales y verificar la ausencia de duplicados antes de crear un nuevo SPN.

Configurar SPN para SQL Server

Cuando vamos a registrar un servidor SQL Server es crucial que el SPN se registre tanto para el nombre del servidor como para su FQDN (Fully Qualified Domain Name). Esto asegura que, independientemente de cómo se conecten los clientes al servidor (mediante el nombre corto o el FQDN), la autenticación Kerberos se realice sin problemas. La sintaxis básica para registrar un SPN para SQL Server es la siguiente y nos servirá tanto para la instancia por defecto del servidor:

setspn -S MSSQLSvc/servidor:puerto dominio\cuentaServicio
setspn -S MSSQLSvc/servidor.dominio.com:puerto dominio\cuentaServicio

Podríamos usar el parámetro -A para crear los Service Principal Names pero yo personalmente prefiero usar -S en el comando setspn ya que verifica automáticamente si el SPN ya existe, previniendo registros duplicados.

Creación de SPN en Instancias Nombradas de SQL Server

Cuando trabajamos con instancias con nombre de SQL Server, la creación de SPN requiere un enfoque ligeramente diferente al de las instancias predeterminadas. Las instancias nombradas utilizan un puerto dinámico por defecto, lo que puede complicar la configuración del SPN. Para facilitar la gestión y evitar problemas de autenticación, deberemos asignar un puerto fijo a la instancia nombrada. Una vez hecho esto, para la configuración del SPN seguiremos un procedimiento similar al de una instancia predeterminada pero, registrando también los Service Principal Names con el nombre de la instancia.

Supongamos que tenemos una instancia con nombre de SQL Server llamada «SQLInstancia» que se ejecuta en el servidor «servidorSQL» con el puerto fijo 1436. El SPN se configuraría de la siguiente manera:

setspn -S MSSQLSvc/servidorSQL:1436 dominio\cuentaServicio
setspn -S MSSQLSvc/servidorSQL.dominio.com:1436 dominio\cuentaServicio
setspn -S MSSQLSvc/servidorSQL:SQLInstancia dominio\cuentaServicio
setspn -S MSSQLSvc/servidorSQL.dominio.com:SQLInstancia dominio\cuentaServicio

Realmente podríamos dejar el puerto dinámico por defecto y registrar el SPN solo por el nombre pero, no soy partidario de ello. Este método que os acabo de enseñar nos asegura que la autenticación Kerberos funciona correctamente independientemente de si la conexión se realiza por nombre de la instancia o por el puerto.

Eliminación de SPN

Eliminar un SPN incorrecto o redundante es una operación delicada, pero a veces necesaria para resolver conflictos o corregir configuraciones. Si detectamos que un SPN fue registrado incorrectamente, o si estamos migrando un servicio y necesitamos limpiar los registros antiguos, podemos utilizar el comando setspn -D para borrar los registros de SPN.

Por ejemplo, si necesitamos eliminar un SPN registrado para una instancia predeterminada en el servidor «servidorSQL», el comando sería:

setspn -D MSSQLSvc/servidorSQL:1433 dominio\cuentaServicio

O bien, para eliminar un SPN de una instancia con nombre:

setspn -D MSSQLSvc/servidorSQL\SQLInstancia dominio\cuentaServicio

Es importante tener precaución al usar el comando setspn -D, ya que eliminar un SPN incorrectamente puede llevar a problemas de autenticación, especialmente en entornos de producción. Antes de realizar la eliminación, es recomendable listar todos los Service Principal Names registrados con setspn -L cuentaServicio para confirmar que estamos eliminando el SPN correcto.

Además, cuando eliminemos un SPN debemos gestionarlo cuidadosamente, asegurándonos de que el servicio no se quede sin SPN registrado, lo que podría causar fallos en la autenticación de los usuarios y, por tanto, afectar a la disponibilidad del servicio.

SPN para Otros Servicios de SQL Server

Además de las instancias del motor de base de datos de SQL Server, otros servicios de SQL Server, como SQL Server Reporting Services (SSRS), SQL Server Analysis Services (SSAS) y SQL Server Integration Services (SSIS), también requieren la configuración de SPN para soportar la autenticación Kerberos. Aunque su uso está disminuyendo en favor de Power BI y otras herramientas nuevas en la nube como Fabric aún es común encontrarse con instalaciones locales de estos servicios que tendremos que administrar. Cada uno de estos servicios tiene sus propios requisitos y consideraciones para la correcta configuración de SPN. Vamos a ver cómo configurar los Service Principal Names para cada uno de estos servicios.

SQL Server Reporting Services (SSRS) y Power BI Report Server (PBIRS)

SQL Server Reporting Services (SSRS) es un servicio que se utiliza para generar, administrar y entregar informes a través de una interfaz web. De la misma manera y sobre esa base existe un servicio local de Power BI Server para publicar informes llamado Power BI Report Server (PBIRS). Para asegurar que las conexiones a SSRS y PBIRS se autentiquen correctamente mediante Kerberos, debemos registrar un SPN para el servicio HTTP que utilizan.

Supongamos que un SSRS o PBIRS está configurado en un servidor llamado «servidorSQL» con el nombre de instancia «Reportes» y está accesible a través de HTTP. Los Service Principal Names se configuran de la siguiente manera:

setspn -S HTTP/servidorSQL dominio\cuentaServicio
setspn -S HTTP/servidorSQL.dominio.com dominio\cuentaServicio

IMPORTANTE: Además del registro de Kerberos en SSRS deberemos habilitar esta conexión en el fichero de configuración «RsReportServer.config». Para ello nos aseguraremos de añadir (o que esté añadida) la palabra clave «<RSWindowsNegotiate>» como primera entrada en el apartado «<AuthenticationTypes>«.

Consideraciones Adicionales para HTTPS

Si SSRS o PBIRS están configurados para utilizar HTTPS, la configuración del SPN no cambia en cuanto a la necesidad de registrar los Service Principal Names para el servicio HTTP. Sin embargo, es crucial que el certificado SSL esté correctamente configurado y que el nombre del certificado coincida con el nombre de host utilizado para acceder al servidor. Esto asegura que la autenticación Kerberos funcione sin problemas en un entorno seguro.

Configuración para Entornos con Nombres de Alias o CNAME

En algunos escenarios, es posible que PBIRS esté configurado para acceder a través de un nombre de alias o un CNAME, lo cual es común en configuraciones de alta disponibilidad o cuando se utiliza un balanceador de carga. En estos casos, también debemos registrar un SPN para el alias o CNAME, de manera que las autenticaciones se gestionen correctamente:

setspn -S HTTP/aliasPBIRS dominio\cuentaServicio
setspn -S HTTP/aliasPBIRS.dominio.com dominio\cuentaServicio

Esto asegura que cualquier solicitud de autenticación Kerberos que se realice a través del alias sea manejada adecuadamente, previniendo errores de autenticación que podrían derivar en problemas de acceso a los informes.

SQL Server Analysis Services (SSAS)

SQL Server Analysis Services (SSAS) es un servicio que proporciona herramientas de bases de datos tabulares y de cubos para análisis y minería de datos. Para SSAS, el SPN debe registrarse para el servicio «MSOLAPSvc.3». Si, por ejemplo, tenemos una instancia de SSAS llamada «Analisis» en el servidor «servidorSQL», los Service Principal Names se configurarán de la siguiente manera:

setspn -S MSOLAPSvc.3/servidorSQL:puerto dominio\cuentaServicio
setspn -S MSOLAPSvc.3/servidorSQL.dominio.com:puerto dominio\cuentaServicio

Si SSAS utiliza un puerto predeterminado o fijo, ese puerto debe incluirse en el SPN. Es importante verificar el puerto en uso antes de registrar el SPN para evitar errores en la configuración.

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) no requiere típicamente la configuración de un SPN, ya que SSIS es más comúnmente utilizado en el contexto de ejecución local de paquetes. Sin embargo, si SSIS está configurado para ejecutar paquetes en un servidor remoto y queremos o necesitamos usar la autenticación Kerberos, necesitaremos registrar un SPN para el servicio de agente de SQL Server.

El SPN para SQL Server Agent, que es responsable de ejecutar trabajos que pueden incluir paquetes SSIS, se configuraría de la siguiente manera para una instancia predeterminada en «servidorSQL»:

setspn -S SQLServerAgent/servidorSQL dominio\cuentaServicio
setspn -S SQLServerAgent/servidorSQL.dominio.com dominio\cuentaServicio

SQL Server Browser Service

SQL Server Browser Service es el servicio responsable de enrutar las solicitudes de conexión a la instancia correcta de SQL Server en servidores que ejecutan múltiples instancias. Aunque no es común, si necesitamos configurar Kerberos para este servicio, el SPN se registraría así:

setspn -S MSOLAPDisco\servidorSQL dominio\cuentaServicio
setspn -S MSOLAPDisco\servidorSQL.dominio.com dominio\cuentaServicio

Este SPN permitiría que el SQL Server Browser Service maneje correctamente las conexiones basadas en Kerberos, asegurando la autenticación en entornos con múltiples instancias o nombres de alias.

Mantenimiento de SPN

El mantenimiento continuo de los Service Principal Names es tan importante como su configuración inicial. Debemos estar atentos a cualquier cambio en la infraestructura, como la migración de SQL Server a un nuevo servidor, el cambio de cuentas de servicio o la actualización del nombre del dominio, ya que estos eventos pueden requerir una actualización de los SPN correspondientes.

Es recomendable establecer procedimientos regulares de monitorización para asegurar que los SPN siguen registrados correctamente. Si detectamos un problema de autenticación que sugiere un fallo en Kerberos, el primer paso que debemos realizar es verificar el estado de los Service Principal Names.

Herramientas como klist o el ya mencionado comando setspn pueden ser útiles para diagnosticar problemas de tickets Kerberos. Del mismo modo, el visor de eventos de Windows puede proporcionarnos detalles adicionales sobre fallos de autenticación que nos ayudarán a diagnosticar los problemas.

En caso de encontrarnos con un SPN duplicado o incorrecto, la solución pasa por eliminar el registro incorrecto utilizando el comando setspn -D, como ya hemos visto, y, acto seguido, registrar de nuevo el o los SPN correctos. Esta intervención debe realizarse con cuidado, ya que eliminar un SPN sin registrar el nuevo puede resultar en una pérdida de conectividad con el servicio SQL Server.

SPN y Always On

En entornos de alta disponibilidad, como los clusters de failover de SQL Server o las configuraciones Always On, la gestión de los Service Principal Names se vuelve aún más compleja. En estos escenarios, los SPN deben configurarse no sólo para la instancia de SQL Server, sino también para el nombre del recurso del clúster o el listener de Always On.

Por ejemplo, en un entorno Always On, los SPN deben registrarse tanto para cada réplica como para el listener. Esto garantiza que, en caso de failover, los clientes puedan seguir conectándose al servicio SQL Server utilizando Kerberos, sin experimentar interrupciones. El manejo incorrecto de los Service Principal Names en estos entornos puede provocar fallos en la autenticación y en la conmutación por error, por lo que es esencial prestar mucha atención en estos casos.

Conclusión

Los SPN de Kerberos son un componente crítico en la infraestructura de autenticación de SQL Server. Su correcta configuración y mantenimiento nos aseguran que los entornos de base de datos funcionan de manera segura y eficiente. A medida que la complejidad de nuestras infraestructuras crezca, es fundamental que prestemos atención a los detalles de configuración de SPN, especialmente en entornos de alta disponibilidad.

Un SPN mal configurado no solo puede comprometer la seguridad, sino que también puede afectar negativamente el rendimiento y la disponibilidad de nuestros servicios. Por lo tanto, debemos abordar la gestión de SPN con el mismo rigor y precisión que aplicamos a otros aspectos de la administración de bases de datos. Al hacerlo, garantizaremos que SQL Server siga siendo un pilar confiable en nuestras infraestructuras críticas.

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Sep

2024

Autenticación en SQL Server

Hace unos días hablábamos sobre la diferencia entre los inicios de sesión y los usuarios en SQL Server y Azure SQL y pudimos ver cómo teníamos a nuestro alcance varios métodos de autenticación para iniciar sesión. Como vimos los logins podían ser usuarios nativos de SQL Server o heredados del dominio de Windows o de Azure (lo que se conoce como Entra ID y antes como Azure Active Directory). Esta autenticación es un aspecto crítico para los sistemas de datos, la seguridad de nuestros datos depende en gran medida de cómo controlamos el acceso a ellos.

En este artículo, vamos a explorar en profundidad los distintos modos de autenticación disponibles en SQL Server y Azure SQL. Por un lado, tendremos los métodos tradicionales como la autenticación de SQL Server y Windows, y por otro lado, la integración con Azure Entra ID (anteriormente conocido como Azure Active Directory). También quiero entrar en los protocolos subyacentes, Kerberos y NTLM, sobre los que se basan estos métodos de autenticación.

Modos de Autenticación en SQL Server y Azure SQL

SQL Server y Azure SQL ofrecen varias opciones para autenticar usuarios. Cada una de estas opciones está diseñada para escenarios específicos, y la elección de un método sobre otro puede tener implicaciones significativas en la seguridad y la facilidad de administración.

Autenticación de SQL Server

La autenticación de SQL Server es probablemente el método más tradicional. Se basa en un sistema interno de gestión de inicios de sesión y contraseñas dentro de SQL Server. Este método no depende del sistema operativo ni de ningún servicio externo para validar las credenciales, lo que significa que los usuarios y las contraseñas se gestionan directamente en el propio motor de bases de datos. Este tipo de autenticación es ideal en escenarios donde necesitamos un control granular sobre el acceso de usuarios que no están en nuestro dominio de Windows o cuando estamos trabajando en entornos mixtos.

Autenticación de Windows

La autenticación de Windows permite a los usuarios autenticarse usando sus credenciales de dominio de Windows. Este método es mi preferido en entornos donde se utiliza Active Directory para gestionar usuarios y grupos. La principal ventaja de la autenticación de Windows es que podemos integrar sin problemas SQL Server en un entorno de seguridad ya establecido. Además nos permite hacer uso de los grupos de Directorio Activo, lo que nos puede simplificar enormemente la tarea y aumentar la seguridad. Por último, otra ventaja es que no solo simplifica la gestión de credenciales, sino que también permite aprovechar protocolos de seguridad avanzados como Kerberos, lo que añade una capa adicional de protección como veremos más adelante.

Autenticación con Azure Entra ID

Con la aparición de servicios en la nube, la autenticación a través de Azure Entra ID se ha convertido en una opción cada vez más importante, especialmente para entornos de Azure SQL. Este método permite a los usuarios autenticarse mediante sus credenciales de Azure Entra ID, lo que facilita una integración más fluida con otros servicios de Azure. Además, permite implementar las características de seguridad avanzadas de Azure como la autenticación Multifactor (MFA) y las políticas de acceso condicional, que no son posibles con los métodos tradicionales de autenticación.

Protocolos de Autenticación: Kerberos y NTLM

Al hablar de autenticación en entornos Windows, es fundamental entender los protocolos que operan en segundo plano. Kerberos y NTLM son los dos principales protocolos de autenticación utilizados, cada uno con características y aplicaciones distintas. A pesar de que ambos sirven para el mismo propósito, sus diferencias son significativas, especialmente en términos de seguridad y rendimiento. Para tomar decisiones informadas sobre cuál utilizar, es esencial comprender cómo funcionan y en qué se distinguen.

NTLM

NTLM (NT Lan Manager) es un protocolo de autenticación desarrollado por Microsoft que ha estado en uso desde la era de Windows NT. A pesar de su antigüedad y las limitaciones de seguridad que presenta, NTLM sigue siendo utilizado en escenarios donde Kerberos no está disponible o no puede ser implementado, como en redes que no están unidas a un dominio.

El proceso de autenticación con NTLM es relativamente sencillo pero menos seguro que Kerberos. NTLM se basa en un desafío-respuesta (challenge-response), donde el cliente primero establece una conexión con el servidor y envía el nombre de usuario. El servidor genera un valor de desafío (un número aleatorio) que se envía al cliente. El cliente, a su vez, cifra este desafío utilizando un hash de la contraseña del usuario y lo envía de vuelta al servidor. El servidor compara este resultado con lo que esperaba y, si coinciden, se concede el acceso.

Una de las principales limitaciones de NTLM es la falta de autenticación mutua. Es decir, mientras que el servidor autentica al cliente, el cliente no autentica al servidor, lo que deja abierta la posibilidad de ataques como el «man-in-the-middle». Además, NTLM es vulnerable al ataque de «pass-the-hash», donde un atacante puede reutilizar el hash de la contraseña para acceder a recursos sin conocer la contraseña original.

Autenticación NTLM en SQL Server

En SQL Server, NTLM se utiliza principalmente cuando Kerberos no está configurado correctamente o cuando la conexión se realiza en un entorno de trabajo que no soporta Kerberos, como un grupo de trabajo (WORKGROUP) en lugar de un dominio. También, cuando accedamos desde el propio servidor local y no por la red, SIEMPRE se va a usar NTLM. Por último, es común ver NTLM en escenarios legacy, donde las aplicaciones antiguas no son compatibles con Kerberos.

Kerberos

Kerberos es un protocolo de autenticación mucho más avanzado que NTLM, introducido en Windows 2000. Basado en un sistema de «tickets», Kerberos no solo ofrece mayor seguridad, sino también un rendimiento mejorado en comparación con NTLM.

Kerberos opera utilizando un tercero de confianza, conocido como el Key Distribution Center (KDC), que emite tickets de autenticación. El proceso comienza cuando un usuario solicita acceso a un servicio. El cliente primero se autentica ante el KDC, que le proporciona un Ticket Granting Ticket (TGT). Este TGT permite al usuario solicitar tickets de servicio (Service Tickets) para acceder a diferentes recursos en la red. Cada ticket de servicio se presenta al servidor para establecer la autenticación, y dado que estos tickets están cifrados, Kerberos ofrece una mayor protección contra ataques.

Una de las características más importantes de Kerberos es la autenticación mutua, donde tanto el cliente como el servidor validan las identidades del otro, reduciendo significativamente el riesgo de ataques de suplantación de identidad. Además, Kerberos es más eficiente en términos de red y recursos, ya que no requiere múltiples rondas de comunicación para completar la autenticación como en NTLM.

Autenticación Kerberos en SQL Server

En SQL Server, Kerberos es el protocolo que se usa preferentemente siempre y cuando esté disponible, es decir, configurado correctamente en un entorno de dominio de Active Directory. Esto no solo mejora la seguridad, sino que también nos permite el uso de características avanzadas como la delegación de autenticación, que es esencial para aplicaciones que requieren pasar las credenciales de usuario a través de múltiples capas de servicios. Sin esta delegación de autenticación, por ejemplo, es imposible “saltar” desde tu ordenador local a un servidor vinculado configurado en tu servidor SQL con el inicio de sesión de Active Directory.

Comparación: NTLM vs Kerberos

Cuando comparamos NTLM y Kerberos en entornos SQL Server, es evidente que Kerberos ofrece numerosas ventajas, tanto en términos de seguridad como de eficiencia. Sin embargo, la elección entre uno y otro puede depender de varios factores, incluidos los requisitos del entorno y la compatibilidad de las aplicaciones.

Seguridad

Kerberos es claramente superior en términos de seguridad. La autenticación mutua y el uso de tickets cifrados hacen que Kerberos sea menos susceptible a los ataques de red comunes. Por el contrario, NTLM, con su enfoque de desafío-respuesta y su vulnerabilidad al ataque pass-the-hash, es menos seguro y, por lo tanto, menos adecuado para entornos donde la seguridad es una preocupación primordial.

Rendimiento

En términos de rendimiento, Kerberos también tiene la ventaja. Al usar tickets, Kerberos reduce la carga de comunicación y mejora la eficiencia de la autenticación, especialmente en redes grandes con muchos usuarios y servicios. NTLM, aunque funcional, puede generar una carga adicional en la red debido a la necesidad de múltiples rondas de autenticación.

Compatibilidad y Configuración

A pesar de sus ventajas, Kerberos requiere una configuración más compleja y solo funciona en entornos de dominio de Active Directory. NTLM, aunque menos seguro, es más sencillo de implementar y funciona en una gama más amplia de escenarios, incluidos aquellos que no están dentro de un dominio.También deberemos tener en cuenta la complejidad extra de configuración de un entorno compatible con Kerberos donde tendremos que registrar correctamente los SPN (Service Principal Names) cuando usemos una cuenta de servicio que no sea la por defecto en una instancia por defecto y siempre que usemos instancias con nombre.

Conclusión

La elección del modo de autenticación en SQL Server o Azure SQL no es trivial y debe basarse en las necesidades específicas de seguridad y administración de cada entorno. La autenticación de SQL Server proporciona flexibilidad en escenarios específicos, mientras que la autenticación de Windows y Azure Entra ID ofrecen ventajas significativas en términos de seguridad y facilidad de gestión. Además, el entendimiento de los protocolos subyacentes como Kerberos y NTLM nos permite tomar decisiones más informadas sobre cómo proteger nuestras bases de datos contra amenazas externas. Al final, lo más importante es elegir el método de autenticación que no solo se alinee con las políticas de seguridad de la organización, sino que también se adapte a las características y necesidades del entorno en el que operamos.

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Sep

2024

Fin de soporte de SQL Server 2019, ¿estamos preparados?

Empieza septiembre, se acaban las vacaciones de verano y, con la vuelta a la oficina, es momento de empezar a prepararnos para un hito importante para todos los que trabajamos con bases de datos: el fin del ciclo de vida de SQL Server 2019. Será el 28 de febrero de 2025 y, aunque parece una fecha aún lejana, lo cierto es que el tiempo para planificar y llevar a cabo una migración es elevado y, teniendo en cuenta que en muchos casos hablamos de varias instancias para migrar, el tiempo es más bien justo.

Como decía, esta fecha pone fin al soporte técnico y a las actualizaciones que no sean de seguridad para esta versión de SQL Server, lo que implica una necesidad urgente de evaluar nuestras estrategias de migración y considerar futuras implementaciones. Este evento no solo nos afecta desde un punto de vista operativo, sino que también abre la puerta a nuevas oportunidades y desafíos en el mundo de la gestión de datos.

Qué significa el fin del ciclo de vida de SQL Server 2019

Cuando hablamos del fin del ciclo de vida de un producto, en este caso SQL Server 2019, nos referimos al momento en el que Microsoft deja de ofrecer soporte técnico, actualizaciones de corrección de errores y mejoras. A partir de esa fecha, cualquier fallo que se descubra en SQL Server 2019 no será corregido por Microsoft, lo que nos deja expuestos a posibles riesgos. Además, sin soporte oficial, la resolución de problemas técnicos puede volverse mucho más complicada y costosa. Por suerte aún nos queda un plazo llamado soporte extendido en el que Microsoft sigue proporcionando actualizaciones de seguridad pero, no deberíamos recurrir a esto por los riesgos antes mencionados.

El fin del ciclo de vida de SQL Server 2019 no debería sorprendernos, ya que forma parte del ciclo natural de cualquier software. Sin embargo, la realidad es que muchos entornos productivos aún están basados en versiones que están próximas a quedar obsoletas, lo que nos obliga a actuar con rapidez para asegurar la continuidad de nuestros sistemas.

Planificación para la migración: ¿Hacia dónde debemos movernos?

La migración de SQL Server 2019 a versiones más recientes o a otros sistemas no es una tarea trivial. Requiere de una planificación meticulosa, que debe incluir una evaluación detallada de las aplicaciones y bases de datos que dependen de SQL Server 2019, así como una comprensión clara de las mejoras y cambios que ofrecen las versiones más nuevas. Ya dedicamos un artículo completo a planificar una migración que os recomiendo leer.

El paso lógico sería migrar a SQL Server 2022 que, no solo ofrece nuevas características de seguridad y rendimiento, sino que también está diseñada para integrarse más eficazmente con los servicios en la nube de Azure. Además, SQL Server 2022 mejora la integración con servicios de análisis avanzados y ofrece un mejor soporte para grandes volúmenes de datos.

También podríamos migrar nuestras bases de datos a la nube de Azure, ya sea a bases de datos de Azure SQL o a una instancia administrada de SQL en Azure. Cualquiera de estas dos soluciones SAAS son una buena opción y la elección entre una y otra deberá ser en base a las características y limitaciones de cada una de ellas. Podéis ampliar información sobre este tema en este otro artículo.

Para aquellos que buscáis alternativas más allá del ecosistema de Microsoft, también es un buen momento para considerar otras opciones de bases de datos, tanto relacionales como no relacionales. Las bases de datos en la nube, como Amazon RDS o Google Cloud SQL, ofrecen opciones robustas y escalables que pueden ser atractivas para ciertos entornos empresariales. También se puede considerar la transición a bases de datos open-source como PostgreSQL o MySQL, que han ganado popularidad en los últimos años gracias a su flexibilidad y bajo coste.

El futuro de los clústeres de Big Data en SQL Server

Uno de los aspectos más significativos que desaparece con el fin del ciclo de vida de SQL Server 2019 es el soporte para los clústeres de Big Data, una característica que se introdujo con esta versión. Estos clústeres permitían a las organizaciones gestionar grandes volúmenes de datos utilizando una combinación de tecnologías SQL y NoSQL, incluyendo HDFS (Hadoop Distributed File System) y Spark. Aunque los clústeres de Big Data en SQL Server ofrecían una solución integrada para la gestión de grandes volúmenes de datos, la realidad es que su adopción ha sido limitada.

Con el fin del soporte, es crucial que consideremos qué alternativas existen para gestionar grandes volúmenes de datos en el futuro. Aquí es donde entran en juego otras tecnologías, como Azure Synapse Analytics, que combina la integración de datos, el análisis de grandes volúmenes de datos y la inteligencia artificial en una única solución. Azure Synapse ofrece un entorno mucho más flexible y escalable que los clústeres de Big Data de SQL Server, y es capaz de manejar no solo datos estructurados, sino también semiestructurados y no estructurados.

Otra opción a considerar es la adopción de arquitecturas de datos modernas basadas en el concepto de data lakehouse, que combina lo mejor de los data lakes y los data warehouses. Esta arquitectura es cada vez más popular, ya que permite almacenar grandes volúmenes de datos sin necesidad de preocuparse por la estructura de estos datos desde el principio. Además, soluciones como Databricks y Snowflake están ganando terreno como alternativas viables para la gestión y análisis de grandes volúmenes de datos.

Retos y oportunidades al abandonar SQL Server 2019

La transición hacia nuevas versiones de SQL Server o la adopción de nuevas tecnologías de gestión de datos no está exenta de desafíos. Uno de los principales retos que enfrentaremos será la necesidad de garantizar la compatibilidad de las aplicaciones existentes con las nuevas plataformas. Muchas aplicaciones empresariales están diseñadas y optimizadas para versiones específicas de SQL Server, lo que puede hacer que la migración requiera no solo la migración de SQL Server por nuestra parte sino que los equipos de desarrollo lleven a cabo una reescritura significativa del código o incluso una reingeniería completa de las aplicaciones.

Otro reto importante es la gestión de los datos históricos. Las organizaciones que han acumulado grandes volúmenes de datos a lo largo de los años deberán planificar cómo migrar estos datos de manera eficiente y sin pérdida de información. La migración de grandes volúmenes de datos puede ser un proceso largo y costoso, que debemos planificar y ejecutar de forma cuidadosa.

Sin embargo, junto con estos retos, también se presentan nuevas oportunidades. La adopción de nuevas tecnologías de bases de datos y análisis de datos nos permite aprovechar las últimas innovaciones en el campo de la inteligencia artificial y el machine learning. Estas tecnologías nos ofrecen la capacidad de analizar datos en tiempo real, identificar patrones y tomar decisiones informadas de manera más rápida y precisa.

Conclusión

El fin del ciclo de vida de SQL Server 2019 es un recordatorio claro de la naturaleza en constante evolución de la tecnología. Aunque la transición a nuevas versiones o tecnologías puede parecer desafiante, es también una oportunidad para mejorar nuestras capacidades de gestión de datos, optimizar nuestros sistemas y estar mejor preparados para enfrentar los desafíos del futuro.

Es crucial que no dejemos esta planificación para el último momento. Identificar nuestras necesidades, evaluar nuestras opciones y comenzar a implementar las soluciones más adecuadas garantizará que nuestras operaciones continúen sin interrupciones. En este entorno tan dinámico, estar un paso adelante no solo es una ventaja, sino una necesidad para seguir siendo competitivos en la gestión de datos.

Ago

2024

Log de errores de SQL Server

Si hay una herramienta imprescindible para un administrador de bases de datos esa es, sin duda, el log de errores. Este archivo nos proporciona un registro detallado de los eventos que ocurren en el sistema, permitiéndonos identificar problemas, realizar diagnósticos precisos y, en definitiva, mantener la estabilidad y el rendimiento de nuestras instancias de SQL Server. Sin embargo, para aprovechar al máximo esta herramienta, es fundamental comprender cómo configurarla adecuadamente, cómo interpretar la información que nos ofrece y qué hacer cuando necesitamos reiniciarla. En este artículo, profundizaremos en estos aspectos para que podamos sacarle todo el partido posible al log de errores.

¿Qué es el log de errores de SQL Server?

El log de errores de SQL Server es un archivo que recoge información relevante sobre los eventos que ocurren en la instancia de SQL Server. Este log incluye desde mensajes informativos y advertencias hasta errores críticos que pueden afectar el rendimiento o la disponibilidad del servidor. Es un recurso de gran valor para nosotros, ya que nos proporciona un historial detallado de la actividad del servidor, incluyendo fallos de autenticación, problemas de conectividad, errores de bases de datos y cualquier otra incidencia relevante que se produzca durante la operativa normal de SQL Server.

Configuración del log de errores de SQL Server

El log de errores de SQL Server se configura automáticamente durante la instalación, pero esto no significa que no podamos ajustar sus parámetros para adaptarlos a nuestras necesidades. Uno de los primeros aspectos que debemos considerar es el número de archivos de log que SQL Server retiene. Por defecto, se guardan 6 archivos de log, pero este número puede modificarse según lo que consideremos más adecuado para nuestra operación. Si necesitamos mantener un historial más largo de errores, podemos aumentar este número hasta un máximo de 99. También podemos ajustar el tamaño máximo de los ficheros de log para mantener un control más exhaustivo. Para hacerlo, en SSMS, nos posicionamos sobre la carpeta “Administración”, hacemos clic derecho en la carpeta “Log de SQL Server” y ahí en “Configurar”.

Estos simples ajustes nos permitirán mantener un registro más extenso de la actividad del servidor, lo cual es especialmente útil en entornos con alta criticidad donde los errores pasados pueden ser relevantes para la resolución de incidentes futuros.

Leer el log de errores

Una vez configurado el log, el siguiente paso es saber cómo leerlo e interpretarlo correctamente. SQL Server ofrece varias formas de acceder al contenido del log de errores, siendo la más común a través de SQL Server Management Studio (SSMS). Desde SSMS, podemos encontrar el log de errores en la carpeta de «Administración» y seleccionando «Logs de SQL Server». Aquí podremos ver una lista de los archivos de log disponibles, y al hacer doble clic en uno de ellos, podremos explorar los eventos registrados.

Cada entrada del log está compuesta por una fecha y hora, un nivel de gravedad y un mensaje. La fecha y hora nos indican cuándo ocurrió el evento, mientras que el nivel de severidad nos da una idea de la gravedad del problema. Los mensajes pueden variar en detalle, pero es importante estar atentos a ciertos patrones o palabras clave como «Error», «Failed» o «Severe», que suelen indicar problemas críticos que requieren atención inmediata.

Además de SSMS, también podemos utilizar T-SQL para consultar el contenido del log de errores. Para ello usaremos el procedimiento almacenado xp_readerrorlog. Por ejemplo, el siguiente comando nos muestra los errores más recientes:

EXEC xp_readerrorlog 0, 1, N'Error';

Este comando filtra las entradas del log, devolviendo sólo aquellos registros que contienen la palabra «Error». Es una forma rápida de identificar problemas graves sin necesidad de revisar manualmente cada línea.

Lectura del log en texto plano y acceso desde el sistema de archivos

Si necesitamos acceder al log de errores en texto plano o el servicio de SQL Server no está arrancado, podemos localizar el archivo directamente en el sistema de archivos del servidor.

Esto es especialmente útil cuando nos enfrentamos a una instancia que no arranca y no sabemos por qué ya que SQL Server mientras arranca va dejando registro en el log y ahí es donde podemos encontrar el problema. Incluso no encontrar log del intento de arranque nos va a dar una pista, en concreto que el servicio ni se puede empezar a iniciar lo que, normalmente, es debido a un fallo con la cuenta de servicio. El archivo se encuentra en la carpeta de instalación de SQL Server, dentro del directorio LOG. La ruta por defecto es de esta carpeta es C:\Program Files\Microsoft SQL Server\MSSQL{NumeroDeVersion}.{NombreInstancia}\MSSQL\Log\.

Si no lo tenemos claro, podemos buscar la ruta de logs en el servicio de SQL Server. En concreto, si abrimos las propiedades del servicio de SQL Server en el administrador de configuración de SQL Server y nos vamos a los parámetros de inicio del servicio vamos a poder ver un parámetro -E con la ruta del log de errores.

En este directorio, encontraremos el archivo ERRORLOG, que es el log de errores actual, junto con archivos numerados que representan los logs anteriores (ERRORLOG.1, ERRORLOG.2, etc.). Estos archivos pueden abrirse con cualquier editor de texto, como el Bloc de notas, permitiéndonos revisar los eventos registrados incluso si SQL Server no está en ejecución.

Aspectos críticos a tener en cuenta

A lo largo de mi experiencia, he aprendido que ciertos eventos en el log de errores requieren una atención especial. Por ejemplo, los errores relacionados con la memoria o el almacenamiento pueden tener un impacto inmediato en el rendimiento del sistema, mientras que los fallos en los trabajos de mantenimiento pueden afectar la integridad de los datos a largo plazo. Por ello, es crucial revisar periódicamente el log en busca de indicios de problemas potenciales, incluso si el sistema parece estar funcionando correctamente.

Otro punto que debería merecer nuestra atención es la repetición de ciertos errores. Un error aislado puede no ser motivo de preocupación, pero si observamos que un mismo mensaje aparece repetidamente, es probable que estemos ante un problema subyacente que requiere investigación y resolución. La repetición de errores de autenticación, por ejemplo, podría indicar intentos fallidos de acceso no autorizado o problemas con la configuración de seguridad.

Mi recomendación en este apartado es que configuréis alertas para los errores más críticos como os expliqué en este otro artículo.

Cómo reiniciar el log de errores de SQL Server

Llega un momento en el que el log de errores puede haberse llenado tanto de información que ya no es relevante, o bien necesitamos limpiar el registro para facilitar el análisis de nuevos eventos. En estos casos, reiniciar el log de errores es una práctica recomendada. Reiniciar el log no elimina los archivos existentes, sino que crea un archivo nuevo, lo que nos permite empezar a registrar eventos desde cero mientras mantenemos un historial accesible. El proceso de reinicio es sencillo y se puede realizar mediante el siguiente comando T-SQL:

EXEC sp_cycle_errorlog;

Este comando cierra el log de errores actual y crea un nuevo archivo. Es una operación segura que no afecta el rendimiento del servidor, pero debe ser utilizada con precaución, especialmente si estamos en medio de una investigación de errores, ya que el nuevo archivo comenzará a registrar sólo los eventos que ocurran después de la ejecución del comando. Mi recomendación en este sentido es programar este comando en un job que se ejecute de manera mensual o semanal en función del número de eventos que se generen normalmente en nuestro sistema. Esta práctica, junto con una configuración de retención de ficheros acorde a nuestras necesidades, nos va a facilitar mucho la lectura del log en caso de problema.

Conclusión

El log de errores de SQL Server es una herramienta fundamental para los administradores de bases de datos, y su correcta configuración y uso pueden marcar la diferencia entre la detección temprana de un problema y una crisis mayor. Configurar adecuadamente el número de archivos de log, saber cómo leer e interpretar la información, y estar atentos a eventos críticos son prácticas esenciales que no debemos subestimar. Asimismo, el reinicio del log nos permite mantener un registro ordenado y manejable, facilitando la identificación de nuevos eventos. En resumen, dominar el manejo del log de errores de SQL Server es una habilidad indispensable que nos ayudará a mantener la estabilidad y seguridad de nuestras instancias, asegurando un rendimiento óptimo y una operación sin contratiempos.

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Ago

2024

Gestión avanzada de Jobs: Permisos, proxys y Credenciales

Hoy quiero profundizar sobre un tema que ya comentamos de pasada cuando hablamos del Agente de SQL Server y es la gestión avanzada de los jobs. Cualquiera que haya trabajado con SQL Server y haya necesitado algo más que un simple almacén donde leer y escribir datos sabe que los jobs son un aliado indispensable para automatizar tareas repetitivas o programadas. Dentro de este contexto, los jobs juegan un papel crucial al permitirnos ejecutar de manera automática una variedad de tareas, desde copias de seguridad hasta la ejecución de scripts complejos. Sin embargo, más allá de crear y ejecutar jobs básicos, el manejo avanzado de estos, así como asignar bien permisos sobre el agente y el uso de proxys y credenciales, son aspectos que pueden marcar la diferencia en la administración eficiente y segura de nuestro entorno de bases de datos.

Jobs del Agente de SQL Server

Los jobs del Agente de SQL Server son estructuras flexibles y robustas que permiten ejecutar un conjunto de pasos de manera programada o bajo demanda. Cada job puede estar compuesto por uno o más pasos, que pueden ser scripts T-SQL, comandos de sistema operativo, paquetes SSIS, entre otros. La granularidad y flexibilidad que nos ofrecen los jobs nos permite orquestar tareas complejas, que en algunos casos serían difíciles de gestionar manualmente. La combinación de poder programar ejecuciones de script con un uso avanzado de procedimientos almacenados y otros objetos de sistema nos permite hacer cosas que de otra manera serían muy complejas.

Cuando creamos un job, lo primero que hacemos es asignar una serie de atributos esenciales, como son el nombre del job, el propietario, la categoría, y por supuesto, los pasos que se deben ejecutar. Es fundamental que definamos correctamente estos atributos, ya que una mala configuración puede llevar a errores en la ejecución o a problemas de seguridad. Por ejemplo, el propietario del job determina los permisos con los que se ejecutarán los pasos, lo que nos lleva al siguiente punto: la importancia de los permisos y las credenciales y los proxys.

La importancia de los permisos en los jobs

El Agente de SQL Server opera bajo un contexto de seguridad bien definido que se basa en los permisos de los usuarios y roles asignados dentro del servidor. Los permisos determinan qué acciones puede realizar un usuario sobre los jobs, incluyendo la creación, edición, eliminación y ejecución. Sin embargo, cuando quieras profundizar en la administración de permisos del agente vas a notar inmediatamente que están muy limitados.

Permisos del owner y del rol sysadmin

Cuando creamos un job, se asigna automáticamente un propietario (owner), que generalmente es el usuario que lo crea si no definimos otra cosa. Este owner tiene control total sobre el job, lo que incluye la capacidad de editar, pausar, detener, y eliminar el job sin restricciones. El problema es que solo este usuario será capaz de editar ese job (a excepción de los usuarios del rol sysadmin). Los miembros del rol sysadmin tienen privilegios sobre todos los jobs, lo que les permite editar, ejecutar o eliminar cualquier job, incluso si no fueron creados por ellos. Un usuario sysadmin tiene la capacidad de gestionar cualquier job en el servidor, sin importar quién sea el propietario, pero nadie más, no existe ningún permiso que podamos asignar a un usuario no sysadmin para administrar los jobs.

Usuarios no sysadmin

Los problemas, por tanto, comienzan a surgir cuando un usuario que no es miembro del rol sysadmin intenta gestionar un job del que no es propietario. En este escenario, el usuario se enfrenta a una serie de restricciones significativas. Por defecto, si no somos los propietarios de un job, no podemos editarlo ni cambiar su configuración, lo que incluye la modificación de los pasos del job, la programación, o incluso la habilitación o deshabilitación del mismo.

Esta limitación está diseñada para proteger la integridad y la seguridad de los jobs, evitando que usuarios sin permisos adecuados realicen cambios potencialmente dañinos o no autorizados. Sin embargo, también puede ser una barrera en entornos colaborativos, donde varios administradores de bases de datos necesitan trabajar en conjunto y gestionar los mismos jobs.

Estrategias ante las restricciones de permisos en la edición de jobs

Dado que la restricción de permisos es una medida de seguridad esencial y no parece que esté en la hoja de ruta de Microsoft cambiarla, es fundamental buscar soluciones que permitan la gestión colaborativa de jobs sin comprometer la seguridad del sistema. A continuación, os presento algunas estrategias para manejar estas limitaciones.

Uso del rol SQLAgentOperatorRole

La primera opción para otorgar permisos de gestión sobre jobs sin dar acceso completo como sysadmin es agregar al usuario al rol SQLAgentOperatorRole en la base de datos msdb. Este rol permite a los usuarios ejecutar, detener, iniciar y ver la historia de cualquier job, pero sigue sin permitir la creación ni edición de jobs de los cuales no son propietarios. Si un usuario necesita la capacidad de editar un job, deberá ser agregado como propietario del job o se le deben asignar permisos sysadmin.

Cambio de ownership de los jobs

Vista la limitación anterior del rol SQLAgentOperatorRole , una solución práctica sería cambiar el propietario del job al usuario que necesita gestionarlo. Esto se puede hacer fácilmente con una instrucción T-SQL, pero requiere permisos sysadmin o el propietario actual para ejecutar el cambio. Además desde ese mismo momento el propietario anterior dejará de tener permisos. En este punto es importante destacar que podemos definir como propietario de un job a un usuario que esté asociado a un login de SQL o de Windows pero en ningún caso a un rol o a un grupo de AD.

Este método, por tanto, aunque funciona, requiere de una gestión cuidadosa para evitar confusión sobre quién es responsable de cada job y para mantener un registro claro de la propiedad de los jobs en un entorno compartido. Además de requerir de intervención manual cuando el propietario del job no está disponible y otro compañero necesita editarlo.

Te recomiendo este video sobre como cambiar el propietario de varios jobs de manera masiva.

Usuario compartido como Owner

Los que me conocen saben que yo no soy partidario de compartir usuarios, me parece una mala práctica de seguridad. Sin embargo, vistas las limitaciones con la edición de jobs no hay otra alternativa factible. Un login de SQL compartido con un usuario asociado que actúe como propietario de los jobs permitirá a los usuarios loguearse con esa cuenta para la edición de los jobs. Dado que es un tema delicado de seguridad debemos mantener los permisos de este usuario lo más restringidos posibles y, en un escenario ideal, que solo tenga permisos sobre la base de datos MSDB. Para que esto sea posible, deberemos recurrir a un proxy para la ejecución de los pasos del job o nos encontraremos con problemas de permisos para acceder a los datos.

Credenciales y proxys

En entornos corporativos, es común que los jobs necesiten realizar tareas que requieren permisos elevados o acceder a recursos externos, como carpetas de red o servidores remotos. Como ya hemos visto, en las situaciones donde los jobs requieren permisos específicos para realizar tareas, pero no se desea otorgar permisos sysadmin, se pueden utilizar credenciales y proxys. Mediante la creación de proxys asociados a credenciales, los usuarios pueden ejecutar ciertos pasos del job con permisos elevados sin necesidad de ser sysadmin ni owner del job. Este enfoque garantiza que las tareas críticas se realicen de manera segura y controlada.

¿Qué son las Credenciales en SQL Server?

Una credencial en SQL Server es un objeto que almacena información de autenticación, como un nombre de usuario y una contraseña, que se utiliza para acceder a recursos externos al servidor SQL. Por ejemplo, si un job necesita copiar un archivo desde una ubicación de red, y esta acción requiere permisos específicos, podemos crear una credencial con las credenciales adecuadas y asignarla al job. Esto no solo centraliza la gestión de permisos, sino que también nos permite modificar las credenciales sin necesidad de cambiar los jobs que las utilizan.

¿Qué son los Proxys en SQL Server?

Un proxy en SQL Server es un mecanismo que permite a un job ejecutar pasos con los permisos asociados a una credencial específica. Esto es especialmente útil cuando queremos restringir los permisos del Agente de SQL Server para que solo realice determinadas tareas bajo un contexto de seguridad controlado.

Por ejemplo, supongamos que tenemos un job que ejecuta un paquete SSIS que necesita acceso a un servidor FTP para transferir archivos. Podríamos crear un proxy asociado a una credencial con los permisos necesarios para acceder al servidor FTP, y luego configurar el job para que utilice ese proxy al ejecutar el paso correspondiente. De esta manera, nos aseguramos de que el job solo pueda acceder a los recursos necesarios, minimizando el riesgo de comprometer la seguridad del sistema.

Configuración de Proxys y Credenciales: Mejores Prácticas

A la hora de configurar proxys y credenciales en SQL Server, es esencial seguir una serie de buenas prácticas para garantizar la seguridad y el correcto funcionamiento de los jobs.

En primer lugar, es recomendable que las credenciales se almacenen de forma segura y que su acceso esté restringido a los usuarios que realmente lo necesitan. Cuando estamos trabajando en entornos donde la seguridad es crítica, podríamos considerar el uso de un servicio de administración de secretos externo que permita gestionar las credenciales de manera centralizada.

En segundo lugar, al configurar proxys, es importante asignar sólo los permisos estrictamente necesarios. Esto se alinea con el principio de mínimo privilegio, del que ya hemos hablado y que dicta que un usuario o proceso solo debe tener los permisos necesarios para realizar su tarea y nada más. Además, es recomendable revisar y auditar periódicamente los proxys y las credenciales configuradas en el sistema para asegurarnos de que estén alineadas con las políticas de seguridad de la organización.

Finalmente, es importante documentar adecuadamente todos los proxys y credenciales configurados. En caso de que se produzcan cambios en el personal o en la estructura de permisos, tener una documentación clara puede ayudar a realizar los cambios sin interrumpir el funcionamiento de los jobs.

Conclusión

La gestión avanzada de jobs en SQL Server, junto con el uso correcto de proxys y credenciales, no solo nos permite automatizar tareas de manera eficiente, sino que también es clave para mantener la seguridad y el control en entornos complejos. Al utilizar credenciales y proxys, podemos asegurarnos de que los jobs se ejecuten con los permisos adecuados, minimizando el riesgo de accesos no autorizados o mal configurados.

Por otro lado, la gestión de permisos en el Agente de SQL Server es un aspecto crucial que impacta directamente en la capacidad de los usuarios para gestionar jobs y, sin embargo, muy complicado de gestionar correctamente.

Para sortear estas limitaciones, es fundamental implementar estrategias que permitan la colaboración segura, como el uso de roles específicos como SQLAgentOperatorRole, el cambio de ownership de jobs o la configuración de proxys y credenciales. Cada enfoque tiene sus pros y contras, pero con una gestión cuidadosa, es posible equilibrar la seguridad y la eficiencia en la administración de jobs en SQL Server

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Infra

Kerberos SPN para SQL Server

¿Qué es un SPN y por qué es importante en SQL Server?

Configuración de SPN

Configurar SPN para SQL Server

Creación de SPN en Instancias Nombradas de SQL Server

Eliminación de SPN

SPN para Otros Servicios de SQL Server

SQL Server Reporting Services (SSRS) y Power BI Report Server (PBIRS)

Configuración para Entornos con Nombres de Alias o CNAME

SQL Server Analysis Services (SSAS)

SQL Server Integration Services (SSIS)

SQL Server Browser Service

Mantenimiento de SPN

SPN y Always On

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes