JanLeeYu February 2016

How to encrypt a function in SQL Server

How can I encrypt a function in SQL Server so that it cannot be edited by another users? Thanks in advance.

Answers


Om Chabra February 2016

To do what you are doing you can do any of the following: 1. set useSSL equal to true 2. set a password on your database


Dan Field February 2016

Use the ENCRYPTION option:

CREATE FUNCTION testFunction ()
RETURNS int
WITH ENCRYPTION
AS
BEGIN
RETURN 1
END

This means the definition won't be stored anywhere that is easily readable, for example:

SELECT m.definition
FROM sys.sql_modules m
JOIN sys.objects o 
on M.object_id = o.object_id AND o.type = 'FN' AND o.name = 'testFunction'

will return null, and if a user (even sysadmin) tries to use SSMS to modify the function they'll get a MessageBox stating that the text is encrypted and can't be retrieved. However, per MSDN,

The definition of functions created by using the ENCRYPTION option cannot be viewed by using sys.sql_modules; however, other information about the encrypted functions is displayed.

and

Applies to: SQL Server 2008 through SQL Server 2016.

Indicates that the Database Engine will convert the original text of the CREATE FUNCTION statement to an obfuscated format. The output of the obfuscation is not directly visible in any catalog views. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime. For more information about accessing system metadata, see Metadata Visibility Configuration.

Using this option prevents the function from being published as part of SQL Server replication. This option cannot be specified for CLR functions.

You should still ensure that proper privileges are maintained to deal with such scenarios. You can read a bit more in depth about


Anti-weakpasswords February 2016

You can't prevent other users from editing it, if they have the right permissions.

You can't prevent them from having the right permissions if they have physical access to the SQL Server.

  • They can simply drop it and then create whatever they want with the same name; thus having their own code take over for your original code

  • They can decrypt it in a variety of ways; see the dba.stackexchange question How to view an encrypted view or stored procedure

    • Red Gate SQL Prompt does it on its own

    • Replace the contents with the same name, then XOR the results to see the original

    • Get even trickier with looking into RAM.

Post Status

Asked in February 2016
Viewed 3,644 times
Voted 10
Answered 3 times

Search




Leave an answer