I use keyword WITh ENCRYPTION to encrypt my procedures that other users can'
see them.
For example:
CREATE PROCEDURE test
WITH ENCRYPTION
AS
select 1
GO
If I click on this procedure in enterprise manager, I get message:
Error 20585:[SQL-DMO]/*****Encrypted object is not transferable,and script
can not be generated.*****/
Everything is fine so far.
But if I go to master database I can find the sintax of this procedure.
For example, if I use RedGate SQL bundle to compare 2 databases, I can see
the sintax of all procedures on some database even if they are encrypted.
Is there any way to prevent other users to see my code?
If I sell my program with my database to other company, every one who has
access to their database, can see my code and still it.
Any suggestion?
Thank you,
SimonThe WITH ENCRYPTION option only provides a trivial level of security. You ma
y
find other third-party tools that claim to do better but ultimately you
cannot stop an administrator using SQL Profiler to examine your code.
IMO you should not try to hide code in this way. Admins may have a
legitimate need to see what code is running. The best protection for your
intellectual property is a licence agreement, not phoney and obstructive
encryption. Just my opinion.
David Portas
SQL Server MVP
--|||Simon even with encryption there's plenty of tools on the web that people ca
n
use to decrypt it. All that I can say is implement physical security - ie.
Memory stick
and get your DB to sort out security issues. If you have an extra hour a day
for admin you can always use sourcesafe.
As for the code sitting in the database, your never safe. There's no IP
(Intellectual Property ) when you are working for a company, they pay you
thus they own everything you create during work hours.
You can write manual encryptions by putting your code in extended procs, and
atleast like this you remove the google junkies from your code. Just google
"sql decryption tools" .
With dlls it's a bit more difficult to see the code, although someone can
still just catch the code through profiler...
No that I think of it, if you don't have the DBA on your side your "￡$*￡$
"simon" wrote:
> I use keyword WITh ENCRYPTION to encrypt my procedures that other users ca
n'
> see them.
> For example:
> CREATE PROCEDURE test
> WITH ENCRYPTION
> AS
> select 1
> GO
>
> If I click on this procedure in enterprise manager, I get message:
> Error 20585:[SQL-DMO]/*****Encrypted object is not transferable,and script
> can not be generated.*****/
> Everything is fine so far.
> But if I go to master database I can find the sintax of this procedure.
> For example, if I use RedGate SQL bundle to compare 2 databases, I can see
> the sintax of all procedures on some database even if they are encrypted.
> Is there any way to prevent other users to see my code?
> If I sell my program with my database to other company, every one who has
> access to their database, can see my code and still it.
> Any suggestion?
> Thank you,
> Simon
>
>|||The text of encrypted procedures does not show up in SQL Profiler. That's
one of the reasons I dislike encryption, because it make debugging and
performance tuning a real hassle. But I agree with the rest of your post.
Jacco Schalkwijk
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:C451F175-1594-4DBC-AE84-24779EC8ABFF@.microsoft.com...
> The WITH ENCRYPTION option only provides a trivial level of security. You
> may
> find other third-party tools that claim to do better but ultimately you
> cannot stop an administrator using SQL Profiler to examine your code.
> IMO you should not try to hide code in this way. Admins may have a
> legitimate need to see what code is running. The best protection for your
> intellectual property is a licence agreement, not phoney and obstructive
> encryption. Just my opinion.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks for the correction Jacco. Actually I've not even tried Profiler with
encryption -I just unwisely made an assumption. Sorry Simon. Another reason
to avoid encryption though.
David Portas
SQL Server MVP
--|||just an addition, it'd show up as *encrypted* only after you have sp3
installed. so, you'd see the entire text if you're running earlier bits.
-oj
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23blcesxSFHA.3636@.TK2MSFTNGP14.phx.gbl...
> The text of encrypted procedures does not show up in SQL Profiler. That's
> one of the reasons I dislike encryption, because it make debugging and
> performance tuning a real hassle. But I agree with the rest of your post.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:C451F175-1594-4DBC-AE84-24779EC8ABFF@.microsoft.com...
>|||>> No that I think of it, if you don't have the DBA on your side your "$*$
What do you mean by this?
Thanks
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:99588A6B-5C8A-4A2A-B64C-65A5139EDD1A@.microsoft.com...
> Simon even with encryption there's plenty of tools on the web that people
> can
> use to decrypt it. All that I can say is implement physical security - ie.
> Memory stick
> and get your DB to sort out security issues. If you have an extra hour a
> day
> for admin you can always use sourcesafe.
> As for the code sitting in the database, your never safe. There's no IP
> (Intellectual Property ) when you are working for a company, they pay you
> thus they own everything you create during work hours.
> You can write manual encryptions by putting your code in extended procs,
> and
> atleast like this you remove the google junkies from your code. Just
> google
> "sql decryption tools" .
> With dlls it's a bit more difficult to see the code, although someone can
> still just catch the code through profiler...
> No that I think of it, if you don't have the DBA on your side your "$*$
> "simon" wrote:
>sql
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Tuesday, March 27, 2012
Encryption on stored procedures problem
Help! We do not use encryption at all on our stored procedure code, but all
the sudden "WITH ENCRYPTION" has been appearing in our stored procedures by
itself. I don't know if it is a 3rd party tool we are useing or what (we use
a few sql tools from red-gate), but now we can not view or edit our own
stored procedures. all the DBO's are gettin "Error 20585: [SQL-DMO]/******
Encrypted object is not transferable, and script can not be generate.
*******/ what in the world can I do to fix this? because it is saying this
on our development database and our live database both, which are mirror
images of each other (well, dev is a mirror of live by a backup process that
occurs on a scheduled timeframe).
How can we see our code?!You mean you don't keep separate copies of your scripts? In that case:
http://www.securiteam.com/tools/6J00S003GU.html
[url]http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5[/ur
l]
Then get yourself a source control system if you don't already have
one.
David Portas
SQL Server MVP
--|||> but now we can not view or edit our own stored procedures.
Why is your code not in source control?
the sudden "WITH ENCRYPTION" has been appearing in our stored procedures by
itself. I don't know if it is a 3rd party tool we are useing or what (we use
a few sql tools from red-gate), but now we can not view or edit our own
stored procedures. all the DBO's are gettin "Error 20585: [SQL-DMO]/******
Encrypted object is not transferable, and script can not be generate.
*******/ what in the world can I do to fix this? because it is saying this
on our development database and our live database both, which are mirror
images of each other (well, dev is a mirror of live by a backup process that
occurs on a scheduled timeframe).
How can we see our code?!You mean you don't keep separate copies of your scripts? In that case:
http://www.securiteam.com/tools/6J00S003GU.html
[url]http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5[/ur
l]
Then get yourself a source control system if you don't already have
one.
David Portas
SQL Server MVP
--|||> but now we can not view or edit our own stored procedures.
Why is your code not in source control?
Thursday, March 22, 2012
encryption ?
Hello,
My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write the
se stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
Thanks
On Thu, 20 May 2004 12:56:03 -0700, dev wrote:
>Hello,
>My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write th
ese stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
>Thanks
Hi Dev,
Two options:
1) Store your stored procedures as text files on your computers. Copy and
paste the code into and out of your development tool, unless it provides
load and save facilities (like Query Analyzer does).
2) Don't use encryption on your development database. Reexecute all
procedures with encryption on a seperate database, then ship that database
to your customers.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo,
In the 1st idea, do you mean save in separate .sql files ? The 2nd idea wont work for me I think because I won't be shipping the database, instead I will include the .sql files generated after using "Create Scripts".
dev
My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write the
se stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
Thanks
On Thu, 20 May 2004 12:56:03 -0700, dev wrote:
>Hello,
>My application will have more than 100 stored procedures and I want to use the "WITH ENCRYPTION" clause so that they are not visible to the my application's customer using EM. I will be writing the stored procedures in VS.Net server explorer to write th
ese stored procedures. The problem is that if I add "WITH ENCRYP.." right there while coding the stored procedures then after saving the sto. proc. even I cannot access it, as it is encrypted. I will be using the "Create Script" utility of SQL Server to
create scripts for tables, stored procedures etc and then will execute this script on client's machines. Is there any way I can continue seeing the stored procedure but not the client.
>Thanks
Hi Dev,
Two options:
1) Store your stored procedures as text files on your computers. Copy and
paste the code into and out of your development tool, unless it provides
load and save facilities (like Query Analyzer does).
2) Don't use encryption on your development database. Reexecute all
procedures with encryption on a seperate database, then ship that database
to your customers.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo,
In the 1st idea, do you mean save in separate .sql files ? The 2nd idea wont work for me I think because I won't be shipping the database, instead I will include the .sql files generated after using "Create Scripts".
dev
Wednesday, March 21, 2012
encrypting tables
Hi Guys,
is there any option to encrypt table structure in sql 2000.
procedures and triggers i can encrypt.
for eg: i have one user who is having only select
permission on one table but he can see all the tables
structure and procedure even though he can not edit.
any workaround to achieve this?
Pls advice me.
Thanks
BijuNo. There is no built in functionality to encrypt the table structure.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be careful. The built-in encryption mechanisms of MS are completely
vulnerable. Decryption programs are freely available. The closest you will
ever get to securing an MSSQL database is with the NelLib offerings. We
have investigated this for six months now and have been deeply disappointed
with the weakness of the security in MSSQL. If you are writing mission
critical applications using MSSQL as your backend our advice is that you
make absolutely certain that you are satisfied with living with the security
holes. A good place to start is www.sqlsecurity.com and all the links and
literature advertised there. Hopefully MS will address these issues with
the next version of MSSQL.
"biju george" <anonymous@.discussions.microsoft.com> wrote in message
news:17e5201c42228$4dcaf780$a001280a@.phx
.gbl...
> Hi Guys,
> is there any option to encrypt table structure in sql 2000.
> procedures and triggers i can encrypt.
> for eg: i have one user who is having only select
> permission on one table but he can see all the tables
> structure and procedure even though he can not edit.
> any workaround to achieve this?
> Pls advice me.
> Thanks
> Biju
is there any option to encrypt table structure in sql 2000.
procedures and triggers i can encrypt.
for eg: i have one user who is having only select
permission on one table but he can see all the tables
structure and procedure even though he can not edit.
any workaround to achieve this?
Pls advice me.
Thanks
BijuNo. There is no built in functionality to encrypt the table structure.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Be careful. The built-in encryption mechanisms of MS are completely
vulnerable. Decryption programs are freely available. The closest you will
ever get to securing an MSSQL database is with the NelLib offerings. We
have investigated this for six months now and have been deeply disappointed
with the weakness of the security in MSSQL. If you are writing mission
critical applications using MSSQL as your backend our advice is that you
make absolutely certain that you are satisfied with living with the security
holes. A good place to start is www.sqlsecurity.com and all the links and
literature advertised there. Hopefully MS will address these issues with
the next version of MSSQL.
"biju george" <anonymous@.discussions.microsoft.com> wrote in message
news:17e5201c42228$4dcaf780$a001280a@.phx
.gbl...
> Hi Guys,
> is there any option to encrypt table structure in sql 2000.
> procedures and triggers i can encrypt.
> for eg: i have one user who is having only select
> permission on one table but he can see all the tables
> structure and procedure even though he can not edit.
> any workaround to achieve this?
> Pls advice me.
> Thanks
> Biju
Encrypting SPs and maybe triggers, how?
Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
BobUse the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
BobUse the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>
Encrypting SPs and maybe triggers, how?
Don't want my sa to be able to tinker with Stored procedures or triggers on
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
Bob
Use the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>
the production machine. I saw some software that had them encrypted. Could
not go in and see their text. How can this be done?
Thanks for any help.
Bob
Use the WITH ENCRYPTION clause. However, I believe there are tools
available on the Internet to unencrypt the text. Also, note that once
encrypted the object cannot be scripted out for other purposes. Be sure to
save the original DDL in a secure location if needed in the future.
HTH
Jerry
"RDufour" <rdufour@.sgiims.com> wrote in message
news:O9FtvxuuFHA.4040@.TK2MSFTNGP10.phx.gbl...
> Don't want my sa to be able to tinker with Stored procedures or triggers
> on
> the production machine. I saw some software that had them encrypted. Could
> not go in and see their text. How can this be done?
> Thanks for any help.
> Bob
>
Monday, March 19, 2012
Encryptin stored procedures
Is there any way to generate a script of an encrypted stored procedure?
I supose that itsn't, so, is there are any way to encrypt a script so that I
can generate a encrypted stored prodcedure?Search google for "decrypt SQL stored procedure"
Not sure what you mean by the second part. Specifying WITH ENCRYPTION for
your CREATE PROCEDURE script will generate an encrypted stored procedure but
the script itself is plain text.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gaby" <msdn@.rmya.com.ar> wrote in message
news:OcjhQ5CvEHA.944@.TK2MSFTNGP11.phx.gbl...
> Is there any way to generate a script of an encrypted stored procedure?
>
> I supose that itsn't, so, is there are any way to encrypt a script so that
> I
> can generate a encrypted stored prodcedure?
>|||Hi Gaby,
I am just checking on your progress regarding the information that was sent
you! Have you tried the google as MVP suggested you? I wonder how the
testing is going. If you encounter any difficulty, please do not hesitate
to let me know. Please post here and let me know the status of your issue.
Without your further information, it's very hard for me to continue with
the troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
I supose that itsn't, so, is there are any way to encrypt a script so that I
can generate a encrypted stored prodcedure?Search google for "decrypt SQL stored procedure"
Not sure what you mean by the second part. Specifying WITH ENCRYPTION for
your CREATE PROCEDURE script will generate an encrypted stored procedure but
the script itself is plain text.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gaby" <msdn@.rmya.com.ar> wrote in message
news:OcjhQ5CvEHA.944@.TK2MSFTNGP11.phx.gbl...
> Is there any way to generate a script of an encrypted stored procedure?
>
> I supose that itsn't, so, is there are any way to encrypt a script so that
> I
> can generate a encrypted stored prodcedure?
>|||Hi Gaby,
I am just checking on your progress regarding the information that was sent
you! Have you tried the google as MVP suggested you? I wonder how the
testing is going. If you encounter any difficulty, please do not hesitate
to let me know. Please post here and let me know the status of your issue.
Without your further information, it's very hard for me to continue with
the troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Encrypted Stored Proc Performance
Does anyone know if there executing encrypted stored procedures in SQL
Server 2005 is any slower than regular ones?
Thanks,
KripHi, Krip,
There really should be no difference in performance, since the execution of
the stored procedure is not from the text. Maybe recompiles take slightly
longer, but I would not know.
The gentleman in this thread from 2004 felt that his encrypted stored
procedures ran faster, but the full testing method was questioned.
http://groups.google.com/group/micr...80e7a94e230ea8f
From the same time period Jacco Schalkwijk (SQL Server MVP) noted that the
difference was negligable.
RLF
"Krip" <amk@.kynetix.com> wrote in message
news:DC3CA6E3-A4D6-4B1C-99AB-88660359C9B7@.microsoft.com...
> Does anyone know if there executing encrypted stored procedures in SQL
> Server 2005 is any slower than regular ones?
> Thanks,
> Krip
>|||Thanks Russell. Makes sense. Good to hear from you!
-Krip
Server 2005 is any slower than regular ones?
Thanks,
KripHi, Krip,
There really should be no difference in performance, since the execution of
the stored procedure is not from the text. Maybe recompiles take slightly
longer, but I would not know.
The gentleman in this thread from 2004 felt that his encrypted stored
procedures ran faster, but the full testing method was questioned.
http://groups.google.com/group/micr...80e7a94e230ea8f
From the same time period Jacco Schalkwijk (SQL Server MVP) noted that the
difference was negligable.
RLF
"Krip" <amk@.kynetix.com> wrote in message
news:DC3CA6E3-A4D6-4B1C-99AB-88660359C9B7@.microsoft.com...
> Does anyone know if there executing encrypted stored procedures in SQL
> Server 2005 is any slower than regular ones?
> Thanks,
> Krip
>|||Thanks Russell. Makes sense. Good to hear from you!
-Krip
Sunday, March 11, 2012
Encrypt Stored Procedure
Hi All
I am looking for a way to encrypt our stored procedures in a SQL Server 2000
database so that the Intellectual Property is not readily accessible. I am
aware of the WITH ENCRYPT option when creating the stored procedure however I
am looking for a solution that is not easily able to be un-encrypted. Is
anyone aware of a solution that will encrypt stored procedures?
Thanks
Try a third party product like :
http://www.ecatenate.com/dblockdown_product_info.html
or
http://www.sql-shield.com/
I have never tried either
"David" wrote:
> Hi All
> I am looking for a way to encrypt our stored procedures in a SQL Server 2000
> database so that the Intellectual Property is not readily accessible. I am
> aware of the WITH ENCRYPT option when creating the stored procedure however I
> am looking for a solution that is not easily able to be un-encrypted. Is
> anyone aware of a solution that will encrypt stored procedures?
> Thanks
I am looking for a way to encrypt our stored procedures in a SQL Server 2000
database so that the Intellectual Property is not readily accessible. I am
aware of the WITH ENCRYPT option when creating the stored procedure however I
am looking for a solution that is not easily able to be un-encrypted. Is
anyone aware of a solution that will encrypt stored procedures?
Thanks
Try a third party product like :
http://www.ecatenate.com/dblockdown_product_info.html
or
http://www.sql-shield.com/
I have never tried either
"David" wrote:
> Hi All
> I am looking for a way to encrypt our stored procedures in a SQL Server 2000
> database so that the Intellectual Property is not readily accessible. I am
> aware of the WITH ENCRYPT option when creating the stored procedure however I
> am looking for a solution that is not easily able to be un-encrypted. Is
> anyone aware of a solution that will encrypt stored procedures?
> Thanks
Encrypt Stored Procedure
Hi All
I am looking for a way to encrypt our stored procedures in a SQL Server 2000
database so that the Intellectual Property is not readily accessible. I am
aware of the WITH ENCRYPT option when creating the stored procedure however
I
am looking for a solution that is not easily able to be un-encrypted. Is
anyone aware of a solution that will encrypt stored procedures?
ThanksTry a third party product like :
http://www.ecatenate.com/dblockdown_product_info.html
or
http://www.sql-shield.com/
I have never tried either
"David" wrote:
> Hi All
> I am looking for a way to encrypt our stored procedures in a SQL Server 20
00
> database so that the Intellectual Property is not readily accessible. I a
m
> aware of the WITH ENCRYPT option when creating the stored procedure howeve
r I
> am looking for a solution that is not easily able to be un-encrypted. Is
> anyone aware of a solution that will encrypt stored procedures?
> Thanks
I am looking for a way to encrypt our stored procedures in a SQL Server 2000
database so that the Intellectual Property is not readily accessible. I am
aware of the WITH ENCRYPT option when creating the stored procedure however
I
am looking for a solution that is not easily able to be un-encrypted. Is
anyone aware of a solution that will encrypt stored procedures?
ThanksTry a third party product like :
http://www.ecatenate.com/dblockdown_product_info.html
or
http://www.sql-shield.com/
I have never tried either
"David" wrote:
> Hi All
> I am looking for a way to encrypt our stored procedures in a SQL Server 20
00
> database so that the Intellectual Property is not readily accessible. I a
m
> aware of the WITH ENCRYPT option when creating the stored procedure howeve
r I
> am looking for a solution that is not easily able to be un-encrypted. Is
> anyone aware of a solution that will encrypt stored procedures?
> Thanks
Encrypt Stored Procedure
Hi All
I am looking for a way to encrypt our stored procedures in a SQL Server 2000
database so that the Intellectual Property is not readily accessible. I am
aware of the WITH ENCRYPT option when creating the stored procedure however I
am looking for a solution that is not easily able to be un-encrypted. Is
anyone aware of a solution that will encrypt stored procedures?
ThanksTry a third party product like :
http://www.ecatenate.com/dblockdown_product_info.html
or
http://www.sql-shield.com/
I have never tried either
"David" wrote:
> Hi All
> I am looking for a way to encrypt our stored procedures in a SQL Server 2000
> database so that the Intellectual Property is not readily accessible. I am
> aware of the WITH ENCRYPT option when creating the stored procedure however I
> am looking for a solution that is not easily able to be un-encrypted. Is
> anyone aware of a solution that will encrypt stored procedures?
> Thanks
I am looking for a way to encrypt our stored procedures in a SQL Server 2000
database so that the Intellectual Property is not readily accessible. I am
aware of the WITH ENCRYPT option when creating the stored procedure however I
am looking for a solution that is not easily able to be un-encrypted. Is
anyone aware of a solution that will encrypt stored procedures?
ThanksTry a third party product like :
http://www.ecatenate.com/dblockdown_product_info.html
or
http://www.sql-shield.com/
I have never tried either
"David" wrote:
> Hi All
> I am looking for a way to encrypt our stored procedures in a SQL Server 2000
> database so that the Intellectual Property is not readily accessible. I am
> aware of the WITH ENCRYPT option when creating the stored procedure however I
> am looking for a solution that is not easily able to be un-encrypted. Is
> anyone aware of a solution that will encrypt stored procedures?
> Thanks
encrypt stored proc
Is there a way to find the server name in a stored procedure?
I would like to encrypt my procedures when they are created on my clients'
databases, but I do not want them encrypted when I install them on my box fo
r
testing.
Thanks.>> Is there a way to find the server name in a stored procedure?
See @.@.SERVERNAME in SQL Server Books Online.
Anith|||is there a way to determine the server name prior to running the
WITH ENCRYPTION statement?
"Anith Sen" wrote:
> See @.@.SERVERNAME in SQL Server Books Online.
> --
> Anith
>
>|||>> is there a way to determine the server name prior to running the WITH
It is not fully clear what you are trying to achieve. In the case of a
stored procedure, WITH ENCRYPTION indicates that SQL Server encrypts the
data containing the text of the CREATE PROCEDURE statement in the system
procedure syscomments.
You will have to maintain multiple copies of the stored procedure to switch
between servers based on the server names.
Anith
I would like to encrypt my procedures when they are created on my clients'
databases, but I do not want them encrypted when I install them on my box fo
r
testing.
Thanks.>> Is there a way to find the server name in a stored procedure?
See @.@.SERVERNAME in SQL Server Books Online.
Anith|||is there a way to determine the server name prior to running the
WITH ENCRYPTION statement?
"Anith Sen" wrote:
> See @.@.SERVERNAME in SQL Server Books Online.
> --
> Anith
>
>|||>> is there a way to determine the server name prior to running the WITH
It is not fully clear what you are trying to achieve. In the case of a
stored procedure, WITH ENCRYPTION indicates that SQL Server encrypts the
data containing the text of the CREATE PROCEDURE statement in the system
procedure syscomments.
You will have to maintain multiple copies of the stored procedure to switch
between servers based on the server names.
Anith
Encrypt SQL Command to any SQL Server
Hi,
I'm a developer and in my applications I send commands to the SQL Server
databases to create, or recreate, Stored Procedures, Functions and Triggers.
Recently I realize that my clients, using SQL Profiler, could see the code
of that functions, SPs and Triggers, and even change it after they know it.
This means that my intellectual property is not safe, and the clients has
access to my source code.
I want to know if there is same way to encrypt the commands that I send to
any SQL Server database in order to not being seen at profiler, at least for
that kind of commands.
I notice that profiler automatically not show commands like sp_addlogin
stored procedures...
Thanks for your help,
Pedro GonalvesYou can encrypt the communications between you application and the server wi
th the use of SSL.
Several sources of information exists on using SSL with SQL Server:
Configuring SSL on Servers
http://www.microsoft.com/resources/..._certsetssl.asp
7 Steps to SSL Encryption
http://www.winnetmag.com/SQLServer/...6908/26908.html
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/defaul...kb;en-us;276553
How To: Use SSL to Secure Communication with SQL Server 2000
http://msdn.microsoft.com/library/d.../>
NetHT19.asp
Are just a few to get you going on the subject.
Randy Dyess
www.Database-Security.Info|||Hi Randy,
Yes. It's a way. But the problem is that I can't control my clients SQL
servers. This is a way to encrypt comunications with YOURS database server.
As I sell aplications, that my clients install in their servers, I simply
can't do it. And even with SSL, I think that Profiler can access to the
t-sql commands.
I just want to garantee that several commands, the source code commands like
stored procedures and trigger, that my application send to the database to
create functionality, aren't seen by the client.
Have you an ideia for this?
"Randy Dyess" <anonymous@.discussions.microsoft.com> wrote in message
news:A9A67657-6E3D-4E9E-A281-18561C51A8E4@.microsoft.com...
> You can encrypt the communications between you application and the server
with the use of SSL.
> Several sources of information exists on using SSL with SQL Server:
> Configuring SSL on Servers
>
http://www.microsoft.com/resources/...ssl.asp
> 7 Steps to SSL Encryption
> http://www.winnetmag.com/SQLServer/...6908/26908.html
> HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
> http://support.microsoft.com/defaul...kb;en-us;276553
> How To: Use SSL to Secure Communication with SQL Server 2000
>
> Are just a few to get you going on the subject.
> Randy Dyess
> [url]www.Database-Security.Info" target="_blank">http://msdn.microsoft.com/library/d...e-Security.Info
>|||To add to the other responses, SQL Profiler will hide text that contains
sensitive commands like 'sp_password'. Consequently, you can include a
comment with this text in the batches you want to hide like the example
below.
Note that object text encryption is basically just obfuscation. A
determined user could reverse engineer the object to view the source. To
protect your intellectual property, you should explicitly state in your
license agreement that reverse engineering of your application code,
including SQL objects, is prohibited.
CREATE PROC usp_EncryptedProcedure1
WITH ENCRYPTION
AS
--sp_password
SELECT 'this create will not appear in profiler'
GO
CREATE PROC usp_EncryptedProcedure2
WITH ENCRYPTION
AS
SELECT 'this create will appear in profiler'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Pedro Gonalves" <anonymous@.microsoft.com> wrote in message
news:%23ZRLGYu$DHA.2336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm a developer and in my applications I send commands to the SQL Server
> databases to create, or recreate, Stored Procedures, Functions and
Triggers.
> Recently I realize that my clients, using SQL Profiler, could see the code
> of that functions, SPs and Triggers, and even change it after they know
it.
> This means that my intellectual property is not safe, and the clients has
> access to my source code.
> I want to know if there is same way to encrypt the commands that I send to
> any SQL Server database in order to not being seen at profiler, at least
for
> that kind of commands.
> I notice that profiler automatically not show commands like sp_addlogin
> stored procedures...
> Thanks for your help,
> Pedro Gonalves
>|||Just a small addition - if you really need good encryption of your objects,
onsider using a 3rd party tool like
http://www.ecatenate.com/dblockdown_product_info.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uLnYMm5$DHA.2448@.TK2MSFTNGP12.phx.gbl...
> To add to the other responses, SQL Profiler will hide text that contains
> sensitive commands like 'sp_password'. Consequently, you can include a
> comment with this text in the batches you want to hide like the example
> below.
> Note that object text encryption is basically just obfuscation. A
> determined user could reverse engineer the object to view the source. To
> protect your intellectual property, you should explicitly state in your
> license agreement that reverse engineering of your application code,
> including SQL objects, is prohibited.
>
> CREATE PROC usp_EncryptedProcedure1
> WITH ENCRYPTION
> AS
> --sp_password
> SELECT 'this create will not appear in profiler'
> GO
> CREATE PROC usp_EncryptedProcedure2
> WITH ENCRYPTION
> AS
> SELECT 'this create will appear in profiler'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro Gonalves" <anonymous@.microsoft.com> wrote in message
> news:%23ZRLGYu$DHA.2336@.TK2MSFTNGP11.phx.gbl...
> Triggers.
code
> it.
has
to
> for
>|||Thanks Dan, this is what I needed.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uLnYMm5$DHA.2448@.TK2MSFTNGP12.phx.gbl...
> To add to the other responses, SQL Profiler will hide text that contains
> sensitive commands like 'sp_password'. Consequently, you can include a
> comment with this text in the batches you want to hide like the example
> below.
> Note that object text encryption is basically just obfuscation. A
> determined user could reverse engineer the object to view the source. To
> protect your intellectual property, you should explicitly state in your
> license agreement that reverse engineering of your application code,
> including SQL objects, is prohibited.
>
> CREATE PROC usp_EncryptedProcedure1
> WITH ENCRYPTION
> AS
> --sp_password
> SELECT 'this create will not appear in profiler'
> GO
> CREATE PROC usp_EncryptedProcedure2
> WITH ENCRYPTION
> AS
> SELECT 'this create will appear in profiler'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro Gonalves" <anonymous@.microsoft.com> wrote in message
> news:%23ZRLGYu$DHA.2336@.TK2MSFTNGP11.phx.gbl...
> Triggers.
code
> it.
has
to
> for
>|||Pedro,
There is no good solution I'm afraid. Anything you encrypt there are ways o
f
decrpyting. If you want to protect your work/ideas/code then do that with a
legally binding agreement that customers have to sign.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||The next version of SQL Shield (www.sql-shield.com) will include protection
from the profiler. Currently this program protects stored procedures from
decryption by cracking tools.
I'm a developer and in my applications I send commands to the SQL Server
databases to create, or recreate, Stored Procedures, Functions and Triggers.
Recently I realize that my clients, using SQL Profiler, could see the code
of that functions, SPs and Triggers, and even change it after they know it.
This means that my intellectual property is not safe, and the clients has
access to my source code.
I want to know if there is same way to encrypt the commands that I send to
any SQL Server database in order to not being seen at profiler, at least for
that kind of commands.
I notice that profiler automatically not show commands like sp_addlogin
stored procedures...
Thanks for your help,
Pedro GonalvesYou can encrypt the communications between you application and the server wi
th the use of SSL.
Several sources of information exists on using SSL with SQL Server:
Configuring SSL on Servers
http://www.microsoft.com/resources/..._certsetssl.asp
7 Steps to SSL Encryption
http://www.winnetmag.com/SQLServer/...6908/26908.html
HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
http://support.microsoft.com/defaul...kb;en-us;276553
How To: Use SSL to Secure Communication with SQL Server 2000
http://msdn.microsoft.com/library/d.../>
NetHT19.asp
Are just a few to get you going on the subject.
Randy Dyess
www.Database-Security.Info|||Hi Randy,
Yes. It's a way. But the problem is that I can't control my clients SQL
servers. This is a way to encrypt comunications with YOURS database server.
As I sell aplications, that my clients install in their servers, I simply
can't do it. And even with SSL, I think that Profiler can access to the
t-sql commands.
I just want to garantee that several commands, the source code commands like
stored procedures and trigger, that my application send to the database to
create functionality, aren't seen by the client.
Have you an ideia for this?
"Randy Dyess" <anonymous@.discussions.microsoft.com> wrote in message
news:A9A67657-6E3D-4E9E-A281-18561C51A8E4@.microsoft.com...
> You can encrypt the communications between you application and the server
with the use of SSL.
> Several sources of information exists on using SSL with SQL Server:
> Configuring SSL on Servers
>
http://www.microsoft.com/resources/...ssl.asp
> 7 Steps to SSL Encryption
> http://www.winnetmag.com/SQLServer/...6908/26908.html
> HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate Server
> http://support.microsoft.com/defaul...kb;en-us;276553
> How To: Use SSL to Secure Communication with SQL Server 2000
>
> Are just a few to get you going on the subject.
> Randy Dyess
> [url]www.Database-Security.Info" target="_blank">http://msdn.microsoft.com/library/d...e-Security.Info
>|||To add to the other responses, SQL Profiler will hide text that contains
sensitive commands like 'sp_password'. Consequently, you can include a
comment with this text in the batches you want to hide like the example
below.
Note that object text encryption is basically just obfuscation. A
determined user could reverse engineer the object to view the source. To
protect your intellectual property, you should explicitly state in your
license agreement that reverse engineering of your application code,
including SQL objects, is prohibited.
CREATE PROC usp_EncryptedProcedure1
WITH ENCRYPTION
AS
--sp_password
SELECT 'this create will not appear in profiler'
GO
CREATE PROC usp_EncryptedProcedure2
WITH ENCRYPTION
AS
SELECT 'this create will appear in profiler'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Pedro Gonalves" <anonymous@.microsoft.com> wrote in message
news:%23ZRLGYu$DHA.2336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm a developer and in my applications I send commands to the SQL Server
> databases to create, or recreate, Stored Procedures, Functions and
Triggers.
> Recently I realize that my clients, using SQL Profiler, could see the code
> of that functions, SPs and Triggers, and even change it after they know
it.
> This means that my intellectual property is not safe, and the clients has
> access to my source code.
> I want to know if there is same way to encrypt the commands that I send to
> any SQL Server database in order to not being seen at profiler, at least
for
> that kind of commands.
> I notice that profiler automatically not show commands like sp_addlogin
> stored procedures...
> Thanks for your help,
> Pedro Gonalves
>|||Just a small addition - if you really need good encryption of your objects,
onsider using a 3rd party tool like
http://www.ecatenate.com/dblockdown_product_info.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uLnYMm5$DHA.2448@.TK2MSFTNGP12.phx.gbl...
> To add to the other responses, SQL Profiler will hide text that contains
> sensitive commands like 'sp_password'. Consequently, you can include a
> comment with this text in the batches you want to hide like the example
> below.
> Note that object text encryption is basically just obfuscation. A
> determined user could reverse engineer the object to view the source. To
> protect your intellectual property, you should explicitly state in your
> license agreement that reverse engineering of your application code,
> including SQL objects, is prohibited.
>
> CREATE PROC usp_EncryptedProcedure1
> WITH ENCRYPTION
> AS
> --sp_password
> SELECT 'this create will not appear in profiler'
> GO
> CREATE PROC usp_EncryptedProcedure2
> WITH ENCRYPTION
> AS
> SELECT 'this create will appear in profiler'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro Gonalves" <anonymous@.microsoft.com> wrote in message
> news:%23ZRLGYu$DHA.2336@.TK2MSFTNGP11.phx.gbl...
> Triggers.
code
> it.
has
to
> for
>|||Thanks Dan, this is what I needed.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uLnYMm5$DHA.2448@.TK2MSFTNGP12.phx.gbl...
> To add to the other responses, SQL Profiler will hide text that contains
> sensitive commands like 'sp_password'. Consequently, you can include a
> comment with this text in the batches you want to hide like the example
> below.
> Note that object text encryption is basically just obfuscation. A
> determined user could reverse engineer the object to view the source. To
> protect your intellectual property, you should explicitly state in your
> license agreement that reverse engineering of your application code,
> including SQL objects, is prohibited.
>
> CREATE PROC usp_EncryptedProcedure1
> WITH ENCRYPTION
> AS
> --sp_password
> SELECT 'this create will not appear in profiler'
> GO
> CREATE PROC usp_EncryptedProcedure2
> WITH ENCRYPTION
> AS
> SELECT 'this create will appear in profiler'
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro Gonalves" <anonymous@.microsoft.com> wrote in message
> news:%23ZRLGYu$DHA.2336@.TK2MSFTNGP11.phx.gbl...
> Triggers.
code
> it.
has
to
> for
>|||Pedro,
There is no good solution I'm afraid. Anything you encrypt there are ways o
f
decrpyting. If you want to protect your work/ideas/code then do that with a
legally binding agreement that customers have to sign.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||The next version of SQL Shield (www.sql-shield.com) will include protection
from the profiler. Currently this program protects stored procedures from
decryption by cracking tools.
Friday, March 9, 2012
Encrypt all
Hi to all
i have A LOT of stored procedures that i have to encrypt. There is any nice way to do it automatically without having to ALTER them with the ENCRYPTION keyword one by one?
Thanx in advanceI don't believe there is another way to do it. BOL makes it very clear the syntax required for altering a SP.
Good luck and I hope you have plenty of time on your hand.|||can i say "oh sh*t" ? :-)
i expected that anyway...just need to be sure of it
thanx|||I think you will be saying on S**t for every one you have to do manually!
Just remember to backup them up safely becaus once encrypted... you know the rest!
Good luck and have fun. Perhaps you could use this weekend to do it?! :)|||Every encrypted procedure can be decrypted, so if you just want to protect the code by doing that, don't waste your time. The code can be easily extracted even if is "encrypted".
Originally posted by Crespo-n00b
I think you will be saying on S**t for every one you have to do manually!
Just remember to backup them up safely becaus once encrypted... you know the rest!
Good luck and have fun. Perhaps you could use this weekend to do it?! :)|||mmm...not so sure of it. the only method to decrypt a sql server encryption should the the brute-force method, since encryption is irreversible.
am i wrong? if yes can you show the proof?|||Well, take a look at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
Originally posted by manowar
mmm...not so sure of it. the only method to decrypt a sql server encryption should the the brute-force method, since encryption is irreversible.
am i wrong? if yes can you show the proof?
i have A LOT of stored procedures that i have to encrypt. There is any nice way to do it automatically without having to ALTER them with the ENCRYPTION keyword one by one?
Thanx in advanceI don't believe there is another way to do it. BOL makes it very clear the syntax required for altering a SP.
Good luck and I hope you have plenty of time on your hand.|||can i say "oh sh*t" ? :-)
i expected that anyway...just need to be sure of it
thanx|||I think you will be saying on S**t for every one you have to do manually!
Just remember to backup them up safely becaus once encrypted... you know the rest!
Good luck and have fun. Perhaps you could use this weekend to do it?! :)|||Every encrypted procedure can be decrypted, so if you just want to protect the code by doing that, don't waste your time. The code can be easily extracted even if is "encrypted".
Originally posted by Crespo-n00b
I think you will be saying on S**t for every one you have to do manually!
Just remember to backup them up safely becaus once encrypted... you know the rest!
Good luck and have fun. Perhaps you could use this weekend to do it?! :)|||mmm...not so sure of it. the only method to decrypt a sql server encryption should the the brute-force method, since encryption is irreversible.
am i wrong? if yes can you show the proof?|||Well, take a look at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
Originally posted by manowar
mmm...not so sure of it. the only method to decrypt a sql server encryption should the the brute-force method, since encryption is irreversible.
am i wrong? if yes can you show the proof?
encryping - decrypting stored procedures
Hi all...
Im wondering how do you encrypt and decrypt a stored procedure within sql server 2000 ?
i want to be able to encrypt data been inserted / updated using triple des algorithm and then on select / read - decrypt it.
Does anyone know a solution / stored procedure on how to do this? any ideas.
Thanks
Paul..
Im also wondering if there is a away of doing this on the code side without using stored procedures (asp.net c#) i have been able to encrypt and decrpt to a string but unable to do it through the datagrid object (dataset) or a datareader..
Any tips / help would be helpful
Thanks..You may implement a sql server function to encrypt and decrypt when insert or select.
Just my 2 cent.
Scott
Software Design Group|||The following article could possibly provide some thoughts;
http://www.15seconds.com/issue/030310.htm
encription in stored procedures
Hi All!
I'm a newby in SQL Server and I've got a question.
Is it really helps for securing to use encripted stored procedures and what
kind of problems it causes for me as a DBA and as a client apps developer?
Thanks for any advice!
DmitryThe answer depends on how you view the stored procedure. If you feel that
the procedure is so important that you do not want anyone to see it
(including yourself), then you can encrypt it. Encrypting a stored procedure
ensure that no one can see it. This includes, the user, the owner/creator
and the system administrator. Encrypting a stored procedure also ensures
that it does not get published in SQL Server Replication.
That said, from an application point of view and the DBA, the only advantage
that I see is that your procedure cannot be viewed by others. I'm not aware
of any performance issues because of encrypting a stored procedure and the
only problem that I see is, in case you want to view the source, you have to
do so from your source-control tool or from a local copy of the procedure
source.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:uudfaIivDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi All!
> I'm a newby in SQL Server and I've got a question.
> Is it really helps for securing to use encripted stored procedures and
what
> kind of problems it causes for me as a DBA and as a client apps developer?
> Thanks for any advice!
> Dmitry
>|||Hi,
Encryption of stored procedure is useful only when you don't want your clients or others to view the stored procedure script (basically using SP_HELPTEXT <SPName>). Disadvantage is that, even the creator will not be able to see the script, hence the creator should have an copy of the SP script to make modification at the later stage
Hope this help
Thank
GYK|||Just be aware that there are, unfortunately, tools "out there" to decrypt
encrypted procedures etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:uudfaIivDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi All!
> I'm a newby in SQL Server and I've got a question.
> Is it really helps for securing to use encripted stored procedures and
what
> kind of problems it causes for me as a DBA and as a client apps developer?
> Thanks for any advice!
> Dmitry
>
I'm a newby in SQL Server and I've got a question.
Is it really helps for securing to use encripted stored procedures and what
kind of problems it causes for me as a DBA and as a client apps developer?
Thanks for any advice!
DmitryThe answer depends on how you view the stored procedure. If you feel that
the procedure is so important that you do not want anyone to see it
(including yourself), then you can encrypt it. Encrypting a stored procedure
ensure that no one can see it. This includes, the user, the owner/creator
and the system administrator. Encrypting a stored procedure also ensures
that it does not get published in SQL Server Replication.
That said, from an application point of view and the DBA, the only advantage
that I see is that your procedure cannot be viewed by others. I'm not aware
of any performance issues because of encrypting a stored procedure and the
only problem that I see is, in case you want to view the source, you have to
do so from your source-control tool or from a local copy of the procedure
source.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:uudfaIivDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi All!
> I'm a newby in SQL Server and I've got a question.
> Is it really helps for securing to use encripted stored procedures and
what
> kind of problems it causes for me as a DBA and as a client apps developer?
> Thanks for any advice!
> Dmitry
>|||Hi,
Encryption of stored procedure is useful only when you don't want your clients or others to view the stored procedure script (basically using SP_HELPTEXT <SPName>). Disadvantage is that, even the creator will not be able to see the script, hence the creator should have an copy of the SP script to make modification at the later stage
Hope this help
Thank
GYK|||Just be aware that there are, unfortunately, tools "out there" to decrypt
encrypted procedures etc.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dmitry Karneyev" <karneyev@.msn.com> wrote in message
news:uudfaIivDHA.2448@.TK2MSFTNGP12.phx.gbl...
> Hi All!
> I'm a newby in SQL Server and I've got a question.
> Is it really helps for securing to use encripted stored procedures and
what
> kind of problems it causes for me as a DBA and as a client apps developer?
> Thanks for any advice!
> Dmitry
>
Wednesday, March 7, 2012
Encourage use of UDFs instead of Procedures when possible?
Over the past few months one of the issues that repeats in this forum has to do with the use of stored procedure output as input to other procedures. One method of dealing with this is to convert the stored procedure to a function if possible and use the function for the process.
I was wandering if it would be a good idea to change my database standards to reflect a preference to use functions over stored procedures -- especially in a read-only context. I realize that in some cases this might cause performance problems, but I sill want to recruit opinions on this issue.
Anybody?
Dont do it....
Functions will use row by row operations (Like a cursor)
sprocs will perform set-based operations.
we are dealing with that at my current position. Removing UDFs in favor of subqueries or sproc calls.
|||Thank you.|||
UDFs come in 3 flavors - scalar UDFs, inline table-valued functions and non-inline table-valued functions. The performance of the TVFs goes in the following order:
#1. Inline TVFs - Since the TVF definition is parsed into the query, compiled and optimized. This is similar to how views are resolved in a query
#2. Non-inline TVFs - Cost and Row estimates will be inaccurate due to use of table variable. So plan choices are limited and performance will suffer in most cases
Next, the scalar UDFs are a different beast altogether. In general, it should be avoided in queries (WHERE clause, SELECT list etc) since it introduces performance problems and bad plans. Often inlining the expression in the scalar UDFs in queries will give orders of magnitude performance benefits and better plans. In SQL Server 2005, you can use WITH SCHEMABINDING on scalar UDFs so that the query optimizer knows whether a UDF is performing data access or not. This can result in slightly better performance. The main thing to note about UDFs is that you are doing a row-by-row call in queries and there is lot of overhead to that. For CPU and calculation intensive scalar UDFs, you should consider migrating to CLR UDFs since those are compiled and provide better performance for the complex ones.
So the choice of converting the SP logic to UDFs should consider above points. If you want to reuse results then consider moving the queries to views or inline TVFs. If you want to reuse the logic and calculations then try the CLR UDFs for complex calculations or just inline the calculations in queries for best performance.
|||Thank you
Umachandar:
You stated, "... The main thing to note about UDFs is that you are doing a row-by-row call in queries and there is lot of overhead to that. ..." I want to make sure I clearly understand this. You mean this for all function types or is this quote only in the context of scalar UDFs?
|||It is true for scalar UDFs. But depending on the query plan, you will encounted similar situation for multi-statement TVFs. For example, this will happen if you have a nested loop join. In this case, you will end up querying the TVF for each row in the input and this will be bad because there is no statistics on the table variable used in the multi-statement TVF.|||Perfect. Thank you both for your responses.
:-)
Friday, February 17, 2012
Empty table problem
I am creating three tables in a DataSet using three stored procedures and I need a way to know that either the tables have at least one row or preferably which if any has no rows. How can I do that?
using three SP's, one for each table:
SqlCommand cmd =newSqlCommand("CompanyCheck", con);SqlCommand cmd2 =newSqlCommand("ContractorVerify", con);
SqlCommand cmd3 =newSqlCommand("StoreLocation", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd2.CommandType = CommandType.StoredProcedure;
cmd3.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.CompanyID", CompanyID);
cmd2.Parameters.AddWithValue("@.CompanyID", CompanyID);
cmd3.Parameters.AddWithValue("@.CompanyID", CompanyID);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "CompanyInfo");
da.SelectCommand = cmd2;
da.Fill(ds, "ContractorInfo");
da.SelectCommand = cmd3;
da.Fill(ds, "StoreInfo");
}
catch
{ throw new ApplicationException("Data error"); }
finally
{
con.Close();
}
Thank you
Zoltac,
I would try
if (ds.Tables.Count == 3)
{
if(ds.Tables[0].Rows.Count > 0)
{ }
if(ds.Tables[1].Rows.Count > 0)
{ }
if(ds.Tables[2].Rows.Count > 0)
{ }
Is this what you are looking for? If not, I might be misunderstanding your question.
Thank you - Exactly what I was looking for - maybe one of these days I will finally get it. I need a big wall chart with all the properties and methods, etc,, etc. and maybe then I will have a clue as to what Net 2.0. is made up from. appreciate the quick response.
Subscribe to:
Posts (Atom)