question

CarlosD-0727 avatar image
0 Votes"
CarlosD-0727 asked ErlandSommarskog commented

Encrypt stored proceudres with password.

Hi All,
Is there a way to encrypt stored procedure code with password ?. I am not talking about the keyword "WITH ENCRYPTION" in the stored procedure when creating it but actually applying a password to it so that I can also decrypt it with that password. Don't want to create a master key and certificate for it either. Is there a way ? This is for any SQL Server versions from 2012 to 2019.

Thanks.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is your use case for needing this? Typically, one keeps stored procedures in source control to avoid reverse engineering existing database objects.

0 Votes 0 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

No. Think of it. To be able to run the stored procedure, SQL Server needs to be able read the SQL code. So if there were such a feature, user would only be able to run the procedure if the user has the password.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @CarlosD-0727,

No. It is not possible. Refer to How to Encrypt a Stored Procedure in SQL Server.

Best regards,
Cathy


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarlosD-0727 avatar image
0 Votes"
CarlosD-0727 answered

Thank you all. I knew it but just wanted to verify. The reason for this is because we are managing other companies database servers and implementing our own stored procedures. When the company ends the contract, we need to remove these stored procedures and not have them use them anymore.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

For what you describe, I create a "license check" encrypted proc. That proc just returns 0 or 1 if the date is <= a date like 1/1/2021. Then each encrypted proc, calls this proc first before running and reports "License violation" after the date. If you need to extend the procs past that date, you just replace the encrypted proc and a new date.

There is not really a good way to stop someone from using code on their server.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarlosD-0727 avatar image
0 Votes"
CarlosD-0727 answered CarlosD-0727 commented

The point is that the SPs will be used as long as we are in contract with the customer but once the contract ends we will delete the SPs and other items we implement as part of DB management.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Not sure what your concern is - if you remove the stored procedures from the system then they no longer exist and cannot be used. If the problem is identifying your objects - create a schema and place all of your objects in that schema, then you can easily identify your objects.

0 Votes 0 ·

I guess Carlos's concern is that the customer may copy the procedures, and thus take benefit of them after their engagement has ended. This is obviously something that should be outlawed in the license agreement, but it can be difficult to legally enforce with a customer who is dead set on cheating - not the least if the customer company is a lot bigger than yours.

0 Votes 0 ·

Thanks Erland. That is exactly what it is.

0 Votes 0 ·
arunvijayakumar avatar image
0 Votes"
arunvijayakumar answered ErlandSommarskog commented

Another way of doing it would be locking the procedure, which is handled by DB Admin. In this way, Company ending their contract will not be able to download or even look at the stored proc's.

This also helps to reuse if it needs to be implemented in any other company.




--please don't forget to Accept as answer if the reply is helpful--

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry, but you completely missed what it is all about. Application locks has no relation to what Carlos is asking for. They are a great feature for several things, for instance like making sure that not two instance of the same procedure runs at the same time. But they have nothing to with permissions. And anyone can use them - it does not have to be a DB Admin.

Furthermore, in Carlos's case, he also concerned what DB admins or sysadmins can do, since these are people working for the customer.

0 Votes 0 ·
FarazTaha-2049 avatar image
0 Votes"
FarazTaha-2049 answered ErlandSommarskog commented

I have same problem, I want to do store procedure encryption with password, want to hide the logic behind the procedures...

  • With Encryption (can easily decrypted by db forge decryptor I tried my self).

  • Procedure Locking will not work as well if it will be on client premises.

Is there any way around for Procedure encryption with password or any other type of decryption.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you want to hide the logic in the procedure, you need to avoid that users get permission to read the procedure code. To do this, you cannot permit installation at a customer site, but you need to host the procedure yourself, for instance in Azure SQL DB.

As for why, see my Answer above.

0 Votes 0 ·

As told you that would not be solution, as I have to host on the demand of client

0 Votes 0 ·

Well, then you cannot not hide your logic.

But a good license agreement which controls what the customer can do and not do with the code is a good idea.

0 Votes 0 ·