Database Mail XPs disabled so can't send mail!
-
Good afternoon,
I have a stored procedure (scheduled to run every hour) which checks disk space on our SQL Servers.
Unfortunately I am unable to use sp_send_dbmail as it's disabled and due to various restrictions I've been advised to peform this using an alternative method.
A colleague of mine gave me the following vb script:
Function SendEmail (StrOutgoingEmail,StrSubjectLine,StrMessageBody)
'On Error Resume Next
Set cdoConfig = CreateObject("CDO.Configuration")
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = 2 'cdoSendUsingPort
.Item(cdoSMTPServer) = "pod51013.outloock.com"
.Update
End With
Set objCDO = CreateObject("CDO.Message")
Set objMessage = CreateObject("CDO.Message")
objMessage.Configuration = cdoConfig
objMessage.Subject = StrSubjectLine
objMessage.From = "test"
objMessage.To = StrOutgoingEmail
'objMessage.To = "email@email.com"
'objMessage.textbody = "Hello"
objMessage.HTMLBody = StrMessageBody '"
Hello world"
objMessage.Send
set objCDO=nothing
set objMessage=nothing
set db=nothing
set InputFileSet=nothing
set OutputFileSet=nothing
set SeedFileset=nothing
end Function
he also suggested use it as follows:
declare @v_Return int
declare @v_File varchar(100)
set @v_File='c:\blah\MailSender.vbs'
exec @v_Return=master..xp_cmdshell @v_File
I also have the details of the external SMTp server and port number but I'm not sure how to change the vb script.
Any advice please?
Thanks in advance.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
database mail is disabled, but xp_cmdShell is enabled?
that is so backward security wise.
did you try the process you outlined here and sent something to yourself? did it work?
let me help think outside fo the box:
1. do you have to do this in TSQL? can this be done in an applicaiton, or as an outside process instead?
for example, putting all the emails that need to be sent into a table, and then have an outside process read the table and send the emails? that way each email can be marked as sent/failed.
you could even use a DIFFERENT SQL server to pickup the stuff from that table, if the other table had database mail enabled.
2. would you consider adding a CLR to send the emails instead of using xp_cmdshell?
I can give you .net code examples, like this one below, but i'm not sure which direction you might want to take.
'.NET 2.0 or above, i believe:
Private Function SendEmails(ByVal StrOutgoingEmail, ByVal StrSubjectLine, ByVal StrMessageBody) As Boolean
Dim strFrom As String = "notifications@Abu_Dinas_Website.com"
Dim strSMTPServer As String = "mail.Abu_Dinas_Website.com"
Dim strSMTPUser = "notifications@Abu_Dinas_Website.com" '--the username the mailserver requires to AUTH the outbound email
Dim strSMTPPass = "NothTheRealSMTPPassword"
Try
Dim message As New System.Net.Mail.MailMessage(strFrom, StrOutgoingEmail)
message.Subject = StrSubjectLine
message.Body = StrMessageBody
message.IsBodyHtml = False 'Was This an html string?
Dim emailClient As New System.Net.Mail.SmtpClient(strSMTPServer)
Dim SMTPUserInfo As New System.Net.NetworkCredential(strSMTPUser, strSMTPPass)
emailClient.UseDefaultCredentials = False
emailClient.Credentials = SMTPUserInfo
Try
emailClient.Send(message)
Catch ex As Exception
MsgBox(ex.Message)
End Try
SendEmails = True
Catch e As Exception
SendEmails = False
'Console.WriteLine(e.Message)
End Try
End Function
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! -
Lowell (8/9/2012)
database mail is disabled, but xp_cmdShell is enabled?that is so backward security wise.
Those in charge have told me that it would take too long to enable database mail (ISO etc..) so they advised I find an alternative method.
did you try the process you outlined here and sent something to yourself? did it work?
Yes I have been "trying" to get it to work but I'm not even sure how to pass the parameters to the VB script!
let me help think outside fo the box:
1. do you have to do this in TSQL? can this be done in an applicaiton, or as an outside process instead?
for example, putting all the emails that need to be sent into a table, and then have an outside process read the table and send the emails? that way each email can be marked as sent/failed.
Okay it's like this:
1) 4 Servers - 1 SQL server instance per server
2) Need to notify myself when any of the servers has low disk space.
The Plan:
1) Schedule a SQL server job on each instance to run every hour and check available disk space.
2) If disk space below a specified threshod then I should get an email notification
2. would you consider adding a CLR to send the emails instead of using xp_cmdshell?
Possibly....
I can give you .net code examples, like this one below, but i'm not sure which direction you might want to take.
'.NET 2.0 or above, i believe:
Private Function SendEmails(ByVal StrOutgoingEmail, ByVal StrSubjectLine, ByVal StrMessageBody) As Boolean
Dim strFrom As String = "notifications@Abu_Dinas_Website.com"
Dim strSMTPServer As String = "mail.Abu_Dinas_Website.com"
Dim strSMTPUser = "notifications@Abu_Dinas_Website.com" '--the username the mailserver requires to AUTH the outbound email
Dim strSMTPPass = "NothTheRealSMTPPassword"
Try
Dim message As New System.Net.Mail.MailMessage(strFrom, StrOutgoingEmail)
message.Subject = StrSubjectLine
message.Body = StrMessageBody
message.IsBodyHtml = False 'Was This an html string?
Dim emailClient As New System.Net.Mail.SmtpClient(strSMTPServer)
Dim SMTPUserInfo As New System.Net.NetworkCredential(strSMTPUser, strSMTPPass)
emailClient.UseDefaultCredentials = False
emailClient.Credentials = SMTPUserInfo
Try
emailClient.Send(message)
Catch ex As Exception
MsgBox(ex.Message)
End Try
SendEmails = True
Catch e As Exception
SendEmails = False
'Console.WriteLine(e.Message)
End Try
End Function
Thanks for this. Do you think this is a better alternative to using a VB script?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Following on from Lowells posts and thinking slightly outside the box you could always forget a SQL solution and maybe just use some freeware software like this one (http://www.paessler.com/disk_space_monitor) and have that running on a little monitor server somewhere. note i havent tried this software in particular as we use OpManager which isnt free.
On a side note its likely your network/security team have software already for monitoring servers so you could just ask to be added onto the alerts, or maybe get them to set a couple of new ones up for you.
-
parameters for a vbs file are limited in size for the command line...4096 chars max length, i think, right?
so while you could send parameters to teh fiel (no spaces allowed in strings i think), it'd e clunky.
you'll end up needing to have the vbscript create an ADODB.Recordset, connect to a server and gather the data, either directly or from an aggragated table,a nd then have the script send the email.
From my vb6/SQL2000 days, i did that, but i don't think i have any perfect examples; i used a different DLL than CDO in those days anyway.
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! -
do you have to do this in TSQL?
I think Lowell's right here. There are an infinite number of solutions to this problem that don't involve using TSQL at all.
I can't help but think of Maslow's Hammer in this situation.
-
Scott D. Jacobson (8/9/2012)
do you have to do this in TSQL?
I think Lowell's right here. There are an infinite number of solutions to this problem that don't involve using TSQL at all.
I can't help but think of Maslow's Hammer in this situation.
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! -
Nice!
-
Abu Dina (8/9/2012)
Lowell (8/9/2012)
database mail is disabled, but xp_cmdShell is enabled?that is so backward security wise.
Those in charge have told me that it would take too long to enable database mail (ISO etc..) so they advised I find an alternative method.
I'd really have to challenge that assertion. Actually enabling database mail takes about 2-5 minutes per server, with possibly a few more minutes to set up some email profiles to be used. At worse it might require finding the right time (since you often have to disable the SQL Agent while you enable DB mail).
My gut feeling is that it would have taken them less time to set this up than it took you to put in the original request.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Point taken. :blush:
4 machines running windows server 2003 so why not just configure a low disk alert using peperformanceogs and alerts.
I deserve a slap for this!!!
A T-SQL solution would have worked just as well had db mail been enabled but I should have taken a step back and used a bit of common sense. Lesson learnt.
I still need db mail enabled though. It's a useful feature that I will need very soon.
Thanks for your time.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
I wouldn't be surprised if some just didn't feel like enabling it for you, and used the "it's locked down for security" trump card just to cut off discussion.
I'm agreeing with Matt here, i'd revisit it and move the issue up as far as priority goes.
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! -
Matt Miller (#4) (8/9/2012)
Abu Dina (8/9/2012)
Lowell (8/9/2012)
database mail is disabled, but xp_cmdShell is enabled?that is so backward security wise.
Those in charge have told me that it would take too long to enable database mail (ISO etc..) so they advised I find an alternative method.
I'd really have to challenge that assertion. Actually enabling database mail takes about 2-5 minutes per server, with possibly a few more minutes to set up some email profiles to be used. At worse it might require finding the right time (since you often have to disable the SQL Agent while you enable DB mail).
My gut feeling is that it would have taken them less time to set this up than it took you to put in the original request.
Matt I think you misunderstood. They couldn't enable it due to the procedures involved, our company is strict with ISO. You are right it's a 2 minute job on each sql server instance but they said it'd be quicker to do it another way.
See my reply to Lowell above. I think I was just being stupid earlier on today!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
I deserve a slap for this!!!
I thought Lowell and I were gently nudging rather than outright slapping 😎
-
Scott D. Jacobson (8/9/2012)
I deserve a slap for this!!!
I thought Lowell and I were gently nudging rather than outright slapping 😎
Nudge, push, shove whatever it takes as long as it makes me walk a straight path.
Thanks for your time.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn -
Abu Dina (8/9/2012)
Matt Miller (#4) (8/9/2012)
Abu Dina (8/9/2012)
Lowell (8/9/2012)
database mail is disabled, but xp_cmdShell is enabled?that is so backward security wise.
Those in charge have told me that it would take too long to enable database mail (ISO etc..) so they advised I find an alternative method.
I'd really have to challenge that assertion. Actually enabling database mail takes about 2-5 minutes per server, with possibly a few more minutes to set up some email profiles to be used. At worse it might require finding the right time (since you often have to disable the SQL Agent while you enable DB mail).
My gut feeling is that it would have taken them less time to set this up than it took you to put in the original request.
Matt I think you misunderstood. They couldn't enable it due to the procedures involved, our company is strict with ISO. You are right it's a 2 minute job on each sql server instance but they said it'd be quicker to do it another way.
See my reply to Lowell above. I think I was just being stupid earlier on today!
Well - I didn't pick up on is being based on an ISO-related complaint, but fankly even that doesn't hold water. Pretty much every other solution will involve either installing new components (CDO), enabling new items (i.e. SMTP service on local machine to email), which by all rights would be at least as invasive as the supported, recommended, secure solution (i.e. configure an already installed component).
That said - if you manage to find something that meets your need and makes them happy, then all is good:).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply