June 1, 2016 at 5:49 am
Hello folks,
I m a newbie for SQL Server. I have a question about Database Mail XP violation for SQL Server 2008 R2. I want to disable this option but I need to send email about SQL Server Agent Jobs completed notifications. So, if I disable Database Mail XPs, I would not send email about that notifications.
Do you have any solution to send e-mail while Database Mail Option is disabled?
Thank you..
June 1, 2016 at 6:08 am
not sure why you think it's a security violation.
if database mail is enabled, the only users that can use it are sysadmins of sql, or users who have been explicitly added to the role "DatabaseMailUserRole" in the msdb database.
even then, they can only use database mail if they know how...you can make all the profiles private, so they have to pass the @profile_name parameter, and do everything via TSQL script, or an application they create that uses the right commands....who do you have with that skillset?
so if you enable it, jobs could send emails, just as you want.
to answer your question, you do it the hard way, and could roll your own common language runtime to send emails, and add a step to each job to call some code that looks up any errors ,calls the CLR, and email the errors gathered manually from the script.
Lowell
June 1, 2016 at 6:27 am
Thank you for valuable response.
I heard that is a violation from many security organizations like CIS(Center for Internet Security) You can see details about that violation.
2.4 Set the 'Database Mail XPs' Server Configuration Option to 0 (Scored)Profile Applicability:
Level 1 - SQL Server 2008.x
Description:
Enables the generation and transmission of email messages from SQL Server.
Rationale:
Disabling Database Mail reduces the SQL Server surface, eliminates a DOS attack vector and channel to exfiltrate data from the database server to a remote host.
Audit:
Run the following T-SQL command:
SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'Database Mail XPs';
Both value columns must show 0.
Remediation:
Run the following T-SQL command:
EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;
Btw, you can see all security benchmarks list below;
June 1, 2016 at 6:37 am
A DOS attack, as in denial of service attack?
That's a very poor way to try and justify disabling that setting. I don't believe the person who developed the list knew anything about SQL server.
just by checking your own email, you already know sending a spoofed email/spam mail via SMTP is already being accomplished without knowing someone else's email settings.
to use database mail,either a login or the server itself has to have already been compromised.
the server itself would have to be exposed to an attack vector that allows a brute force test of username/password combinations, or the server would have to have already been compromised, to even get to database mail.
that is a security recommendation i would reject with a long explanation as to why they are incorrect.
Lowell
June 1, 2016 at 7:34 am
I think the message that CIS is trying to convey is that if it is not needed then it should not be enabled, because it is another function that could potentially be exploited. However if you have a requirement for e-mails then you have the justification for enabling it.
DISA STIGs are often very similar in their wording as well.
Joie Andrew
"Since 1982"
June 1, 2016 at 8:40 am
In fact, I need to disable that option because our auditors don't want to enabled this opiton. They think that is a security violation.
I will try to write to Windows Event Viewer that logs of SQL Server Agent. Then maybe I can send e-mail using a tool when error happened.
Thank you for your response.
June 1, 2016 at 8:52 am
Well, if you have a requirement to send out an e-mail notification and your Auditors want you to disable database mail then there seems to be a logic problem. Wouldn't any solution you put in place to replace database mail open up a similar attack vector than having database mail would have?
To go back to your original question if the system has PowerShell 3.0 or later you can use send-mailmessage to send an e-mail.
Joie Andrew
"Since 1982"
June 1, 2016 at 12:27 pm
Like Lowell pointed out, the only logins who can use database mail are those with the sysadmin server role and those who have been granted the DatabaseMailUserRole database role in msdb. Furthermore, you can create your profiles so that logins can use only those profiles they're explicitly authorized to use.
The point of requirements like this isn't to take SQL Server out at the knees and prevent you from using it, but rather to turn off the stuff you don't need. If you need to use it, then use it. Now, if you're concerned that it could be exploited because someone could break in using the sa login, then my opinion is that you have a much bigger problem on your hands because that means that the sa login is enabled. If someone breaks in with the sa login, it doesn't matter what you've configured because they have the privs to configure whatever they want. They also have the privs to steal all of your data.
My point is that, IMHO, the sa login should be disabled and left alone. Then you won't have to worry about anyone using the well-known login to break in to your system.
June 2, 2016 at 1:45 am
Thank you guys for answers. I m not sure how to fix that problem. When I take an action, I will inform you again.
Thank you again..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply