Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem in SQL Server - Service Account Expand / Collapse
Author
Message
Posted Sunday, June 23, 2013 11:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:52 PM
Points: 14, Visits: 105
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

Post #1466578
Posted Sunday, June 23, 2013 11:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:18 AM
Points: 2,224, Visits: 2,660
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’! **
Post #1466583
Posted Monday, June 24, 2013 12:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:52 PM
Points: 14, Visits: 105
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
Post #1466599
Posted Tuesday, June 25, 2013 3:51 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:57 PM
Points: 88, Visits: 714
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
Post #1467399
Posted Tuesday, June 25, 2013 9:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 1,151, Visits: 1,588
Sometimes this will tell you the group (sometimes not if theyre in multiple groups with access)
xp_logininfo 'loginName'

Post #1467433
Posted Tuesday, June 25, 2013 11:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:52 PM
Points: 14, Visits: 105
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
Post #1467450
Posted Wednesday, June 26, 2013 1:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 1,151, Visits: 1,588
You need to change the default database for the login which is running the maintenance plan, it must be set to the reportserver database.
Post #1467474
Posted Wednesday, June 26, 2013 1:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1467481
Posted Wednesday, June 26, 2013 1:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:52 PM
Points: 14, Visits: 105
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
Post #1467484
Posted Wednesday, June 26, 2013 1:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:16 AM
Points: 1,151, Visits: 1,588
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
Post #1467489
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse