--Encriptando un Store procedure
USE prueba
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123'
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = '123'
GO
/*the following code allows the service instance to open the master key*
USE prueba ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY /*close your master key*/
CLOSE ALL Keys
create PROCEDURE demo
with encryption
AS
BEGIN
SELECT * from datos
END
GO
exec demo
EXEC sp_helptext 'demo' --Verifico el contenido del SP pero no lo muestra al estar encriptado
SELECT definition FROM sys.sql_modules WHERE OBJECT_ID = OBJECT_ID('demo');
--Mostrar los resultado de un Query en XML
SET STATISTICS XML ON
GO
select * from datos
SET STATISTICS XML OFF
GO
Common Table Expression (CTE)
with MyCTE(x)
as
(select x='hello')
select x from MyCTE
with MyCTE(strnombre)
as
(select strnombre = convert(varchar(8000),'hector')
union all
select strnombre + 'a' from MyCTE
where len(strnombre) < orderid =" ord.OrderID" productid =" det.ProductID" dbname = "pubs" status =" -32768" name =" 'pubs'" disk =" 'e:/MSSQL/BACKUP/bdprueba.BAK'" stats =" 10"> [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @date_var }
| RETAINDAYS = { days | @days_var } ]
[ [ , ] PASSWORD = { password | @password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @text_variable } ]
[ [ , ] MEDIANAME = { media_name | @media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]
Por último, los permisos que se deben de tener para poder realizar un respaldo es ser miembro del rol de servidor sysadmin o miembro de los roles de bases de datos db_owner y db_backupoperator.
Existen otros tipos de respaldos, como son los diferenciales que solo guardan los cambios a partir de un respaldo completo y los respaldos transaccionales los cuales guardan el contenido del log de transacciones. Este último solo aplican para el modo de recuperación Full y Bulk-Logged pero los explicare después en otro articulo.
10 PASOS PARA MEJORAR LA SEGURIDAD EN SQL SERVER
Mantener una buena seguridad de nuestros datos puede ser una tarea muy dura, pero para los dba's no hay nada mejor que ejecutar una prueba de penetración sobre nuestros manejadores de bases de datos y ver que los resultados fueron positivos, es decir que no tenemos vulnerabilidades de seguridad. Este articulo se enfoca en como reforzar la seguridad en SQL Server.
Autentificación de Windows
Siempre se recomienda usar la seguridad de windows en nuestros servidores de bases de datos, ya que este método nos quita del 95% de los problemas de seguridad incluyendo virus. De esta manera para un hacker para poder penetrar a nuestra base de datos primero tendría que autentificarse en el dominio, que es mucho más difícil que autentificarse en SQL Server otra ventaja es que el password no esta viajando por la red.
Cuenta de Administrador de SQL Server (sa)
Debemos de evitar el uso de la cuenta de SA, aun si somos administradores, es preferible el crear una cuenta para nosotros con permisos restringidos, ya que la mayoría de las veces sólo entramos a realizar alguna modificación de datos o a monitorear el equipo.
Por ningún motivo los desarrolladores deben de tener este usuario, ya que nunca falta que lo usan para meterlo en sus aplicaciones y ahorrase tiempo de estar asignando los permisos necesarios para sus aplicaciones.
Debemos de cambiar periódicamente este password y si tenemos una buena administración, no debemos de tener ningún problema al cambiarlo, es decir que las aplicaciones comenzaran a fallar debido a que están usando la cuenta de administrador.
Remover BUILTIN/Administrators
Esta es una de las mayores vulnerabilidades de SQL Server, porque esta cuenta le da acceso a todo el que sea administrador de la red, del servidor a entrar al manejador de base de datos, haciéndonos perder el control de quien le mete mano a la base de datos.
Lo primero que se debe de hacer después de realizar una instalación de SQL Server es eliminar esta cuenta, pero debemos de tener cuidado de que la cuenta que levanta a SQL Server tenga un login creado dentro del manejador con permisos de administrador, de no ser así tendremos problemas al reiniciar SQL Server y el Agente de SQL Server.
Cambiar la cuenta con la que inicia SQL Server
Es preferible siempre cambiar la cuenta con la que SQL Server inicia, LocalSystem por alguna que no sea descriptiva, es decir que no diga SQLAdmin, o algo similar, ya que es una manera fácil de que un hacker identifique que es una cuenta con permisos sobre la Base de datos. Ahora este cambio es preferible hacerlo desde el Administrador de SQL Server, dando botón derecho sobre el nombre del servidor, seleccionando propiedades y la pestaña de seguridad de esta manera SQL Server automáticamente le asigna los permisos necesarios a la cuenta que asignemos.
Esta nueva cuenta que asignemos no debe de tener permisos de administrador sobre el servidor, y debemos estar seguros que la politica de “Logon Locally” este negada para esta cuenta
Auditar los intentos fallidos y accesos denegados
La mejor manera de detectar que tenemos un intruso es colocando alarmas en nuestro sistema. Habilitando la opción (Propiedades de SQL Server / Pestaña de seguridad) Failed Login, podemos detectar en el log de errores cuando un usuario trato de firmarse a SQL Server y su acceso fue denegado, y si llegáramos a ver este mensaje muy concurrente o hasta varios en menos de 1 minuto podemos estar seguros de que se trata de algún intruso tratando de acceder a nuestra base de datos.
El siguiente paso a esta alarma seria abrir un trace y capturar solo los intentos fallidos de conexión con el Hostname, esto nos dirá el nombre de la máquina de donde se esta intentando acceder a nuestra base de datos.
Otra manera de colocar una alarma en nuestro SQL Server es dar de alta una alerta que nos mande un mensaje ya sea por medio de un net send o un mail. En lo particular a mi me gusta habilitar el error #229 que pertenece a cualquier mensaje de permiso denegado que es escrito en el log de errores de SQL Server. Esta alarma la puedes habilitar ejecutando el siguiente query.
sp_configure 'allow updates',1
go
reconfigure WITH OVERRIDE
go
UPDATE sysmessages SET dlevel = (dlevel | 0x80) WHERE error = 229
Go
sp_configure 'allow updates',0
go
reconfigure WITH OVERRIDE
go
Por ultimo, si nosotros fuéramos hakers, nos gustaría ocultar la actividad que llevemos acabo sobre la base de datos, la manera de hacer esto sería ciclar el log de errores 5 veces, mediante el comando DBCC ERRORLOG, para esto es recomendable definir en el registro que queremos mantener más de 5 logs de errores como historial, al menos 10. La forma de hacer esto es la siguiente:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer]
"NumErrorLogs"=dword:00000010
Es conveniente de vez en cuando auditar los usuarios que no tienen password, esto se puede hacer mediante un query, debemos de tomar en cuenta que las cuentas de windows no almacenan un passsword, esto lo podemos filtrar mediante la columna isntname = 0.
use master
go
select name, password from syslogins where password is null and name is not null
and isntname = 0
Mantener actualizados los service packs y hot fixes
Manteniéndonos actualizados con los service packs y hot fixes es una manera de mantenernos protegidos de los hakers más avanzados. La mayoria de las vulnerabilidades son corregidas en estos parches. Una manera de mantenerse informado es subscribiendose a las alertas de seguridad de Microsoft en:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/bulletin/notify.asp. Una vez inscrito un mensaje sera enviado a nuestro correo electrónico, desafortunadamente no existe manera de filtrar solo aquellos que afecten a SQL Server.
Proteger los extends procedures
El problema con estos extends procedures es que son ejecutados con la cuenta que inicia SQL Server . Si no estamos usando replicación o SQL Mail, es conveniente iniciar SQL Server con una cuenta de sistema. Después de hacer eso debemos de bloquear los extends procedures que no usemos como xp_cmdshell. Este comando nos permite ejecutar cualquier comando del sistema operativo siendo una puerta de seguridad abierta para un hacker.
Cambiar el puerto de SQL Server y bloquearlo
Una manera de evitar hakers intentando encontrar servidores de SQL Server es cambiando el puerto de SQL Server, la mayoría de los hackers se fastidian de estar buscando puertos por la red, y para evitar que este escaseando puertos de SQL Server debemos de meterlos a firewall. El puerto lo podemos cambiar en la utilería de red “Server Network Configuration”, seleccionar TCP/IP y seleccionando propiedades. Por default tenemos siempre 1433 para la instancia por default, cambiemos esta por otra. Debemos de tomar en cuenta que si necesitamos acceder al SQL Server desde fuera de la red, informar al administrador de nuestra red del cambio.
Control de acceso por medio de store procedures
Siempre debemos de tratar de dar acceso a los datos por medio de store procedure y no directo a las tablas o evitar darlo con el rol db_datareader y db_datawriter. Una ventaja de hacer esto aparte de la seguridad es que si necesitáramos hacer un cambio en alguna aplicación solo sería en el store procedure y no recompilando toda la aplicación.
Protegiendo el Sistema Operativo
Debemos recordar que si no esta seguro el sistema operativo, entonces nuestra base de datos esta vulnerable a un ataque. Por ello también como DBA’s debemos preocurar que el sistema operativo cuenta con los service packs necesarios.
COMO MOVER DE RUTA LA BASE DE DATOS MASTER
Este artículo muestra la manera de mover de ruta la base de datos master.
1. Cambiar el directorio de master (datos y log) en SQL Server Enterprise Manager. Nota Puedes aquí también mover la ruta del log de errores.
2. Da botón derecho en el SQL Server Enterprise Manager y presiona propiedades.
3. Presiona en “Startup Parameters” y podras ver las siguientes entradas:
-dD:MSSQLdatamaster.mdf
-eD:MSSQLlogErrorLog
-lD:MSSQLdatamastlog.ld
-d ruta para el archivo de datos de master
-e ruta errorlog
-l ruta para el archivo de log de master
4. Remueva los valores actuales e ingrese los nuevos valores
5. Detener SQL Server
6. Copiar master.mdf, mastlog.ldf a la nueva ruta
7. Iniciar SQL Server
Después puedes revisar con un sp_helpfile sobre master que tus rutas sean las correctas.
COMO MONITOREAR Y EXPANDIR UN LOG DE TRANSACCIONES
Monitorear el log de transacciones es una de las actividades más importantes para los administradores de bases de datos, ya que en caso de que este llegara a llenarse, no podrían llevarse a cabo más transacciones sobre esta base de datos quedando fuera de servicio.
Monitoreando el log de transacciones
Monitorear el log regularmente puede ayudarnos a resolver varios problemas dentro de nuestros sistemas, ya que este puede indicarnos si existen demasiadas transacciones realizadas por una sola aplicación, que podría resultar en un mal diseño o simplemente la necesidad de planear mejor los recursos de log en nuestro servidor de base de datos.
Es muy importante tener en cuenta que si el log de transacciones llegara a saturarse, SQL Server no podrá realizar más cambios dentro de nuestra base de datos.
La manera de monitorear un log de transacciones, puede llevarse a cabo de 2 maneras, una de ellas es mediante un comando desde el analizador de consultas y la otra utilizando los contadores de SQL Server desde el sistema operativo.
• Desde el analizador de consultas ejecutar el comando DBCC SQLPERF(LOGSPACE).
• Utilizando los contadores de SQL Server que se describen a continuación.
Contador Descripción
Log Bytes Flushed/sec Número total de bytes del log de transacciones vaciados
Log Flushes/sec Número de vaciados del log de transacciones
Log Flush Waits/sec Número de confirmaciones (commit) en espera al momento de vaciar el log de transacciones.
Percent Log Used Porcentaje del log de transacciones usado.
Log File(s) Size(KB) Tamaño total del log de transacciones de la base de datos
Log Cache Hit Ratio Lecturas realizadas a través de la caché del administrador de registro.
Situaciones en las que se produce mucha actividad en el log de transacciones
Algunas de las situaciones por la que podría presentarse mucha actividades en el log de transacciones y saturarlo son:
• Cargar información en una tabla que tiene indices. SQL Server almacena en el log todos los inserts y cambios en los índices. Cuando se carga en tablas que no tienen indices SQL Server solo reserva extents para el log.
• Transacciones que realizan muchas modificaciones (INSERT, UPDATE,DELETE) a una tabla en una sola transacción. Esto generalmente occurre cuando la sentencia WHERE es muy general, causando que muchos registros sean modificados.
Expandiendo el log de transacciones
Expandir un log de transacciones debe de realizarse solamente si en verdad es requerido por la aplicación y no solo por el echo de asignar más espacio, ya que para ello existen los respaldos del log de transacciones en donde se vacia el espacio ocupado del log.
Para asignar espacio de log a una base de datos pues realizarse mediante el SQL Server Enterprise Manager o la sentencia ALTER DATABASE, en esta caso hablaremos solamente de la sentencia ALTER DATABASE
Ejemplo:
Agregar dos archivos de log a una base de datos
El ejemplo siguiente agrega dos archivos de log de 5 MB a una base de datos.
USE master
GO
ALTER DATABASE Test1
ADD LOG FILE ( NAME = test1log2,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test2log.ldf',
SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB),( NAME = test1log3,
FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\test3log.ldf',
SIZE = 5MB, MAXSIZE = 100MB,
FILEGROWTH = 5MB)
GO
CÓMO CONFIGURAR SQL SERVER 2000 PARA ESCUCHAR EN VARIOS PUERTOS TCP ESTÁTICOS
En cada biblioteca de red del servidor, SQL Server configura un subproceso de "escucha" distinto. Si el servidor se satura de solicitudes de conexión, las conexiones de clientes se restablecen y se exige que vuelvan a establecer una nueva sesión. A fin de aumentar el rendimiento del servidor SQL Server para procesar más solicitudes de conexión simultáneamente, pueden agregarse puertos TCP adicionales en los que escuchar. Para agregar puertos TCP adicionales, siga estos pasos:
1. Inicie la herramienta de red de SQL Server 2000. Seleccione TCP, haga clic en Propiedades y, a continuación, escriba el puerto adicional separado por una coma; por ejemplo: 1433,5000.
2. Detenga y vuelva a iniciar el servidor SQL Server y revise el registro de errores, que debe ser similar a lo siguiente:
Servidor SQL Server a la escucha en TCP, Memoria compartida, Canalizaciones con nombre.
Servidor SQL Server a la escucha en 157.54.178.42:1433, 157.54.178.42:5000, 127.0.0.1:1433, 127.0.0.1:5000.
3. Utilice la herramienta de red de cliente de SQL Server para modificar los clientes, de forma que repartan la carga entre todos los puertos TCP. Por ejemplo, dos servidores Web que se conectan al mismo servidor SQL Server en el puerto 1433 pueden dividir la carga entre los puertos 1433 y 5000. Esto permite al administrador separar el tráfico de varios puertos y aumenta el rendimiento de la conexión.
Setting up Database Mail for SQL 2005
Problem
Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail. This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work. With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain. So how do you setup Database Mail?
Solution
There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server 2005 or by using SQL Server Management Studio. For this exercise we will walk through how to setup Database Mail by using the GUI.
To setup Database Mail, connect to your server and expand the Management node of the tree and then right click on "Database Mail".
Then select "Configure Database Mail' and you will get the following welcome screen and the click "Next".
The following screen will appear and select "Set up Database Mail by performing..." and click "Next".
If Database Mail has not been enabled, you will get this following screen. Just click "Yes" to enable it. If it has already been enabled this screen will not appear.
Enter in a name for the Profile and also a description and click "Add..."
The following screen will appear. Fill out the details for your mail account that will be used to send out email from SQL Server. When you are done click "OK".
After you click "OK" you will be brought back to this screen and the SMTP details will now show for the account you just setup. Click "Next" to continue.
On the next screen you will see the name of the profile that you just setup. Click on the checkbox to allow this to be a Public profile and also select "Yes" for the default profile and then click "Next".
The following screen has some additional parameters that can be set to control how mail is sent. You can make changes or leave the defaults. When you are done click "Next".
A summary screen will appear that shows you all of the options that were selected. If everything is correct click "Finish" or click "Back" to go back and make changes.
When you click "'Finish" the next screen will appear that shows you the status of installing Database Mail. When this has finished just click "Close" to close this screen.
To test Database Mail, right click on Database Mail and select "Send Test E-Mail".
Fill in a "To:" email address and change the body of the email if you want and then click "Send Test E-Mail".
After you have sent the email you will get this message box to confirm if the email was received or not. If it was you can click "OK" to close the screen or click "Troubleshoot" which will launch the help information to see what the issue may be and how it can be resolved.
COMO HACER UN BACKUP FULL , DIFERENCIAL Y DE LOG :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[postilion_monitor_sqlagent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[postilion_monitor_sqlagent]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BkPostilionDiff]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_BkPostilionDiff]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BkPostilionFull]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_BkPostilionFull]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_BkPostilionLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_BkPostilionLog]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ShutDownDBUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ShutDownDBUsers]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postilion_monitor_sqlagent
AS
BEGIN
exec xp_servicecontrol N'QUERYSTATE', N'SQLServerAgent'
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure sp_BkPostilionDiff
As
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'E:\POSTILION Backups Automaticos\bkNuevaArq\'
SET @name='postilion'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),120)
select @fileDate=replace(@fileDate,'-','')
select @fileDate=replace(@fileDate,':','')
select @fileDate=replace(@fileDate,' ','')
SET @fileName = @path + @name + 'Dif' + '_' + @fileDate + '.BAK '
BACKUP DATABASE @name TO DISK = @fileName WITH DIFFERENTIAL
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure sp_BkPostilionFull
As
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'E:\POSTILION Backups Automaticos\bkNuevaArq\'
SET @name='postilion'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),120)
select @fileDate=replace(@fileDate,'-','')
select @fileDate=replace(@fileDate,':','')
select @fileDate=replace(@fileDate,' ','')
SET @fileName = @path + @name + 'full' + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure sp_BkPostilionLog
As
DECLARE @name VARCHAR(50)
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
DECLARE @fileDate VARCHAR(20)
SET @path = 'E:\POSTILION Backups Automaticos\bkNuevaArq\'
SET @name='postilion'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),120)
select @fileDate=replace(@fileDate,'-','')
select @fileDate=replace(@fileDate,':','')
select @fileDate=replace(@fileDate,' ','')
SET @fileName = @path + @name + 'Log' + '_' + @fileDate + '.BAK'
BACKUP LOG @name TO DISK = @fileName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_ShutDownDBUsers
@dbname sysname, -- Name of database to shut down (Required)
@postshutdowncmd nvarchar(256)='' -- Optional command to execute after last kill
AS
-- This stored procedure will close all user connections to a database
-- and then execute the optionally specified command.
BEGIN
DECLARE @killcmd nvarchar(128)
DECLARE @spid int
DECLARE killcursor CURSOR FOR
SELECT DISTINCT spid from master.dbo.sysprocesses
WHERE dbid -- Only select SPID > 10 to avoid killing any system processes
in (SELECT dbid from master.dbo.sysdatabases WHERE name = @dbname) and spid >= 10
OPEN killcursor
FETCH killcursor into @spid
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @killcmd = 'kill ' + convert(varchar(10),@spid)
PRINT @killcmd
EXECUTE sp_executesql @killcmd
FETCH killcursor into @spid
END
CLOSE killcursor
DEALLOCATE killcursor
IF @postshutdowncmd <> ''
EXECUTE sp_executeSQL @postshutdowncmd
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
