Login failed for user 'NT AUTHORITY\SYSTEM', Very straing

Question

Login failed for user 'NT AUTHORITY\SYSTEM', Very straing

Viewing 11 posts - 16 through 25 (of 25 total)

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

barry 13081 SSC Enthusiast Points: 173 More actions · Answer 1

I am seeing this also. I granted owner permission to the NTAUTHORITY account for the first database it tries to hit. It run this batch twice:

SET NOCOUNT ON

SELECT size / 128.0 as fileSize,

FILEPROPERTY(name, 'SpaceUsed') / 128.0 as fileUsed,

CASE WHEN max_size = -1 OR max_size = 268435456 THEN -1 ELSE max_size / 128 END as fileMaxSize,

CASE WHEN growth = 0 THEN 0 ELSE 1 END as IsAutoGrow,

is_percent_growth as isPercentGrowth,

growth as fileGrowth,

physical_name

FROM sys.master_files WITH (NOLOCK)

WHERE type = 0 AND is_read_only = 0

AND database_id = 36

Then this batch:

SET NOCOUNT ON

SELECT fg.name as fileGroupName,

fg.data_space_id as fileGroupId,

fg.is_read_only as fileGroupReadOnly

FROM sys.filegroups fg WHERE type = 'FG' AND fg.is_read_only = 0

Then is tries to open another db and fails and goes back and runs the first batch on the first db again.

Anyone figured this out yet?

barry

barry 13081 SSC Enthusiast Points: 173 More actions · Answer 2

barry 13081

SSC Enthusiast

Points: 173

July 24, 2014 at 10:34 am

#1732150

Oops, I didn't notice the 2nd page of replies.

djordan 4543 SSCommitted Points: 1991 More actions · Answer 3

I had a problem very similar to yours.

Do you have Operations Manager by any chance?

I do and operations manager was probing a database every 15 mins for stats info. The prob was the db was in single user mode therefore I kept getting a login error.

CooLDBA Hall of Fame Points: 3301 More actions · Answer 4

andersson_par (5/3/2013)
SCOM it is!
To make this work use a service account to run the scom agent service with this:
Member of "Performance Monitor Users" local group
Member of "Event Log Readers" local group if OS is Windows 2008 or Windows 2008 R2
Member of "Distributed COM Users" local group if SQL Server is running in a clustered configuration
Full access to Cluster if SQL Server is running in a clustered configuration
Permission to Log On Locally
SQL permission to VIEW ANY DEFINITION
SQL permission to VIEW SERVER STATE
SQL permission to login in each database including system databases
Member of "SQLAgentReaderRole" in msdb database

Thanks for sharing!

jazzgilbert SSC Veteran Points: 221 More actions · Answer 5

Thanks anthony.green, didn't know about/how to run a trace, that helped me.

sqlsolver SSC Veteran Points: 243 More actions · Answer 6

You can resolve this by adding read access for the system account to the database. For the error:

Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'model'.

adding a mapping to the model db with public access for the NT AUTHORITY\SYSTEM login eliminated the error.

Jay@Work SSCrazy Points: 2633 More actions · Answer 7

Thought I would add to this thread as I have just had the exact same issue and have nailed the culprit.

System Center Management Service Host Process

The network admin set-up SCOM to monitor SQL but did not set the "run as" account. Therefore it runs under the nt authority/system account and subsequently gets access denied when trying to poll the DBs. My trace and SQL error logs were identical to that posted above.

Sean Pearce SSCoach Points: 15999 More actions · Answer 8

This was resolved 3 years ago.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

IQVasya Valued Member Points: 65 More actions · Answer 9

What can I do if the login fails on my DR server? We use log shipping, so all DBs are in restoring mode.

Jay@Work SSCrazy Points: 2633 More actions · Answer 10

Indeed. Apologies. Like another earlier poster I too missed the subsequent pages of replies and didn't see that the offending process had been identified.

I wish I had it would have saved me an hour or so of further digging around before finding what it was and coming back to report.

JasonRowland SSC Eights! Points: 951 More actions · Answer 11

I had some login failures today from NT Authority\SYSTEM and it turned out to be from SCOM.

Here's the error from the SQL Error log:
Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'DatabaseName'

and here's the message from the Operations Manager log:
Management Group: PRODGSSCOM. Script: DiscoverSQL2014FileGroups.js : Cannot login to database [servername][MSSQLSERVER:DatabaseName]