How to configure database smtp mail in sql server

To configure database SMTP mail in SQL Server, you'll need to follow these steps:

Step 1: Enable Database Mail

  1. Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  2. In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server" and select "Properties".
  3. In the "SQL Server Properties" window, click on the "Database Settings" tab.
  4. Under "Database Mail", click on the "Enable Database Mail" checkbox.
  5. Click "OK" to save the changes.

Step 2: Configure Database Mail Profile

  1. In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server" and select "Database Mail".
  2. In the "Database Mail" window, click on the "New Mail Profile" button.
  3. In the "New Mail Profile" window, enter a name for your mail profile (e.g., "MyMailProfile").
  4. Enter the SMTP server name, port number, and authentication details (e.g., username and password).
  5. Click "OK" to save the changes.

Step 3: Configure Database Mail Account

  1. In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server" and select "Database Mail".
  2. In the "Database Mail" window, click on the "New Mail Account" button.
  3. In the "New Mail Account" window, select the mail profile you created in Step 2.
  4. Enter the email address and display name for the mail account.
  5. Click "OK" to save the changes.

Step 4: Test Database Mail

  1. In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server" and select "Database Mail".
  2. In the "Database Mail" window, click on the "Send Test Mail" button.
  3. If the test mail is sent successfully, you should receive an email at the email address you specified in Step 3.

Step 5: Configure SQL Server Agent to Use Database Mail

  1. In the Object Explorer, expand the "SQL Server" node, then right-click on "SQL Server Agent" and select "Properties".
  2. In the "SQL Server Agent Properties" window, click on the "Alert System" tab.
  3. Under "Database Mail", select the mail profile you created in Step 2.
  4. Click "OK" to save the changes.

Now, you should be able to use Database Mail to send emails from your SQL Server instance. You can use the sp_send_dbmail stored procedure to send emails programmatically.

Here's an example of how to use sp_send_dbmail:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MyMailProfile',
    @recipients = '[email protected]',
    @subject = 'Test Email',
    @body = 'This is a test email sent from SQL Server.'

Replace MyMailProfile with the name of your mail profile, and [email protected] with the email address of the recipient.