MS SQL Server Tools - Documented Bug in SQL Server 2005?

Author

Documented Bug in SQL Server 2005?

John T

2006-02-04, 8:23 pm

I set up a WMI alert in SQL2K5, which invokes a response launching a job.
The alert is AUDIT_LOGIN_FAILED. The response job is to insert the contents
of the token $(WMI(TextData)) into a table.

The job step text looks like:
INSERT INTO dbo. ddl_event_test_table
(event_source, event_data)
VALUES('AUDIT_LOGIN_
FAILED', REPLACE('$(WMI(TextD
ata))', '''', ''''''))

The error looks like:
Executed as user: MyDomain\MyServiceAc
count. Incorrect syntax near 'bubba'.
[SQLSTATE 42000] (Error 102).

The profile trace looks like:
INSERT INTO dbo. ddl_event_test_table
(event_source, event_data)
VALUES('AUDIT_LOGIN_
FAILED', REPLACE('Login failed for user 'bubba'.
[CLIENT: <local machine>]', '''', ''''''))

I have tried this with and without a REPLACE. The result is the same. Is
this documented? Is there a workaround?

Thanks for all input.

Erland Sommarskog

2006-02-05, 11:23 am

John T (JohnT@discussions.microsoft.com) writes:
> I set up a WMI alert in SQL2K5, which invokes a response launching a job.
> The alert is AUDIT_LOGIN_FAILED. The response job is to insert the
> contents of the token $(WMI(TextData)) into a table.
>
> The job step text looks like:
> INSERT INTO dbo. ddl_event_test_table
(event_source, event_data)
> VALUES('AUDIT_LOGIN_
FAILED', REPLACE('$(WMI(TextD
ata))', '''', ''''''))
>
> The error looks like:
> Executed as user: MyDomain\MyServiceAc
count. Incorrect syntax near
> 'bubba'. [SQLSTATE 42000] (Error 102).
>
> The profile trace looks like:
> INSERT INTO dbo. ddl_event_test_table
(event_source, event_data)
> VALUES('AUDIT_LOGIN_
FAILED', REPLACE('Login failed for user 'bubba'.
> [CLIENT: <local machine>]', '''', ''''''))
>
> I have tried this with and without a REPLACE. The result is the same. Is
> this documented? Is there a workaround?

Documented? Sort of. On
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-
f849e5fc4d43.htm
it says:

SQL Server Agent replaces an exact value for the token. Your job steps
must take this into account and correctly quote the tokens you use.

Bug? Certainly quite a serious design flaw in my eyes. There is no way you
can save this situation with replace(). As I understand it, SQL Server
Agent pastes in the value of the token, and it's your job to put that
value in a syntactic correct context. Which is quite an uphill battle in
this case. SQL Server Agent would need to provide to means to expand a
token: as-is and as-string-literal. Say that the syntax for the latter
would be $$(WMI(TextData)). That would expand the string to

N'Executed as user: MyDomain\MyServiceAc
count. Incorrect syntax near
''bubba''. [SQLSTATE 42000] (Error 102).'

An alternative would be that ' were not expanded as such but as some
innocent character, for instance `.

The workaround you can employ is to say SET QUOTED_IDENTIFIER OFF in
the script. Then you can say:

INSERT INTO dbo. ddl_event_test_table
(event_source, event_data)
VALUES('AUDIT_LOGIN_
FAILED', "$(WMI(TextData))")

But that would not be waterproof, since you would get the same syntax
error for a message that includs a ".

I would suggest that you submit a bug/suggestion for this on
http://lab.msdn.microsoft.com/productfeedback/. If you submit it as
a bug, it may be closed as "By Design". I would not expect this to
be fixed in SQL 2005, as it would be a feature change. Then again,
since there is a potential source for SQL injection here, maybe there
is cause for alarm. Feel free to include to suggestions I've given here
in your submission on the Feedback Centre.

Disclaimer: I have very little experience of working with tokens in
SQL Server Agent, so I may have missed something obvious. (Which is
why I don't want to submit any bug/suggestion myself.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sponsored Links