October 25, 2016 at 12:05 pm
I had a standalone machine running SQL Server 2008 R2 that I joined to a domain. I set up an MSA and set all of the SQL services to log in under it.
I can access the DB remotely and run queries, but database mail doesn't work. I created a user in MSDB for the MSA account and added it
to the DatabaseMailUserRole role. I get the following error when I send a test email:
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
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.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
2) Exception Information
===================
Exception Type: System.Data.SqlClient.SqlException
Errors: System.Data.SqlClient.SqlErrorCollection
Class: 16
LineNumber: 1
Number: 15247
Procedure: sp_PostAgentInfo
Server: CCPC\GRDB
State: 1
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: User does not have permission to perform this action.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean)
HelpLink: NULL
StackTrace Information
===================
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
14090 SW TENNESSEE LN
October 26, 2016 at 12:51 pm
When you made the changes for the service accounts, where did you change the service accounts? In the services applet, Configuration Manager, or what? Based on the messages below, it looks like it may have not updated everything in SQL Server.
Message: Mail configuration information could not be read from the database.
2) Exception Information
Message: User does not have permission to perform this action.
What permissions does the Agent service account have on the server? Also, are you a sysadmin on the server?
And when you say you "created a user in MSDB for the MSA account" do you mean managed service account? You generally shouldn't have to manually create anything in SQL Server for the service accounts so trying to understand what you did here.
Sue
October 27, 2016 at 2:54 pm
I'm afraid I don't remember all of the steps I took. There were many failed attempts to get this working.
The MSA (Managed Service Account) was created on the Domain Controller.
In SSMS:
use master;
GO
CREATE LOGIN [MyDomain\MyMSA$] FROM WINDOWS;
GO
USE msdb;
GO
CREATE USER [MyDomain\MyMSA$]FROM LOGIN [MyDomain\MyMSA$];
GO
ALTER ROLE DatabaseMailUserRoleADD MEMBER [MyDomain\MyMSA$];
GO
Prior to doing that, I got an error about the user accessing objects in msdb.
14090 SW TENNESSEE LN
October 27, 2016 at 4:02 pm
gary.rumble (10/27/2016)
I'm afraid I don't remember all of the steps I took. There were many failed attempts to get this working.The MSA (Managed Service Account) was created on the Domain Controller.
In SSMS:
use master;
GO
CREATE LOGIN [MyDomain\MyMSA$] FROM WINDOWS;
GO
USE msdb;
GO
CREATE USER [MyDomain\MyMSA$]FROM LOGIN [MyDomain\MyMSA$];
GO
ALTER ROLE DatabaseMailUserRoleADD MEMBER [MyDomain\MyMSA$];
GO
Prior to doing that, I got an error about the user accessing objects in msdb.
Okay...first I'm not sure it's fully supported to use MSAs on 2008R2 or earlier. But some people have managed to get it working
If that's all you added above, don't think that's going to work too well for you.
If SQL Server was running under other accounts, you would typically want to change them using SQL Server Configuration Manager as it will handle some of the additional access needed. You want to verify that they are correct in Configuration Manager. If it's already running under the correct MSA, I'd still hit apply on that just to make sure. It should just have the account with the dollar sign (and no password or the dots in there for the password). You will also want to add the MSA to sysadmins server role on SQL.
Rights and ACLs on the server could be a issue so this has the list of rights and ACLs needed by the different services:
https://msdn.microsoft.com/en-us/library/ms143504(v=sql.105).aspx
I'm not sure if you need to change the groups or not on that server and when I just searched on that the MS docs just say it's not supported on 2008. But on a 2008 install, you should have groups for the services you installed...something like SQLServerMSSQLUser$ServerName$MSSQLSERVER(Or Instance name)...you'll see the other groups with similar names, just the part before the server name is different for the different services. AgentUser is for SQL Agent, MSASUser for Analysis Services, ReportServerUser for Reporting Services. Those are created during the installation. The accounts you put in for the services are members of those respective groups. But like I said, I don't know if you need to add the managed service account to those groups. I would think it wouldn't hurt but not sure. The benefit would be that it should take care of the other rights and ACLs from the link above.
Hope that helps some -
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply