Advantages and disadvantages of stored proc encryption?

Security is good for me as for developer. But what are advantages and
divantages of stored proc encryption?
May be encrypted sp's are executing slow?Though encryption is good from security point of view, SQL Server 2000
stored procs can be easily decrypted. In terms of performance there's no
difference, as the execution plan will be the same. But if you do encrypt,
make sure you have the source readily available.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
Security is good for me as for developer. But what are advantages and
divantages of stored proc encryption?
May be encrypted sp's are executing slow?|||...also, troubleshooting in the production environment becomes a bit more d
ifficult. Like execute
the proc, compare the source (which you don't have) to the execution plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:udzTFmxnFHA.3540@.TK2MSFTNGP10.phx.gbl...
> Though encryption is good from security point of view, SQL Server 2000
> stored procs can be easily decrypted. In terms of performance there's no
> difference, as the execution plan will be the same. But if you do encrypt,
> make sure you have the source readily available.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> divantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>
>|||Oleg,
better is upload the code and its revisions to VSS
"Narayana Vyas Kondreddi" wrote:

> Though encryption is good from security point of view, SQL Server 2000
> stored procs can be easily decrypted. In terms of performance there's no
> difference, as the execution plan will be the same. But if you do encrypt,
> make sure you have the source readily available.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> divantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>
>|||Thank you All.
By the way,
what about encryption in sql2005 editions?
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> divantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>|||The encryption is trivial to break with freely available tools.
Encrypted code has very little to do with real security. It's pretty
much a cosmetic feature that *may* limit low-level meddling by
incompetent users but won't protect you against smart hackers.
David Portas
SQL Server MVP
--

ADO error handling when connecting to SQL Server 2000

I use Delphi to connect to SQL Server 2000 using the ADO components. I'm
executing stored procedure that makes some calls to another stored procs. In
one of the sub called stored procs foreign key is violated and error message
is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
constraint ..' i see it in the SQL Query analyser).
When i use this connection string:
Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
ID=sa;Initial Catalog=TechDB;Data Source=.
the error is not reported in my application, it seems that the driver
ignores the error message. I have no Exception generated. So i tried with
another driver for SQL Server (ODBC). The ADO connection string was:
Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
Source=LOCA_RMP;Initial Catalog=TechDB
This one is reporting error in the above situation (i get Exception with
'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ). But
the problem is that it generates exception when 'print' command is executed,
so it thinks that every message is an error. Besides the connecting thru
ODBC DSN seems to be slower and i think it's not native for ADO .
I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
messy to use "if @.@.error<>0 set @.local_error=1" after every line of code. Is
there another way to do this? Am i supposed to raiserror at the end of the
stored proc?
Any suggestions are appreciated,
Georgi PeshterskiFor a complete discussion on Error handling in Stored Procs, see
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"George Peshterski" <unseen@.mail.bg> wrote in message
news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
> I use Delphi to connect to SQL Server 2000 using the ADO components.
> I'm
> executing stored procedure that makes some calls to another stored procs.
> In
> one of the sub called stored procs foreign key is violated and error
> message
> is generated ('INSERT statement conflicted with COLUMN FOREIGN KEY
> constraint ..' i see it in the SQL Query analyser).
> When i use this connection string:
> Provider=SQLOLEDB.1;Password="";Persist Security Info=True;User
> ID=sa;Initial Catalog=TechDB;Data Source=.
> the error is not reported in my application, it seems that the driver
> ignores the error message. I have no Exception generated. So i tried with
> another driver for SQL Server (ODBC). The ADO connection string was:
> Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data
> Source=LOCA_RMP;Initial Catalog=TechDB
> This one is reporting error in the above situation (i get Exception
> with
> 'INSERT statement conflicted with COLUMN FOREIGN KEY constraint ...' ).
> But
> the problem is that it generates exception when 'print' command is
> executed,
> so it thinks that every message is an error. Besides the connecting thru
> ODBC DSN seems to be slower and i think it's not native for ADO .
> I know that @.@.ERROR is used to detect errors in SQL Server 2000 but it's
> messy to use "if @.@.error<>0 set @.local_error=1" after every line of code.
> Is
> there another way to do this? Am i supposed to raiserror at the end of the
> stored proc?
> Any suggestions are appreciated,
> Georgi Peshterski
>|||Thank you for the useful links, i'm reading the articles now and there are
many things i didn't knew.
As for my problem i found that the foreign key error that ADO hides happens
in INSTEAD OF trigger in the subprocedures. I read that error in trigger
cancels the batch but i'm still curious why ADO isn't reporting it to me,
i'm going to examine carefully the articles about this problem ...
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> For a complete discussion on Error handling in Stored Procs, see
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "George Peshterski" <unseen@.mail.bg> wrote in message
> news:eF%234vneDFHA.2216@.TK2MSFTNGP09.phx.gbl...
procs.
with
it's
code.
the
>|||Make sure you have SET NOCOUNT ON at the beginning of your procs and
triggers. This will suppress DONE_IN_PROC messages that are returned as
empty closed recordsets in ADO.
Hope this helps.
Dan Guzman
SQL Server MVP
"George Peshterski" <unseen@.mail.bg> wrote in message
news:e6k39MgDFHA.392@.TK2MSFTNGP14.phx.gbl...
> Thank you for the useful links, i'm reading the articles now and there are
> many things i didn't knew.
> As for my problem i found that the foreign key error that ADO hides
> happens
> in INSTEAD OF trigger in the subprocedures. I read that error in trigger
> cancels the batch but i'm still curious why ADO isn't reporting it to me,
> i'm going to examine carefully the articles about this problem ...
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%23B7yxteDFHA.3688@.TK2MSFTNGP14.phx.gbl...
> procs.
> with
> it's
> code.
> the
>

ADO client disconnects after running a long query

I am having a problem executing long running queries from an ASP application
which connects to SQL Server 2000. Basically, I have batches of queries that
are run using ADO in a loop written in VBScript. This works pretty well
until the execution time of a single query starts to exceed some threshold,
which I am trying to narrow down. I can typically run 2 - 10 queries in a
loop, with the run time being anywhere from under a minute to an hour or
more. Now that this application is being subjected to run against some large
databases (25 - 40G), I'm having problems getting the application to
continue beyond the first query if it takes a while to run.

I used SQL Profiler to try to diagnose what was going on. I can see the
query executes to completion, but immediately after completing I can see an
"Audit Logout" message, which apparently means that the client has
disconnected. The query durations vary from 45 or 50 minutes to up to over
90 minutes. I have the ADO connection and query timeouts set to very large
values, e.g. 1000 minutes, so I can't think its that. My guess is that there
is some IIS setting or timeout that I am running up against and the
connection to SQL Server is just dropped for some reason.

The configuration is

NT 4.0 SP6
SQL Server 2000 SP3
IIS 4.0
Internet Explorer 5.5

I'm only running into this problem on the very largest databases we run
against. The vast majority continue to function properly, but this is going
to happen more often as time goes on the databases continue to grow in size.

Any advice is appreciated,

-GaryGary wrote:
> I am having a problem executing long running queries from an ASP
> application which connects to SQL Server 2000. Basically, I have
> batches of queries that are run using ADO in a loop written in
> VBScript. This works pretty well until the execution time of a single
> query starts to exceed some threshold, which I am trying to narrow
> down. I can typically run 2 - 10 queries in a loop, with the run time
> being anywhere from under a minute to an hour or more. Now that this
> application is being subjected to run against some large databases
> (25 - 40G), I'm having problems getting the application to continue
> beyond the first query if it takes a while to run.
> I used SQL Profiler to try to diagnose what was going on. I can see
> the query executes to completion, but immediately after completing I
> can see an "Audit Logout" message, which apparently means that the
> client has disconnected. The query durations vary from 45 or 50
> minutes to up to over 90 minutes.

!??!?
Don't use ASP for this. Why tie up a thread on your web server for that
long??

I'm willing to be that this entire task could be done within a scheduled
stored procedure.

> I have the ADO connection and query
> timeouts set to very large values, e.g. 1000 minutes, so I can't
> think its that. My guess is that there is some IIS setting or timeout

Probably ScriptTimeout

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:udDZnd$fEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Don't use ASP for this. Why tie up a thread on your web server for that
> long??

I have no choice at the moment. I realize it's not an optimal solution. Also
there are rarely more than a couple requests at a time going on.

> I'm willing to be that this entire task could be done within a scheduled
> stored procedure.

Some of it is scheduled, but it's designed to work on-demand as well.

> > I have the ADO connection and query
> > timeouts set to very large values, e.g. 1000 minutes, so I can't
> > think its that. My guess is that there is some IIS setting or timeout
> Probably ScriptTimeout

This is already set to a really large value. (way more than an hour) But
thanks for the suggestions.

-Gary|||"Gary" wrote:
> My guess is that there is some IIS setting or
> timeout that I am running up against and the
> connection to SQL Server is just dropped for
> some reason.

Gary,

If your ASP times out, you definitely want the connection to stop since
there's no reason for it run anymore :)

In any event, there is an IIS timeout setting, but you can change it in your
script:

<%
Server.ScriptTimeout = Number_Of_Seconds
%
Craig|||Gary wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:udDZnd$fEHA.1972@.TK2MSFTNGP09.phx.gbl...
>> Don't use ASP for this. Why tie up a thread on your web server for
>> that long??
>>
> I have no choice at the moment. I realize it's not an optimal
> solution. Also there are rarely more than a couple requests at a time
> going on.
>> I'm willing to be that this entire task could be done within a
>> scheduled stored procedure.
> Some of it is scheduled, but it's designed to work on-demand as well.

You can kick off a scheduled job that runs a stored procedure using
sp_start_job. To pass parameter values to the procedure, you can insert them
into a control table where they can be read by the stored procedure.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

AdjustTokenPrivileges Error?

Hi,

I have created two packages.. a child package and a main package that is responsable by executing all his childs in a specific order...

What start happening is that the child when executed alone is ok, but the the main package executes it... then i get a DOS Windows saying "AdjustTokenPrivileges () Failed (00000514)"

What can be causing this? I haven't changed anything and the package was executing right some days ago...

Best Regards,

Luis Sim?es

I have one package that is causing this error as well. Anyone know why? It only generates the error when I try to insert into a database. otherwise it does not generate this error and runs fine.

AdjustTokenPrivileges Error?

Hi,

I have created two packages.. a child package and a main package that is responsable by executing all his childs in a specific order...

What start happening is that the child when executed alone is ok, but the the main package executes it... then i get a DOS Windows saying "AdjustTokenPrivileges () Failed (00000514)"

What can be causing this? I haven't changed anything and the package was executing right some days ago...

Best Regards,

Luis Sim?es

I have one package that is causing this error as well. Anyone know why? It only generates the error when I try to insert into a database. otherwise it does not generate this error and runs fine.