Solved

DB Mail Configuration Wizard hangs & is not responding

Posted on 2013-01-03
10
596 Views
Last Modified: 2013-01-09
I've installed SQL Server 2008 R2 and applied SP1.
Tried to configure db mail but the Database Mail Configuration Wizard hangs and says "Not Responding".  

No error is thrown on-screen.  

No error in db logs in SQL Server.

Event Viewer on the server has this informational message:
"Login failed for user '<domain\user_id>'.  Reason Failed to open the explicitly specified database. [Client:<my_ip_address_here>]"

The userid given in the message is the one that both the MSSQLSERVER and SQL Server Agent services use.  SQL Server isn't having any other problems so I'm confused as to how there could be a problem with the userid.  Have no idea what "explicitly specified database" means.

Help!
0
Comment
Question by:Eric3141
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 2

Author Comment

by:Eric3141
ID: 38740259
That error is in the event viewer every 1 minute so may or may not have anything to do with the db mail configuration wizard.
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38740290
Go to the Login properties of that user, check the default database specified, make sure that the user has access to the DB and the DB exists in the instance.
0
 
LVL 2

Author Comment

by:Eric3141
ID: 38740357
Default db = master.  

This userid has sysadmin privileges, and I had already explicitly granted it db_owner on the Master db as well.  

After I had granted sysadmin and db_owner privileges to the user, I disconnected from db instance in SSMS (I had logged on with with another account) and tried to reconnect with the problem user.  I get this error:

Login failed for user'domain\my_user'.  Error 18456.

Log file shows state 38 as well.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 15

Expert Comment

by:Anuj
ID: 38740399
State 38 means either the default database is missing or the user is not authorized to connect to the given database. Looks odd, can you confirm you are using correctlogins?
0
 
LVL 2

Author Comment

by:Eric3141
ID: 38740665
OK, I found out the userid in question is a service account & cannot log into the domain directly.  Thus failed to log into SSMS when I tried.  Howerver, the 2 services that SQL is using are using this userid and users not getting errors and can connect via their applications.  And error I gave you is showing up in the event viewer every 1 minute.

The user already had sysadmin privileges.  I then explicitly granted db_owner on the default db (master) and restarted the services.  Errors persist.  

Our other db server uses the same network service userid and has sysadmin privileges and has no problem.  That db server is SQL 2008 while the problem one is 2008 R2 SP1.

Ideas?
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38740694
Its recommended to use a domain account for running Services, can you try using this?
0
 
LVL 2

Author Comment

by:Eric3141
ID: 38740801
I put my network userid as the logon for the service and the errors in event viewer went away.  Put the non-domain userid back and they returned.

This was not a problem on SQL 2008 on our stand alone box.  
New server (that I'm having this trouble with) is virtual and SQL 2008 R2.

Our network god is not going to want to approve the user of a domain account for a service as we've never done that here.  

Can you point me to documentation that says a regular domain account should be used?

Ideas on why this was not a problem with SQL 2008?

** Any idea the impact this will have if I don't change it?  Our webapp can connect to the database and I can log onto the db instance in SSMS.  Network god will ask me the impact (other than these entries in event viewer) if we don't change it.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38743619
Try using the sa credentials.
0
 
LVL 2

Accepted Solution

by:
Eric3141 earned 0 total points
ID: 38744442
Found the problem & resolved.

Somehow in Reporting Services Manager on the 014 db server, the location of the ReportServer db was specified to be 044 db server.  Thus the network userid used to run the SQL db services was trying to connect to the ReportServer db on 044 which does not exist because Reporting Services is not installed on that db server.  Thus the "logon failure" errors in Event Viewer on 014.

044 is a test db server.  We've not ever done anything with Reporting Services yet.  Have no idea how I managed to do this but glad I found and corrected the problem.
0
 
LVL 2

Author Closing Comment

by:Eric3141
ID: 38758231
Hey guys, I would award you credit for helping but I want to add this solution to the knowledge base in case it helps others who have a similar problem.  So I had to specify my solution as the only one.

Thx again a bunch for helping.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question