August 10, 2009 at 10:14 am
Hi,
On one of our SQL2005 Server Standard edition with SP3 (9.0.4220), I am not able to open SQL error log in SSMS and not able to expand SQL Error Log Folder in SSMS\Management\SQL Server Logs tree view.
When I click + sign before SQL Server Logs folder in SSMS, getting an error msg box with error:
Failed to retrieve data for this request.(Microsoft.SqlServer.SmoEnum)
Additional Information:
An exception occurred while executing a Transect-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
A severe error occurred on the currentcommand. The results, if any, should be discarded
(Microsoft SQL Server)
Application Event log records this:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:0, OS: 0 [Microsoft][SQL Native Client]Invalid connection string attribute.
Exec Sp_Enumerrorlogs returns this:
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found
SQL Agent log is accessible from SSMS and log shows bunch of below errors:
[382] Logon to server 'DEVSQL05' failed (ConnAttemptCachableOp)
[165] ODBC Error: 0, Invalid connection string attribute [SQLSTATE 01S00]
[252] Failed to retrieve operator 1 from the server
Any help on this issue?
Thanks.
JP
August 10, 2009 at 11:32 am
Hello,
to work with ERROLOG you can check the following links:
http://sqlpost.blogspot.com/2009/07/sql-server-working-with-errorlog.html
http://sqlpost.blogspot.com/2009/07/sql-server-sql-tips-select-errolog.html
In the other hand, you can check with windows explorer the default path where the errolog files are located:
C:\Program Files\Microsoft SQL Server\MSSQL.#installation\MSSQL\LOG
in my case is
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
you can check there the possible issues, if the files are there and if the issue is the SSMS itself, you can trying resintalling your SSMS tool.
With Regards,
Victor Alvarez
http://sqlpost.blogspot.com
August 10, 2009 at 11:44 am
Victor. Thaks for your reply.
I checked error log path assigned to sql and also in registry. They all looks fine. I can open the error log file from windows explorer. Also noticed that SQL error log shows logs related backups/restore but corresponding SQL Agent jobs shows no history for these runs.
I tried to view SQL error log from SSMS from different SQL machines but no success with this particular SQL server. It opens fine for other SQL Servers.
No idea where I should look for.
Thanks.
JP
August 10, 2009 at 11:54 am
Can you view the logs logged onto the SQL server itself as an Administrator or Sysadmin?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 10, 2009 at 12:05 pm
************
Can you view the logs logged onto the SQL server itself as an Administrator or Sysadmin?
------------
Shawn Melton
Forum Etiquette by Jeff Moden
*************
My account has local Admin/sysadmin rights on SQL Server. I am NOT able to see error logs from remote clients or from local clients on the Server itself.
Thanks.
September 14, 2009 at 10:39 am
I am having the same issue (unable to read the sql ERRORLOG trhough SSMS or sp_readerrorlog).
sp_readerrorlog error message:
Msg 22004, Level 16, State 1, Line 0
error log location not found
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
What I can say with a high level of confidence is that it has nothing to do SSMS. My specific issue is with reading the ERRORLOG on a SQL Server 2005 Express Edition instnace (multiple to be more exact) which is running Windows Vista. The same SSMS client is able to read the ERRORLOG on approximately 70 other SQL Server Instances.
I still do not know what the solution is but am wondering if it may have something to do with firewall \ security \ networking settings or possibly MSDTC.
I will provide feedback when I find a solution - if anyone has additional ideas I would appreciate the help.
Thanks,
Jack
September 14, 2009 at 1:12 pm
Are you running the services under a domain account? If so make sure the service account has access to the folder.
September 14, 2009 at 2:56 pm
The SQL Server services actually are configured to run under the Network Service account.
I think you are on to something as I am getting a login failure being logged in Event Log when I try to view the SQL ERRORLOG.
I will switch the SQL services to run under a domain account (which is a member of the local ADMINs group) and see if that resolves the issue.
As a side note, here are the exceprts from the Application event log.
[font="Courier New"]Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. [CLIENT: ]
The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
Severity: 16 Error:18456, OS: 18456 [Microsoft][SQL Native Client][SQL Server]Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.
the message resource is present but the message is not found in the string/message table[/font]
September 14, 2009 at 4:22 pm
I changed the MSSQLSERVER service to run under a domain account and that resolved the issue.
Interestingly enough, on other SQL Instances running on Win2K3 - we run the MSSSQLSERVER service as the Network Service built-in account and vieing the ERRORLOG works just fine.
Details
SQL Ver = 2005.090.2047.00
SQL Edition = Express Edition
Windows Ver = 5.2 (3790) (Win2K3)
SQL services ruuning under - Network Service
ERRORLOG viewing = Ok
SQL Ver = 2005.090.2047.00
SQL Edition = Express Edition
Windows Ver = 6.0 (6001) (Vista)
SQL services ruuning under - Network Service
ERRORLOG viewing = Fails
But, the issue that I mentioned here is happening on the Vista machine. So, it must be a Vista setting issue, but I do not know exacly what. I may open a case with Microsoft.
Also, if you have any additional isight, I would appreciate it.
August 5, 2010 at 7:47 pm
I ran into the same issue. My solution was to add the machine name to the loopback address:
127.0.0.1 localhost vmwindows1
Where vmwindows1 is the node hosting the SQL Server instance.
September 27, 2010 at 1:02 pm
Hi All,
Today, finally I was able to fix this problem with 'Errolog location not found'.
Thanks to all for ther post that helped me eliminate other possibilies.
On the server having this problem, I found this registry entry which I thought unusual and was not there on the servers w/o such problem.
\\Hkey_local_Machine\Microsoft\MSSQLServer\Client\ConnectTo.
When I created Server alias, this entry was updated and suddenly problem go away. Error Log was opening fine from SSMS as well using sps.
For further testing, i removed Server Alias from SQL Config manager, this registry entry disappeared. So the corrupted registry key was the culprit. I suspect this key was created when Oracle Client tools was installed on this SQL Server.
Thanks.
September 12, 2011 at 9:38 pm
Thanks a lot for your suggestion. Its working ...working great!! I would like to appreciate you.
February 13, 2012 at 9:02 pm
just been reading this post, had exactly the saem problem on 2008 SQL server that is not in a domain. Added the server/instance name to the loopback in the hosts file -- everything now works.
Thank you so much.
Steve
May 21, 2012 at 10:55 am
I too had the similiar issue with unable to open the error log along with the following error in eventviewer:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.
I was also unable to connect to SSMS using Windows authentication with error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
Solution:
I disabled the authentication loopback check by setting the DisableLoopbackCheck value in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa registry subkey to 1 and it worked for me.
To set the DisableLoopbackCheck registry entry to 1, follow below steps on all nodes of cluster.
a. Click Start, click Run, type regedit, and then click OK.
b. Locate the following registry path:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
c. Right-click Lsa, select New, and then click DWORD Value.
d. Type DisableLoopbackCheck, and then press ENTER.
e. Right-click DisableLoopbackCheck, and then click Modify.
f. In the Value data box, type 1, and then click OK.
Thanks,
Alex Austin
SQL DBA
June 12, 2012 at 11:46 pm
Thanks much Jeffrey! Adding the loop back address to the hosts file worked for me...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply