SQL Agent Job Notification – Email Without Configuring Database Mail

sql-server-2012sql-server-agent

Is there any way to send sql agent job notification email without configuring Database Mail?

Currently I am using Database Mail but due to security concern, i need to disable Database Mail feature.

Thanks.

Best Answer

You can easily invoke a Powershell script: (Change the FromEmailAddress, ToEmailAddress and SmtpClient information)

$computerName = gc env:computername
$emailFrom = $computerName + “<EmailFromAddress>.com”
$emailTo = "<EmailToAddress>.com"
$subject = "Testing email"
$emailBody = "This is an example of sending email via Powershell"
$message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
$message.Priority = "HIGH"
$smtpServer = "<YourSmtpServer>.com"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)

Related Solutions

SQL Server Agent Database Mail – How to Enable Using T-SQL

The following T-SQL will enable Database Mail in SQL Server Agent:

USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', 
    N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', 
    N'REG_SZ', N'<profilename>'
GO

For future reference, you can easily determine this by expanding the "SQL Server Agent" node in SQL Server Management Studio's Object Explorer pane. Then right-click on "SQL Server Agent", click "Properties", select "Alert System", enter the appropriate details, and click on "Script":

enter image description here

SQL Server – Fixing Login Error for SQL Server Agent Account

The user account that is running the SQL Server agent might not have permissions to use the database mail profile you can check that with EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

But you are having some issues with the agent account security, please try the following steps.

Remove all extra privileges from the Agent user account (or create a new domain account) Running the SQL Server agent with domain admin privs is just terrifying.
Change the user for the Agent service using the SQL Server Configuration Manager. You should never change it any other way as it will set all the necessary permissions. Change the account to network service and then back again and make sure beforehand that the agent user is neither a local or domain administrator.
Check the mail profile on the SQLAgent settings and make sure that it's not private and that the agent user account can use it (The user account will be made sysadmin by the Configuration manager and should be able to use all accounts)

Best Answer

Related Solutions

SQL Server Agent Database Mail – How to Enable Using T-SQL

SQL Server – Fixing Login Error for SQL Server Agent Account

Related Question