August 1, 2023 at 7:52 pm
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided. see error below:
I'm trying to login into a sql server on another domain (DEV). I run a command to launch SSMS and then login to a DEV server as follows:
%windir%\system32\RUNAS.exe /smartcard /user:ABC@dev.com /netonly "C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe"
This used to work, but lately me and several other colleagues are experiencing the same issue.
Server Name: Abcdef,1234
Error Number: 18456
Severity: 14
State: 1
Line Number: 65536
------------------------------
Program Location:
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ConnectToServer(UIConnectionInfo connectionInfo, IDbConnection liveConnection, Boolean validateConnection)
August 1, 2023 at 9:42 pm
Without being in your environment, this is just guesswork, but my GUESS is that the server "Abcdef" is on a different domain than dev.com or possibly that server lost the connection or trust to the domain controller. The absolute first step I would do is review and validate logs. SQL Logs and Windows logs on all machines in the communication chain. Determine ALL errors, warnings, and messages that are happening during your authentication and determine the best approach to fix it.
My troubleshooting steps would be to:
1 - log into the machine Abcdef as a user from the dev.com domain
2 - log into the SQL instance hosted on Abcdef on port 1234 from the server Abcdef to ensure that it isn't something getting lost in your runas
3 - get your AD admin to verify that the server Abcdef is still joined to the dev.com domain and didn't get disjoined for some odd reason
4 - if all of the above are successful, break your runas into multi-step to ensure that it is running how you expect and not something weird/new with the SSMS version you are running.
5 - verify SPN's
When I had a similar issue (not identical, but it was similar) the issue was with the SPN's. Had my IT department fix the SPN's and the problem was resolved. I listed that option last as since for your situation it used to work and suddenly stopped, it likely isn't SPN's UNLESS you (or someone) was monkeying around with SPN's. In my case it worked until I moved my SQL instances to new servers and we missed setting up the SPN's.
What I mean with step 4 is to start a command prompt on the local machine as that user and start up SSMS manually.
Now, what I would be curious about is what changed between when it was working and when it stopped working. SOMETHING changed - I would work to figure out what changed. Untested updates on the server? Untested updates on the local machine such as updates to SSMS? Untested updates on the AD controller? Maintenance on the AD controller? Certificate expiration in the stack somewhere (your PC, the server, AD, DC, CA, etc)?
The other thing I would do is google the issue. Quick google of it brought me to this link which sounds exactly like your problem:
BUT there are multiple solutions as it depends on your environment.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2023 at 1:30 pm
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
This usually points to Kerberos double hop issues. Check you SPN's.
You can use this tool to check it: https://www.microsoft.com/en-US/download/details.aspx?id=39046
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply