SQL Server - How to retrieve source code from an encrypted object (WITH ENCRYPTION)

Views: 4.816 views

In this article

Reading Time: 8 minutes

Hello people,
All right with you ?

In this post I would like to demonstrate to you How to retrieve source code from an encrypted object (WITH ENCRYPTION) in SQL Server. How many times have I seen programmers encrypting objects in SQL Server in the false hope that this code will really be protected from changes and previews by other users.

Introduction

For those who have been working in IT for a while, I think they are unanimous that there is no really effective source code protection. If you program in .NET, you have the JetBrains dotPeck. If you encrypt your objects in Oracle Database, there is the website Unwrap It! that breaks this ONLINE encryption. In PHP, I've used Zend Guard to encrypt source files and there are online tools that also break encryption.

In SQL Server this is no different either. There are even tools (Redgate SQL Prompt, ApexSQL Complete and many others) that do this for you automatically, showing the source code of encrypted objects so naturally and transparently that you don't even realize that the object was encrypted.

Although it is possible to break object encryption in SQL Server, it does not directly mean a security breach. In order to break this encryption, you will need to use a DAC connection, which I already explained how to enable and use in the post. Enabling and using dedicated remote administrator connection (DAC) in SQL Server.

The ACD connection is a dedicated connection for sysadmins (which greatly limits users who can perform this procedure) who need to connect to instances that are experiencing issues that prevent new connections, such as limiting concurrent users of the instance reached.

As the form of connection is different (including using a dedicated port), this “special” connection is always available, even in this situation. It is worth mentioning that this connection only allows 1 user connected at a time, so if someone is already connected, someone else will not be able to use it.

All this requirement of using the DAC connection is simply so that you can read data from the sys.sysobjvalues table. Even if you are sysadmin, you will only be able to access the data in this table using the DAC connection.

Example WITHOUT using the DAC connection

Msg 208, Level 16, State 1, Line 16
Invalid object name 'sys.sysobjvalues'.

Example Using DAC Connection

SQL Server uses the RC4 ™ stream cipher encryption algorithm to encrypt objects and stores this data in the imageval column of the sys.sysobjvalues table. In this post, I will demonstrate an algorithm that allows you to retrieve this information and return the original source code of the encrypted object.

How to list encrypted objects in the database

To identify which objects in your database are encrypted, I separated 4 queries to achieve this goal:

Using the sys.sql_modules view:

SELECT 
    B.[name],
    B.[type_desc]
FROM 
    sys.sql_modules A
    JOIN sys.objects B ON A.[object_id] = B.[object_id]
WHERE 
    A.[definition] IS NULL

SELECT

B.[name],

B.[type_desc]

FROM

sys.sql_modules A

JOIN sys.objects B ON A.[object_id] = B.[object_id]

WHERE

A.[definition] IS NULL

Using the OBJECTPROPERTY function:

SELECT 
    [name],
    [type_desc]
FROM 
    sys.objects
WHERE 
    OBJECTPROPERTY([object_id], 'IsEncrypted') = 1

SELECT

[name],

[type_desc]

FROM

sys.objects

WHERE

OBJECTPROPERTY([object_id], 'IsEncrypted') = 1

Using the sys.syscomments view:

SELECT 
    A.[name],
    A.[type_desc]
FROM 
    sys.objects A
    JOIN sys.syscomments B ON A.[object_id] = B.id
WHERE
    B.[encrypted] = 1

SELECT

A.[name],

A.[type_desc]

FROM

sys.objects A

JOIN sys.syscomments B ON A.[object_id] = B.id

WHERE

B.[encrypted] = 1

Using the INFORMATION_SCHEMA database:

SELECT
    ROUTINE_NAME,
    ROUTINE_TYPE
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_DEFINITION IS NULL

SELECT

ROUTINE_NAME,

ROUTINE_TYPE

FROM

INFORMATION_SCHEMA.ROUTINES

WHERE

ROUTINE_DEFINITION IS NULL

How to create an encrypted SP

For this post, I will create a very simple and encrypted SP, and I will demonstrate how to get its source code.

USE [dirceuresende]
GO

CREATE PROCEDURE dbo.stpTeste_Decrypt
WITH ENCRYPTION
AS 
BEGIN
	
    ----------------------------- Comentário da SP -----------------------------
    PRINT 'dirceuresende'
	
    SELECT 'dirceuresende'

END

USE [dirceuresende]

CREATE PROCEDURE dbo.stpTeste_Decrypt

WITH ENCRYPTION

BEGIN

----------------------------- Comentário da SP -----------------------------

PRINT 'dirceuresende'

SELECT 'dirceuresende'

END

As you can see below, you cannot view the source code of an encrypted object using the sys.sql_modules view, even if you are a member of role sysadmin.

Not even using the sp_helptext system procedure is it possible to obtain the source code for this object, returning the alert message “The text for object 'dbo.stpTeste_Decrypt' is encrypted.”, As shown below:

Breaking Object Encryption by SSMS

To break the encryption of objects by SQL Server Management Studio (SSMS), start a DAC connection on the desired instance. To do so, just add the prefix “ADMIN:” before the name of your instance, thus: “ADMIN: server \ instance”, as shown in the image below:

Now use the code below and you will now be able to view the source code of your encrypted object.

Source code

USE [dirceuresende]
GO

SET NOCOUNT ON

-- Aqui você tem que alterar para o objeto que você quer descriptografar
DECLARE
    @ObjectOwnerOrSchema NVARCHAR(128) = 'dbo', 
    @ObjectName NVARCHAR(128) = 'stpTeste_Decrypt'


DECLARE 
    @i INT,
    @ObjectDataLength INT,
    @ContentOfEncryptedObject NVARCHAR(MAX),
    @ContentOfDecryptedObject NVARCHAR(MAX),
    @ContentOfFakeObject NVARCHAR(MAX),
    @ContentOfFakeEncryptedObject NVARCHAR(MAX),
    @ObjectType NVARCHAR(128),
    @ObjectID INT
 

SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
 
IF @ObjectID IS NULL
BEGIN
    RAISERROR('The object name or schema provided does not exist in the database', 16, 1)
    RETURN
END
 

IF NOT EXISTS(SELECT TOP 1 * FROM sys.syscomments WHERE id = @ObjectID AND encrypted = 1)
BEGIN
    RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)
    RETURN
END
 

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL
    SET @ObjectType = 'PROCEDURE'
ELSE
    IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL
        SET @ObjectType = 'TRIGGER'
    ELSE
        IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL
            SET @ObjectType = 'VIEW'
        ELSE
            SET @ObjectType = 'FUNCTION'
 

SELECT TOP 1 @ContentOfEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE [objid] = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
AND valclass = 1 and subobjid = 1
 
SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2
SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'
 
WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength
BEGIN
    IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)
    ELSE
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))
END
 

SET XACT_ABORT OFF
BEGIN TRAN
 
EXEC(@ContentOfFakeObject)
 
IF @@ERROR <> 0
    ROLLBACK TRAN
 
SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE [objid] = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
AND valclass = 1 and subobjid = 1
 
IF @@TRANCOUNT > 0
    ROLLBACK TRAN
 
SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'
 
WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength
BEGIN
    IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)
    ELSE
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))
END
 
SET @i = 1
SET @ContentOfDecryptedObject = N''
 
WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength
BEGIN
    IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength
        SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000)
    ELSE
        SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2))
END
 
WHILE (@i <= @ObjectDataLength)
BEGIN
    SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1,
        NCHAR(
            UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^
            (
                UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^
                UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))
            )))
 
    SET @i = @i + 1

END
 
SET @i = 0
 
WHILE DATALENGTH(@ContentOfDecryptedObject)/2 > (@i + 1)*2000
BEGIN
    PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))
    SET @i = @i + 1
END
 
PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

USE [dirceuresende]

SET NOCOUNT ON

-- Aqui você tem que alterar para o objeto que você quer descriptografar

DECLARE

@ObjectOwnerOrSchema NVARCHAR(128) = 'dbo',

@ObjectName NVARCHAR(128) = 'stpTeste_Decrypt'

DECLARE

@i INT,

@ObjectDataLength INT,

@ContentOfEncryptedObject NVARCHAR(MAX),

@ContentOfDecryptedObject NVARCHAR(MAX),

@ContentOfFakeObject NVARCHAR(MAX),

@ContentOfFakeEncryptedObject NVARCHAR(MAX),

@ObjectType NVARCHAR(128),

@ObjectID INT

SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

IF @ObjectID IS NULL

BEGIN

RAISERROR('The object name or schema provided does not exist in the database', 16, 1)

RETURN

END

IF NOT EXISTS(SELECT TOP 1 * FROM sys.syscomments WHERE id = @ObjectID AND encrypted = 1)

BEGIN

RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)

RETURN

END

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL

SET @ObjectType = 'PROCEDURE'

ELSE

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL

SET @ObjectType = 'TRIGGER'

ELSE

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL

SET @ObjectType = 'VIEW'

ELSE

SET @ObjectType = 'FUNCTION'

SELECT TOP 1 @ContentOfEncryptedObject = imageval

FROM sys.sysobjvalues

WHERE [objid] = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

AND valclass = 1 and subobjid = 1

SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2

SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength

BEGIN

IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)

ELSE

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))

END

SET XACT_ABORT OFF

BEGIN TRAN

EXEC(@ContentOfFakeObject)

IF @@ERROR <> 0

ROLLBACK TRAN

SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval

FROM sys.sysobjvalues

WHERE [objid] = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

AND valclass = 1 and subobjid = 1

IF @@TRANCOUNT > 0

ROLLBACK TRAN

SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength

BEGIN

IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)

ELSE

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))

END

SET @i = 1

SET @ContentOfDecryptedObject = N''

WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength

BEGIN

IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength

SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000)

ELSE

SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2))

END

WHILE (@i <= @ObjectDataLength)

BEGIN

SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1,

NCHAR(

UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^

(

UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^

UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))

)))

SET @i = @i + 1

END

SET @i = 0

WHILE DATALENGTH(@ContentOfDecryptedObject)/2 > (@i + 1)*2000

BEGIN

PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))

SET @i = @i + 1

END

PRINT(SUBSTRING(@ContentOfDecryptedObject, 1 + 2000*@i, 2000*(@i + 1)))

Result:

Breaking object encryption by CLR (C #)

Now that I've explained how to break objects, I'll show you a more practical and functional solution in everyday life that you can allow even non-sysadmin users to see the source of encrypted objects without having to open it. a new DAC connection, even from another server or instance.

For this, I will use the CLR to make the connection via DAC, using a sysadmin user. This way, even if the person is not sysadmin, just if they have permission in this role, they can view the source of unencrypted objects. To break the code, I will use the same code shown above, just running it through CLR (C #) on a DAC connection, transparent to the user using the function.

Source code

using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read
    )]
    public static SqlString fncDescriptografa_Objeto(SqlString Ds_Servidor, SqlString Ds_Database, SqlString Ds_Schema, SqlString Ds_Objeto)
    {


        var conexaoLocal => "data source=LOCALHOST;initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='usuario';password='senha'";
        var servidor = conexaoLocal.Replace("LOCALHOST", $"ADMIN:{Ds_Servidor.Value}");

        using (var con = new SqlConnection(servidor))
        {

            con.Open();

            using (var cmd = new SqlCommand($@"
USE [{Ds_Database.Value}];

DECLARE 
    @ObjectOwnerOrSchema NVARCHAR(128) = '{Ds_Schema}', 
    @ObjectName NVARCHAR(128) = '{Ds_Objeto}'

DECLARE 
    @i INT,
    @ObjectDataLength INT,
    @ContentOfEncryptedObject NVARCHAR(MAX),
    @ContentOfDecryptedObject NVARCHAR(MAX),
    @ContentOfFakeObject NVARCHAR(MAX),
    @ContentOfFakeEncryptedObject NVARCHAR(MAX),
    @ObjectType NVARCHAR(128),
    @ObjectID INT
 
SET NOCOUNT ON
 
SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
IF (@ObjectID IS NULL)
BEGIN
    RAISERROR('The object name or schema provided does not exist in the database', 16, 1)
    RETURN
END
 
IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1)
BEGIN
    RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)
    RETURN
END
 
IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL
    SET @ObjectType = 'PROCEDURE'
ELSE
    IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL
        SET @ObjectType = 'TRIGGER'
    ELSE
        IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL
            SET @ObjectType = 'VIEW'
        ELSE
            SET @ObjectType = 'FUNCTION'
 

SELECT TOP 1 @ContentOfEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
        AND valclass = 1 and subobjid = 1
 
SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2 
SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'
 
WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength
BEGIN
    IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)
    ELSE
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))
END
 
SET XACT_ABORT OFF
BEGIN TRAN
 
EXEC(@ContentOfFakeObject)
 
IF @@ERROR <> 0
    ROLLBACK TRAN
 
SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
AND valclass = 1 and subobjid = 1


IF @@TRANCOUNT > 0
    ROLLBACK TRAN


SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'


WHILE DATALENGTH(@ContentOfFakeObject) / 2 < @ObjectDataLength
BEGIN
    IF DATALENGTH(@ContentOfFakeObject) / 2 + 4000 < @ObjectDataLength
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)
    ELSE
        SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject) / 2))
END


SET @i = 1

SET @ContentOfDecryptedObject = N''


WHILE DATALENGTH(@ContentOfDecryptedObject) / 2 < @ObjectDataLength
BEGIN
    IF DATALENGTH(@ContentOfDecryptedObject) / 2 + 4000 < @ObjectDataLength
        SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000)
    ELSE
        SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject) / 2))
END


WHILE (@i <= @ObjectDataLength)
BEGIN

    SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1,
        NCHAR(
            UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^
            (
                UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))
            ))
    )

    SET @i = @i + 1

END


SELECT @ContentOfDecryptedObject", con) { CommandType = CommandType.Text })
            {

                var resultado = (cmd.ExecuteScalar() != null) ? (string) cmd.ExecuteScalar() : "";

                return resultado;

            }

        }

    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

using System.Data;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(

DataAccess = DataAccessKind.Read,

SystemDataAccess = SystemDataAccessKind.Read

)]

public static SqlString fncDescriptografa_Objeto(SqlString Ds_Servidor, SqlString Ds_Database, SqlString Ds_Schema, SqlString Ds_Objeto)

{

var conexaoLocal => "data source=LOCALHOST;initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='usuario';password='senha'";

var servidor = conexaoLocal.Replace("LOCALHOST", $"ADMIN:{Ds_Servidor.Value}");

using (var con = new SqlConnection(servidor))

{

con.Open();

using (var cmd = new SqlCommand($@"

USE [{Ds_Database.Value}];

DECLARE

@ObjectOwnerOrSchema NVARCHAR(128) = '{Ds_Schema}',

@ObjectName NVARCHAR(128) = '{Ds_Objeto}'

DECLARE

@i INT,

@ObjectDataLength INT,

@ContentOfEncryptedObject NVARCHAR(MAX),

@ContentOfDecryptedObject NVARCHAR(MAX),

@ContentOfFakeObject NVARCHAR(MAX),

@ContentOfFakeEncryptedObject NVARCHAR(MAX),

@ObjectType NVARCHAR(128),

@ObjectID INT

SET NOCOUNT ON

SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

IF (@ObjectID IS NULL)

BEGIN

RAISERROR('The object name or schema provided does not exist in the database', 16, 1)

RETURN

END

IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1)

BEGIN

RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)

RETURN

END

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL

SET @ObjectType = 'PROCEDURE'

ELSE

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL

SET @ObjectType = 'TRIGGER'

ELSE

IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL

SET @ObjectType = 'VIEW'

ELSE

SET @ObjectType = 'FUNCTION'

SELECT TOP 1 @ContentOfEncryptedObject = imageval

FROM sys.sysobjvalues

WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

AND valclass = 1 and subobjid = 1

SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2

SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength

BEGIN

IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)

ELSE

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2))

END

SET XACT_ABORT OFF

BEGIN TRAN

EXEC(@ContentOfFakeObject)

IF @@ERROR <> 0

ROLLBACK TRAN

SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval

FROM sys.sysobjvalues

WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

AND valclass = 1 and subobjid = 1

IF @@TRANCOUNT > 0

ROLLBACK TRAN

SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N'] WITH ENCRYPTION AS'

WHILE DATALENGTH(@ContentOfFakeObject) / 2 < @ObjectDataLength

BEGIN

IF DATALENGTH(@ContentOfFakeObject) / 2 + 4000 < @ObjectDataLength

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)

ELSE

SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject) / 2))

END

SET @i = 1

SET @ContentOfDecryptedObject = N''

WHILE DATALENGTH(@ContentOfDecryptedObject) / 2 < @ObjectDataLength

BEGIN

IF DATALENGTH(@ContentOfDecryptedObject) / 2 + 4000 < @ObjectDataLength

SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000)

ELSE

SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject) / 2))

END

WHILE (@i <= @ObjectDataLength)

BEGIN

SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1,

NCHAR(

UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^

(

UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^ UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))

))

)

SET @i = @i + 1

END

SELECT @ContentOfDecryptedObject", con) { CommandType = CommandType.Text })

{

var resultado = (cmd.ExecuteScalar() != null) ? (string) cmd.ExecuteScalar() : "";

return resultado;

}

After you create the role in your CLR project and publish the change, let's use it:

Remember that in this solution, the user who is using the function need not (and should not) be using a DAC connection.

References:
https://sqlperformance.com/2016/05/sql-performance/the-internals-of-with-encryption
https://www.codeproject.com/Articles/5068/RC-Encryption-Algorithm-C-Version
https://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/
https://pt.linkedin.com/pulse/uma-procedure-criptografada-e-agora-f%C3%A1bio-oliveira

That's it folks!
I hope you enjoyed this post and see you next time.

SQL Server how to retrieve view get decrypt stored procedure source code encrypted objects WITH ENCRYPTION

Marcos Guarnier said:

22 July 2019 to 09: 40

Good morning Dirceu, great material. Helped me a lot. Thanks for sharing.

- Dirceu Resende said:
  
  19 from 2019 from September to 00: 31
  
  You're welcome, Marcos.
  I'm happy to know that you liked
  
Rafael said:

2 January 2019 to 09: 40

Good Morning! Very good post, by the way as most. Unfortunately I enabled dac but I can not login, is giving the following error:
“Dedicated administrator connections via ssms are not supported, as this establishes several connections by design”

[email protected] said:

December 19 from 2018 to 08: 39

Good morning,

Great post, but it didn't work for me

- Dirceu Resende said:
  
  December 21 from 2018 to 07: 19
  
  Hi, Renato!
  Alright?
  
  What version of your SQL Server? Did you connect using the DAC connection?

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - How to retrieve the source code of an encrypted object (WITH ENCRYPTION)

Introduction

How to list encrypted objects in the database

How to create an encrypted SP

Breaking Object Encryption by SSMS

Source code

Breaking object encryption by CLR (C #)

Source code

SQL Server how to retrieve view get decrypt stored procedure source code encrypted objects WITH ENCRYPTION

You may also like ...

Comments

Leave a Comment Cancel reply

Introduction

How to list encrypted objects in the database

How to create an encrypted SP

Breaking Object Encryption by SSMS

Source code

Breaking object encryption by CLR (C #)

Source code

SQL Server how to retrieve view get decrypt stored procedure source code encrypted objects WITH ENCRYPTION

You may also like ...

SQL Server - The activated proc '[dbo]. [Sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue'

SQL Server - How to Run PowerShell and Prompt-DOS (MS-DOS) Scripts Using CLR (C #)

SQL Server - Power BI Gateway issue using external IP or hostname to access database

Comments

Leave a Comment Cancel reply