After configuring Database Mail on a clustered SQL Server 2008 R2 instance using a script based on the Create a Database Mail profile in 4 Steps (or less) article, the test email was not delivered. Some investigation showed that the email was actually not sent. I also checked the Database Mail profile, the account settings and other configurations. The queries I used to retrieve this information are shown below.
USE [msdb]
GO
-- configuration settings
SELECT * FROM dbo.sysmail_profile;
SELECT * FROM dbo.sysmail_account;
SELECT * FROM dbo.sysmail_server;
SELECT * FROM dbo.sysmail_servertype;
SELECT * FROM dbo.sysmail_configuration;
GO
-- queued email status
SELECT * FROM dbo.sysmail_allitems;
SELECT * FROM dbo.sysmail_sentitems;
SELECT * FROM dbo.sysmail_unsentitems;
SELECT * FROM dbo.sysmail_faileditems;
GO
The SQL Server Agent log showed the following entries:
[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), connection parameters: Server Name: SQLSrv01\INST1,)
[355] The mail system failed to initialize; check configuration settings
[264] An attempt was made to send an email when no email session has been established
The first thing I checked was whether network access from the database server to the mail server on port 25 was open.
telnet [mailserver-fqdn] 25
The mail server responded so network access was present. This also confirmed that there wasn’t a firewall in the way either. I also verified with the Email Administrators that the mail server accepted Anonymous Requests, which it did. I went back to the original error messages, namely the one containing the text:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.
This reminded me of connection failures from client machines when attempting to open a connection to an SQL Server instance which is listening on a non-default port (i.e. not 1433). In those cases the SQL Server Browser was off as the security hardening, as it was in this case. The solution in such cases was to set the Server or Data Source parameter in the connection string of the client application to include the actual listening port number. Since there is no way to set the Database Mail server (or I don’t know about it…) I thought of createing an Alias using SQL Server Configuration Manager. Since the entries are written to the registry of the local machine, in a clustered environment the process has to be repeated on each node of the cluster. Once the Alias was created I restarted the SQL Server Agent service and sent a test email, which was delivered to my mailbox. You might also find the following articles useful in identifying issues with Database Mail: