Login sa failed
-
Hi All,
Please advise,
Logon Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 192.***.254.***].
Is some application using an old password? is there anyway I can ask the network or developer to check on something.
What can be possible resolution or steps to tackle the problem.
What can be possible resolution?
-
Identify the host from where the login attempt is originating from by doing nslookup on the IP address. That should give you a hint on whether it's someones work station in your organization or an App server. May be it's just an individual attempting to connect to SQL server from his/her SSMS using 'sa' account. Check in the SQL logs for any pattern in failed connection attempts from 'sa' login.
'sa' account should not be used by any application to talk to SQL. I've seen few vendor products which asks for sysadmin rights specifically, even in those cases, a separate login must be used with sysadmin rights, but not the actual 'sa' account itself. -
nslookup shows it is coming from Domain controller...and not one but team is aware they get multiple errors not sure what is the cause...is there any way to find the root cause..thanks
-
Domain Controller? I think you are interpreting the nslookup results wrong. Also, you can create a trace or an extended event session or create an audit to capture more details of these login attempts.
-
using ping -a will return the machine name, i like it better than nslookup..
then you can check the machine itself for either applications, or windows tasks, or even a linked server from another server, etc for what might be trying to connect.
ping -a 192.***.254.***Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
We have 3 nodes B01 and B02. the IP which is in the error is for B02.
I might be wrong but I ran the nslookup cmd on B02 and it gives the follwing:
Server name : DC01..domain name
Name : B02
IP: same as in the error. - Lowell - Thursday, November 8, 2018 3:09 PM
The IP in the login error message is of B02 secondary node. Where should I run the ping -a cmd. On which server.? thanks
-
you could run it from any machine on the network, so no difference; DNS is DNS from any machine.
the IP resolves to some server name, which i think you will identify as your B02 node on your cluster, right?the suggestion for an extended event or trace now comes into play, because you might be able to identify the issue based on ApplicationName, default database it was connecting with, etc.
the sql log is going to have not too much more info, but check teh default trace:
declare @TraceIDToReview int
declare @path varchar(255)SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE 1=1
AND StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
AND TE.name = 'Audit Login Failed'
--AND LoginName LIKE '%myspecificuser%'
--AND TextData IS NOT NULLLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks, yes your right, logs does not help.
So running default trace do I have to change any value in above script?
I tried to run this in Test environment and it returns blank results.
Also, after running in prod environment do I have to disable the trace again? -
No, you don't have to change anything in the above script provided. If you look at the script provided by Lowell, it's basically querying the default trace(trace id =1). This script just reads the Default trace for failed login events...The reason why it might have resulted blank results in your test environment is there are no failed login occurrences present in the def trace on your test Instance. Also, remember default trace doesn't hold the contents for ever..they get rolled over. You might want to run this script to see if you can get any results as soon as you notice login failed messages in your SQL log.
- sizal0234 - Thursday, November 8, 2018 3:47 PM
The default trace is something that should already be running on the server, you can check like this:
SELECT * FROM sys.traces WHERE is_default = 1
You shouldn't be starting and stopping or disabling the default trace. -
Data from default trace..
The login is failed by username "Domain\dbc_agent" in all the places, Db is 'master'.
The maintenance plan is running and has 3 jobs for backup and cleanup.
When I ran the default trace and try to find the query using SPID it gives me an EMPTY column
. Now, the question on the table is it a bug? or am I missing anything?
Logs does not show anything more than this error. -
i think the [Domain\dbc_agent] login issue the query finds is a separate issue than the process logging in as sa, i would look at the Error/Severity columns to confirm.
if you add to the query to limit it to sa, doe sit find any results, ie
WHERE 1=1
AND StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--AND TE.name = 'Audit Login Failed'
AND LoginName ='sa'
--AND TextData IS NOT NULLLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
The reason of getting Login failed error could be many, either your username or password is incorrect or your instance of SQL Server or password for login has expired. Here is the step by step process to resolve SQL Server error 18456:
https://www.sqlserverlogexplorer.com/fix-microsoft-sql-server-login-failed-error-18456/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply