SQL Server Performance

xp_sendmail and DTS Send Mail Security Concern

Discussion in 'SQL Server DTS-Related Questions' started by DBADave, Jan 9, 2004.

  1. DBADave New Member

    I learned today that xp_sendmail is run under the SQL Server's security context, probably local administrator. This gives a person who executes xp_sendmail the ability to retrieve files and directories stored on the database server and send them as an attachment via email. The good news is only system administrators and the database owner have the ability to run xp_sendmail, unless you grant execute permission to a user id.

    I also learned today email can be sent through DTS by using the Send Mail task. This also runs under the security context of SQL Server and allows the package owner the ability to send, as attachments, files and directories located on the database server. The bad news is anyone in the group Public can create a DTS package.

    Why would Microsoft restrict security for xp_sendmail and not for Send Mail in DTS? This appears to be a contradiction in security philosophies.

    Dave
  2. Twan New Member

    Hi Dave,

    have you actually tried to set up a DTS job as a non-privileged user to try it? Perhaps there are other security measures in place? Also the assumption by MS is that SQL does not run as a local/domain admin account, this is a common misconfiguration though.

    I can't try it myself because I have no SQLMail enabled anywhere. In my opinion it is an evil product, besides it is not recommended for any secure installation anyway. To allow sending of email by designated people I wrote a stored procedure to use CDONTS to send messages via my email server. This has the advantage of not needing any email software on the database server, but attachments still have to be accessible by the SQL service account

    Cheers
    Twan
  3. satya Moderator

    You can deny access to PUBLIC on the MSDB as DTS packages creates over there.
    And due to these security loopholes with XP_SENDMAIL, CDONTS became popular and widely used.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. DBADave New Member

    Satya,

    I had someone remind me that DTS Packages can be saved to files so denying Public access to MSDB won't resolve the problem.

    Where can I get more information on CDONTS? Are there any good sites for CDONTS or should I just search Google?

    Thanks, Dave
  5. satya Moderator

  6. DBADave New Member

    Satya,

    I must be doing something wrong. I just denied EXECUTE on all MSDB stored procedures that contain the words DTS. I also ran DENY ALL to PUBLIC in MSDB. After creating an account, AAA, with no permissions I registered the DB server in EM using this account name. I'm not allowed to save the package, which is what I expected, but I am still able to execute the DTS code prior to trying to save the package.

    Any suggestions?

    Dave
  7. satya Moderator

    Have you deleted the guest user from MSDB, may try before creating a package.
    Restrict login permission to MSDB as suggested will stop accessing the dts packages and other jobs.

    And I think its still possible to create package using other tools supplied alongwith SQL server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. DBADave New Member

    Yes. I had already deleted Guest. I cannot see MSDB when I register a server in EM using the generic id, which is as expected. However I can right click on Local Packages, choose New Package and send and email without trying to save the package.

    When I open query analyzer I am denied access to MSDB so I know the permissions are working at some level. I'm guessing the Send Mail portion of DTS has nothing to do with the user's permissions and only looks at the security context used by SQL Server. In my case the SQL Server service is using a Domain account that has local administrator rights. I should probably start looking into not assigning local administrator rights to the service.

    Dave
  9. satya Moderator

    Good insight, I appreciate it.
    For other reasons or if jobs are involved in network transactions then you may re-think about denying local admin privilege to the SQL services account.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. DBADave New Member

    Do you know what will be impacted by not using a service account with Local Administrator permissions? All I could find on Technet is the three items below.

    Local Admin rights are needed to perform the following tasks
    1 - Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator
    2 - Use the autorestart feature
    3 - User run-when-idle jobs

    I thought I read something about it impacting replication and other forms of server to server communication, but I couldn't find anything on this subject over the weekend.

    Thanks, Dave
  11. satya Moderator

    I'm sure this (removing local admin) will have adverse affect on the replication as network transition is involved.

    All the 3 tasks defined a 100% right and if any part of SQL transactions involves network interaction then better not to touch the section. Above that SQLAgent jobs will fail if backup job involves copy files or log shipping.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. Twan New Member

    Hi Dave,<br /><br />If you remove local admin privileges from the SQL service account then you will need to explicitly grant it NT rights on the directories that you are using across the network and locally (other than the MSSQL install directories which are covered by changing the account in EM)<br /><br />You also won't be able to use administrative shares to talk to remote servers. i.e. c$ is not going to work is the service account is not a local admin<br /><br />We run our servers with standard user accounts and run SQL replication without any issues, so it is definitely possible <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Cheers<br />Twan
  13. FrankKalis Moderator

  14. DBADave New Member

    I've been having a forum conversation with Chip on SQLSecurity.com regarding this subject. He suggested using his Lockdown script, but for DTS that script only locks down MSDB and DTS packages do not need to be saved to MSDB. He has some good suggestions on his site, which I will further investigate.

    Any additional information you have on locking this down would be helpful.

    Thanks, Dave
  15. FrankKalis Moderator

Share This Page