Is this the correct group to post problems about proxy accounts?

I have already posted this but have not received an answer.
Hi there,
I have set the proxy user details using
xp_sqlagent_proxy_account 'set' and the command completes successfully, so
the logon details are correct. I have also logged onto the server using
those details just to make sure and that works OK. However, when I fire a
trigger that is making a call to xp_cmd_shell, I get the message "Logon
failure: unknown user name or bad password" Anyone know what the issue is
here?
This is SQL Server 2000 SP3 on Windows 2000 SP4.Try resetting the proxy account using the user and password
you can log in with - the easiest way to reset is to select
the properties of SQL Server Agent, on the Job Control tab,
click on Reset Proxy Account.
-Sue
On Wed, 27 Jul 2005 15:17:40 +0100, "Waldy"
<waldy@.notmail.com> wrote:

>I have already posted this but have not received an answer.
>Hi there,
> I have set the proxy user details using
>xp_sqlagent_proxy_account 'set' and the command completes successfully, so
>the logon details are correct. I have also logged onto the server using
>those details just to make sure and that works OK. However, when I fire a
>trigger that is making a call to xp_cmd_shell, I get the message "Logon
>failure: unknown user name or bad password" Anyone know what the issue is
>here?
>This is SQL Server 2000 SP3 on Windows 2000 SP4.
>

Is this statement vulnerable to code injection?

I found a bit of code my comapny got froma third party group that I
thought was vulnerable to code injection but I can't prove it since I
can't figure out a way to hack it. I can point the boss to sources that
say this type of programming is a Bad Thing (TM) but they want more.
Can someone help me?
The code is in asp and looks something like the following:
sLookupText = <users free text input>
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
sLookupText = Trim(sLookupText)
...
sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
Have com object execute sql.
The tricky part is that the dynamic sql is executed by the same third
party venders com objects so I don't see what happens to the query
between the time the asp hands it off and it is executed.
This is the error I get from the com obvject when I run it with the
input 1/'';select * from x;
Zero records match the criteria '1/'';select * from x;'
If I take the exact same query and run it in Query Analyzer I get the
message
Unclosed quotation mark before the character string ...
Which says I'm wrong when I think \' escapes the ' mark. So now I'm
wondering if maybe the code IS safe since the com object is sending a
different message and all the quotes are doubled in the message and all
the % are stripped.
Do the two lines
sLookupText = Replace(sLookupText,"'","''")
sLookupText = Replace(sLookupText,"%","")
make it safe?
Thoughts?Michael, the only way to know what the com object sent to SQL Server is usin
g
SQL Profiler. I think the error show the com object is replacing de ' with
'', but using SQL Profiler is the easer way to know.|||sSQL = "select <parameter list> from table where <some field> like " +
sLookupText + "% order by <order clause>"
sLookupText = "1;-- ALTER DATABASE MSDB SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;GO;DROP DATABASE MSDB;--"
Upps... That can be something very wacky. Dynamic SQL is the hell for
security, so better have a chat with the vendor :-)
HTH, Jens Suessmeyer.|||I, uh, don't think I want to test that particular example! that drop
database part looks scary, though I'm guessing that rollback immediate
does something to undo it?
Anyway, from what I can tell, with your intput the query will be
coverted into.
select <parameter list> from table where <some field> like '1;-- ALTER
DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;GO;DROP DATABASE
MSDB;--%' order by <order clause>
As far as I can tell you never escaped out of the single quotes! Why do
you think any sql is injected?|||Michael wrote:
> I found a bit of code my comapny got froma third party group that I
> thought was vulnerable to code injection but I can't prove it since I
> can't figure out a way to hack it. I can point the boss to sources
> that say this type of programming is a Bad Thing (TM) but they want
> more. Can someone help me?
> The code is in asp and looks something like the following:
> sLookupText = <users free text input>
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> sLookupText = Trim(sLookupText)
> ...
> sSQL = "select <parameter list> from table where <some field> like " +
> sLookupText + "% order by <order clause>"
> Have com object execute sql.
> The tricky part is that the dynamic sql is executed by the same third
> party venders com objects so I don't see what happens to the query
> between the time the asp hands it off and it is executed.
> This is the error I get from the com obvject when I run it with the
> input 1/'';select * from x;
> Zero records match the criteria '1/'';select * from x;'
> If I take the exact same query and run it in Query Analyzer I get the
> message
> Unclosed quotation mark before the character string ...
> Which says I'm wrong when I think ' escapes the ' mark. So now I'm
> wondering if maybe the code IS safe since the com object is sending a
> different message and all the quotes are doubled in the message and
> all the % are stripped.
> Do the two lines
> sLookupText = Replace(sLookupText,"'","''")
> sLookupText = Replace(sLookupText,"%","")
> make it safe?
>
They make it safe when the hacker uses ' to close the quote. However, what
if he gets clever and uses the char function? OK you can do some
validation/replacing on that. But then, what if he switches to one of the
other techniques to be found in these articles:
http://www.nextgenss.com/papers/adv...l_injection.pdf
http://www.nextgenss.com/papers/mor...l_injection.pdf
The safest way to run the above query is by using parameters. There is no
way to inject sql if data is being passed via parameters.
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.|||Llike Bob already said, he could use the CHAR function.
BTW, Rollback won=B4t do any undo of the ALTER DATABASE rather than
rolling back all transaction which are currently running.
HTH, jens Suessmeyer.|||Well, I found another section of the web site with a less protected
query. I was able to convert the query (where the stuff in <> is either
table specific information not relevant to the question or user
input)...
select <columnlist> from <table> where <column> like '<users input>%'
order by <columnlist>
to
select <columnlist> from <table> where <column> like '%'; update
<table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
by <columnlist>
by entering
%'; update <table2> set <column> = 1 where <pk column> = <pk
value>;commit;--
as the input. Nice, huh! But when I checked the database nothing
happened. When I went to the DBA he used Embarcadero DBArtisan
(whatever that is) to verify that the test was sent as expected. It
was, but no injection attack worked! Any idea why? Does sqlserver allow
multiple commands on a single line? I seem to recall on my Internet
travels that one guru said that it didn't.
I really don't like seeing a dynamic query being constructed like this
but I can't seem to prove it is a problem :( I feel it is WRONG in my
bones, but I need proof to leverage a change.|||Can anyone explain why the injection attack did not work?|||Can you post a repro, so we have something to test?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michael" <miteke@.gmail.com> wrote in message
news:1143732749.601504.212240@.e56g2000cwe.googlegroups.com...
> Can anyone explain why the injection attack did not work?
>|||On 29 Mar 2006 11:53:30 -0800, Michael wrote:

>Well, I found another section of the web site with a less protected
>query. I was able to convert the query (where the stuff in <> is either
>table specific information not relevant to the question or user
>input)...
>select <columnlist> from <table> where <column> like '<users input>%'
>order by <columnlist>
>to
>select <columnlist> from <table> where <column> like '%'; update
><table2> set <column> = 1 where <pk column> = <pk value>;commit;--order
>by <columnlist>
>by entering
>%'; update <table2> set <column> = 1 where <pk column> = <pk
>value>;commit;--
>as the input. Nice, huh! But when I checked the database nothing
>happened. When I went to the DBA he used Embarcadero DBArtisan
>(whatever that is) to verify that the test was sent as expected. It
>was, but no injection attack worked! Any idea why? Does sqlserver allow
>multiple commands on a single line? I seem to recall on my Internet
>travels that one guru said that it didn't.
Hi Michael,
SQL Server definitely allows multiple commands on one line.
Have you checked that the command that was eventually actually sent to
the server for execution was exactly as above? I'd recommend you to
insert a PRINT statement in the application just before the SQL gets
sent to the server. Or, if you can't touch the application, set up
Profiler to run a trace. A smart parser that removes quotes, reserverd
words, punctuation marks and such would have prevented your attempt to
inject SQL.
If you have verified that this is the actual code that ran, then you can
be 100% sure that SQL Server tried to do the update. It might have
failed because the SQL is executed in the context of an account with
limited permissions (that would have resulted in an error - did you see
an error when you tested it? Maybe the app has intercepted the error
message? Again, running Profiler might reveal more info). Another reason
why this update might fail is an AFTER trigger initiating a ROLLBACK, or
an INSTEAD OF trigger simply disregarding your change; in those cases,
you don't even get an error message.
Hugo Kornelis, SQL Server MVP

Is this query possible?

I want to sum up the amt field where the acct or group is A1000. Since
A1110 belongs to group A1100, and A1100 belongs to group A1000 it
should pick those amts up as well. The total should be 600. I have
the sql below but I can't figure how to pick up acct A1110 since it
should be rolling up to A1000 because of A1100.

select sum(amt) from tbl_bal
where acct='A1000' or group='A1000';

tbl_bal
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300I think this violates some of the basic principles of relational model
(not sure which ones). However I would love to see some sort of
solution to this problem though. There is some type of process to break
this into normal table with right groupings.

mouac01@.yahoo.com wrote:

Quote:

Originally Posted by

I want to sum up the amt field where the acct or group is A1000. Since
A1110 belongs to group A1100, and A1100 belongs to group A1000 it
should pick those amts up as well. The total should be 600. I have
the sql below but I can't figure how to pick up acct A1110 since it
should be rolling up to A1000 because of A1100.
>
select sum(amt) from tbl_bal
where acct='A1000' or group='A1000';
>
tbl_bal
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300

|||mouac01@.yahoo.com wrote:

Quote:

Originally Posted by

I want to sum up the amt field where the acct or group is A1000. Since
A1110 belongs to group A1100, and A1100 belongs to group A1000 it
should pick those amts up as well. The total should be 600. I have
the sql below but I can't figure how to pick up acct A1110 since it
should be rolling up to A1000 because of A1100.
>
select sum(amt) from tbl_bal
where acct='A1000' or group='A1000';
>
tbl_bal
acctgroupamt
A1000A1000100
A1100A1000200
A1110A1100300

I would agree with othell...@.yahoo.com about the design...at least its
not clear.
In SQL Server 2005 this approach might work depending on the tables
design.

WITH Balance(Acct, amt)
AS
(
SELECT [group], amt FROM tbl_bal WHERE acct = 'A1000'
UNION ALL
SELECT G.acct , G.amt FROM tbl_bal G
INNER JOIN Balance B
ON G.[group] = B.acct WHERE G.acct <'A1000'
)
SELECT SUM(amt) FROM Balance;|||Would need a few more rows to make sure but from what you have given
you need to do a google search on -- recursive SQL -- web is better.
It is not fun or pretty but it is rather common and far better example
exists then I could copy/paste here.

is this possible?

hi

I would like to display agregated data, as well as my image, but it seems that this requires me to put my image file field in my group by statement too, but sql does not seem to allow sorting of images. Is what I am trying to possible? Or do I have to do both things separate?

here is the code:

select tbltimesheets.weekno, sum(tblentries.hrsnorm) as hrsnorm, sum(tblentries.hrspot) as hrspot, sum(tblentries.hrsnpot) as hrsnpot, tblusers.username, images.imagefile from
tbltimesheets
inner join tblentries on tbltimesheets.tskey = tblEntries.TSKey
inner join tblusers on tbltimesheets.userkey = tblusers.userkey
inner join images on images.userkey = tblusers.userkey
group by tbltimesheets.weekno, tblusers.username, images.imagefileYou have to ddo the thing separate. YOu can use some smart SQL for this, though, working, and still do it in one query.

Or, instead summing, you could use another method (like one returning the first image in the group).

Is there any chance to execute or call PL/SQL SP in SQl Server 2000 T-sql

HI Group,

Is there any chance to execute or call PL/SQL Sp in SQL Server 2000 T-Sql ?
If any chance, please kindly sent steps .
Regards
ravi Shankar.

You can call a PL/SQL SP indirectly using an Oracle package wrapper procedure that returns a PL/SQL table as output which can be called via OPENQUERY. I have posted lot of examples that demonstrate this technique 3-4 years back in the public newsgroups. Use link below:

http://groups.google.com/group/microsoft.public.sqlserver.programming/search?group=microsoft.public.sqlserver.programming&q=oracle+stored+procedure+call+umachandar&qt_g=1

You will find more examples if you search for my name and appropriate keywords (oracle, linked servers, stored procedure etc…).