CODEWITHSUNDEEP
sql-serverencryption
Michael
Michael

Reputation: 3150

Is there a way to insert an encrypted script into a SQL Server database?

My company considers database scripts we write part of our intellectual property.

With new releases, we deliver a 2-part setup for our users:

I know I can encrypt a stored procedure on a database once the script is present, but is there any way to insert it in an encrypted form? I don't want plain-text to be able to be intercepted across the "wire" (or more accurately, between the SQL script executable and the server).

I'm not really tied to the tool we're using - I just want to know if it's possible without having to resort to something hokey.

Upvotes: 3

Views: 3335

Answers (3)

Nath_Math
Nath_Math

Reputation: 259

Try using Enctyptpassphrase and DecryptPassPharse functions.

Use ENCRYPTBYPASSPHRASE to encrypt all your DDL statements and then DECRYPTBYPASSPHRASE on the server to decrypt and execute.

declare @encrypt varbinary(200) 
select @encrypt = EncryptByPassPhrase('key', 'your script goes here' )
select @encrypt 

select convert(varchar(100),DecryptByPassPhrase('key', @encrypt ))

Create a procedure that would look like this 

CREATE PROCEDURE DBO.ExecuteDDL
(
@script varbinary(max) 
)
  AS
BEGIN

DECLARE @SQL nvarchar(max)

SET @SQL = (select convert(varchar(max),DecryptByPassPhrase('key', @script )))

EXECUTE sp_executesql @SQL        
END

Once this is in place you can publish scripts to your server like this

Upvotes: 3

Alejandro
Alejandro

Reputation: 7819

There's little you can do to reliably prevent the code in the database to be read by anyone who really wants. The WITH ENCRYPTION parameter is really just an obfuscation and many simple scripts are able to get it again in plain text, and when the database is being upgraded, ultimately the profiler will always be able to catch the ALTER PROCEDURE statement with the full text. Network tracers can be evaded by using an encrypted connection to the server.

The real problem comes from the fact that the database is installed in a server that your users own and fully control (correct me if that's not the case). No matter what you do, they'll have full access to the whole database, it's schema, and internal programming inside sprocs/functions.

The closest I can think of to prevent that is to switch to CLR stored procedures, which are installed by copying a DLL to the server and registering within SQL Server. They pose other problems, as they are totally different to program and may not be the best tool for what you use a sproc normally. Also, since the are made of standard .NET code, they can also be trivially decompiled.

The only way I can think of fully protecting the database structure and code would be to put it in a server of yours, that you expose to your customers though, say, a webservice or a handful of sprocs as wrappers, so no one can peek inside.

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56755

This isn't plain-text and last I checked, it still works:

declare @_ as varbinary(max)
set @_ =0x0D000A005000520049004E0054002000270054006800690073002000620069006E00610072007900200073007400720069006E0067002000770069006C006C002000650078006500630075007400650020002200530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A00450043005400530022003A0027000D000A00530045004C0045004300540020002A002000460052004F004D0020005300590053002E004F0042004A0045004300540053000D000A00
exec (@_)

Technically, it's not encryption, but it's not plaintext either and it can server as the basis for some mild encryption pretty easily.

Upvotes: 0

Related Questions