Documented Bug in SQL Server 2005?

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(TextData))', '''', ''''''))
The error looks like:
Executed as user: MyDomain\MyServiceAccount. 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.
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(TextData))', '''', ''''''))
> The error looks like:
> Executed as user: MyDomain\MyServiceAccount. 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\MyServiceAccount. 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx