April 11, 2018 at 2:15 am
Hi Everyone
I ran into the following issue in my new production database running 2016 Enterprise Edition on Windows 2012 R2
After i enabled Database Mail i was getting the following error:
Date 4/11/2018 10:47:03 AM
Log Database Mail (Database Mail Log)
Log ID 111
Process ID 1636
Mail Item ID 30
Last Modified 4/11/2018 10:47:03 AM
Last Modified By sa
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2018-04-11T10:47:03). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender).
)
Then when i gave sysadmin permissions to the domain account i got the error below:
1) Exception Information ===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: Mail configuration information could not be read from the database. Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32) HelpLink: NULL Source: DatabaseMailEngine
HResult: -2146232832 StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object obj) 2) Exception Information =================== Exception Type: System.InvalidCastException Message: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'. Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32) HelpLink: NULL Source: DatabaseMailEngine HResult: -2147467262 StackTrace Information =================== at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
I removed SP1 and CU8 and the database mail worked fine
Has anyone got this situation happen with them?
I cant apply the SP or CU or itll stop my database mail unless i manually add a new column called Timeout to the msdb.dbo.sysmail_server table
Is this a known bug?
Have a good day
Kal
April 11, 2018 at 3:39 am
So i reapplied SP1 and CU8 again and with no luck so i removed CU8 and applied CU7 and voila it works
Anyone face the same issue?
Does the account for SQL Server Agent need to be part of the sysadmin role as ive found no mention of this on here:
To use SQL Server Agent, users must be a member of one or more of the following fixed database roles:
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
These roles are stored in the msdb database. By default, no user is a member of these database roles. Membership in these roles must be granted explicitly. Users who are members of the sysadmin fixed server role have full access to SQL Server Agent, and do not need to be a member of these fixed database roles to use SQL Server Agent. If a user is not a member of one of these database roles or of the sysadmin role, the SQL Server Agent node is not available to them when they connect to SQL Server by using SQL Server Management Studio.
Hence as long as the account is using one or more of the database roles then it doesnt need the sysadmin role
Is this correct?
This site says the engine service account needs to have the sysamin role:
SQL Server Agent Login and Privileges
The per-service SID of the SQL Server Agent service is provisioned as a Database Engine login. The per-service SID login is a member of the sysadmin fixed server role.
I am confused so i have to give the database engine account sysadmin so the sql server agent account works fine?
thanks
Kal
April 12, 2018 at 10:05 am
Hi Kal,
Not pretty sure whether my reply would be useful for you, but i have encountered a similar issue after applying the SP1 CU2 which had GDR 4206 (KB4019089) and SP1 update for SQL Server 2016 (KB3182545).
I was not recieving any emails from the DB mail and when i queried sysmail_allitems in the MSDB with below query, found that the send_status was unsent.
SELECT sent_status, *FROM sysmail_allitems;
After searching for any fixes online i found the below link which is a known issue after SP1 CU2 update.
https://support.microsoft.com/en-gb/help/3186435/fix-sql-server-2016-database-mail-does-not-work-on-a-computer-that-doe
I followed the steps and all the unsent were sent immediately and this fixed my problem.
Thanks,
Sai
April 12, 2018 at 10:42 am
Hi Sai
Thanks for your email
I don’t know if anyone experienced this issue but on windows 2012 R2 if you try to install Sql server 2016 without adding .Net 3.5 then the SQL server installation fails so I find it strange that the MS article talks about .Net 3.5.
Anyways try to install CU8 and see if the mail stops like mine did. I think CU8 might have a bug in it but let someone else confirm my finding.
Thanks again Sai
Have a good day
Kal
April 12, 2018 at 11:11 am
hurricaneDBA - Thursday, April 12, 2018 10:42 AMHi SaiThanks for your email I don’t know if anyone experienced this issue but on windows 2012 R2 if you try to install Sql server 2016 without adding .Net 3.5 then the SQL server installation fails so I find it strange that the MS article talks about .Net 3.5. Anyways try to install CU8 and see if the mail stops like mine did. I think CU8 might have a bug in it but let someone else confirm my finding. Thanks again Sai Have a good dayKal
The installations failed on Windows 2012 (and others) due to windows security updates that prevented .Net 3.5 feature from being enabled. When SQL Server would try to enable it and fail, the SQL Server installation would fail. Database mail needs .Net 3.5. There have since been Windows updates released that address the issues. There are quite a few articles about this. There have been three updates I've seen sited to cause the issue. One of the articles on the Windows fixes for this issue -
Update for the .NET Framework 3.5 on Windows 8, Windows 8.1, Windows Server 2012, and Windows Server 2012 R2
Sue
April 12, 2018 at 11:16 am
Sue_H - Thursday, April 12, 2018 11:11 AMhurricaneDBA - Thursday, April 12, 2018 10:42 AMHi SaiThanks for your email I don’t know if anyone experienced this issue but on windows 2012 R2 if you try to install Sql server 2016 without adding .Net 3.5 then the SQL server installation fails so I find it strange that the MS article talks about .Net 3.5. Anyways try to install CU8 and see if the mail stops like mine did. I think CU8 might have a bug in it but let someone else confirm my finding. Thanks again Sai Have a good dayKalThe installations failed on Windows 2012 (and others) due to windows security updates that prevented .Net 3.5 feature from being enabled. When SQL Server would try to enable it and fail, the SQL Server installation would fail. Database mail needs .Net 3.5. There have since been Windows updates released that address the issues. There are quite a few articles about this. There have been three updates I've seen sited to cause the issue. One of the articles on the Windows fixes for this issue -
Update for the .NET Framework 3.5 on Windows 8, Windows 8.1, Windows Server 2012, and Windows Server 2012 R2Sue
Here is the other SQL Server 2016 fix I couldn't find earlier related to this - it addresses the issues with SP1 CU1:
FIX: SQL Server 2016 Database Mail does not work on a computer that does not have the .NET Framework 3.5 installed
Sue
April 12, 2018 at 12:19 pm
Thanks Sue
Learn something new everyday
Kal
April 12, 2018 at 12:45 pm
hurricaneDBA - Thursday, April 12, 2018 12:19 PMThanks Sue Learn something new everydayKal
I got hit by it early on. Initially it was reported to be just one update that caused it. There were some steps to allow enabling .Net 3.5 and then they had two more updates that broke it. It was obnoxious.
Sue
April 12, 2018 at 1:13 pm
Well CU8 for Sql server 2016 breaks the database mail sending feature but it works with CU7 which is pretty annoying also
Makes you think how much testing is done prior to releasing CUs
Kal
April 12, 2018 at 1:42 pm
hurricaneDBA - Thursday, April 12, 2018 1:13 PMWell CU8 for Sql server 2016 breaks the database mail sending feature but it works with CU7 which is pretty annoying also Makes you think how much testing is done prior to releasing CUs Kal
Testing? What testing..... 🙂 We are not alone in that thinking if you follow many forums, blogs, etc.
Unfortunately, the priorities seem to be features over stability so things end up half baked way too often.
Sue
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply