Logon to server '%Servername%' failed SQL Server issue)

Question

Logon to server '%Servername%' failed SQL Server issue)

rahultiwari7

SSC Veteran

Points: 231
More actions
May 11, 2018 at 1:07 am

#330810

Dear All,
As said in subject i am getting Frequent Event Logs in my SQL Server from long time and due to this a lot of connectivity drop from database is happening. can anyone suggest where should i look up to resolve this issue. as this Server is on High end Hardware tried monitoring performance no issues found.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Is msdb the default database for the logins that are failing? If so, probably best to change it either to master, or to whatever single user database the login uses.

John

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 2

John Mitchell-245523 - Friday, May 11, 2018 2:32 AM
Is msdb the default database for the logins that are failing? If so, probably best to change it either to master, or to whatever single user database the login uses.
John

No msdb is not the default database. its master in all the users who access SQL Server (as our SQL server has multiple DB's with multiple users accessing it checked everyone's properties for Default database, it's "master" in our case). I saw "Login failed for user NT Authority/Network Service" what does that mean for SQL DB?

Thank you early reply.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

In that case, I'm guessing that the connection strings specify msdb as the database. A quick and dirty fix would be to grant the logins access to msdb; the proper way of doing it is to examine the application code to find out why it's attempting to access that database.

Edit: what account does the SQL Server Agent service run under? If it's a local system account, try changing to a domain account (make sure you do this in SQL Server Configuration Manager) and see whether that makes any difference.

John

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 4

John Mitchell-245523 - Friday, May 11, 2018 3:01 AM
In that case, I'm guessing that the connection strings specify msdb as the database. A quick and dirty fix would be to grant the logins access to msdb; the proper way of doing it is to examine the application code to find out why it's attempting to access that database.
John

Can you be more specific! what i have to do for
checking connection string and is msdb access can be given to all users?

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 5

John Mitchell-245523 - Friday, May 11, 2018 3:01 AM
In that case, I'm guessing that the connection strings specify msdb as the database. A quick and dirty fix would be to grant the logins access to msdb; the proper way of doing it is to examine the application code to find out why it's attempting to access that database.
Edit: what account does the SQL Server Agent service run under? If it's a local system account, try changing to a domain account (make sure you do this in SQL Server Configuration Manager) and see whether that makes any difference.
John

it is a domain account with "Domain Admins" access

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

rahul.t 65927 - Friday, May 11, 2018 4:31 AM
John Mitchell-245523 - Friday, May 11, 2018 3:01 AM
In that case, I'm guessing that the connection strings specify msdb as the database. A quick and dirty fix would be to grant the logins access to msdb; the proper way of doing it is to examine the application code to find out why it's attempting to access that database.
John
Can you be more specific! what i have to do for
checking connection string

Look at your application code and find every instance where "msdb" appears in the connection string

and is msdb access can be given to all users?

It can be, yes, but I wouldn't recommend it. That's why I said it's a quick and dirty fix.

rahul.t 65927 - Friday, May 11, 2018 4:33 AM
John Mitchell-245523 - Friday, May 11, 2018 3:01 AM
Edit: what account does the SQL Server Agent service run under? If it's a local system account, try changing to a domain account (make sure you do this in SQL Server Configuration Manager) and see whether that makes any difference.
John
it is a domain account with "Domain Admins" access

That's very bad. Anyone with sufficient access to SQL Server will be able to create a SQL Server Agent job that could cause all sorts of damage: add other users to Domain Admins, remove computer accounts from AD, and so on.

My guess now is that you have a service running under a local system account (I suspected it was SQL Server Agent, but you say it's running under a domain account) that's attempting to connect to msdb.

John

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 7

John Mitchell-245523 - Friday, May 11, 2018 4:45 AM
rahul.t 65927 - Friday, May 11, 2018 4:31 AM
John Mitchell-245523 - Friday, May 11, 2018 3:01 AM
In that case, I'm guessing that the connection strings specify msdb as the database. A quick and dirty fix would be to grant the logins access to msdb; the proper way of doing it is to examine the application code to find out why it's attempting to access that database.
John
Can you be more specific! what i have to do for
checking connection string
Look at your application code and find every instance where "msdb" appears in the connection string
and is msdb access can be given to all users?

It can be, yes, but I wouldn't recommend it. That's why I said it's a quick and dirty fix.
rahul.t 65927 - Friday, May 11, 2018 4:33 AM
John Mitchell-245523 - Friday, May 11, 2018 3:01 AM
Edit: what account does the SQL Server Agent service run under? If it's a local system account, try changing to a domain account (make sure you do this in SQL Server Configuration Manager) and see whether that makes any difference.
John
it is a domain account with "Domain Admins" access
That's very bad. Anyone with sufficient access to SQL Server will be able to create a SQL Server Agent job that could cause all sorts of damage: add other users to Domain Admins, remove computer accounts from AD, and so on.
My guess now is that you have a service running under a local system account (I suspected it was SQL Server Agent, but you say it's running under a domain account) that's attempting to connect to msdb.
John

does that also leads to connection drops in DB's ? because i can see when this event generates in SQL server Agent Log same time Windows Event Log records Event ID 7034 (SQL server Terminated unexpectedly). are they both connected? (i mean SQL login failed and SQL server crashing at same time)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

rahul.t 65927 - Friday, May 11, 2018 4:53 AM
does that also leads to connection drops in DB's ? because i can see when this event generates in SQL server Agent Log same time Windows Event Log records Event ID 7034 (SQL server Terminated unexpectedly). are they both connected? (i mean SQL login failed and SQL server crashing at same time)

I don't know - I can't see your screen. Are you saying that SQL Server crashes every time you have one of these login failures? How often does that happen?

John

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 9

John Mitchell-245523 - Friday, May 11, 2018 5:02 AM
rahul.t 65927 - Friday, May 11, 2018 4:53 AM
does that also leads to connection drops in DB's ? because i can see when this event generates in SQL server Agent Log same time Windows Event Log records Event ID 7034 (SQL server Terminated unexpectedly). are they both connected? (i mean SQL login failed and SQL server crashing at same time)
I don't know - I can't see your screen. Are you saying that SQL Server crashes every time you have one of these login failures? How often does that happen?
John

its not random but 45-90min everyday.

Sue_H SSC Guru Points: 90860 More actions · Answer 10

rahul.t 65927 - Friday, May 11, 2018 5:14 AM
John Mitchell-245523 - Friday, May 11, 2018 5:02 AM
rahul.t 65927 - Friday, May 11, 2018 4:53 AM
does that also leads to connection drops in DB's ? because i can see when this event generates in SQL server Agent Log same time Windows Event Log records Event ID 7034 (SQL server Terminated unexpectedly). are they both connected? (i mean SQL login failed and SQL server crashing at same time)
I don't know - I can't see your screen. Are you saying that SQL Server crashes every time you have one of these login failures? How often does that happen?
John
its not random but 45-90min everyday.

What is in the SQL Server error logs around this time? For the login failures, what is in the SQL Server logs including the state number, etc for the login failures?
Are other logins failing as well? That should all be in the SQL Server error log, the one you posted is from SQL Server Agent error log.

Sue

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 11

Sue_H - Friday, May 11, 2018 7:45 AM
rahul.t 65927 - Friday, May 11, 2018 5:14 AM
John Mitchell-245523 - Friday, May 11, 2018 5:02 AM
rahul.t 65927 - Friday, May 11, 2018 4:53 AM
does that also leads to connection drops in DB's ? because i can see when this event generates in SQL server Agent Log same time Windows Event Log records Event ID 7034 (SQL server Terminated unexpectedly). are they both connected? (i mean SQL login failed and SQL server crashing at same time)
I don't know - I can't see your screen. Are you saying that SQL Server crashes every time you have one of these login failures? How often does that happen?
John
its not random but 45-90min everyday.
What is in the SQL Server error logs around this time? For the login failures, what is in the SQL Server logs including the state number, etc for the login failures?
Are other logins failing as well? That should all be in the SQL Server error log, the one you posted is from SQL Server Agent error log.
Sue

These are yesterday's Logs which i have been trying to debug. i see a lot of issues with my DB Server :ermm: (the Event ID u are asking each time SQL server service terminates is 7034)
Other Logins are good they are working fine i am checking it frequently. Yes that Log is from SQL Server Log. Windows Log shows error only if SQL Server service terminates. so mostly i have to rely on these logs.

(Think i might have to start looking for another job! (JK)

rahultiwari7 SSC Veteran Points: 231 More actions · Answer 12

rahultiwari7 - Friday, May 11, 2018 5:14 AM
John Mitchell-245523 - Friday, May 11, 2018 5:02 AM
rahul.t 65927 - Friday, May 11, 2018 4:53 AM
does that also leads to connection drops in DB's ? because i can see when this event generates in SQL server Agent Log same time Windows Event Log records Event ID 7034 (SQL server Terminated unexpectedly). are they both connected? (i mean SQL login failed and SQL server crashing at same time)
I don't know - I can't see your screen. Are you saying that SQL Server crashes every time you have one of these login failures? How often does that happen?
John
its not random but 45-90min everyday.

"The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 60000 milliseconds: Restart the service". Does this anyway related to it? (Event ID 7031)

Sue_H SSC Guru Points: 90860 More actions · Answer 13

rahultiwari7 - Monday, May 14, 2018 10:51 PM
"The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 60000 milliseconds: Restart the service". Does this anyway related to it? (Event ID 7031)

Yes...that's the event id for an unexpected termination of a service.
That log is pretty ugly. SQL Server service keeps shutting down so you want to review the logs on startup and see what is going on when that happens. And then SQL Server Agent can't log on at all. It almost looks like one of the system databases is what is causing the problems. Look at one of the SQL Server error logs when it starts up. I think there should be some more errors in there when starting up.

Sue

​Logon to server '%Servername%' failed SQL Server issue)

Logon to server '%Servername%' failed SQL Server issue)