SOLVED: 5.2.8.2 Login failed for user 'NT AUTHORIT...

PDinCA · March 8, 2012 4:54PM

Every instance I login to is telling me the above, with respect to the master database, and tells me it cannot refresh the suggestions.

I am logging into each instance that are ALL on the the SAME Windows domain, with my Windows credentials for the SAME domain - why "Anonymous"?

I am the ADMIN on EVERY box, without exception. I am in the sysadmin role, without exception.

This NEVER happened in 5.2.6, which I was on until the update this morning.

Please fix this apparent bug.

UPDATE @ 23:22 UTC:
Uninstalled this version because even when I select a database and refresh suggestions, I GET NO TABLES AT ALL!!! GRRRRRR!

FURTHER UPDATE:
Really hosed! 5.2.6 gives me the SAME error.

Ideas, please?????????

ANOTHER UPDATE: 00:18 hrs UTC March 8th:
1. Rebooted the SSMS Server (it's Windows after all)
2. Logged into Dev server. SQL Prompt 5.2.6 promptly BARFs - pun intended.
3. Restarted SSMS.
4. Disabled the SQL History feature of SSMS Tools.
5. Logged into Production using SQL Authentication.
6. SQL Prompt didn't barf.
7. Pushed my luck by logging into the Dev server and... NO BARF!
Hypotheses:
1. SQL Prompt 5.2.8.2 and SSMS Tools don't play nice together and 5.2.8.2 leaves something behind that 5.2.6 also falls over.
2. SQL Prompt 5.2.8.2 initializes differently from 5.2.6 and left behind something that SSMS continued to load/reference that required a SQL Authentication login to rectify, i.e., SSMS Tools interference may be a red herring and it's a Windows vs. SQL login problem.

There's still some kind of issue - I won't be upgrading SQL Prompt to 5.2.8.2 any time soon!

Brian Donahue · March 12, 2012 1:45PM

I can't say what the problem is, based on this information, but if you get anynymous logon and you are logged into the same domain as the SQL Server, then it must be some sort of double-hop issue (maybe going through a linked server, to another SQL Server, and NTLM does not allow that). Feel free to email the logs into support and quote F0058243.

PDinCA · March 12, 2012 4:07PM

Double-hop is highly doubtful for two reasons:

1. I "fixed" the problem by logging in with a SQL Login, then immediately logging into the same server with my Windows credentials. 2. I've been connecting to the target server, from the central development server, for over a year with no issues until 5.2.8.2 came along. As I've uninstalled 5.2.8.2, will the logs remain? If so, where would I find them, please?

Brian Donahue · March 13, 2012 5:09AM

All Red Gate tools leave the logs in the app data folder: %userprofile%\appdata\local\red gate\sql prompt 5 .

PDinCA · March 13, 2012 1:13PM

Logs emailed. Thanks, Brian.

PDinCA · March 14, 2012 1:37PM

Please point me to a download for the 5.2.8.2 version as, despite having uninstalled that version and installing 5.2.6 from the 2/17/2012 SQLToolbelt.exe, SSMS is still showing 5.2.8.2 as the current version.

Thanks.

Brian Donahue · March 14, 2012 2:01PM

You can try ftp://support.red-gate.com/patches/sql_ ... .2.6.1.exe .

PDinCA · March 14, 2012 2:05PM

Well, it turns out that what the sticky on the forum page at http://www.red-gate.com/messageboard/viewtopic.php?t=14857 ALLEGES to be a download of 5.2.6 is actually a direct download of 5.2.8.2. http://www.red-gate.com/products/sql-development/sql-prompt/download

(Remainder of original post removed problem persists...)

PDinCA · March 14, 2012 2:15PM

And, just like that, the bug RETURNS!

I'll hook up the Red Flag and see what gives...

"SOLVED" removed from the title

PDinCA · March 14, 2012 2:23PM

Emailed you, Brian, the debug file content, which is clear of all exceptions:

<?xml version="1.0" encoding="utf-8"?><RedFlagResults xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ProgramName="smss " OSVersion="Microsoft Windows NT 5.2.3790 Service Pack 2" DotNetVetsion="" Bitenss="32"><Exceptions /><Modules /><Messages /><AppDomainList /></RedFlagResults>

Still need help, because this happens on every query window open and I need it gone so I can get some work done...

PDinCA · March 14, 2012 2:58PM

Removed 5.2.8.2 and installed 5.2.6.1 from the link in Brian's post.

SAME RESULT.

Version confirmed to be 5.2.6.1

Assistance requested...

PDinCA · March 14, 2012 3:32PM

Moved on...

Connected via SQL Login.

Connected using Windows login.

Problem goes away.

PDinCA · March 14, 2012 3:34PM

Ran "Check for Updates" - NONE AVAILABLE!

Used prior 5.2.8.2 download...

PDinCA · March 14, 2012 3:49PM

Same issue w/ 5.2.8.2 re-update.

SQL then Windows login cleared it.

Working... :roll:

Brian Donahue · March 15, 2012 8:21AM

When this sort of thing happens, I usually suspect daffy .NET connection pooling. You could try disabling pooling by adding "Pooling=false" to the "additional connection parameters" in SSMS if this happens again.

PDinCA · March 16, 2012 7:54PM

Added Pooling=false (pasted the exact phrase sans double-quotes from the post above) and I get snippets but ALL tables are missing.

The same, original, error message appears connecting to ANY server with WINDOWS Authentication. EVERY server is on the same domain as the development server. I am 1 of only TWO Users connected to the server via RDP from W7 using TCP/IP, logging in with credentials on the Development Server's domain.

I have logged off the box and logged back again.

Ran Windows Update and rebooted the server, too.

I took the DRASTIC step of using Revo Uninstaller to rip 5.2.8.2 off the box before the reboot, then installed 5.2.2 after the reboot and IT WORKS! Using Revo, I cleared all trace of the Experimental Features registry content, plus deleted all the config xml files.

I am officially DEAD IN THE WATER WITH REGARD TO 5.2.6 and 5.2.8.2. Pity, because partial matching was VERY useful. Assistance hereby requested on Monday March 19th 2012. I'm on Pacific Time. Please email me with steps I can assist with debugging this catastrophic issue. Thanks.

Box:
Windows Server 2003 R2 Standard SP2 with all latest updates. 32-bit. At the time of writing, of the 3.5GB memory, 1.8GB is available. SSMS is 2008R2 targeting SQL2005EE instances.

Mike U · March 19, 2012 6:47AM

I've had a look through the detailed log of changes between 5.2.2 and 5.2.6, and the only change I can spot that would affect database connections is the experimental support for synonyms.

With the "synonym suggestions" experimental feature enabled, SQL Prompt will try to identify the target object type for all synonyms present in the database. If you have a synonym pointing to a target on a linked server, and the linked server connection causes problems, I could imagine that the login error you have described could occur. However, SQL Prompt should not even attempt to load synonym information unless the "synonym suggestions" experimental feature is enabled. (As an aside, if this was enabled in 5.2.8, it would remain enabled in a downgrade to 5.2.6, because the enabled state is stored in %LocalAppData%\Red Gate\SQL Prompt 5\PluginOptions.xml)

One way to find out whether this is the cause of the problem would be to use SSMS to connect to the server and manually run the same query that SQL Prompt uses to obtain the synonym information:

USE &#123;test_database_name&#125;;

SELECT  object_id ,
             schema_id ,
             name ,
             base_object_name ,
             OBJECTPROPERTYEX&#40;object_id, 'BaseType'&#41; AS base_type
FROM     sys.synonyms;

It sounds like the problem is occurring on master, so the first thing to try would be putting master in place of {test_database_name}.

If this also produces an error, it would indicate that something in the synonym configuration is causing a problem. You should still be able to work with the latest version of SQL Prompt as long as the synonym experimental feature is disabled. If the query doesn't cause any problems, then something else must be going on, and I'll need to investigate it further with you.

PDinCA · March 19, 2012 12:13PM

Thanks, Mike.

I'll install SQL Prompt on a different server on the same domain that can also access other servers/instances. I'll run 5.2.2 first, then 5.2.6 and run the synonym query.

Will post when done...

PDinCA · March 19, 2012 1:24PM

Well knock me down with a feather!

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Query RESULTS are OK, but the exception still occurs.

object_id      schema_id   name                                  base_object_name                                                          base_type
1339151816         1       t_meter_raw_source                &#91;xcProd1&#93;.&#91;xcp_trans&#93;.&#91;dbo&#93;.&#91;t_meter_raw&#93;                      NULL
1355151873         1       t_location_variable_ref_source &#91;xcProd1&#93;.&#91;xcp_trans&#93;.&#91;dbo&#93;.&#91;t_location_variable_ref         NULL
2091154495         1       t_meter_clean_source             &#91;xcProd1&#93;.&#91;xcp_trans&#93;.&#91;dbo&#93;.&#91;t_meter_clean&#93;                    NULL

The problem is due to LINKED SERVER definitions on the target Server.

I decided to script out the Linked Server on BOX3 from BOX2. That Linked Server points to BOX1 on the SAME domain. I'd forgotten that to define or script-out THAT Linked Server def I need to be ON THE BOX. So, when I tried THAT with 5.2.2.1 BTW:

===================================

Script failed for Synonym 'dbo.t_location_variable_ref_source'.  &#40;Microsoft.SqlServer.Smo&#41;

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+&#40;&#40;KJ_PCU_Main&#41;.110617-0038+&#41;&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+Synonym&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList&#40;DependencyCollection depList, SqlSmoObject&#91;&#93; objects&#41;
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList&#40;Urn&#91;&#93; urns, SqlSmoObject&#91;&#93; objects&#41;
   at Microsoft.SqlServer.Management.Smo.Scripter.Script&#40;Urn&#91;&#93; urns, SqlSmoObject&#91;&#93; objects&#41;
   at Microsoft.SqlServer.Management.Smo.Scripter.EnumScript&#40;Urn&#91;&#93; urns&#41;
   at Microsoft.SqlServer.Management.Smo.Scripter.Script&#40;Urn&#91;&#93; urns&#41;
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptGenerator.ScriptCreate&#40;SqlTextWriter sqlwriter, Server server, Urn&#91;&#93; urns, SqlScriptOptions options&#41;
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ScriptNodeActionContext.Script&#40;SqlTextWriter writer&#41;
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.SqlScriptMenu.OnScriptItemClick&#40;Object sender, EventArgs e&#41;

===================================

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. &#40;.Net SqlClient Data Provider&#41;

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.5292&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------
Server Name: xcDev3
Error Number: 18456
Severity: 14
State: 1
Line Number: 1


------------------------------
Program Location:

   at System.Data.SqlClient.SqlConnection.OnError&#40;SqlException exception, Boolean breakConnection&#41;
   at System.Data.SqlClient.SqlInternalConnection.OnError&#40;SqlException exception, Boolean breakConnection&#41;
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning&#40;TdsParserStateObject stateObj&#41;
   at System.Data.SqlClient.TdsParser.Run&#40;RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj&#41;
   at System.Data.SqlClient.SqlDataReader.HasMoreRows&#40;&#41;
   at System.Data.SqlClient.SqlDataReader.ReadInternal&#40;Boolean setTimeout&#41;
   at System.Data.SqlClient.SqlDataReader.Read&#40;&#41;
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery&#40;ExecuteSql execSql, String query&#41;
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider&#40;StringCollection query, Object con, StatementBuilder sb, RetriveMode rm&#41;
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults&#40;StringCollection query, Object con, StatementBuilder sb&#41;
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData&#40;ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb&#41;
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure&#40;SqlEnumResult sqlresult, ResultType resultType&#41;
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult&#40;EnumResult result&#41;
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData&#40;EnumResult erParent&#41;
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData&#40;&#41;
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData&#40;Request req, Object ci&#41;
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData&#40;Object connectionInfo, Request request&#41;
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader&#40;Request req&#41;
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader&#40;String&#91;&#93; fields, OrderBy&#91;&#93; orderby&#41;
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeKeepDirtyValues&#40;&#41;
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker&#40;DependencyCollection depList, SqlSmoObject&#91;&#93; objects&#41;
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList&#40;DependencyCollection depList, SqlSmoObject&#91;&#93; objects&#41;

Very clear that Synonym to a linked-server is the culprit.

The actual Synonym and Linked Server definitions are (obfuscated)

CREATE SYNONYM &#91;dbo&#93;.&#91;t_location_variable_ref_source&#93; FOR &#91;BOX1&#93;.&#91;DBtrans&#93;.&#91;dbo&#93;.&#91;t_location_variable_ref&#93;

EXEC master.dbo.sp_addlinkedserver @server = N'BOX1', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BOX1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BOX1',@useself=N'False',@locallogin=N'THISDOMAIN\AppAdmin',@rmtuser=N'RawTransfer',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BOX1',@useself=N'False',@locallogin=N'THISDOMAIN\DBAdmin',@rmtuser=N'RawTransfer',@rmtpassword='########'
GO

(If the Linked Server def looks funky it's because it was left alone once working...

)

Apologies to Brian Donahue for overlooking the double-hop comment - this is that issue... :oops:

PDinCA · March 19, 2012 5:06PM

Upgraded back to 5.2.8.2 and made sure Synonym feature in Experimental is OFF. All is well!

Changed title of thread to SOLVED:

As part of the development of the Synonym feature, please use case F0058243 and this thread as collateral to fix the bug for prime-time.

Regards,

Stephen.

tjanuario · March 21, 2012 12:12PM

I just had a user who was having a similar problem, although not on every server. He just recently updated from 5.1 to 5.2 and was getting the same error (cannot retrieve objects from master" Login failed for user "NT AUTHORITY\ANONYMOUS LOGON").

We finally determined that the cause was a new feature, synonym suggestion, which was turned on. He turned it off and the suggestions started populating.

Mike U · March 21, 2012 12:24PM

Just wanted to let you know that in the next release (which should be out soon) we've added better error handling to the synonym loading code.

This should mean that errors dereferencing a synonym will no longer cause you to lose suggestions - we'll just be unable to determine the synonym type and therefore might suggest it in places where it wouldn't actually be valid.

Just a reminder though that the synonym support is still an experimental feature that won't be enabled by default. If you enable synonym support and unexpected errors occur, the first thing to try is disabling the feature again.

Mike U · April 3, 2012 9:28AM

Just to let you know that SQL Prompt 5.3 should now handle broken synonyms more gracefully. You should now be able to enable the synonyms feature without breaking caching; any broken synonyms will be ignored.

PDinCA · April 3, 2012 12:16PM

Thanks, Mike. I downloaded 5.3 yesterday and will try the synonym feature again.

SOLVED: 5.2.8.2 Login failed for user 'NT AUTHORIT...

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk