November 3, 2017 at 4:45 am
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")
}
}
November 3, 2017 at 5:00 am
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.
November 3, 2017 at 5:09 am
Can you please explain why RAISERROR ... WITH LOG is unsuitable for your purposes?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2017 at 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.
November 3, 2017 at 5:40 am
giles.clapham - Friday, November 3, 2017 5:25 AMIt 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2017 at 5:42 am
Writing to the Event Log is also possible using Powershell, I believe ... did you consider that avenue?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2017 at 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
November 3, 2017 at 8:05 am
giles.clapham - Friday, November 3, 2017 6:32 AMThanks 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
November 3, 2017 at 8:18 am
Jeff Moden - Friday, November 3, 2017 8:05 AMHeh.... "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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2017 at 1:03 pm
Phil Parkin - Friday, November 3, 2017 8:18 AMJeff Moden - Friday, November 3, 2017 8:05 AMHeh.... "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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply