Security violation for Database Mail XPs Option

  • myalliku

    SSC Eights!

    Points: 809

    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..

  • Lowell

    SSC Guru

    Points: 323331

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • myalliku

    SSC Eights!

    Points: 809

    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;

    https://benchmarks.cisecurity.org/tools2/sqlserver/CIS_Microsoft_SQL_Server_2008_R2_Database_Engine_Benchmark_v1.0.0.pdf

  • Lowell

    SSC Guru

    Points: 323331

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Joie Andrew

    One Orange Chip

    Points: 27219

    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"

  • myalliku

    SSC Eights!

    Points: 809

    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.

  • Joie Andrew

    One Orange Chip

    Points: 27219

    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"

  • Ed Wagner

    SSC Guru

    Points: 286941

    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.

  • myalliku

    SSC Eights!

    Points: 809

    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 9 (of 9 total)

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