Codifique objetos encriptados de bases de datos SQL

Una de las maneras de mantener su procedimiento almacenado, función o vista ocultos del esquema es usar WITH ENCRYPTION. De esta forma usted puede bloquear el acceso al script DDL de los objetos de manera que los usuarios no autorizados no puedan verlos:

CREATE FUNCTION [dbo].[ufnGetAccountingEndDateEnc]()
RETURNS [datetime] 
with encryption 
AS 
BEGIN
    RETURN DATEADD(millisecond, -2, CONVERT(datetime, '20040701', 112));
END;
GO

El problema es que una vez que un objeto es encriptado ni siquiera los sysadmins pueden ver su código. No hay una solución por defecto en SQL Server Management Studio para revertir la encriptación, o des encriptar un objeto, y los métodos más comúnmente usados para obtener el DDL del objeto fallan.

Si usted intenta con la opción Script function as en Object Explorer para una función encriptada, usted obtendrá el siguiente mensaje cuando esté conectado como un sysadmin:

El procedimiento almacenado sp_helptext, el cual muestra la definición de un rol definido por el usuario, un procedimiento almacenado, una función, un desencadenador, una columna computada, una restricción CHECK, una vista, o un objeto de sistema como un procedimiento almacenado del sistema, falla cuando se trata de objetos encriptados:

exec
sp_helptext [ufnGetAccountingEndDateEnc]

Los resultados en:

The text for object ‘ufnGetAccountingEndDateEnc’ is encrypted.

El texto y las columnas de texto en la table de Sistema syscomments son NULL para objetos encriptados:

SELECT
* FROM syscomments

Incluso verificando el cache no ayuda, dado que en lugar del DDL in la columna te texto, NULL es mostrado para los objetos encriptados:

Es por eso que es recomendado guardar una copia del SQL original para los objetos encriptados. Pero, ¿qué hacer cuando usted no ha hecho estas copias o sólo heredó una base de datos con objetos encriptados?

Use una conexión DAC y consulte las tablas del sistema

Use un miembro del rol sysadmin de SQL Server para conectarse usando DAC. Escriba una consulta que lee tablas del sistema y obtenga el DDL del objeto encriptado desde la columna imageval en sys.sysobjvalues.

La mayor desventaja de esta solución es su complejidad

Una solución más simple es usar ApexSQL Script, una herramienta de migración de bases de datos que codifica objetos de bases de datos incluyendo a aquellos encriptados y datos en un solo o múltiples scripts SQL de despliegue, soluciones .NET o instaladores ejecutables.

1. Inicie ApexSQL Script
2. Cree un nuevo proyecto

3. En el diálogo Project, selecciones el SQL Server y provea credenciales, si usted ha seleccionado autenticación SQL. Note que usted debe ingresar usando un rol sysadmin de SQL Server. De otra manera, usted obtendrá:

--Function [dbo].[ufnGetContactInformationEnc] is encrypted.
It could not be scripted

4. Haga clic en el botón Connect

5. Seleccione una base de datos desde la lista:

   

6. Haga clic en el botón Open

7. Los objetos encriptados son mostrados en la pestaña Structure de la Cuadrícula Principal como todos los otros objetos no encriptados:

   

8. Haga clic derecho en el procedimiento almacenado encriptado y seleccione View DDL for this object

   El script CREATE es mostrado:

   

9. Cierre la ventana Internal viewer
10. Seleccione los cuadros de verificación a la izquierda de los objetos que desea codificar

11. En la pestaña Home, haga clic en el botón Script para crear el script DDL para los objetos seleccionados:

    

12. En la pestaña Scripting mode del Script wizard, seleccione Structure para Scripting mode y elija TSQL para Output type:

    

13. En la pestaña Dependencies, deje Include dependent database objects des-seleccionado si usted no quiere codificar los objetos que dependen de los objetos seleccionados para codificar en la Cuadrícula Principal

14. En la pestaña Output file options, debajo de la pestaña General, seleccione la opción Create and write to file y especifique la ruta del archivo, si usted no desea que el archivo sea grabado en la ubicación por defecto:

    

15. En la pestaña Output file, seleccione la opción Script each object into an individual file desde el menú desplegable Granularity:

    

16. Haga clic en el botón Create

    Después de que los scripts son creados, usted obtendrá el siguiente mensaje

    

    Y el script generado por la función encriptada es:

    /*=============================================================
    SCRIPT HEADER
    
    VERSION:   1.01.0001
    DATE:      02-28-2015 05:03:16
    SERVER:    CRAZY
    
    DATABASE: AdventureWorks2014
      Function:  ufnGetAccountingEndDateEnc
    =============================================================*/
    
    SET ARITHABORT ON;
    SET NUMERIC_ROUNDABORT OFF;
    SET CONCAT_NULL_YIELDS_NULL ON;
    SET ANSI_WARNINGS ON;
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    GO
    
    -- BEGINNING TRANSACTION STRUCTURE
    PRINT 'Beginning transaction STRUCTURE';
    BEGIN TRANSACTION _STRUCTURE_;
    GO
    -- Create Function [dbo].[ufnGetAccountingEndDateEnc]
    PRINT 'Create Function [dbo].[ufnGetAccountingEndDateEnc]';
    GO
    SET ANSI_NULLS ON;
    SET QUOTED_IDENTIFIER ON;
    GO
    
    IF @@ERROR <> 0
    OR @@TRANCOUNT = 0
        BEGIN
            IF @@TRANCOUNT > 0
                BEGIN
                    ROLLBACK
                END;
            SET NOEXEC ON;
        END;
    GO
    CREATE FUNCTION dbo.ufnGetAccountingEndDateEnc(
                                                  )
    RETURNS datetime
        WITH ENCRYPTION
    AS
    BEGIN
        RETURN DATEADD( millisecond, -2, CONVERT( datetime, '20040701', 112
                                                )
                      );
    END;
    
    GO
    
    IF @@ERROR <> 0
    OR @@TRANCOUNT = 0
        BEGIN
            IF @@TRANCOUNT > 0
                BEGIN
                    ROLLBACK
                END;
            SET NOEXEC ON;
        END;
    GO
    
    -- COMMITTING TRANSACTION STRUCTURE
    PRINT 'Committing transaction STRUCTURE';
    IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRANSACTION _STRUCTURE_
        END;
    GO
    
    SET NOEXEC OFF;
    GO 
    

Si usted tiene procedimientos almacenados, funciones y vistas encriptados, y no tiene archivos de script para ellos, usted no tiene que usar DAC y consultas complejas para tablas de Sistema. Use ApexSQL Script para codificar objetos encriptados sin problemas como cualquier otro objeto no encriptado.

Traductor: Daniel Calbimonte