Strange SQL Login failure issue.

Question

Strange SQL Login failure issue.

jessymcjessface

SSC Enthusiast

Points: 193
More actions
November 2, 2023 at 1:28 pm

#4319730

I have 2 x servers.

The same 3rd party app on each. The same config screen for SQL login details. Listener details, windows auth, domain\user, password. All details are correct & the same.

Server 1 - connection into SQL is made, all good.
Server 2 - Login failed for user 'domain\user' ... Attempting to use an NT account name with SQL Server Auth. Error 18456, severity 14, state 6.
I have run profiler to capture the failures & verified that the app causing the issue = the failed client PID.

The app owner says "I've set it up right - not my issue". I have 3000 failures a day in my logs. I need to resolve. Any ideas on what could be transforming the call for win auth to a call to SQL auth - server setting? GPO setting? Any idea how to troubleshoot or somehow trace what the app is passing through before it gets to SQL? I'm not an app developer but my app developer isn't very helpful.

Any ideas?
November 7, 2023 at 6:19 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

A whole bunch more functionality available in Extended Events. Here's a blog post showing how to use them to troubleshoot timeouts. There's also other events mentioned in the post as well as a link to some other troubleshooting methods around connections & connectivity.

BTW, the problem, almost without doubt, is on the client side.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

error message is very clear - a attempt was done to login to the server with a connection string that is a AD account, but trying to connect as if it was a SQL Login - e.g. by supplying the password and/or not setting the integrated security parameter

you dev should look at https://www.connectionstrings.com/sql-server/

and for the particular user that is trying to connect to the server it should use the "trusted connection" string - if they are trying to connect as a different user than the one executing the code then they either need to use a SQL Login (e.g. non AD based, SQL based with uid/pwd being supplied) or he needs to impersonate the AD user BEFORE attempting to connect and the connection needs to be done under the impersonated user (using the trusted connection string)

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Are you able at all to post screenshots of the config pages, maybe a fresh pair of eyes can pinpoint the issue also?

jessymcjessface SSC Enthusiast Points: 193 More actions · Answer 4

Thanks all.

Most grateful as ever! Even if it's just for ideas / the offer of more eyeballs (I can't post out though) & confirmation that it's probably not bloody SQL!! Having said that, in my quirky workplace I often have to go the extra mile to prove what it is in order to prove what it's not.

I've perused extended_events, ran various sysinternals / procmon style apps, chased down registry entries, compared win versions & settings but I'm still stuck.

I'm going back to the app folks having done the above due diligence to tell them I'm blocking them at the firewall level until they sort it.

Have a lovely evening all.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

Every time I have seen "Attempting to use an NT account name with SQL Server Auth" it means that my SQL instance is set up for Windows Authentication (instead of mixed mode) AND someone or some app is trying to connect using SQL Server authentication. 9/10 times the problem is with the connection string. The other 1/10 times (that I have seen) it is that the app doesn't support windows authentication for whatever dumb reason (we have a few of those and they are all 3rd party apps). ALL of our in-house developed apps use windows authentication as it is MUCH harder to impersonate an account to try to get around any auditing. For the few apps that don't support windows authentication, we do our best to make the SQL accounts secure.

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.