SQL Server Performance Forum – Threads Archive

xp_sendmail and DTS Send Mail Security Concern

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

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

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.

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

Dave Yes, if you can save DTS package to the file and you can deny permission to the MSDB for public group specifically if in doubt. Get more information about CDONTS from this KBAhttp://support.microsoft.com/view/tn.asp?kb=312839 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.

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

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.

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

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.

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

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.

Hi Dave, 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) 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 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=’;-)’ /> Cheers Twan

For security question on SQL Server I found this to be an excellent sourcehttp://www.sqlsecurity.com . If you are interested I can sent you stuff from Openhack, which among others contain an absolute minimum configuration provided by Microsoft.
Frank
http://www.insidesql.de
http://www.familienzirkus.de

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

Here you go
http://www.insidesql.de/binaries/Microsoftprovideddocumentationandscripts.zip
http://www.insidesql.de/binaries/OpenHack2002appsourcecode.zip Frank
http://www.insidesql.de
http://www.familienzirkus.de

]]>