SQL server 2000 and Outlook security issues?
-
Hi,
I have just started working for a new company as a SQL developer come general dogs body and the resident SQL DBA is telling me that he will not install outlook on any of the SQL servers as this is a potential security issue.
Is this true, is yes, then is there a solution? If it is not true, how did this rumour come about?
Being able to use SQL mail would save me a lot of messing about and help streamline a lot of the processes I have inherited from the last incumbent.
Cheers
-
Outlook is a client application that does not belong on a Server. I can understand why a good administrator of a server or a sql server does not want that kind of applications on de server.
Why Microsoft ever invented such a stupid solution, I really do not understand. Why didn't they make the functionality in simplified form available as part of SQL Server? On dll would be enough, i think.
-
Do you allow mail to go into your SQL Server or are you simply pushing messages outwards?
Our network guy installed Outlook but configured the Exchange server so that only internal mail could be sent from the server and only a few selected people could send email to the server.
In the past someone managed to get SQL MAIL functioning with outlook express but I can't remember how it was done.
-
Neither of the preceeding comments addresses the security issue. I can see the point made by one of the comments that some kind of scaled down version be available, but the idea that "client" applications should not be on a server seems to me to be extreme. I wonder if he has also removed Notepad. Depending on the server, installing Outlook can be useful, and I have not seen any security issues so far. We use it on our import server to provide messages to both the IT and operations staff to provide notifications and numbers of completed processes done through our DTS packages. I wonder if the person who made that comment uses DTS packages, and if so, uses the email steps.
Occasionally something hangs (I'm using Outlook 2000 with SQL 2000) and I have to restart the server, so it's probably not a good idea for a 24/7 mission critical server.
I would certainly be interested in knowing if there are security issues, but if there are not, client or not, it is a good solution for us, especially as we use DTS packages extensively.
-
Outlook is a memory hog and some versions have been known to not release memory to other applications even when closed down. It also checks for new mail every few minutes and can literally bring the PC/server to a standstill until it has finished.
If you only want to send emails you can use CDO, which is automatically installed with IIS, which is automatically installed with SQL Server.
You can use the following stored procedure (downloaded from elsewhere on this site) to send emails:
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) = "somedefault.emailaddress@somewhere",
@To varchar(100) ,
@cc varchar(300)= " ",
@Subject varchar(100)=" ",
@Body varchar(4000) =" ",
@Attachment varchar(255) = " "
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
Comments are added to the stored procedure where necessary.
Usage is:
exec master.dbo.sp_send_cdosysmail
@from='some.address@somewhere',
@to='some.email.address@somewhere',
@cc='some.email.address@somewhere',
@Subject='SQL server notification email',
@body='Job "some job name" has failed on server xxx'
***********************************************************************/
AS
Declare @iMsg int
Declare @iBp int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing"
.Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'MailServerName'--EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
.value'">http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").value', 2--EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusername"
.value'">http://schemas.microsoft.com/cdo/configuration/sendusername").value', 'your smtp server authentication user name (if necessary)'--EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.Fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendpassword"
.value'">http://schemas.microsoft.com/cdo/configuration/sendpassword").value', 'your smtp server authentication password (if necessary)'-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'CC', @cc
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', NULL, @Attachment
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
---------------------------------------------------------------
An example of how it is called:
exec master.dbo.sp_send_cdosysmail
@from='some.email.address@somewhere',
@to='some.email.address@somewhere',
@cc='',
@Subject='SQL server notification email',
@body='Job "some job name" has failed on server xxx'
Hope this helps
Peter
-
We do not use DTS so far on our SQL Servers. For the same reason as stated below. I have seen too many DTS job's going wrong. Every single failure in the Server is one too many. Therefore we use other solutions.
Restarting a server is not an option during working hours. For that reason I will not install extra client applications.
About your comment on notepad; I think this is a little silly don't you think? Notepad is very very very simple stand alone application, that never ever caused a machine to crash by my knowledge. Outlook is a totally other kind of application with to many dependencies on OS and Network.
-
I don't think the mention of Notepad was "silly" at all. It sounded to me as if Outlook was forbidden simply because it is a client application, but there are others. You have clarified above as a client application with lots of dependencies, which changes things somewhat. Since you're not using DTS package, it probably doesn't matter a whole lot, although I wonder if you can still get job email notifications. If you were using it, however, (and since a single server could manage all your DTS jobs, you could put Outlook only on that server, which could then be restarted if necessary, something we have to do about once every 3 months), I would suggest that your ruling an Outlook installation out would be counterproductive.
By the way, our DTS packages, some very complex, work very well. It's one of SQL Server 2Ks best features.
-
SQL Mail can be a headache...I've tried that myself in the past as well and was not happy with it. (see: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=122828)
I've also tried using CDO to send emails which works fine - but that has its secuirty downsides as well(you need to grant EXEC on sp_OA procedures which are reserved for use by sysadmin).
I think the best way to go for emails is by using xpsmtp. (see: http://www.sqldev.net/xp/xpsmtp.htm for details) It's easy to implement, easy to use, and reliable...
-John
-
The one big failure of using Outlook for sending alert messages is that if the Exchange server is unavailable, Outlook loses its connection.
Once that happens you can try using xp_stopmail and xp_startmail to "reboot" it, but more often than not it requires the services to be restarted. Not the ideal solution when you need to get notification of a failure
We use xp_smtp_sendmail (http://www.sqldev.net) for all emails on our 12 SQL Servers.
To receive alert emails you need to setup the alert to execute a scheduled job. This job then uses SQL Agent tokens (see above website for details) to provide the body of the email message.
For job failures, I have a seperate step in each job that sends an email on failure.
For something like DTS, instead of having a SendMail task, you just have an ExecuteSQL task.
--------------------
Colt 45 - the original point and click interface
-
The thing to remember with security is who and what are you protecting the server from.
In our case SQLMail can only be used to send internal mail and can only receive mail from a limited number of addresses. This satisfies our particular security needs.
As far as I remember SQLMail simply needs a MAPI profile, in which case the following article may be of interest.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306962
-
I am in favor of anything that gets the Job done, with minimal impact on the database server as the primary consideration.
SQL Mail uses a MAPI connection to Exchange, the reason that you install Outlook on the SQL Server is to install the CDO library and to provide a UI to create / maintain the profile.
On my servers I install the minimal Outlook 2003 (the one that comes with Exchange, not from Office). Of course you have to choose the Custom install in order to install CDO. Turn off the fluff while you are at it.
After Install I always use the Control Panel Mail applet to create the profile, the trick here is to install it as the service, which can be a real problem for SYSTEM as the service account. And to install it for any user profile that will attempt to change these properties in SQL Server.
Last I never leave the server this way, I change the NTFS permissions on the C:\Program Files\Microsoft Office directory to restrict for Everyone except for Administrators.
Watch out for people changing the default MAPI profile, causing it to no longer work.
The only real fault that I can find is the limitations as implemented in the xp_ extended stored procedures.
After the Exchange server was unavailable, issue this from QA:
DECLARE @Err int
EXEC master.dbo.xp_stopmail
EXEC @Err = master.dbo.xp_startmail
IF @Err <> 0
RAISERROR('xp_startmail: Failed to start SQL Mail session.',19,1) WITH LOG
ELSE
BEGIN
EXEC dbo.xp_cmdshell 'net stop sqlserveragent'
WAITFOR DELAY '0:0:01'
EXEC dbo.xp_cmdshell 'net start sqlserveragent'
END
If you get an error, issue again until you no longer recieve an error for xp_stopmail
Andy
-
Peter, you are da man!
First you complain about Outlook having a memory leak and next you provide code that is almost guaranteed to be the cause for a memory leak, namely the CDO object left in memory for every OLE object error.
You really should add better error handling like a IF @@ERROR <> 0 GOTO for any T-SQL operation that may error after creating the @iMsg object reference, and IF @hr <> 0 GOTO after any OLE object operation that may error, so the EXEC @hr = sp_OADestroy @iMsg will always be performed.
Andy
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply