CLR to write to the Windows Event Log

Question

CLR to write to the Windows Event Log

giles.clapham

SSCrazy

Points: 2204
More actions
November 3, 2017 at 4:45 am

#324396

I have a requirement to write an error to the Windows Event Log when the number of rows in a table exceeds a certain number.
My issue is that the Event Sorurce needs to be the name of the Database and the only way I can see of doing that was by writing a CLR, however when I execute it I get the following error:
System.Security.SecurityException: Request for the permission of type 'System.Diagnostics.EventLogPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Reading up on that it appears that the System.Diagnostics library is restricted but as I need to be able to deploy this DB anywhere running the .NET Framework Configuration Tool isn't an option.
So if there is something I'm doing that's wrong or there is a better solution I'd be very grateful.
My simplified C# code is below (Please bear in mind that I'm not a C# programmer) and I've not even got as far as passing in the Database name and row count as parameters yet:
using System;
using System.Diagnostics;
using System.Threading;
public class mySample
{
public static void Main()
    {
       // Create an EventLog instance and assign its source.
       EventLog myLog = new EventLog();
       myLog.Source = "DatabaseName";
       // Write an entry to the Event Log.
       myLog.WriteEntry("Testing 123")
    }
}

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

giles.clapham SSCrazy Points: 2204 More actions · Answer 1

Just for clarification, when I tested my code I used an existing Source name, rather than "DatabaseName" as per my example code as to avoid another level of complexity.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 2

Can you please explain why RAISERROR ... WITH LOG is unsuitable for your purposes?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

giles.clapham SSCrazy Points: 2204 More actions · Answer 3

It is because the requirement I've been given is to use the Database Name as the Event Source and RAISERROR... WITH LOG records it as MSSQL$InstanceName.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

giles.clapham - Friday, November 3, 2017 5:25 AM
It is because the requirement I've been given is to use the Database Name as the Event Source and RAISERROR... WITH LOG records it as MSSQL$InstanceName.

OK. Have you tried marking the DB containing the assembly as TRUSTWORTHY (link)? Assuming that's an OK thing to do in your situation, of course.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

Writing to the Event Log is also possible using Powershell, I believe ... did you consider that avenue?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

giles.clapham SSCrazy Points: 2204 More actions · Answer 6

Thanks Phil,
I hadn't considered that route but now I have and had my taste of success.
There's some tweaking I need to do and I will have to run it from a SQL Server Agent Job as xp_CmdShell is not allowed on any of our instances but it still a great shout.
In case anyone is interested there is a useful guide on Technet on how to use powershell to do this very thing.
https://blogs.technet.microsoft.com/heyscriptingguy/2013/06/20/how-to-use-powershell-to-write-to-event-logs/
Thank again
Giles

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

giles.clapham - Friday, November 3, 2017 6:32 AM
Thanks Phil,
I hadn't considered that route but now I have and had my taste of success.
There's some tweaking I need to do and I will have to run it from a SQL Server Agent Job as xp_CmdShell is not allowed on any of our instances but it still a great shout.
In case anyone is interested there is a useful guide on Technet on how to use powershell to do this very thing.
https://blogs.technet.microsoft.com/heyscriptingguy/2013/06/20/how-to-use-powershell-to-write-to-event-logs/
Thank again
Giles

Heh.... "xp_CmdShell is not allowed on any of our instances". Another great example of how unjustified visceral fear of xp_CmdShell makes something so much more complicated. Despite what many claim, xp_CmdShell isn't a security issue. The only people or things that can turn it on or off are those with "sysadmin" or "control server" privs. Unless someone has made the mistake of granting people with lesser privs the ability to run it directly, only those same people or things can use it. Remember also that ANYONE, including any form of internal or external attacker that gets either one of those two privs can turn it on and won't even act as anything more than a 3ms speed bump in attack software if they get in with those privs.

Also remember that if an attacker DOES get in with "sysadmin" privs, they have PowerShell and a whole bunch of other tools to wreak havoc and steal a data payload and they can do so without you ever knowing.

xp_CmdShell isn't a security problem. Bad security is a security problem. Having xp_CmdShell turned off does nothing to improve security. The fact that it being turned on is logged serves only as written testament that you're going to read about yourself in the news in the morning.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246979 More actions · Answer 8

Jeff Moden - Friday, November 3, 2017 8:05 AM
Heh.... "xp_CmdShell is not allowed on any of our instances". Another great example of how unjustified visceral fear of xp_CmdShell makes something so much more complicated. Despite what many claim, xp_CmdShell isn't a security issue. The only people or things that can turn it on or off are those with "sysadmin" or "control server" privs. Unless someone has made the mistake of granting people with lesser privs the ability to run it directly, only those same people or things can use it. Remember also that ANYONE, including any form of internal or external attacker that gets either one of those two privs can turn it on and won't even act as anything more than a 3ms speed bump in attack software if they get in with those privs.
Also remember that if an attacker DOES get in with "sysadmin" privs, they have PowerShell and a whole bunch of other tools to wreak havoc and steal a data payload and they can do so without you ever knowing.
xp_CmdShell isn't a security problem. Bad security is a security problem. Having xp_CmdShell turned off does nothing to improve security. The fact that it being turned on is logged serves only as written testament that you're going to read about yourself in the news in the morning.

Jeff, this comes up so frequently that you should consider adding another item in your 'Helpful Links' section 'Why xp_CmdShell isn't a security problem', or similar.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 9

Phil Parkin - Friday, November 3, 2017 8:18 AM
Jeff Moden - Friday, November 3, 2017 8:05 AM
Heh.... "xp_CmdShell is not allowed on any of our instances". Another great example of how unjustified visceral fear of xp_CmdShell makes something so much more complicated. Despite what many claim, xp_CmdShell isn't a security issue. The only people or things that can turn it on or off are those with "sysadmin" or "control server" privs. Unless someone has made the mistake of granting people with lesser privs the ability to run it directly, only those same people or things can use it. Remember also that ANYONE, including any form of internal or external attacker that gets either one of those two privs can turn it on and won't even act as anything more than a 3ms speed bump in attack software if they get in with those privs.
Also remember that if an attacker DOES get in with "sysadmin" privs, they have PowerShell and a whole bunch of other tools to wreak havoc and steal a data payload and they can do so without you ever knowing.
xp_CmdShell isn't a security problem. Bad security is a security problem. Having xp_CmdShell turned off does nothing to improve security. The fact that it being turned on is logged serves only as written testament that you're going to read about yourself in the news in the morning.
Jeff, this comes up so frequently that you should consider adding another item in your 'Helpful Links' section 'Why xp_CmdShell isn't a security problem', or similar.

Agreed. I wrote a presentation on the subject and have given it several times. It might be time to convert it to an article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)