1

I want to execute a query in our client's server using remove access. If I execute a query something like this Update abc set col1=12 where id = 2 they will understand what we are doing. So I want some method for encryption and decryption like this:

In our server I encrypt a query like this:

encrypt(Update abc set col1=12 where id = 2)

So I get output like:

0x0100CF465B7B12625EF019E157120D58DD46569AC7BF4118455D12625EF019E157120D58DD46569AC7BF4118455D

And I execute a this encrypted query on client's machine like this:

decrypt(0x0100CF465B7B12625EF019E157120D58DD46569AC7BF4118455D12625EF019E157120D58DD46569AC7BF4118455D)

So our client can't understand what we have executed.

Improve this question
7
  • Interesting idea, but what's the question? Commented Jul 4, 2012 at 8:37
  • 3
    What a bizarre requirement, what level of access does your client have to the database, what level of access do you have? Commented Jul 4, 2012 at 8:38
  • 4
    If you are running remotely, how will they know what you are doing, if they are running and watching with SQL Profiler, this won't obfuscate anything. It would just make you look shady. Commented Jul 4, 2012 at 8:42
  • 1
    What's to stop them using the decrypt function? Commented Jul 4, 2012 at 9:01
  • 1
    @ypercube Of course it is legal. They are technical persons. If they see our queries, they can capture it and may execute it again themselves. So why should we show them our queries? Commented Jul 4, 2012 at 10:04

3 Answers 3

Reset to default
1

you can create a simple decrypt function like this..

create proc exec_decrypt(@sql_str varbinary(8000))
as
begin
declare @qry varchar(8000);
select @qry=cast(@sql_str as varchar(8000));
exec(@qry);
end

which accepts a varbinary string and converts to varchar and then execute it..

You can generate the encrypted query by using the satament below

select CAST('UPDATE users set name =''alex''' as varbinary(8000))

then execute the proc exec_decrypt in client place by passing the output of the above query as the parameter for the procedure..

Ex:  exec_decrypt 0x55504441544520757365727320736574206E616D65203D27616C657827

Hope this will work for you.. Please note that client should not have any permission on the proc exec_decrypt

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

"Please note that client should not have any permission on the proc exec_decrypt". Not to mention StackOverflow ;-)
@joe G Joseph The procedure you have shown is fine. But can't we increase the length of varbinary?
You can use varbinary(MAX) if you need. varbinary(8000) is more than enough for normal queries
use varbinary(MAX) instead of just varbinary, Because if we dont specify the length, it will take only 30 characters. I will change my original script now..
This is obfuscation not encryption. It is quite a well known technique used in SQL injection attacks. e.g. see here. Maybe might work if the client is clueless however.
|
0

The only way I have found to ensure that no one can view your queries is to put them in a function or procedure using 'with encryption'. Run the below code to see for yourself. Dynamic SQL suggested in the earlier answer doesn't fully solve your problem.

create procedure dbo.dummy_drop_me with encryption as select t.* from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text (r.sql_handle) t where r.session_id = @@spid
go
exec dbo.dummy_drop_me
go
select t.* from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text (r.sql_handle) t where r.session_id = @@spid
go
exec ('select t.* from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text (r.sql_handle) t where r.session_id = @@spid')

The only way I have found to keep someone from capturing your functions and procedures is to create them in their own database on a server where no one has access. Then, attach a copy of the database to the servers where you need them.

Improve this answer

Comments

0

In my opinion, With Encryption is generally counter-productive as there are a number of Sql Server decryption tools. ie dbForge's DB Decryptor. I just used this to decrypt a database's T-SQL, so I could export DACPAC's and BACPAC's for use in Azure DevOps automated deployments. With Encryption causes DACPAC and BACPAC exports to fail. This means, if you implement With Encryption you cannot use DACPAC to create update scripts for DB Patches or direct updates, you cannot use Visual Studio Sql Server Tools projects, which makes DB development harder. You have not secured anything, just made it harder for Dev's and Op's to do their jobs.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.