EndDialog

I have a SB program that sends a message and there is an activation procedure on my queue. The activation procedure takes the message from the queue, does some processing with the data, and ends the conversation. (I may, in the future, consider reusing the dialogs.) What I am wondering about is that I never come across the

'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' message type name that indicates that the dialog has ended. Any ideas why?

From your description it sounds like the activated procedure issues the END CONVERSATION verb after processing the first message. After the END CONVERSATION verb was issued on a handle, you will not receive any other message on that conversation (including EndDialog sent by the peer). If we wouldn't ensure that application would have to keep around the state related to that conversation for an undetermined time.

HTH,

~ Remus

P.S. Thanks for writing those SSB articles

|||

Thanks Remus, that definetly makes sense. Because I won't see that EndDialog message type, what is the best way to go about cleaning the conversation up once I am finished? What I have ran into so far is that when I used END CONVERSATION WITH CLEANUP, the conversation remains in sys.conversation_endpoints with a status of 'Conversing', but if I omit 'WITH CLEANUP', the dialog will close as expected. I would like a way to cleanup that message so that it is removed from the catalog view. Does that make sense?

Thanks again,

Tim

|||

Never ever use END CONVERSATION ... WITH CLEANUP. It is realy a last resort statement itended for administrators. Using it in applications can result in very serious problems.

You must decide who ends the conversation first, depending on the business semantics of the conversation. The party that ends the conversation first is the first one that can say 'I am no longer interested in this conversation, even if this last (EndDialog) message never makes it to my peer'. Some common patterns are:

Notification

1) Initiator begins conversation

2) Initiator sends the notification message

3) Target receives the message

4) Target ends the conversations

5) Initiator receives EndDialog message

6) Initiator ends the conversation

Request-Response (when target does not care is response is lost)

1) Initiator begins conversation

2) Initiator sends the request message

3) Target receives the message

4) Target sends response

5) Target ends the conversations

6) Intiator receives the response

7) Initiator receives EndDialog message

8) Initiator ends the conversation

Request-Response (when target does care is response is lost)

1) Initiator begins conversation

2) Initiator sends the request message

3) Target receives the message

4) Target sends response

5) Intiator receives the response

6) Initiator ends the conversation

7) target receives EndDialog message

8) target ends the conversation

One way stream of messages

1) Initiator begins conversation

2) Initiator sends the one message

2') Initiator sends the one message ...

3) Target receives message(s)

... conversation continues for while with initiator sending messages

4) Initiator decides to intrerupt the stream and sends a special application message (EndOfStream)

5) Target receives the EndOfSream message

6) Target ends the conversations

7) Initiator receives EndDialog message

8) Initiator ends the conversation

One pattern that is actually incorrect is fire-and-forget: initiator begins a conversation, then sends one or more messages, then ends the conversation. This way the initiator never gets any feedback if the messages we actually sent or not. I have a blog entry on this subject: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx

It is important to mention the conversation lifetime role in these patterns. If a conversation is not ended by both sides before it's lifetime expires, the conversation is errored and an Error message is sent to any endpoint still open (not ended). This is why endpoints that still care about delivery of the last message sent cannot issue an END CONVERSATION. They are supposed to send the message and the if they get an EndDialog message is a confrmation of succesfull delivery, while an Error message is an indication of a problem.

As about the endpoint states, a 'Conversing' endpoint is an endpoint that did not receive nor sent an EndDialog message. All of the patterns I mention above clean up the endpoints after issuing the END CONVERSATION. The first endpoint that issues the END CONVERSATION will be kept in the system until the EndDialog message is acknowledged by the peer (in an DISCONNECTED_OUTBOUND state), then it will be closed. An endpoint that has received an EndDialog message will stay in DISCONNECTED_INBOUND state until is explictly ended with END CONVERSATION, then it will be closed.

When initiator endpoint is closed, the endpoint is immedeately deleted.

When target endpoint is closed, the endpoint might be kept around for up to 30 minutes to prevent a replay attack, then it will be deleted.

Another negative side effect of the fire-and-forget pattern is that the target endpoint is not kept for 30 minutes in that case, but until the original conversation lifetime expires. Since most application do not specify a lifetime, that mens the target is scheduled to be deleted 74 years from now, thus being leaked for all practical means.

HTH,

~ Remus

|||Thanks again Remus, this is perfect. I was confused about the dialogs getting cleaned up. I noticed that the WITH CLEANUP cleaned the dialog up, and that is what I wanted at the time. I have since taken this out of all of my code. I didn't realize that the dialogs got cleaned up after 30 minutes to prevent a replay attack. Thanks again for your help...I love this new feature of 2005.
Timsql

Encryption problem

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

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?

Encryption

Is there any way I can encrypt the stored procedure and that only I can only
decrypt it none of other user can do?
I can't use Ecryption reserve word becuase that can also be decrypted
through third party tool, is there any other way I can encrypt my stored
procedure with my own key or like that?
Thanks in advance.
Rogers wrote:
> Is there any way I can encrypt the stored procedure and that only I
> can only decrypt it none of other user can do?
> I can't use Ecryption reserve word becuase that can also be decrypted
> through third party tool, is there any other way I can encrypt my
> stored procedure with my own key or like that?
> Thanks in advance.
There are some thrid-party solutions for object encryption. In general,
you should be working/developing your stored procs (and other objects)
using external files and version control software like PVCS or
SourceSafe. It's not advisable to edit objects directly in the database.
If you use one of the third-party solutions, that should help secure the
objects, but I don't know if those tools support decryption or a more
permanent type of encryption.
I just came across one of these tools today (I have no experience with
the product or affiliation with them):
http://www.sql-shield.com/
David Gugick - SQL Server MVP
Quest Software
|||Thanks David.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%237MM5gwQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Rogers wrote:
> There are some thrid-party solutions for object encryption. In general,
> you should be working/developing your stored procs (and other objects)
> using external files and version control software like PVCS or SourceSafe.
> It's not advisable to edit objects directly in the database. If you use
> one of the third-party solutions, that should help secure the objects, but
> I don't know if those tools support decryption or a more permanent type of
> encryption.
> I just came across one of these tools today (I have no experience with the
> product or affiliation with them):
> http://www.sql-shield.com/
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>

Encrypting Stored Procedure Code

Hi,
Is it possible to encrypt the code within a stored procedure in
Microsoft SQL Server?
My example is:
I've written a stored procedure. I don't want anyone to be able to
view the contents/code within this stored procedure unless I allow them
to see what is in it.
Thanks,
Darrin> Is it possible to encrypt the code within a stored procedure in
> Microsoft SQL Server?
Sure, but it is not very secure. A google search will yield plenty of
decryption algorithms. For example:
http://searchsqlserver.techtarget.c...1056869,00.html|||Once you encrypt a stored procedure you can not decrypt it (within SQL
Server)
So you can't show it to people who you want to show it to
example
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
But like Aaron said this is very weak, there are some vb apps out there
that will decrypt this
http://sqlservercode.blogspot.com/|||I know there are decryptors out there for SQL2000 but do you know of nay tha
t
have been written for SQL2005?
We encrypt our procs mainly as a safeguard against accidentally altering
them but do need a decryptor when we need to edit them. Our upgrade to
SQL2005 will be delayed untill we have a decryptor.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> Sure, but it is not very secure. A google search will yield plenty of
> decryption algorithms. For example:
> [url]http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1056869,00.html[/url
]
>
>|||>I know there are decryptors out there for SQL2000 but do you know of nay
>that
> have been written for SQL2005?
I will admit that I have only spent 5 minutes looking, but I have yet to
find where the encrypted text is stored, since sys.sql_modules.definition,
sys.syscomments.ctext and object_definition() all return NULL. My guess is,
to make the encoding a little more obscure, that they stuff this into
mssqlsystemresource, or hide it in some obscure system view. So, you may be
able to get to it, you may not.

> We encrypt our procs mainly as a safeguard against accidentally altering
> them but do need a decryptor when we need to edit them.
Isn't that what source control is for? And doesn't that defeat the purpose
of encrypting them in the first place? If someone can accidentally alter a
production procedure, they can also do accidentally after using a decryption
method to view the text. Encryption does not, and will never, solve the
problem of lack of adherence to proper process. My suggestion is to correct
the process.
A|||Thanks for the response. Yes, I fully appreciate the importance of source
control and am confident that our dev department use it properly.
As a production DBA, though, looking after 100+ SQL Servers, when called at
2am to fix a performance "issue", the Decryptor is essential as you dont hav
e
the time to delve into VSS. Also its essential when you need to compare an
existing proc to a proc in Source Safe.
With the move to SQL2005, we will, as suggested, need to look at our
process. It may mean moving away from encrypting procs.
-- cranfield, DBA
"Aaron Bertrand [SQL Server MVP]" wrote:

> I will admit that I have only spent 5 minutes looking, but I have yet to
> find where the encrypted text is stored, since sys.sql_modules.definition,
> sys.syscomments.ctext and object_definition() all return NULL. My guess i
s,
> to make the encoding a little more obscure, that they stuff this into
> mssqlsystemresource, or hide it in some obscure system view. So, you may
be
> able to get to it, you may not.
>
> Isn't that what source control is for? And doesn't that defeat the purpos
e
> of encrypting them in the first place? If someone can accidentally alter
a
> production procedure, they can also do accidentally after using a decrypti
on
> method to view the text. Encryption does not, and will never, solve the
> problem of lack of adherence to proper process. My suggestion is to corre
ct
> the process.
> A
>
>

Encrypting DECRYPTED Stored Procedure.....

Hi
At the moment i don't remember but some times back i found an stored procedure that can DECRYPT
all ENCRYPTED objects in sqlServer2000 ( i will try to put URL here) such as stored procedures,Triggers and even View(s).
Now i'm writing a very confidential StoredProcedure and i don't want to be hack in this way.
Is teher any way to prevent this.Has this Bug been fixed by any of Service Packs.?

Thanks in advance.
Kind Regards.

I think you can easily decrypt SQL encryptions because the SQL rand function is not really random this is not just SQL Server because there are infinite numbers between 6 and 13 but all SQL random functions Oracle and MySQL included can only give you whole numbers which makes it easy to be decrypted. And Microsoft tells you it is not deterministic and not to use it to encrypt anything of value.

That said if you don't want your stored proc decrypted go into the first link and download the free book from Microsoft with ready to use encryption code convert that to CLR stored proc so you know the content cannot be decrypted. The second link is a cleaned up version of the free code in Microsoft book, there are encoding problems with the original code. Hope this helps.

http://msdn2.microsoft.com/en-us/library/aa302415.aspx

http://www.obviex.com/Resources/Samples.aspx

encrypting data

Does MS Server 2000 have any way of encrypting data (not just passwords) in
a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
thanks muchSSL allows you to encrypt data sent in packets so intercepted data is not de
cipherable.|||So the data is encrypted enroute but not in the database?|||There are 3rd party products that will do this but MS SQL will not do it
natively.
Christian Smith
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:1320869D-7219-4799-B4CC-4A5648DD663B@.microsoft.com...
> So the data is encrypted enroute but not in the database?|||Hi,
How passwords can be encrypted in MS SQL? Thanks
-- Jan wrote: --
Does MS Server 2000 have any way of encrypting data (not just passwords) in
a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
thanks much|||Jen,
Password can be encrypted using the undocumented pwdencrypt() and pwdcompare
()
functions. pwdencrypt() is used to create a one-way hash of the data and
pwdcompare() is used to test for comparison.
I believe that Microsoft recommends against using these functions because th
e
algorithms could change from version to version.
If you are looking for a third-party solution check out:
Whamware.Crypt from www.whamware.com
ActiveCrypt from www.activecrypt.com
Encryptionizer for SQL Server from www.netlib.com
Tom
"Jen" wrote:
> Hi,
> How passwords can be encrypted in MS SQL? Thanks
> -- Jan wrote: --
> Does MS Server 2000 have any way of encrypting data (not just passwords)[/col
or]
in a table, or can it only be done by a function/stored procedure? If only b
y a
function - does anyone have any advice/suggestions on a good resource about
this?
> thanks much

encrypting data

Does MS Server 2000 have any way of encrypting data (not just passwords) in a table, or can it only be done by a function/stored procedure? If only by a function - does anyone have any advice/suggestions on a good resource about this?
thanks muchSSL allows you to encrypt data sent in packets so intercepted data is not decipherable.|||So the data is encrypted enroute but not in the database?|||There are 3rd party products that will do this but MS SQL will not do it
natively.
Christian Smith
"Jan" <anonymous@.discussions.microsoft.com> wrote in message
news:1320869D-7219-4799-B4CC-4A5648DD663B@.microsoft.com...
> So the data is encrypted enroute but not in the database?|||Hi
How passwords can be encrypted in MS SQL? Thank
-- Jan wrote: --
Does MS Server 2000 have any way of encrypting data (not just passwords) in a table, or can it only be done by a function/stored procedure? If only by a function - does anyone have any advice/suggestions on a good resource about this
thanks much|||Jen,
Password can be encrypted using the undocumented pwdencrypt() and pwdcompare()
functions. pwdencrypt() is used to create a one-way hash of the data and
pwdcompare() is used to test for comparison.
I believe that Microsoft recommends against using these functions because the
algorithms could change from version to version.
If you are looking for a third-party solution check out:
Whamware.Crypt from www.whamware.com
ActiveCrypt from www.activecrypt.com
Encryptionizer for SQL Server from www.netlib.com
Tom
"Jen" wrote:
> Hi,
> How passwords can be encrypted in MS SQL? Thanks
> -- Jan wrote: --
> Does MS Server 2000 have any way of encrypting data (not just passwords)
in a table, or can it only be done by a function/stored procedure? If only by a
function - does anyone have any advice/suggestions on a good resource about
this?
> thanks muchsql

Encrypting a data

Hi,

I have creating a stored procedure in sql server 2005 to encrypt a parameter value that comes from an asp.net page. this value is of type varchar.

Now i have declared a paramater as varbinary to accept this value in the stored procedure. And encrypting it using symmetric key which is encrypted by certificate.

Now when i enter a value into the textbox in asp.net page. it shows

"implicit conversion from varchar to varbinary not possible" pls tell me what is the solution? do i have to convert the value in vb code while accessing the value of the textbox or do i have to convert the value while passing the value when call stored procedure.

pls tell me the solution so that i don't jave to change any coding in vb.

some solution in sql server itselt.

Thank you

Gaurav

If you're passing a varchar to the stored procedure, then rewrite the stored procedure to accept a varchar.

Thanks
Laurentiu

|||

But i have to encrypt that value - the value that is coming from a varchar parameter.

And encryption works on varbinary data type. so do i need to convert that parameter to varbinary in the stored procedure.

Infact i even tried that but it is showing the same result implicit conversion not allowed.

Pls give me a solution. where i could pass a varchar data type value to a stored procedure and encrypt it in the stored procdure.

Thanks

Gaurav

|||

You can encrypt varchar values; it is not true that encryption works only on varbinary data. See the BOL article for EncryptByKey:

http://msdn2.microsoft.com/en-us/library/ms174361(SQL.90).aspx

varchar is explicitly listed as an accepted type for the cleartext data, so you just need to rewrite your procedure to accept a varchar value. Converting the varchar data to varbinary in the procedure does not help, because the problem happens earlier when you pass the varchar value to the procedure that expects a varbinary. If you don't want to change the procedure code to expect a varchar, then you have to explicitly cast the varchar to varbinary before you pass it to the procedure.

For an example that encrypts varchar data, see http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx. There are other examples on my blog that encrypt varchar data besides that one.

Thanks
Laurentiu

Encrypted Stored Procs.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra Banerjee
Hi Soumitra,
To create an encrypted Stored procedure include the keyword "With
encryption".
For more details visit the following URL :
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
create_4hk5.asp
Example :
The WITH ENCRYPTION clause hides the text of a stored procedure from users.
This example creates an encrypted procedure, uses the sp_helptext system
stored procedure to get information on that encrypted procedure, and then
attempts to get information on that procedure directly from the syscomments
table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Do you mean create as in CREATE PROCEDURE.. or in executing?
Persumably there is some tiny overhead in encrypting the text for syscomments but you won't notice it. I've never seen nor head of performance issues. Out of curiosity, why the question?
Alicia
http://www.sqlporn.co.uk

Encrypted Stored Procs.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra BanerjeeHi Soumitra,
To create an encrypted Stored procedure include the keyword "With
encryption".
For more details visit the following URL :
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
create_4hk5.asp
Example :
The WITH ENCRYPTION clause hides the text of a stored procedure from users.
This example creates an encrypted procedure, uses the sp_helptext system
stored procedure to get information on that encrypted procedure, and then
attempts to get information on that procedure directly from the syscomments
table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Do you mean create as in CREATE PROCEDURE.. or in executing?
Persumably there is some tiny overhead in encrypting the text for syscomment
s but you won't notice it. I've never seen nor head of performance issues. O
ut of curiosity, why the question?
Alicia
http://www.sqlporn.co.uk

Encrypted Stored Procs.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra BanerjeeHi Soumitra,
To create an encrypted Stored procedure include the keyword "With
encryption".
For more details visit the following URL :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
create_4hk5.asp
Example :
The WITH ENCRYPTION clause hides the text of a stored procedure from users.
This example creates an encrypted procedure, uses the sp_helptext system
stored procedure to get information on that encrypted procedure, and then
attempts to get information on that procedure directly from the syscomments
table.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'encrypt_this' AND type = 'P')
DROP PROCEDURE encrypt_this
GO
USE pubs
GO
CREATE PROCEDURE encrypt_this
WITH ENCRYPTION
AS
SELECT *
FROM authors
GO
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.|||Do you mean create as in CREATE PROCEDURE.. or in executing
Persumably there is some tiny overhead in encrypting the text for syscomments but you won't notice it. I've never seen nor head of performance issues. Out of curiosity, why the question
Alici
http://www.sqlporn.co.uk|||It does take 'slightly' longer yes.
Personally I feel that Encryping SP is a waste of time as
the encryption SQL Server uses is not very good and can be
broken quite easily.
J
>--Original Message--
>Hi Everybody,
>I have a quick question as follows:
>Does it take longer to create a stored procedure when it
is encrypted versus
>when it is not encrypted?
>Thanks.
>Regards,
>Soumitra Banerjee
>
>.
>

encrypted stored procedure

how to edit encrypted stored procedureYou cannot. You should've kept a copy of the source somewhere safe, before
encrypting the stored procedure.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Marc" <marvistal@.yahoo.com> wrote in message
news:14E47C34-9725-428D-939B-EB8CC411B98C@.microsoft.com...
how to edit encrypted stored procedure|||You can decrypt them back.
Take a look at http://www.sql-shield.com/decrypt-stored-procedure.html
"Marc" <marvistal@.yahoo.com> wrote in message
news:14E47C34-9725-428D-939B-EB8CC411B98C@.microsoft.com...
> how to edit encrypted stored procedure

Encrypted Stored Proc.

Hi Everybody,
I have a quick question as follows:
Does it take longer to create a stored procedure when it is encrypted versus
when it is not encrypted?
Thanks.
Regards,
Soumitra Banerjee
Perhaps - some time will be taken up doing the encryption. I'm wondering why
you would be concerned about how long it takes to create a storred
procedure?
Jim
"Soumitra Banerjee" <sbanerjee@.epacesoftware.com> wrote in message
news:ebbglmTOEHA.2996@.TK2MSFTNGP12.phx.gbl...
> Hi Everybody,
> I have a quick question as follows:
> Does it take longer to create a stored procedure when it is encrypted
versus
> when it is not encrypted?
> Thanks.
> Regards,
> Soumitra Banerjee
>

