Problem in SQL Server - Service Account

Question

Problem in SQL Server - Service Account

amarnath.sahadevan

SSC-Addicted

Points: 420
More actions
June 23, 2013 at 11:19 pm

#274278

Hi All,
Recently I am getting below error in all my SQL server error log, the error occured repeatedly. I just googled for this issues, so far i didn't find the clear solution. Can any one please help me to fix this issue. Thanks
Error Log:
2013-06-24 12:30:41.49 Logon Login failed for user 'DOMAIN\SERVICEACCOUNT'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
2013-06-24 12:31:41.59 Logon Error: 18456, Severity: 14, State: 38.
Regards,
Amar

Viewing 11 posts - 1 through 10 (of 10 total)

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

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Take a look at the name of the database under option "Default database" in the properties of the LOGIN. This database is probably deleted or offline or the user doesn't have access to that database.

Correct the issue with the database or change the name to a database that is accessible for this login.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

amarnath.sahadevan SSC-Addicted Points: 420 More actions · Answer 2

Hi HanShi,

This Login is service account login and it is under Sysadmin role and the default database is "Master" DB in the Login properties.

Regards,

Amar

MiguelSQL Hall of Fame Points: 3112 More actions · Answer 3

I had a similar issue.

Check if the domain Service Account belongs to any group that's already on the Login list, and check the default database setting.

In my case, the Group was listed before (alphabetically) than the actual service account on the login list

Andrew G SSChampion Points: 12809 More actions · Answer 4

Sometimes this will tell you the group (sometimes not if theyre in multiple groups with access)

xp_logininfo 'loginName'

amarnath.sahadevan SSC-Addicted Points: 420 More actions · Answer 5

Hi,

Found the issue, in some of the servers recently we stopped the SSRS service and detached the ReportServer and ReportServerTemp DB from the SQL instances since we are not using SSRS. while running maintanence plan for transactional log back up in the instances, its trying to create transactional log backup for missing DB's, because of unavailability of the database its generating the above mentioned error.

Actually there is a maintanence plan for creating transaction log backup and it is pushed down from master server to all other target servers and option was set to database log backup task is "All user db's excluding system db's", this caused the problem.

Even if I remove the ReportServer DB's in the target servers, the maintanence plan in master server still trying to create a log backup for missing user db's in targer servers.

I just modified Maintanence Plan option to specific databases, still the error is NOT stopped generating. If I attached the ReportServer DB's again, its stopped generating the error.

Not sure, I think need to clear the entries in meta tables in target server related to maint plan... Any idea ?

Thanks for your guidance.

Regards,

Amar

Andrew G SSChampion Points: 12809 More actions · Answer 6

You need to change the default database for the login which is running the maintenance plan, it must be set to the reportserver database.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

amarnath.sahadevan SSC-Addicted Points: 420 More actions · Answer 8

Hi Andrew,

The default DB for the login properties is Master DB and also there is NO reportserver DB's since there is no SSRS service running on the server. Thanks.

Regards,

Amar

Andrew G SSChampion Points: 12809 More actions · Answer 9

I just re-read your last post and understand now, the maintenance plan is still looking for the reportserver db even though its gone.

From what I've read elsewhere, and since you've tried modifying the maintenance plan and still have the issue, you'll need to recreate the master maintenance plan so it can detect the database has been removed.

This is one of the reasons to use a custom backup script instead of maintenance plans. You can get started with Ola Hallagrens backup scripts here: http://ola.hallengren.com/sql-server-backup.html

amarnath.sahadevan SSC-Addicted Points: 420 More actions · Answer 10

Thanks Andrew !

My luck I didn't recreate the Maint plant, just modified the Maint plan and the error gets stopped after sometime.

Planned to implement the custom script in all SQL instance, thanks for the information.

Regards,

Amar