Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions
Problem

You work in a shop that puts business or application logic in the SQL Server using stored procedures, views and functions to return values to the calling applications or perform tasks. This is not unusual in companies that use the SQL Server layer to perform business tasks, such as finance operations, or incorporate application functionality into the programmability layer. You wish to preserve secrecy on some procedures, views or functions in order to maintain security.

Solution

SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery. The contents are not able to be scripted using conventional means in SQL Server Management Studio; nor do the definitions appear in the definition column of sys.sql_modules. This allows the cautious DBA to keep stored procedures and functions securely in source control and protecting the intellectual property contained therein. This tip will focus on encrypting and decrypting a user-defined function.

Encrypting a UDF

To encrypt a user-defined function, simply add WITH ENCRYPTION to the CREATE FUNCTION statement (after the RETURNS element). Throughout this tip, I will be building an encrypted UDF (and decrypting it) to demonstrate the principle.

First, create the UDF. Here’s mine – it’s a simple module that accepts an input string and returns the encrypted varbinary hash value. There are two vital pieces of information here that MUST NOT be given away to the user of the function – the encryption standard, and the salt.

CREATE FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000)
AS BEGIN
    DECLARE @salt VARCHAR(32)    
 DECLARE @outputHash VARBINARY(8000)
 SET @salt = '9CE08BE9AB824EEF8ABDF4EBCC8ADB19'
 SET @outputHash = HASHBYTES('SHA2_256', (@inputString + @salt))
RETURN @outputHash
END
GO

In this example the salt is fixed and an attacker, given the encryption standard (SHA-256) and the salt, could be able to decrypt the hash into plaintext. We can view the definition of a function by finding it in SQL Server Management Studio, right-clicking and scripting out the function:

 

The attacker can also use sp_helptext, or query sys.sql_modules if he/she knows the function name:

SELECT definition
FROM sys.sql_modules 
WHERE definition LIKE ('%getHash%')

There is some protection built in; by using role-based security or sensibly allowing the least required privileges to users, the attack surface can be lessened as the VIEW DEFINITION permission is required (or ownership of the function) in SQL Server 2005 upwards. Note in earlier versions, this permission is not required. The user may also have the permission granted implicitly by holding other permissions on the object. (See ‘Metadata Visibility Configuration’ at http://msdn.microsoft.com/en-GB/library/ms187113.aspx for more detail).

We can amend the function definition like so:

ALTER FUNCTION dbo.getHash ( @inputString VARCHAR(20) )
RETURNS VARBINARY(8000) WITH ENCRYPTION 
-- rest of function here

Note that WITH ENCRYPTION occurs after RETURNS, not before. With stored procedures, the WITH ENCRYPTION option occurs immediately after the CREATE PROCEDURE x ( @somevar) statement.

With our encrypted function we can attempt to script it out in SQL Server Management Studio again, or look at sys.sql_modules. Here’s what we get:

 


Querying sys.sql_modules definition column for this function returns NULL. Executing sp_helptext returns the error:

The text for object 'dbo.getHash' is encrypted.

Note the UDF is exactly as effective as it was before – we can still call it and return a value:

 


I’m using an undocumented stored procedure here called fn_varbintohex to convert the VARBINARY output from my function into a hexadecimal format, for portability between applications and clarity – it’s not directly relevant to this example. Normally the VARBINARY output of HASHBYTES is passed directly to the calling application.

 

Decrypting a Function

Firstly, open a Dedicated Administrator Connection (DAC) to SQL Server. Using SQL Server Management Studio, this is easily done by prefixing admin: to the connection string upon connection of a query window. Note that the DAC can only be used if you are logged onto the server and using a client on that server, and if you hold the sysadmin role. You can also get to it by using SQLCMD with the -A option. Note: DAC won’t work unless you’re using TCP/IP; you’ll get this rather cryptic error (in both SQLCMD and SSMS):

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Client unable to establish 
connection because an error was encountered during handshakes before login.  Common 
causes include client attempting to connect to an unsupported version of SQL Server, 
server too busy to accept new connections or a resource limitation (memory or 
maximum allowed connections) on the server.

If you can’t access the server directly for whatever reason, you can enable remote administrative connections (a remote DAC) as follows. You’ll still need to use TCP/IP:

exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE

The procedure for decryption comes in three steps. First, get the encrypted value of the procedure definition from sys.sysobjvalues (via the DAC connection). Secondly, get the encrypted value of a ‘blank’ procedure, where the definition is filled in by ‘-‘. Thirdly, get the plaintext blank procedure statement(unencrypted). Now XOR them together (XOR is the simplest of decryption procedures and forms the basis of many algorithms including MD5) as shown below to retrieve the output of the procedure. You’ll find my take on this algorithm in the code example below:

-- Connect using the DAC then execute the below

SET NOCOUNT ON
GO

ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS
BEGIN
 PRINT 'This text is going to be decrypted'
END 
GO

DECLARE @encrypted NVARCHAR(MAX)
SET @encrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )
DECLARE @encryptedLength INT
SET @encryptedLength = DATALENGTH(@encrypted) / 2

DECLARE @procedureHeader NVARCHAR(MAX)
SET @procedureHeader = N'ALTER PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))
EXEC sp_executesql @procedureHeader
DECLARE @blankEncrypted NVARCHAR(MAX)
SET @blankEncrypted = ( 
 SELECT imageval 
 FROM sys.sysobjvalues
 WHERE OBJECT_NAME(objid) = 'TestDecryption' )

SET @procedureHeader = N'CREATE PROCEDURE dbo.TestDecryption WITH ENCRYPTION AS '
SET @procedureHeader = @procedureHeader + REPLICATE(N'-',(@encryptedLength - LEN(@procedureHeader)))

DECLARE @cnt SMALLINT
DECLARE @decryptedChar NCHAR(1)
DECLARE @decryptedMessage NVARCHAR(MAX)
SET @decryptedMessage = ''
SET @cnt = 1
WHILE @cnt <> @encryptedLength
BEGIN
  SET @decryptedChar = 
      NCHAR(
        UNICODE(SUBSTRING(
           @encrypted, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @procedureHeader, @cnt, 1)) ^
        UNICODE(SUBSTRING(
           @blankEncrypted, @cnt, 1))
     )
  SET @decryptedMessage = @decryptedMessage + @decryptedChar
 SET @cnt = @cnt + 1
END
SELECT @decryptedMessage

If you still cannot access via DAC or prefer a code-based approach, then you can use one of a number of freeware third-party .NET-based decryption software packages to do this for you. The blog Sqljunkieshare also purports to have a method of doing this (code untested) that looks viable – you can find the link here:

http://sqljunkieshare.com/2012/03/07/decrypting-encrypted-stored-procedures-views-functions-in-sql-server-20052008-r2/ (use at your own risk)

Advertisements

Excel Lookup against the sql table

My working excel vba function:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)
On Error GoTo Error:
SQLookup = “Error”
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

Dim connString As String
connString = “driver={SQL Server};” & _
“server=server_name;Trusted Connect=Yes;database=database_name”
Conn.ConnectionString = connString
Conn.Open

‘ If Conn.State = adStateOpen Then
‘ MsgBox “Welcome to Pubs!”
‘ Else
‘ MsgBox “Sorry. No Pubs today.”
‘ End If

Set RecSet = New Recordset

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” = ‘” & LkUpValue & “‘”, connString

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit Function
Error: SQLookup = Err.Number & “|” & Err.Source & “|” & Err.Description
End Function

My working calling the excel vba function:

=SQLookup(B2 (ExcelLookupFieldName), “LookupSqlColumnName”, “dbo.TableName”, “ReturnSqlColumnName”,$E$3)

Note: $E$3 is the connection string is not being used from the cell E3, it is embeded in the funciton.

Lookup against External Database

At work I had a requirement to find fields in an SQL database corresponding to data in Excel cells.  I looked for an Excel function to do this on the internet, but could not find one. I found this surprising as this must surely be a very common requirement.

While it is possible to access an SQL database data in Excel using the built in options for getting External Data, it is an overkill for a simple lookup to access a few pieces of data.

External data in Excel

To get around this, I wrote a function to perform LOOKUP against an external SQL database. It works in a similar manner to VLOOKUP, the parameters to be passed to it are the lookup value, the field name holding the lookup value, the table name containing the data and field to be looked up.

The connection string to access the database is also passed into the function. By changing the connection string, this function can be customised to lookup data against other databases (such as Microsoft Access).

For this function to work, a reference to “Microsoft ActiveX Data Objects x.x Library ” must be made in VBA (under Tools Menu/References).

Macro References

The code for the function is as follows:

Function SQLookup(LkUpValue As String, LkUpFld As String, TblName As String, FldName As String, ConnStr As String)

Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection

SQLookup = “Error”

On Error GoTo Exit_Func

SQLookup = “No Conn”
Conn.Open ConnStr

Set RecSet = New Recordset

SQLookup = “Error”

RecSet.Open “SELECT ” & FldName & ” FROM ” & TblName & ” where ” & LkUpFld & ” like ‘” & LkUpValue & “‘”, ConnStr, , , adCmdText

SQLookup = RecSet.Fields(FldName)

RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing

Exit_Func:

End Function

As an illustration of this function, let us assume that we have an SQL database named FRUITS containing a table tblFRUIT with the following entries:

Fruit Table

An example of the connection string to this database would be as follows:

“DRIVER=SQL Server;SERVER=SQLSvr;UID=ExcelUser;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=D-SQL01;DATABASE=Fruits”

A good reference for conneciton strings is this site.

To lookup for the Quantity of Grapes from this database, the function would be:
=SQLOOKUP(“Grapes”,”Fruit”,”tblFRUIT”,”Quantity”,A1). The cell A1 contains the connection string.

This function looks for the field “Grapes” under the field “Fruit” from the database table and returns the corresponding value under the field “Quantity”, which is 31.

Like all Excel functions, the various parameters can come from other cells, see example below.

SQLookup Example

Note that this function only returns the first lookup value returned by the query in the code (same as VLOOKUP or HLOOKUP).

A word of caution though: This function is most suited to lookup for a few pieces of data from Excel and is not advisable to use this extensively on a sheet to perform many lookups. For querying large amounts of data from an External database, use Microsoft Query in Excel or the tools provided to access data from external databases.

SQL Error: Agent job error DTExec: The package execution returned DTSER_FAILURE (1)

Scenario

I was running SSIS packages which is available only in 32 bit on 64 bit server and I was receiving the error

Message
Executed as user: SHELTER\SSIS_Admin. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 15:33:53 Error: 2015-12-03 19:29:56.44 Code: 0x00000001 Source: create file Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 15:33:53 Finished: 19:29:56 Elapsed: 14162.4 seconds. The package execution failed. The step failed.

Solution

a

In order to access the network shared folder, I had to add the sql_proxy user which was domain user as a user in that server (the server that has shared the folder)