Encrypted Store Procedure

Yesterday, I would like to test the Encrypted Store
Procedure with the opinion "ENCRYPTION". After appling the
opinion, I found I can't view the source code of the Store
Procedure.
How can I review the source code of the Stroe Procedure Or
edit it? It is very emergent.Look here:
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngW
Id=5
HTH, Jens Süßmeyer.
"Alistair" <alistair.wang@.corp.elong.com> schrieb im Newsbeitrag
news:03d001c3781d$c53cd9b0$a301280a@.phx.gbl...
> Yesterday, I would like to test the Encrypted Store
> Procedure with the opinion "ENCRYPTION". After appling the
> opinion, I found I can't view the source code of the Store
> Procedure.
> How can I review the source code of the Stroe Procedure Or
> edit it? It is very emergent.|||To add to the response by Jens, it's a good practice to keep your DDL
scripts in a source control system. This ensures you always have a copy
and versioning capabilities as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Alistair" <alistair.wang@.corp.elong.com> wrote in message
news:03d001c3781d$c53cd9b0$a301280a@.phx.gbl...
> Yesterday, I would like to test the Encrypted Store
> Procedure with the opinion "ENCRYPTION". After appling the
> opinion, I found I can't view the source code of the Store
> Procedure.
> How can I review the source code of the Stroe Procedure Or
> edit it? It is very emergent.

Encrypted object is not transferable

Hi,
i m getting error "Encrypted object is not transferable, and script cannot be generated", while trying to open few stored procedure. It is extemly important for me to see the script of the sp as I need to study it.
help?
regards,
sim sim
Hi
The SP you are trying to look at is Encrypted.
There are a few tools on the NET to decrypt the SP. Look for "sql decrypt
sp" on Google.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:930C6B12-8E72-4A27-BB16-12E744937B82@.microsoft.com...
> Hi,
> i m getting error "Encrypted object is not transferable, and script
cannot be generated", while trying to open few stored procedure. It is
extemly important for me to see the script of the sp as I need to study it.
> help?
> regards,
> sim sim
>
>

Encrypted object is not transferable

Hi,
i m getting error "Encrypted object is not transferable, and script cannot b
e generated", while trying to open few stored procedure. It is extemly impor
tant for me to see the script of the sp as I need to study it.
help'
regards,
sim simHi
The SP you are trying to look at is Encrypted.
There are a few tools on the NET to decrypt the SP. Look for "sql decrypt
sp" on Google.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"sim sim" <simsim@.discussions.microsoft.com> wrote in message
news:930C6B12-8E72-4A27-BB16-12E744937B82@.microsoft.com...
> Hi,
> i m getting error "Encrypted object is not transferable, and script
cannot be generated", while trying to open few stored procedure. It is
extemly important for me to see the script of the sp as I need to study it.
> help'
> regards,
> sim sim
>
>

EncryptByKey Function always returning null

When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption:

ALTER PROCEDURE [dbo].[ProcMyProc](@.ClearText nvarchar(50))
AS
BEGIN

OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128';

Declare @.Temp varbinary(8000);

Set @.Temp =EncryptByKey(Key_GUID('MyKey'),@.ClearText);

close symmetric key MyKey;

select @.Temp as temp;
END

The result I get for this procedure is null. Is there something wrong with this code?

Hey Muhammad,

I tried out your code and it seems to work for me. Can you try executing your script outside of the proc? You can add in "select * from sys.openkeys" after your open statement just to check to see if the key was actually opened.

Sung

|||

Also, if this still doesn't work, can you let us know what OS version you are using? Is it XP, 2000, or 2003?

Thanks
Laurentiu

|||I found the cause of the problem. I was misspelling the name of the key when passing it to the Key_GUID() function. but shouldn't this give an error saying there's no key with this name instead of just returning null?|||

Hi Muhammad,

The way our built-ins work is that they return null instead of returning an error. I don't remember why exactly this is the case, but I think it has to do with backwards compatibility and parsing logic of the built-ins.

Sung

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

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