|
Home > Archive > microsoft.public.sqlserver.server > June 2002 > Stop stored procedure from displaying in the trace window.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stop stored procedure from displaying in the trace window.
|
|
| Anant Rege 2002-06-24, 6:25 pm |
| Hello
Can anybody tell me how to stop a stored procedure being displayed in the
SQL trace window? Our product installs a few encrypted stored procedures on
the client's server. They are encrypted so that the client can not view the
code behind them. However if the client uses the SQL server trace utility
and decides to step through the code while the procedure is run, the
complete code is displayed in the window. I would like to know how it can be
suppressed.
Thanks
Anant
| |
| Eric Sabine 2002-06-24, 6:38 pm |
| Can't stop it from displaying in trace window.
Since I don't know what version you have, I will assume 7. If you create a
sproc using "with encryption" you can get it out of Enterprise Manager by
generating the sql from the properties of the sproc. It will generate the
SQL with the decrypt keyword and show you what I guess is a hash.
Then send this to your client and when they run it and trace it, they'll see
the hash, not your TSQL.
All this said now, I should remind you that encrypted sprocs can be
de-encrypted with minimal effort.
hth
Eric
"Anant Rege" <arege@remove.this.sympatico.ca> wrote in message
news:ukL7709GCHA.2648@tkmsftngp11...
> Hello
>
> Can anybody tell me how to stop a stored procedure being displayed in the
> SQL trace window? Our product installs a few encrypted stored procedures
on
> the client's server. They are encrypted so that the client can not view
the
> code behind them. However if the client uses the SQL server trace utility
> and decides to step through the code while the procedure is run, the
> complete code is displayed in the window. I would like to know how it can
be
> suppressed.
>
> Thanks
>
> Anant
>
>
>
| |
| California Dreamin 2002-06-24, 7:25 pm |
| How can i dump the top XXX records of a table enough to fill a CD only?
"Anant Rege" <arege@remove.this.sympatico.ca> wrote in message
news:ukL7709GCHA.2648@tkmsftngp11...
> Hello
>
> Can anybody tell me how to stop a stored procedure being displayed in the
> SQL trace window? Our product installs a few encrypted stored procedures
on
> the client's server. They are encrypted so that the client can not view
the
> code behind them. However if the client uses the SQL server trace utility
> and decides to step through the code while the procedure is run, the
> complete code is displayed in the window. I would like to know how it can
be
> suppressed.
>
> Thanks
>
> Anant
>
>
>
| |
| Anant Rege 2002-06-24, 8:25 pm |
| The SQL server version is 7. And I made one error in the original post. The
stored procedure code is displayed in the trace window when 'the stored
procedure is created with encryption' and not when it is run. And your
suggestion does not work. It does not create the SQL for an encrypted
object.
May be I was not very clear in my original post. Let me try again. We have a
shrink wrapped product that we sell to various customers. When the product
is installed at the client site, we run a script (from the compiled
installation program) to create the required stored procedures on the
client's server. All these procedures are created with 'with encryption'
keyword. Once they are created, they stay encrypted. However at the time of
these being created, if the installer opens the SQL trace window and debugs
through the code, the complete syntax of the stored procedure is visible in
the trace window *while* it is being created. And I would like to suppress
that. And I know it is possible to do it because I have seen it working like
this for some other application.
Anant
"Eric Sabine" <mopar41@hotmail.com> wrote in message
news:1SOR8.62688$ZQ6.640431@twister.southeast.rr.com...
> Can't stop it from displaying in trace window.
>
> Since I don't know what version you have, I will assume 7. If you create
a
> sproc using "with encryption" you can get it out of Enterprise Manager by
> generating the sql from the properties of the sproc. It will generate the
> SQL with the decrypt keyword and show you what I guess is a hash.
>
> Then send this to your client and when they run it and trace it, they'll
see
> the hash, not your TSQL.
>
> All this said now, I should remind you that encrypted sprocs can be
> de-encrypted with minimal effort.
>
> hth
> Eric
>
>
> "Anant Rege" <arege@remove.this.sympatico.ca> wrote in message
> news:ukL7709GCHA.2648@tkmsftngp11...
> > Hello
> >
> > Can anybody tell me how to stop a stored procedure being displayed in
the
> > SQL trace window? Our product installs a few encrypted stored procedures
> on
> > the client's server. They are encrypted so that the client can not view
> the
> > code behind them. However if the client uses the SQL server trace
utility
> > and decides to step through the code while the procedure is run, the
> > complete code is displayed in the window. I would like to know how it
can
> be
> > suppressed.
> >
> > Thanks
> >
> > Anant
> >
> >
> >
>
>
| |
|
| "Anant Rege" <arege@remove.this.sympatico.ca> wrote in message
news:urpSx0#GCHA.1968@tkmsftngp11...
> May be I was not very clear in my original post. Let me try again. We have
a
> shrink wrapped product that we sell to various customers. When the product
> is installed at the client site, we run a script (from the compiled
> installation program) to create the required stored procedures on the
> client's server. All these procedures are created with 'with encryption'
> keyword. Once they are created, they stay encrypted. However at the time
of
> these being created, if the installer opens the SQL trace window and
debugs
> through the code, the complete syntax of the stored procedure is visible
in
> the trace window *while* it is being created. And I would like to suppress
> that. And I know it is possible to do it because I have seen it working
like
> this for some other application.
The first thing to recommend is not giving them the scripts to create your
SPs.
Just deliver a detached database that they can attach and use.
The downside to this being that your database may be designed to run in a
different environment to theirs, things like the collation of master do have
an impact on other databases.
Alternatively, have you tried providing a detached database containing
encrypted SPs that create your SPs using dynamic SQL.
I don't know if this would work but it must be worth a try.
Finally, if you have seen someone else hide things from profiler try to work
out how they did it.
If you can tell us where to find a secret script I'm sure someone can work
out what it has done.
Bear in mind that you'll never get the execution plans hidden.
J.T.
--
James Talbut, MCSD, MCDBA. Head of Technology
CAST Systems Limited, 2 Pembroke Avenue, Waterbeach, Cambridge, CB5 9QR
email: j.talbut@cast-systems.com website: www.cast-systems.com
| |
| Anant Rege 2002-06-25, 8:25 pm |
|
"J.T." <J.Talbut@cast-systems.com> wrote in message
news:e5KTVuCHCHA.1168@tkmsftngp13...
> "Anant Rege" <arege@remove.this.sympatico.ca> wrote in message
> news:urpSx0#GCHA.1968@tkmsftngp11...
> > May be I was not very clear in my original post. Let me try again. We
have
> a
> > shrink wrapped product that we sell to various customers. When the
product
> > is installed at the client site, we run a script (from the compiled
> > installation program) to create the required stored procedures on the
> > client's server. All these procedures are created with 'with encryption'
> > keyword. Once they are created, they stay encrypted. However at the time
> of
> > these being created, if the installer opens the SQL trace window and
> debugs
> > through the code, the complete syntax of the stored procedure is visible
> in
> > the trace window *while* it is being created. And I would like to
suppress
> > that. And I know it is possible to do it because I have seen it working
> like
> > this for some other application.
>
> The first thing to recommend is not giving them the scripts to create your
> SPs.
> Just deliver a detached database that they can attach and use.
> The downside to this being that your database may be designed to run in a
> different environment to theirs, things like the collation of master do
have
> an impact on other databases.
>
You don't think I am giving the users scripts to create the encrypted stored
procedures. Do you? That would be the dumbest possible thing to do.
> Alternatively, have you tried providing a detached database containing
> encrypted SPs that create your SPs using dynamic SQL.
> I don't know if this would work but it must be worth a try.
>
Now you are thinking somewhat. Though I am not giving them any database,
there are other ways to pass dynamic SQL statements to the server. That is
exactly what is happening in the installation program. The syntax for the
stored procedures are created dynamically in a *compiled installation
program*. And now stating my problem again, if the user keeps the trace
window open , he can see the *dynamically created SQL statements* in that
window. Your detached database solution will not work any differently.
> Finally, if you have seen someone else hide things from profiler try to
work
> out how they did it.
If I have sufficient time to spend on figuring this out, I will certainly do
it. Though I was hoping, when I posted my query to this newsgroup, that
somebody who already knows the solution will be able to help me out and save
me some time.
Thanks for trying.
> If you can tell us where to find a secret script I'm sure someone can work
> out what it has done.
>
I am not worried about that. I want to know how to keep it secret.
> Bear in mind that you'll never get the execution plans hidden.
>
That's fine. I just want to hide the code behind my stored procedure.
Anant
> J.T.
> --
| |
|
| "Anant Rege" <arege@remove.this.sympatico.ca> wrote in message
news:#$EKgPLHCHA.2628@tkmsftngp11...
> You don't think I am giving the users scripts to create the encrypted
stored
> procedures. Do you? That would be the dumbest possible thing to do.
I don't mean to imply that you are dumb, but unless you are encrypting the
scripts that are in your installation program that is exactly what you are
doing.
Not as explicitly as providing them with a bunch of .sql text files, but it
is all in there for them to read.
To prevent a flame war generating I will assume that you have protected them
in some way.
> > Alternatively, have you tried providing a detached database containing
> > encrypted SPs that create your SPs using dynamic SQL.
> > I don't know if this would work but it must be worth a try.
> Now you are thinking somewhat. Though I am not giving them any database,
> there are other ways to pass dynamic SQL statements to the server. That is
> exactly what is happening in the installation program. The syntax for the
> stored procedures are created dynamically in a *compiled installation
> program*. And now stating my problem again, if the user keeps the trace
> window open , he can see the *dynamically created SQL statements* in that
> window. Your detached database solution will not work any differently.
What happens if you copy a row from syscomments on an attached database and
then recompile it?
Finally, given the weakness of the encryption provided by SQL Server is it
worth bothering?
--
J.T.
--
James Talbut, MCSD, MCDBA. Head of Technology
CAST Systems Limited, 2 Pembroke Avenue, Waterbeach, Cambridge, CB5 9QR
email: j.talbut@cast-systems.com website: www.cast-systems.com
|
|
|
|
|