SQL Server

Aquí encontraras todos nuestros post relacionados con SQL Server desde cero hasta un nivel avanzado. Desde infraestructura hasta modelado de datos.

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
28
Ago
2024

Conociendo los Linked Server

Una de esas características interesantes de SQL Server que a menudo pasan desapercibidas es la capacidad de conectarse y consultar datos de diversas fuentes de datos ajenas a nuestra instancia. Esto se consigue gracias a los Linked Servers (Servidores Vinculados) que facilitan la integración de datos distribuidos, permitiéndonos interactuar con otras bases de datos como si fueran parte de nuestra instancia local de SQL Server. Ya sea porque necesitemos acceder a datos de otro servidor SQL, de Oracle, archivos Excel o incluso consultas LDAP los Linked Server son una herramienta imprescindible.

¿Qué es un Linked Server en SQL Server?

Un linked server, o servidor vinculado, es una opción de SQL Server que nos permite establecer una conexión con otra fuente de datos, que puede ser otra instancia de SQL Server, un servidor Oracle, un servidor MySQL, una hoja de cálculo y muchas más opciones. Esta herramienta está pensada para escenarios donde necesitemos acceder a datos almacenados en distintos sistemas para realizar análisis, reportes o integraciones de datos.

La principal ventaja de los linked servers es que nos permiten ejecutar consultas a datos remotos utilizando la sintaxis de cuatro partes: NombreDelLinkedServer.BaseDeDatos.Esquema.Tabla. Este enfoque simplifica la integración de datos, eliminando la necesidad de replicar físicamente los datos en un solo lugar.

Configuración y uso de Servidores Vinculados en SQL Server

Configurar un Linked Server es un proceso relativamente sencillo que se puede realizar tanto mediante SQL Server Management Studio (SSMS) como con comandos T-SQL.  Para crear un linked server a través de SSMS nos dirigiremos a la carpeta Objetos de Servidor y haremos clic derecho sobre Servidores Vinculados para a continuación seleccionar Nuevo Servidor Vinculado. Se nos abrirá una ventana donde rellenaremos los datos relativos al origen de los datos y a la seguridad (usuario y contraseña remotos). Para crear un Linked Server utilizando T-SQL, el comando es el siguiente:

EXEC sp_addlinkedserver
    @server = 'NombreDelLinkedServer',
    @srvproduct = '',
    @provider = 'NombreDelProveedorOLEDB',
    @datasrc = 'NombreOIPDelServidorRemoto';

Para gestionar la autenticación, que puede ser mediante Windows o especificando credenciales propias, utilizamos:

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'NombreDelLinkedServer',
    @useself = 'False',
    @locallogin = NULL,
    @rmtuser = 'UsuarioRemoto',
    @rmtpassword = 'ContraseñaRemota';

Una vez configurado, podemos realizar consultas distribuidas usando la sintaxis de cuatro partes:

SELECT * FROM NombreDelLinkedServer.BaseDeDatos.Esquema.Tabla;

Uso de Linked Servers con servidores no SQL Server

La sintaxis de cuatro partes es muy útil, pero solo es aplicable cuando el linked server apunta a otra instancia de SQL Server. Cuando necesitamos conectarnos a servidores que no sean SQL Server, como por ejemplo Oracle, MySQL o sistemas de archivos como Excel, esta sintaxis no es compatible. En estos casos, debemos utilizar métodos alternativos para ejecutar nuestras consultas.

¿Por qué no se puede usar la sintaxis de cuatro partes?

La sintaxis de cuatro partes (NombreDelLinkedServer.BaseDeDatos.Esquema.Tabla) depende de la estructura jerárquica de SQL Server, que organiza los objetos en bases de datos, esquemas y tablas de una manera específica. Otros sistemas de bases de datos, como Oracle, tienen una organización interna diferente que no se adapta a esta estructura. Por ejemplo, en Oracle, las bases de datos y esquemas no se organizan de la misma manera, lo que hace que esta sintaxis no sea aplicable y genere errores si la intentamos utilizar.

Alternativa: Uso de OPENQUERY para Consultas con Linked Servers

Para trabajar con linked servers que apuntan a servidores no SQL Server, OPENQUERY es la herramienta adecuada. Esta función permite enviar una consulta SQL completa al servidor remoto, ejecutarla allí y devolver los resultados a SQL Server. 

También podemos usar OPENQUERY para servidores SQL Server en vez de la sintaxis de 4 partes y, aunque pueda parecer más complejo al escribir la consulta, es especialmente útil para mejorar el rendimiento en consultas distribuidas, ya que permite que el servidor remoto procese la consulta completa y solo devuelva los resultados.

La sintaxis de OPENQUERY es la siguiente:

SELECT * 
FROM OPENQUERY(
 NombreDelLinkedServer,
  'SELECT Columna1, Columna2 FROM TablaRemota WHERE Condicion'
);

Esta forma de proceder nos permite aprovechar la sintaxis y capacidades nativas del servidor remoto, como Oracle o MySQL, optimizando la ejecución de las consultas y minimizando la transferencia de datos.

Seguridad en el Uso de Linked Servers

La seguridad es un factor crucial al utilizar linked servers, no olvidemos que estamos accediendo a datos remotos y eso siempre es delicado. Dado que estamos extendiendo nuestras consultas a otros servidores, es fundamental asegurarnos de que las conexiones sean seguras y que las credenciales estén adecuadamente protegidas.

Por ello, es recomendable utilizar la autenticación de Windows siempre que sea posible, ya que nos permite aprovechar las políticas de seguridad de Active Directory. Sin embargo, también es probable que para ello tengamos que configurar la autenticación por Kerberos y registrar SPNs para no tener problemas de inicio de sesión. 

La alternativa sería usar un login de SQL del servidor remoto. En este caso, si necesitamos utilizar autenticación SQL, es importante que las credenciales tengan los mínimos privilegios necesarios en el servidor remoto para realizar las tareas requeridas.

Además, debemos ser muy cuidadosos con las opciones de seguridad como «no delegation» y «mapped logins» para evitar la elevación de privilegios y controlar quién tiene acceso al linked server. Las auditorías regulares de los linked servers y sus usuarios configurados, para mi, son esenciales para mantener un entorno seguro.

Rendimiento al Usar Linked Servers y OPENQUERY

El rendimiento es un aspecto que no debemos pasar por alto al trabajar con linked servers. La latencia de la red y el rendimiento del servidor remoto son factores que pueden afectar considerablemente nuestras consultas distribuidas. Para mitigar estos problemas, debemos optimizar nuestras consultas para que solo traigan los datos necesarios.

Además, no debemos olvidar que, al trabajar con datos de un servidor remoto, nuestro motor de base de datos no va a ser capaz de estimar la cardinalidad de los datos, es decir, no va a saber de antemano cuantos registros le vienen y cuántos recursos asignar para la resolución de esa consulta. En este contexto, el uso de OPENQUERY puede ser una gran ventaja en términos de rendimiento. Al permitir que el servidor remoto procese la consulta completa, reducimos la cantidad de datos que se transfieren y optimizamos la carga en nuestra instancia local de SQL Server.

No obstante, es importante evitar las consultas donde tengamos que trabajar con datos remotos y locales a la vez, normalmente es más eficiente cargar los datos remotos en plano (con las transformaciones que hayamos podido hacer enteramente en el servidor remoto) y luego ya operarlo en combinación con los datos locales. De todas formas, como cada caso es un mundo, lo mejor es probar y ajustar las consultas para asegurarnos de que estamos obteniendo el máximo beneficio de esta técnica. 

En este sentido, también debemos considerar el impacto de las transacciones distribuidas. Cuando nuestras operaciones involucran cambios en múltiples servidores, debemos asegurarnos de que todas las transacciones se manejan correctamente para evitar inconsistencias. Esto puede requerir el uso de coordinadores de transacciones distribuidas (DTC), lo que añade una capa de complejidad y potencial impacto en el rendimiento.

Conclusión

Los Linked Server en SQL Server nos ofrecen una forma versátil y eficiente de interactuar con datos distribuidos en múltiples fuentes. Sin embargo, su uso no está exento de riesgos en cuanto a la seguridad y el rendimiento por lo que debemos ir con cuidado.

Cuando trabajamos con servidores SQL Server, la sintaxis de cuatro partes es una opción sencilla y directa. Sin embargo, cuando nos conectamos a servidores que no son SQL Server, OPENQUERY se convierte en la herramienta clave para ejecutar consultas distribuidas de manera eficiente. Entender las capacidades y limitaciones de cada método nos permitirá aprovechar al máximo los linked servers, garantizando que nuestras aplicaciones funcionen de manera segura y eficiente en entornos distribuidos y heterogéneos.

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
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
21
Ago
2024

Collation en SQL

La collation (intercalación) es uno de esos conceptos cruciales de SQL Server y Azure SQL sin embargo, a menudo se pasa por alto o se confunde con otros términos como idioma o codificación. Dado que la collation impacta directamente en la forma en que se procesan las consultas de texto, su correcta configuración es vital para evitar problemas de rendimiento y coherencia en nuestras aplicaciones. En resumen, la collation define cómo se ordenan y comparan los datos de texto en una base de datos, incluyendo reglas sobre la sensibilidad a mayúsculas, acentos y otros caracteres especiales. Es de esas configuraciones de las que uno solo se acuerda cuando tiene un error y ya es tarde para solucionarlo.

¿Qué es la Collation?

La collation en SQL Server es una configuración que define las reglas para comparar y ordenar cadenas de texto. Estas reglas determinan, por ejemplo, si las comparaciones deben ser sensibles a mayúsculas o minúsculas (case-sensitive o case-insensitive), si deben distinguir entre caracteres con y sin acento (accent-sensitive o accent-insensitive), y cómo se ordenan los caracteres según un conjunto de reglas culturales o técnicas específicas.

Una confusión común es suponer que al establecer una collation se está configurando el idioma de la base de datos o la codificación de los datos. Esto no es correcto. Es importante entender que la collation no es lo mismo que el idioma o la codificación de los caracteres.

Diferencias entre intercalación, idioma y codificación

Mientras que la collation puede estar basada en un conjunto de reglas lingüísticas propias de un idioma (por ejemplo, cómo se ordenan las letras en español), no impone que los datos en la base de datos estén escritos en ese idioma.

El idioma, por definición, se refiere al conjunto de reglas gramaticales y de escritura utilizadas en un texto. La codificación, por su parte, la podemos definir como la manera de representar los caracteres en bytes (por ejemplo, UTF-8 o ASCII). Aunque la collation puede estar influenciada por el idioma, ya que define las reglas de ordenación basadas en ese idioma, no determina el idioma del texto en sí.

Por otro lado, la codificación se encarga de la representación física de los caracteres, es decir, cómo los caracteres son almacenados y transmitidos. SQL Server usa la collation para interpretar estas representaciones y aplicar las reglas correctas durante las comparaciones y ordenaciones, independientemente de la codificación subyacente.

Por ejemplo, si seleccionamos una collation basada en inglés, como SQL_Latin1_General_CP1_CI_AS, esta permitirá que las comparaciones de texto sean insensibles a mayúsculas y acentos, de acuerdo con las reglas del idioma inglés. Sin embargo, esto no impide que los datos almacenados estén en otro idioma, como español o alemán; simplemente determina cómo se manejarán y ordenarán esos datos.

En cuanto a la codificación, ésta se refiere a cómo los datos se almacenan y se interpretan a nivel de bytes. SQL Server maneja esto automáticamente, y la collation se encarga de aplicar las reglas adecuadas durante las operaciones con texto, como ORDER BY o WHERE.

Niveles de Intercalación

SQL Server está preparado para manejar distintas collation a nivel de instancia , base de datos e incluso columna. Cada una de estas intercalaciones tiene un alcance distinto y, por tanto, si deseamos modificarlas el proceso será diferente. Lo que sí os puedo asegurar es que en todos los casos cambiar una collation es peor que un dolor de muelas. Además, una discrepancia de collation puede ser incompatible con operaciones de comparación de datos o de lectura en un objeto y escritura en otro, existen maneras de salvarlo pero, nuevamente, será un suplicio para el desarrollador.

Collation a nivel de instancia en SQL Server

La collation a nivel de instancia se configura durante la instalación de SQL Server y define el comportamiento predeterminado para todas las bases de datos y objetos creados dentro de esa instancia. Esta collation será la de las bases de datos de datos de sistema y es clave en el futuro comportamiento de SQL Server. Además, si no especificamos una collation al crear después una base de datos de usuario o un objeto, heredará la de la instancia.

Esta collation de instancia influye en la forma en que se gestionan las comparaciones y ordenaciones en todas las bases de datos del sistema, afectando aspectos como la sensibilidad a mayúsculas y acentos. Es esencial elegir la collation correcta durante la instalación, ya que cambiarla posteriormente puede ser complicado, requiriendo a veces la reinstalación del servidor o la migración de las bases de datos.

Collation a nivel de base de datos

Cada base de datos en SQL Server puede tener su propia collation, independientemente de la collation de la instancia. Esto es útil cuando necesitamos manejar datos en diferentes idiomas o con diferentes requisitos de comparación. Por ejemplo, una base de datos que maneje datos en japonés puede necesitar una collation distinta de otra que gestione datos en francés no por el idioma o por el juego de caracteres sino porque los criterios de ordenación de los caracteres cambia.

Al crear una base de datos, podemos especificar la collation utilizando la cláusula COLLATE. Si necesitamos cambiar la collation de una base de datos existente, esto se puede hacer con el comando ALTER DATABASE, aunque el cambio no afectará automáticamente a las tablas y columnas ya existentes. Por lo tanto, si cambiamos la collation de una base de datos, debemos asegurarnos de revisar y actualizar también las collations de las columnas si es necesario.

Collation a nivel de columna

El nivel más granular de collation se encuentra en las columnas de las tablas. Esto nos permite tener columnas con diferentes collations dentro de la misma tabla, lo cual es especialmente útil en escenarios donde necesitamos manejar datos de diferentes orígenes o con distintos requisitos culturales.

Para definir la collation de una columna al momento de crear una tabla, utilizamos la cláusula COLLATE después del tipo de dato de la columna. Si necesitamos cambiar la collation de una columna existente, podemos hacerlo con el comando ALTER TABLE ALTER COLUMN, aunque este proceso puede ser costoso en términos de rendimiento, especialmente si la tabla contiene grandes volúmenes de datos.

Cómo trabajar con datos con distinta Collation

Trabajar con datos que tienen diferentes collations requiere precaución, especialmente al realizar comparaciones o uniones entre columnas. SQL Server permite especificar la collation en las consultas utilizando la cláusula COLLATE en las expresiones de texto, lo que nos permite unificar la collation de los datos en una operación específica. Esta es la única forma de poder actuar cuando tenemos distintas collations entre objetos, por ejemplo, entre bases de datos e instancia que, como hemos visto antes afecta a las bases de datos de sistema y por tanto a las tablas temporales en TempDB.

Otro ejemplo, si tenemos dos columnas con diferentes collations y necesitamos compararlas, podemos utilizar COLLATE para especificar la collation a usar durante la comparación, evitando así errores cómo «Cannot resolve the collation conflict«. De esta manera, podemos trabajar con datos multilingües o de diferentes fuentes de manera más segura y eficiente.

Cambiar la Collation: Impacto y consideraciones

Cambiar la collation en SQL Server, ya sea a nivel de instancia, base de datos o columna, no es una tarea trivial y debemos realizarla con precaución. Los cambios a nivel de instancia son los más delicados, ya que afectan a todas las bases de datos y objetos de esa instancia. Esto implica una reinstalación de facto de la instancia y, aunque no perderemos los datos, sí que pueden verse afectados los objetos de instancia como logins, credenciales, etc…

A nivel de base de datos, es fundamental considerar que las tablas y columnas existentes no cambiarán automáticamente, lo que podría llevar a una mezcla de collations dentro de la misma base de datos. Este tipo de situaciones complican la gestión y pueden dar lugar a problemas de rendimiento o coherencia de datos. Por ello, es crucial planificar estos cambios con antelación y realizar pruebas exhaustivas en un entorno controlado antes de aplicarlos en producción.

Conclusión

La correcta gestión de la collation en SQL Server y Azure SQL es esencial para garantizar la integridad y el rendimiento de nuestras bases de datos. Entender las diferencias entre collation, idioma y codificación nos permite tomar decisiones más informadas y evitar errores comunes que pueden surgir al trabajar con datos multilingües o de diferentes fuentes. Al aplicar las mejores prácticas en la configuración y el manejo de collations, podemos optimizar nuestras bases de datos para un rendimiento y consistencia óptimos, asegurando que nuestras aplicaciones manejen datos de texto de manera eficaz y sin sorpresas inesperadas.

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