| Author |
Topic |
|
mtimmerm
Starting Member
2 Posts |
 Posted - 2010-10-27 : 15:30:49
|
| Hi,First of all I must say I find my way in SQL but I am not higly skilled dba. After my holiday I came back and I found some errors in my SQL Server 2008 R2. I asked to the sys admins if there where any change and they told me they had to rename the administrator account :(. Anyway, everything is working again but I have still one problem.Error: 18456, Severity: 14, State: 38.Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]This error message is comming back every minute into my log file. When I use the SQL Profiler and watch to the Audit login this error is not passing by.At the login properties of this user, at the user mapping page I found that the map for master and msdb is selected with the user and a default schema. ( I am not sure why this is?)The server role is public and I selected also sysadmin but that didn't help.Hopefully someone could help me to get this fixed. Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-28 : 00:29:01
|
| did they re-register the SPN after changing service account? are there linked servers pointing to this server? web servers using nt authentication? |
 |
|
|
psychotic_savage
Starting Member
25 Posts |
Posted - 2010-10-28 : 02:25:51
|
| Looks like its from an engine level connection. Have you had a look in SQL Server configuration manager to see if there is a user that matches those credentials. Maybe they need to be reconfigured. Just an idea :) |
|
|
|
mtimmerm
Starting Member
2 Posts |
Posted - 2010-10-29 : 14:51:48
|
| No, they didn't. I have fixed this finaly with SetSPN. Thanks!! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-10-29 : 15:38:29
|
Glad to help  |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-01-24 : 21:00:59
|
| Dear Guru'sI am facing the same problem.Login failed for user 'EDEALER\sqlservices'. Reason: Failed to open the explicitly specified database. [CLIENT: 172.26.4.118]EDEALER is the local hostname.sqlservices is local os id with administrator role and as well sysadm role in sql logins.I went thru all your posting on this matter. Now I am planned to setSPN, but I am not confident enough to do it since this system running as production now. I do not want to interupt the user.Please advice how to do the setSPN.regardssiva |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-25 : 11:33:37
|
| SPN is for domain account, not local account.Make sure the specified database exists, is online and that 'EDEALER\sqlservices' has permission to access it.Can 'EDEALER\sqlservices' access master? |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-01-25 : 22:30:11
|
| Yes 'EDEALER\sqlservices' can access to master database.Master as the default database for this SQL logins. and with sysadmin role too. Its a local id.This id is the SQL Service account for all the SQL Services e.gSQL Server EngineSQL Server AgentSQL Server BrowserSQL Server FullText SearchSQL Server VSS WriterSQL Server Reporting ServicesRegardssiva |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-26 : 09:03:15
|
| Since the login can access master, then it is either permissions, or the database isn't accessible.If, as you said, the account is sysadmin, then it is a database specific error.Make sure the db is online.Are you using Management Studio to connect, or some application? |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-01-29 : 22:00:51
|
| Hi russelSorry for the delay reply.I am using the SQL Studio for logins. My login is successful with 'EDEALER\sqlservices' OS logins. I can connect to the database. All the databases are in ONLINE state.I only sees the error messages in SQL Logs. I dont know from where this error is comming from?RegardsSivashiva :) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-30 : 08:33:24
|
| How often do you see it? Find what (or who) is running at 172.26.4.118.Might be an application with misspelled database name in connection string. |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-01-30 : 21:00:47
|
| The error appeares every 1 minutes.There is no application that connects using this id.For application we have another DB id. not the SQL logins.regardssivashiva :) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-31 : 07:01:54
|
| Is 172.26.4.118 the same box that SQL Server is on?Maybe it's Reporting Services?Anyway, the key is that IP address |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-02-01 : 20:39:34
|
| Dear RusselThanks for the hint, i will try and let you know the status.thanksshiva :) |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-02-02 : 00:17:11
|
| Dear russel and the rest.Thank you russel. as an interim solution, I am able to eliminate the error from sql log.As mentioned by russel, yes its is bcoz of the SQL Server Reporting Services. There is no more error recorded in sql log after i stopped the reporting services.Now my concern is, if the application need to turn on the SQL reporting services, what should i do?Should i change the service account to any other local user or domain user? currently the service account for this reporting services is local system.regardssivashiva :) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-02 : 09:50:29
|
| If you need to turn RS back on a later date, use rsconfig to configure it.I don't install services that we won't use on particular box (RS, OLAP etc). |
|
|
|
datosiva
Starting Member
18 Posts |
Posted - 2011-03-11 : 02:25:58
|
| thanks a lot russelshiva :) |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-11 : 15:09:07
|
| You're welcome :) |
|
|
|
|
Error: 18456, Severity: 14, State: 38 problem