April 2, 2012 at 1:19 pm
Welsh Corgi (4/2/2012)
I changed the Account for the SQL Server and SQL Server Agent Account to an account that it should work with and tried to send a message but all of the message are still queued.
Just to be 100% sure... You changed these using SQL Server Configuration Manager, right? If you do not use that, all heck will break loose with permissions...
Jared
CE - Microsoft
April 2, 2012 at 1:25 pm
SQLKnowItAll (4/2/2012)
Welsh Corgi (4/2/2012)
I changed the Account for the SQL Server and SQL Server Agent Account to an account that it should work with and tried to send a message but all of the message are still queued.Just to be 100% sure... You changed these using SQL Server Configuration Manager, right? If you do not use that, all heck will break loose with permissions...
I changed it in the configuration Manager.
At one point I had to change because I needed to set to start the service automatically.
Is this going to be an unreversable problem?:sick:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2012 at 1:30 pm
SELECT *
FROM sysmail_event_log
Nothing but start & stop DB Mail
SELECT *
FROM sysmail_allitems
I found the items were listed as failed.
So I check the Windows Event Log and I have the following:
1) Exception Information
===================
Exception Type: System.TypeInitializationException
TypeName: System.Data.SqlClient.SqlConnection
Message: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void .ctor(System.String)
HelpLink: NULL
Source: System.Data
StackTrace Information
===================
at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
at Microsoft.SqlServer.Management.Common.SqlConnectionInfo.CreateConnectionObject()
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel)
2) Exception Information
===================
Exception Type: System.TypeInitializationException
TypeName: System.Data.SqlClient.SqlConnectionFactory
Message: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void .cctor()
HelpLink: NULL
Source: System.Data
StackTrace Information
===================
at System.Data.SqlClient.SqlConnection..cctor()
3) Exception Information
===================
Exception Type: System.TypeInitializationException
TypeName: System.Data.SqlClient.SqlPerformanceCounters
Message: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void .ctor()
HelpLink: NULL
Source: System.Data
StackTrace Information
===================
at System.Data.SqlClient.SqlConnectionFactory..ctor()
at System.Data.SqlClient.SqlConnectionFactory..cctor()
4) Exception Information
===================
Exception Type: System.Configuration.ConfigurationErrorsException
Message: Configuration system failed to initialize
BareMessage: Configuration system failed to initialize
Filename: NULL
Line: 0
Errors: System.Configuration.ConfigurationException[]
Data: System.Collections.ListDictionaryInternal
TargetSite: Void EnsureInit(System.String)
HelpLink: NULL
Source: System.Configuration
StackTrace Information
===================
at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName)
at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName)
at System.Configuration.ConfigurationManager.GetSection(String sectionName)
at System.Configuration.PrivilegedConfigurationManager.GetSection(String sectionName)
at System.Diagnostics.DiagnosticsConfiguration.Initialize()
at System.Diagnostics.Switch.InitializeConfigSettings()
at System.Diagnostics.Switch.InitializeWithStatus()
at System.Diagnostics.Switch.get_SwitchSetting()
at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp)
at System.Data.SqlClient.SqlPerformanceCounters..ctor()
at System.Data.SqlClient.SqlPerformanceCounters..cctor()
5) Exception Information
===================
Exception Type: System.Configuration.ConfigurationErrorsException
Message: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config)
BareMessage: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied.
Filename: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config
Line: 0
Errors: System.Configuration.ConfigurationException[]
Data: System.Collections.ListDictionaryInternal
TargetSite: Void ThrowIfErrors(Boolean)
HelpLink: NULL
Source: System.Configuration
StackTrace Information
===================
at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors()
at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey)
6) Exception Information
===================
Exception Type: System.UnauthorizedAccessException
Message: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied.
Data: System.Collections.ListDictionaryInternal
TargetSite: Void WinIOError(Int32, System.String)
HelpLink: NULL
Source: mscorlib
StackTrace Information
===================
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share)
at System.Configuration.Internal.InternalConfigHost.StaticOpenStreamForRead(String streamName)
at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName, Boolean assertPermissions)
at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName)
at System.Configuration.ClientConfigurationHost.OpenStreamForRead(String streamName)
1) Exception Information =================== Exception Type: System.TypeInitializationException TypeName: System.Data.SqlClient.SqlConnection Message: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. Data: System.Collections.ListDictionaryInternal TargetSite: Void .ctor(System.String) HelpLink: NULL Source: System.Data StackTrace Information =================== at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at Microsoft.SqlServer.Management.Common.SqlConnectionInfo.CreateConnectionObject() at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.OpenConnection(SqlConnectionInfo ci) at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.OpenConnection(String dbServerName, String dbName, String userName, String password) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName, Int32 lifetimeMinimumSec, LogLevel loggingLevel) 2) Exception Information =================== Exception Type: System.TypeInitializationException TypeName: System.Data.SqlClient.SqlConnectionFactory Message: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. Data: System.Collections.ListDictionaryInternal TargetSite: Void .cctor() HelpLink: NULL Source: System.Data StackTrace Information =================== at System.Data.SqlClient.SqlConnection..cctor() 3) Exception Information =================== Exception Type: System.TypeInitializationException TypeName: System.Data.SqlClient.SqlPerformanceCounters Message: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. Data: System.Collections.ListDictionaryInternal TargetSite: Void .ctor() HelpLink: NULL Source: System.Data StackTrace Information =================== at System.Data.SqlClient.SqlConnectionFactory..ctor() at System.Data.SqlClient.SqlConnectionFactory..cctor() 4) Exception Information =================== Exception Type: System.Configuration.ConfigurationErrorsException Message: Configuration system failed to initialize BareMessage: Configuration system failed to initialize Filename: NULL Line: 0 Errors: System.Configuration.ConfigurationException[] Data: System.Collections.ListDictionaryInternal TargetSite: Void EnsureInit(System.String) HelpLink: NULL Source: System.Configuration StackTrace Information =================== at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey) at System.Configuration.ClientConfigurationSystem.PrepareClientConfigSystem(String sectionName) at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(String sectionName) at System.Configuration.ConfigurationManager.GetSection(String sectionName) at System.Configuration.PrivilegedConfigurationManager.GetSection(String sectionName) at System.Diagnostics.DiagnosticsConfiguration.Initialize() at System.Diagnostics.Switch.InitializeConfigSettings() at System.Diagnostics.Switch.InitializeWithStatus() at System.Diagnostics.Switch.get_SwitchSetting() at System.Data.ProviderBase.DbConnectionPoolCounters..ctor(String categoryName, String categoryHelp) at System.Data.SqlClient.SqlPerformanceCounters..ctor() at System.Data.SqlClient.SqlPerformanceCounters..cctor() 5) Exception Information =================== Exception Type: System.Configuration.ConfigurationErrorsException Message: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config) BareMessage: An error occurred loading a configuration file: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. Filename: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config Line: 0 Errors: System.Configuration.ConfigurationException[] Data: System.Collections.ListDictionaryInternal TargetSite: Void ThrowIfErrors(Boolean) HelpLink: NULL Source: System.Configuration StackTrace Information =================== at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal) at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors() at System.Configuration.ClientConfigurationSystem.EnsureInit(String configKey) 6) Exception Information =================== Exception Type: System.UnauthorizedAccessException Message: Access to the path 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config' is denied. Data: System.Collections.ListDictionaryInternal TargetSite: Void WinIOError(Int32, System.String) HelpLink: NULL Source: mscorlib StackTrace Information =================== at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy) at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share) at System.Configuration.Internal.InternalConfigHost.StaticOpenStreamForRead(String streamName) at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName, Boolean assertPermissions) at System.Configuration.Internal.InternalConfigHost.System.Configuration.Internal.IInternalConfigHost.OpenStreamForRead(String streamName) at System.Configuration.ClientConfigurationHost.OpenStreamForRead(String streamName) at System.Configuration.BaseConfigurationRecord.InitConfigFromFile()
OMG, it looks like permissions.:crying:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2012 at 1:37 pm
Welsh Corgi (4/2/2012)
SELECT *
FROM sysmail_event_log
Nothing but start & stop DB Mail
SELECT *
FROM sysmail_allitems
I found the items were listed as failed.
Waia minute...you said for all the messages "Message go into the queue but they are never sent." ...but now you are saying everything has a failed status?
there is a big difference between the two....if everything was sitting in the queue as unsent, that implies the service broker's not processing emails....if they are all failed, that imples a simple mailbox setup error.
which is it?
what is the results of these two queries?
SELECT *
FROM msdb.dbo.sysmail_allitems
where sent_status NOT IN('failed','sent')
SELECT
err.[description],
fail.*
FROM [msdb].[dbo].[sysmail_event_log] err
inner join [msdb].[dbo].[sysmail_faileditems] fail
ON err.mailitem_id = fail.mailitem_id
Lowell
April 2, 2012 at 1:40 pm
Looks to me like a permissions issue based on the error messages. Did you restart the service after you changed the login using SQL Server Configuration Manager? (Also, make sure you have not disabled or deleted NT SERVICE\MSSQLSERVER or NT SERVICE\SQLSERVERAGENT virtual logins)
Jared
CE - Microsoft
April 2, 2012 at 1:52 pm
Lowell,
I'm sorry. I don't know where I got unsent but the sent_status is 'unsent'
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2012 at 1:56 pm
SQLKnowItAll (4/2/2012)
Looks to me like a permissions issue based on the error messages. Did you restart the service after you changed the login using SQL Server Configuration Manager? (Also, make sure you have not disabled or deleted NT SERVICE\MSSQLSERVER or NT SERVICE\SQLSERVERAGENT virtual logins)
I restarted the service.
How do I check on the virtual logins?
I did not touch that.
I'm on a Server with Windows Server 2008 R2 x64.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2012 at 2:01 pm
Welsh Corgi (4/2/2012)
SQLKnowItAll (4/2/2012)
Looks to me like a permissions issue based on the error messages. Did you restart the service after you changed the login using SQL Server Configuration Manager? (Also, make sure you have not disabled or deleted NT SERVICE\MSSQLSERVER or NT SERVICE\SQLSERVERAGENT virtual logins)I restarted the service.
How do I check on the virtual logins?
I did not touch that.
I'm on a Server with Windows Server 2008 R2 x64.
The virtual logins will be in the logins of the instance. They will appear with a group icon instead of a login icon. These are used by SQL Server Configuration Manager to make the appropriate permissions to Windows users that are assigned to the service. They will not be assigned to these if config manager is not used, thereby causing permissions issues.
Jared
CE - Microsoft
April 2, 2012 at 2:13 pm
Jared,
All of the Logins are intact.
As far as I recall I had this issue before I ever changed a Server via services or the configuration manager.
If I went into Services to restart a service at one point but I have subsequently restarted and/or changed the Account. Will this reset the permissions?
I tried several goggle searches on the error but no luck.
What is weird is that I can't start to SSIS Service (something to the effect, failed to start in a timely manner).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 2, 2012 at 2:16 pm
Hmm... Can you post the results of sp_configure?
Jared
CE - Microsoft
April 2, 2012 at 2:43 pm
SSIS shouldn't have anything to do with Database Mail.
What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
April 2, 2012 at 2:47 pm
Lynn Pettis (4/2/2012)
SSIS shouldn't have anything to do with Database Mail.What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
There's a bigger picture here, it's hard to track;
Wesh Corgi added a new server, and database mail's not working on the new server, but is fine on many of his other servers; he had a couple of other threads where he was wrestling with trying to get it to work.
this thread is an attempt to get any kind of email working, clearly out of frustration, as he moved off of db_mail and is trying cdonts as an alternative.
Lowell
April 2, 2012 at 2:48 pm
Lowell (4/2/2012)
Lynn Pettis (4/2/2012)
SSIS shouldn't have anything to do with Database Mail.What has me slightly confused is you talk about using Database Mail, but the code you show is using CDONTS. I was unware the Database Mail used CDONTS. I thought it used SMTP to communicate with a mail server to send emails.
Am I missing anything here?
There's a bigger picture here, it's hard to track;
Wesh Corgi added a new server, and database mail's not working on the new server, but is fine on many of his other servers; he had a couple of other threads where he was wrestling with trying to get it to work.
this thread is an attempt to get any kind of email working, clearly out of frustration, as he moved off of db_mail and is trying cdonts as an alternative.
Any chance of getting these consolidated so that we aren't flailing around trying to figure out what is going on?
April 2, 2012 at 3:07 pm
Yup! Stepping away and observing because this is clearly not what I thought... 🙂
Jared
CE - Microsoft
April 2, 2012 at 3:14 pm
In my neverending search for knowledge... I found this article. Welsh, does this help at all?
http://www.webhostgear.com/204.html
Jared
CE - Microsoft
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply