Database mail problem With profile

Question

Database mail problem With profile

steve.pestoff

Mr or Mrs. 500

Points: 525
More actions
March 6, 2012 at 2:07 pm

#257090

I have just installed a new 2008 R2 64bit sql server. I tried setting up database mail on this server.
I created a new profile with the same name as our prod 2008 32 bit server with an R2 at the end of the name of the profile.
Profile for 2008 32 bit is PROD
Profile for 2008 R2 is PRODR2
I used the same SMPT account for the R2 that is used in 2008 32bit
When I bounced SQLagent , the log has the following error.
unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.Data.SqlClient.SqlException: profile name is not valid
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStre)
2012-03-05 16:12:10 - ! [355] The mail system failed to initialize; check configuration settings
When I go to Database Mail configuration on the R2 server there are no profiles.
I tried to create the PRODR2 profile again and it gave me a message that the profile already exists.
When I go to Database Mail configuration on the 2008 32 bit server. I see 2 profiles.
The original profile that was there and the PRODR2 profile.
Any ideas on where I messed up?
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113446 More actions · Answer 1

when you created the r2 profile did you have ssms connected to multiple servers?. You might have opened the wrong servers mail config wizard hence why its showing on the 32bit server and not the 64bit server.

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 2

That may have been the problem.

I created another profile on the R2 server and I am able to send a test email using the profile.

I restarted SQLagent and got the same error as before.

Ant-Green SSC Guru Points: 113446 More actions · Answer 3

that maybe down to permissions, I've seen that before after a restart of the agent it still doesn't work

can you impersonate the SQL agent account and try sending a mail via ssms to resolve that issue on permissions

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 4

I switched user to the SQLService logon. I connect to the R2 database with the SQLService logon.

I successfully sent a test email with the SQLService id.

Ant-Green SSC Guru Points: 113446 More actions · Answer 5

I take it your running the same account for both the server and agent?

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

The database mail setup wizard has an annoying bug that prevents saving changes correctly when multiple instances are open at the same time: http://spaghettidba.com/2011/07/15/an-annoying-bug-in-database-mail-configuration-wizard/

You may have run into this bug.

-- Gianluca Sartori

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 7

I am running the same account for both . It's a windows domain account.

I can send a test email with the profile. However when I use a non sysadmin account to log into sql server and try execute msdb.dbo.sp_sendmail I get the following error.

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42

profile name is not valid

I ran select * from msdb.dbo.sysmail_profile

and the results are

profile_idnamedescriptionlast_mod_datetime last_mod_user

1PRODR2NULL2012-03-06 16:23:32.750 my id

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 8

I have corrected the problem I can now send mail with a non sysadmin account. However when I bounce SQLAGENT I still get the error

SQLSERVERAGENT starting under Windows NT service control

2012-03-07 10:22:52 - ! [260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.Data.SqlClient.SqlException: profile name is not valid

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStre)

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

Weird.

Have you tried dropping and recreating the profile?

-- Gianluca Sartori

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 10

I dropped and recreated the profile. I still get the same error when I restart the service

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 11

I finally got it to work. I went to properties for SQL Server Agent > Alert System and enabled mail profile.

Thanks for all the help !

steve.pestoff Mr or Mrs. 500 Points: 525 More actions · Answer 12

I finally got it working !!! I went to properties on Sql Server Agent > alerts and enabled the mail profile.

Thanks all for your help.