Infra

06
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.

 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.

Publicado por Roberto Carrancio en SQL Server, 0 comentarios
04
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.

Autenticacion-NTLM

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.

Autenticacion-Kerberos

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.

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.

 

Publicado por Roberto Carrancio en Cloud, SQL Server, 1 comentario
02
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.

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!

Publicado por Roberto Carrancio en Cloud, SQL Server, 1 comentario
30
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”.

SQL Server Log Configuration

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.

SQL Server Log Path

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.

 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. 

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
23
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

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.

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
19
Ago
2024

Comprendiendo los servicios de SQL Server

Cuando instalamos SQL Server en nuestro ordenador o en un servidor vamos a ver que se instalan varios servicios que no teníamos antes. Esto es porque SQL Server es mucho más que un motor de base de datos relacional (RDBMS) sino que hay todo un ecosistema a su alrededor que hace posible la ejecución de todas las capacidades adicionales de las que dispondremos. Estos servicios trabajan en conjunto para proporcionarnos las capacidades de automatización, escalabilidad, seguridad, y analítica avanzada, permitiendo que SQL Server sea más que un simple motor de base de datos, como decíamos antes. A continuación, vamos a ver en detalle cada uno de estos servicios y componentes, desglosando su rol dentro del ecosistema de SQL Server para que como administradores de base de datos tengáis un mayor control.

Servicios SQL Server: El motor de base de datos 

El motor de base de datos es el núcleo sobre el que se sustentan todas las operaciones en SQL Server, el motor relacional propiamente dicho. Su principal función es el almacenamiento, procesamiento y seguridad de los datos. Este servicio maneja la ejecución de consultas SQL y la gestión de transacciones, así como  la administración de la integridad referencial y la seguridad. Este motor está diseñado para manejar desde pequeños hasta grandes volúmenes de datos y soportar entornos de gran concurrencia, proporcionándonos un rendimiento constante y confiable. Además, es responsable de la ejecución de procedimientos almacenados, funciones y triggers (desencadenadores), lo que permite la automatización de tareas dentro de la base de datos. Veremos un servicio de SQL Server por cada una de las instancias que tengamos instalada en nuestra máquina y detener estos servicios implica detener esas instancias de SQL Server.

Servicios SQL Server agent: automatización y programación de tareas

El agente de SQL Server es un servicio clave para la administración eficiente de las bases de datos. Su función principal es la automatización de tareas rutinarias, como la ejecución de scripts y la programación de copias de seguridad, lo que nos permite a los administradores mantener la continuidad del servicio y asegurar la optimización y seguridad de las bases de datos. A través de la programación de trabajos, el agente nos permite ejecutar tareas de manera recurrente o en respuesta a eventos específicos, minimizando la intervención manual y asegurando que las bases de datos se mantengan en condiciones óptimas. Además, cuenta con un sistema de alertas que nos notifica a los administradores sobre eventos críticos, como fallos en los trabajos o problemas de rendimiento, lo que facilita una respuesta proactiva.

Este servicio está disponible para todas las instalaciones de SQL Server que no sean una edición Express y veremos uno de ellos por cada una de las instancias que cumplan esa condición. Si detenemos este servicio SQL Server seguirá funcionando pero no el agente de SQL por lo que no podremos hacer uso de jobs ni de alertas.

Servicio SQL Server browser: gestión de conexiones en múltiples instancias

El servicio SQL Server Browser es esencial en entornos donde coexisten múltiples instancias de SQL Server o cuando tenemos una instancia con nombre. Este servicio gestiona las conexiones a las distintas instancias, resolviendo el nombre de la instancia y redirigiendo al cliente al puerto correspondiente. Es particularmente útil en entornos que utilizan instancias con nombres o puertos dinámicos, ya que facilita la administración de las conexiones sin necesidad de configurar manualmente cada una. El Browser también proporciona información sobre las instancias disponibles en una máquina, mejorando la flexibilidad y simplicidad en la conexión a SQL Server.

Se trata de un servicio común para todas las instancias de SQL Server por lo que solo veremos uno por cada servidor, tengamos las instancias que tengamos. Si este servicio está detenido, SQL Server seguirá funcionando pero podemos encontrarnos con problemas de conexión desde los equipos de la red si nuestras instancias tienen nombre o si tienen configurado el puerto dinámico.

Servicios Full-text engine y los servicios SQL full-text filter daemon launcher

El Full-text Engine y su servicio asociado, el Full-text Filter Daemon Launcher, proporcionan capacidades avanzadas de búsqueda en texto completo dentro de SQL Server. Este servicio permite realizar búsquedas complejas en grandes volúmenes de datos textuales, utilizando criterios como palabras clave, frases, y proximidad de términos. Es especialmente útil en aplicaciones que manejan datos no estructurados, como documentos y correos electrónicos, permitiendo a los usuarios realizar consultas sofisticadas que van más allá de las capacidades de búsqueda estándar de SQL. La combinación del Full-text Engine con el motor de base de datos permite realizar consultas mixtas que integran búsquedas textuales y estructuradas, proporcionando un alto nivel de flexibilidad en la recuperación de información. Veremos un servicio SQL Full-text Filter Daemon Launcher por cada una de las instancias que tengamos instalada en nuestra máquina y podremos detenerlo siempre y cuando no usemos estas características.

Servicios PolyBase: integración y análisis de datos externos

PolyBase es una tecnología dentro de SQL Server que nos facilita la consulta de datos almacenados en sistemas externos, como Hadoop o Azure Blob Storage, utilizando T-SQL. Los servicios asociados a PolyBase, como PolyBase Data Movement y PolyBase Engine son los que nos permiten mover y procesar datos entre diferentes nodos en una configuración distribuida. Esto es especialmente relevante cuando trabajamos en entornos de Big Data, donde nos es necesario manejar grandes volúmenes de datos que residen fuera del ecosistema tradicional de SQL Server. En estos entornos, PolyBase nos permite integrar y analizar datos de diversas fuentes sin necesidad de moverlos físicamente a SQL Server, lo que optimiza el rendimiento y reduce los costes de almacenamiento.

Los servicios de PolyBase son comunes para todas las instancias del servidor por lo que solo veremos uno de cada y ya que tienen una funcionalidad muy específica podremos detenerlos si no hacemos uso de ella.

Servicio SQL Server vss writer: copias de seguridad consistentes

El servicio SQL Server VSS Writer es fundamental para la realización de copias de seguridad consistentes de las bases de datos mientras están en uso. Este servicio interactúa con la tecnología de copia de sombra de volumen de Windows (Volume Shadow Snapshot) para asegurar que las copias de seguridad sean coherentes y confiables, incluso en entornos de producción donde las bases de datos están activas. La capacidad de realizar copias de seguridad en vivo sin afectar el rendimiento del sistema es crucial para garantizar la disponibilidad y la integridad de los datos en SQL Server. Además, este tipo de copias de seguridad por snapshot suelen ser instantáneas a la hora de realizarse y mucho (pero muchísimo) más rápidas a la hora de restaurarse.

Se trata, nuevamente, de un servicio común para todas las instalaciones de SQL Server por lo que solo veremos uno y podremos detenerlo si tenemos claro que en ningún caso se realizan este tipo de copias. Sin embargo, este es uno de esos servicios que yo recomiendo tener siempre en ejecución.

Servicios SQL Server launchpad: integración con lenguajes de análisis de datos

SQL Server Launchpad es un servicio que habilita la ejecución de scripts en lenguajes de análisis de datos, como R y Python, directamente dentro de SQL Server. Este servicio permite integrar el análisis avanzado y el machine learning en el entorno de SQL Server, sin necesidad de mover los datos a plataformas externas. La ejecución de modelos de análisis directamente sobre los datos almacenados en SQL Server optimiza el rendimiento y simplifica el flujo de trabajo, lo que es esencial en aplicaciones que requieren un análisis en tiempo real o procesamientos estadísticos complejos. Esta capacidad se puede elegir a nivel de instancia por lo que cada una que tengamos instalada tendrá su servicio y podremos detenerlos si no los necesitamos.

Servicios de otros productos de la familia SQL Server

Como ya sabéis, SQL Server incluye otros servicios además del motor de bases de datos, estos son SSAS, SSRS y SSIS. La integración de SQL Server con estos otros servicios nos permite construir soluciones BI completas y robustas.

Servicios de SSAS

SQL Server Analysis Services (SSAS) es un servicio crucial para la creación de soluciones de inteligencia de negocios (BI). SSAS permite la creación de modelos de datos complejos que pueden ser consultados eficientemente, ofreciendo soporte tanto para modelos multidimensionales (OLAP) como tabulares. Este servicio es la columna vertebral de las capacidades analíticas de SQL Server, permitiendo a las organizaciones realizar análisis profundos de sus datos, identificar patrones y tendencias, y tomar decisiones basadas en datos. Al igual que con SQL Server, podemos tener varias instancias de SSAS y veremos un servicio en ejecución por cada una de ellas.

Servicios de SSIS

SQL Server Integration Services (SSIS) es la plataforma de SQL Server para la integración y transformación de datos. SSIS permite ejecutar complejas operaciones ETL (Extract, Transform, Load), integrando datos de diversas fuentes y transformándolos para su análisis o almacenamiento en bases de datos. Además del servicio del motor de SSIS vamos a encontrar los servicios de escalado de SSIS, como el Scale Out Master y Scale Out Worker que permiten distribuir la carga de trabajo entre múltiples servidores, mejorando el rendimiento y la eficiencia en la ejecución de paquetes SSIS. Esta capacidad de escalabilidad es fundamental en entornos que requieren procesar grandes volúmenes de datos de manera rápida y eficiente. Como solo se puede tener una instalación de SSIS por máquina solo vamos a ver uno de cada uno de estos tres servicios.

Servicio de SSRS

SQL Server Reporting Services (SSRS) es el componente que permite la creación, distribución y administración de informes empresariales. SSRS proporciona herramientas para diseñar informes detallados, que pueden incluir gráficos, tablas y otros elementos visuales, facilitando la interpretación de los datos. Además, SSRS permite automatizar la distribución de informes, asegurando que los responsables de tomar decisiones tengan acceso a la información más actualizada. La capacidad de personalizar y programar informes según las necesidades específicas de la organización hace de SSRS una herramienta indispensable en el ecosistema de SQL Server. SSRS ya ha dejado de distribuirse junto con SQL Server por lo que para instalarlo tendremos que buscar un instalador independiente.

Servicios CEIP en SQL Server, SSAS y SSIS

Los servicios CEIP (Customer Experience Improvement Program) en SQL Server SQL Server Integration Services (SSIS) y SQL Server Analysis Services (SSAS) recolectan información sobre cómo utilizamos estas herramientas, incluyendo estadísticas de rendimiento y uso. Esta información se envía a Microsoft para contribuir a la mejora del producto en futuras versiones. Aunque estos servicios no son críticos para la operación diaria de SQL Server, SSIS o SSAS, pueden ser útiles para los que deseamos contribuir al desarrollo y mejora del software y obtener recomendaciones sobre cómo optimizar su uso.

Conclusión

SQL Server es una plataforma integral que va más allá de ser simplemente un motor de base de datos relacional. Sus diversos servicios, desde la automatización de tareas con el agente de SQL Server hasta las capacidades analíticas avanzadas de SSAS, y la integración de datos externos con PolyBase, permiten a las organizaciones construir soluciones completas y optimizadas para manejar grandes volúmenes de datos. Comprender y aprovechar estos servicios es fundamental para maximizar el rendimiento, la seguridad y la eficiencia en cualquier implementación de SQL Server. La correcta configuración y gestión de estos componentes garantiza que SQL Server no solo cumpla con las demandas actuales, sino que esté preparado para enfrentar los desafíos del futuro en el manejo y análisis de datos.

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.

Publicado por Roberto Carrancio en SQL Server, 0 comentarios
09
Ago
2024

Mantenimiento imprescindible en SQL Server

El mantenimiento de bases de datos en SQL Server, como en todos los sistemas de bases de datos, es una actividad crítica para garantizar el rendimiento y la estabilidad de las aplicaciones que dependen de ellas. Un enfoque proactivo en la gestión de índices, estadísticas, integridad y copias de seguridad nos permite a los administradores evitar problemas potenciales antes de que se conviertan en fallos costosos. En este artículo, profundizaremos en cada uno de estos aspectos, proporcionando una visión detallada y práctica para mantener nuestras bases de datos en condiciones óptimas.

Mantenimiento de índices para mejorar el rendimiento

Los índices son componentes esenciales en nuestras bases de datos, ya que nos permiten un acceso rápido a los datos y mejoran significativamente el rendimiento de nuestras consultas. Prueba de ello es la cantidad de artículos que les hemos dedicado en el blog. Sin embargo, la eficiencia de los índices puede deteriorarse con el tiempo debido a la fragmentación, que ocurre cuando las páginas de datos se desorganizan. Este fenómeno afecta negativamente la rapidez de las búsquedas y actualizaciones y tenemos que prevenirlo y ponerle solución.

Para mantener un rendimiento óptimo, es crucial que monitoricemos y gestionemos los índices de forma periódica. No vamos a entrar en muchos más detalles pues ya le dedicamos un artículo completo a este tema. Simplemente vamos a ver que tenemos dos opciones de mantenimiento, la reorganización y la reconstrucción. La reorganización de índices es una técnica por la cual el motor de base de datos ajusta los índices sin necesidad de reconstruirlos completamente, lo que es en sí un proceso menos intensivo en recursos. Cuando la fragmentación es alta, la reorganización ya no es tan efectiva y debemos recurrir a la reconstrucción de índices, aunque sea un proceso más costoso en términos de tiempo y recursos. Deberemos planificar estas actividades de mantenimiento durante periodos sin uso o de baja actividad para minimizar el impacto a los usuarios.

Mantenimiento de estadísticas

Las estadísticas en SQL Server proporcionan al optimizador de consultas la información necesaria para decidir cuál es el mejor plan de ejecución posible para nuestra consulta, es decir, cómo ejecutar una consulta de la manera más eficiente posible. Sin datos actualizados, el optimizador puede hacer estimaciones inexactas, lo que lleva a un mal rendimiento.

La actualización regular de las estadísticas es una práctica recomendada para mantener el rendimiento del sistema. SQL Server ofrece opciones de actualización automática, pero en entornos con alta carga de trabajo o donde el rendimiento es crítico, puede ser más efectivo realizar actualizaciones manuales programadas. Esto asegura que las estadísticas reflejen con precisión la distribución actual de los datos, lo que es esencial para que el optimizador pueda generar planes de ejecución óptimos. Además no olvidéis que una actualización de estadísticas fuerza la recopilación de los planes de ejecución por lo que tenemos que buscar el equilibrio para mantenerlas actualizadas sin provocar un exceso de recompilaciones de los planes en caché.

Chequeo de integridad de las bases de datos

La integridad de los datos es un aspecto fundamental en la administración de bases de datos. Gracias a las herramientas de SQL Server, como DBCC CHECKDB, vamos a poder verificar la consistencia física de las bases de datos y de sus datos internos. Esta verificación es crucial para identificar y corregir errores en las estructuras de datos, que pueden surgir por diversas razones, desde fallos del hardware de almacenamiento hasta errores humanos o de consistencia en los datos.

Realizar verificaciones de integridad de manera regular ayuda a detectar problemas antes de que afecten la disponibilidad del sistema o provoquen pérdidas de datos. En caso de encontrarnos con corrupción, deberemos tomar acciones inmediatamente para reparar los daños antes de que puedan ir a más. Para esto intentaremos usar las herramientas disponibles como DBCC CHECKDB y, si no es posible restauraremos los datos desde una copia de seguridad anterior al incidente. 

Una vez resuelta la corrupción tendremos que buscar el origen del problema para evitar que se vuelva a repetir. Las causas más comunes de corrupción son fallos en el almacenamiento o interrupciones inesperadas del servicio (como fallos de alimentación). Es común encontrarse con problemas de corrupción también en instalaciones en sistemas operativos de escritorio que, para ahorrar energía, desconectan la alimentación de los discos duros. Esta configuración debe ser deshabilitada si se instala SQL Server en un sistema operativo de escritorio.

Copias de seguridad como medida de protección esencial

Las copias de seguridad deberían ser la piedra angular de cualquier estrategia de administración de bases de datos. Gracias a estas copias vamos a poder recuperar datos en caso de fallos o desastres, asegurando la continuidad del negocio. Es crucial diseñar un plan de copias de seguridad que considere la frecuencia de los respaldos, la retención y el almacenamiento seguro de los mismos.

Como ya vimos en el artículo que les dedicamos, existen diferentes tipos de copias de seguridad, como completas, diferenciales y de log de transacciones, cada una con su propia función y aplicabilidad. También es vital que realicemos pruebas de restauración periódicas para asegurarnos de que los procedimientos de recuperación funcionarán correctamente cuando sean necesarios. Además, es aconsejable que almacenemos las copias de seguridad en ubicaciones seguras y, si es posible, en lugares distintos geográficamente para protegernos contra desastres locales.

Soluciones de mantenimiento para bases de datos en SQL Server

Para que podamos gestionar eficazmente el mantenimiento de bases de datos en SQL Server podemos contar con herramientas y soluciones que automatizan y optimizan estas tareas. SQL Server ofrece varias herramientas nativas, mientras que otros desarrolladores han creado soluciones adicionales que pueden complementar o incluso superar las capacidades integradas. 

Herramientas nativas de SQL Server para el mantenimiento

SQL Server incluye varias características y herramientas integradas que facilitan la gestión del mantenimiento de bases de datos. Entre ellas, el Mantenimiento de Bases de Datos (Database Maintenance) y el Asistente para Mantenimiento (Maintenance Plan Wizard) son particularmente útiles para automatizar tareas como la reorganización y reconstrucción de índices, la actualización de estadísticas, la verificación de integridad y la realización de copias de seguridad.

Estas herramientas nos permiten a los DBAs configurar trabajos programados (jobs) de manera sencilla, utilizando una interfaz gráfica o scripts Transact-SQL. Sin embargo, aunque son bastante flexibles y suficientes para escenarios no muy complejos, es común encontrarnos con que estas herramientas pueden estar limitadas en cuanto a personalización y control exhaustivo sobre nuestras tareas de mantenimiento.

Soluciones avanzadas de Ola Hallengren

Una de las soluciones más reconocidas y ampliamente utilizadas en la comunidad SQL Server es el conjunto de scripts de mantenimiento desarrollado por Ola Hallengren. Estos scripts son muy configurables y nos proporcionan una solución completa para el mantenimiento de índices, copias de seguridad y verificación de integridad. De esta manera, nos permiten adaptar nuestras tareas de mantenimiento a las necesidades específicas de cada entorno.

Yo siempre recomiendo los scripts de Ola Hallengren ya que destacan por su eficiencia frente a las soluciones nativas. Además tienen una integración con el agente de SQL Server que nos facilita la programación y monitorización de las tareas de mantenimiento. Por último, pero no menos importante, estos scripts son gratuitos y se actualizan regularmente, lo que los convierte en una opción robusta y confiable.

Conclusión

Un mantenimiento adecuado y constante de las bases de datos en SQL Server es esencial para garantizar su rendimiento, disponibilidad y seguridad. La gestión eficiente de índices, la actualización estadísticas, las verificaciones regulares de integridad y una estrategia eficaz de copias de seguridad son pilares fundamentales en esta tarea. Adoptar un enfoque proactivo y planificado nos permite evitar problemas antes de que se conviertan en crisis, asegurando que nuestras bases de datos funcionen de manera óptima y que los datos estén siempre disponibles y protegidos.

 

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios