• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 846
  • Last Modified:

Login failed for user 'sa'. [CLIENT: 10.0.0.3]

I have a Backup Module which is part of a SQL DB system written by a company called Digita.  Whenever I launch this backup module, I get the following event appear in my application log -

Event Type:	Failure Audit
Event Source:	MSSQLSERVER
Event Category:	(4)
Event ID:	18456
Date:		10/05/2011
Time:		21:00:02
User:		N/A
Computer:	BENCOMMS
Description:
Login failed for user 'sa'. [CLIENT: 10.0.0.3]

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00   .H......
0008: 09 00 00 00 42 00 45 00   ....B.E.
0010: 4e 00 43 00 4f 00 4d 00   N.C.O.M.
0018: 4d 00 53 00 00 00 07 00   M.S.....
0020: 00 00 6d 00 61 00 73 00   ..m.a.s.
0028: 74 00 65 00 72 00 00 00   t.e.r...

I have spoken with Digita and they do not know what is causing it.  I know my sa password and this system is using the default instance in mixed mode - any idea where I need to look to resolve this issue?
0
fuzzyfreak
Asked:
fuzzyfreak
  • 28
  • 23
  • 8
  • +1
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Seems like someone changed your sa password.
Try connecting to SQL Server with the sa password you have and if it works, just change the password in your Backup module to get it work..
Also check whether your Backup module is pointing your Server name correctly..
0
 
fuzzyfreakAuthor Commented:
Thanks very much for your response, help me out here, I know very little SQL - how would I connect to SQL Server with the sa account?

Did I mention the backup module works fine?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> how would I connect to SQL Server with the sa account?

Just Open SQL Server Management Studio and connect to your SQL Server instance using sa credentials
If it works fine, then your sa password is latest else someone changed it.

>> Did I mention the backup module works fine?

Ok, Kindly let me know more about your backup module.
Is it a scheduled Agent job running in SQL Server or using a third party tool.
Whatever it is, check whether the backup module is using your sa credential correctly.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
fuzzyfreakAuthor Commented:
Thanks, OK, so logging in under sa with the password I have on file is not working, so I have gone back to Digita to enquire as to why.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Yes, If I understand correctly, Digita is a third party company who wrote your Backup application.
First contact with your SQL Server DBA internally and check with Digita later..
0
 
fuzzyfreakAuthor Commented:
Yes, Digita wrote the application and the Backup module.  Having SQL skills in-house is not an option for such a small company so we rely on the companies who write their apps to use SQL to provide us with support, thus with this particular issue, I am having to call upon the likes of yourself.  Digita regard it as a low priority because it isn't stopping the module from working but for me as an Admin, I do not wish to see this event glaring at me every morning in my reports.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok, then check with Digita..

And one more suggestion:

SQL Server by itself has Scheduled Backup options available ( Except Express edition)

So, you can also explore additional options
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
It's a 3'rd party backup solution.

Maybe it's trying to use a database thats not there anymore. I e, the application wants to check something within the database, and keeps it's own list over databases. Now if you drop a database this error could occur.

It's not a invalid password, thats state 8 I belive. But another idea is that you enforce pwd policies, and SA account has a pwd thats not compliant with the AD pwd restriction rules. Then you could get a peculiar error, the pwd matches, but its not ok, so the account should be disabled, but SA can't be disabled. This might giva errors that match your description.

If you use SSMS (SQL Server Management Studio), you could take a look at the errorlog to see if there are any corresponding errors/messages that might shed some light. (its under Management in the object explorer window).

As a last idea, create a SQL account, not SA, and assign the db_backupoperator role to this account. Now this should have the rights nessessary to backup and restore databases.

Regards Marten
0
 
fuzzyfreakAuthor Commented:
From the SQL Log -

2011-05-11 10:19:06.37 Logon       Error: 18456, Severity: 14, State: 8.
2011-05-11 10:19:06.37 Logon       Login failed for user 'sa'. [CLIENT: 10.0.0.3]
2011-05-11 10:19:06.98 spid51      Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

Digita could not work out why this was happening.  I pointed out that the password does not work so they reset it so I can now login under sa but this has not resolved the problem.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Well, this is a classic password missmatch.

They need to tell you where they store the SA password, and how to change it.

Regards //Marten
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> I pointed out that the password does not work so they reset it so I can now login under sa but this has not resolved the problem.

As martenrune already pointed out, you need to check with Digita and find out where they have stored / configured your sa password and change it accordingly..
0
 
fuzzyfreakAuthor Commented:
Thanks guys, I have now e-mailed Digita, though judging by their previous response to this issue, I am not holding my breath.
0
 
fuzzyfreakAuthor Commented:
Still no response from them, unfortunately this issue is in their hands.
0
 
fuzzyfreakAuthor Commented:
I had to chase them for an answer and this was it -

"We simply do not have an answer for this. The backup tool does not store the SA password, it uses windows authentication to connect to the database, the fact that the database backup tool can be accessed shows this is the case.

From the call history I can see a colleague has noted that the same error message appears in your event viewer for other instances of SQL proving it is more likely an issue with the SQL installations or with the server itself. It is also noted that you have performed system restores which can cause problems with registry entries relating to SQL Server.

Assuming this is not affecting the running of any of our products unfortunately this is not something we are going to look into further."
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> The backup tool does not store the SA password, it uses windows authentication

If it uses Windows Authentication, then you would not receive sa login failure for sure..
So I suspect that some other thing is using your sa login and you need to find it to resolve your issue better..
0
 
fuzzyfreakAuthor Commented:
But the event is generated as soon as their backup tool is launched!
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Then I suspect that there is some bug in their tool
0
 
fuzzyfreakAuthor Commented:
Hmmm, unfortunately, I feel I am a little stuck.  If there was a bug in their tool, given the tone of their e-mail, they would never admit to this, nor do anything about it.  I suppose you have to see it from their point of view, if I am the only customer reporting this, then why would they do anything about it?

I wonder if there are any experts here I could send it to for analysis.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> If there was a bug in their tool, given the tone of their e-mail, they would never admit to this, nor do anything about it.

I suspected that there is a bug in their tool with the information you have mentioned earlier:

1. It's invoked or used by their program only
2. It uses Windows Authentication

In that case, it should not use sa credentials and lock it frequently..

Or else check what other applications are connected to your SQL Server causing this event..
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
This client 10.0.0.3
is the one where the backuptool is installed on, as an adminserver for this product? Right?

//Marten
0
 
fuzzyfreakAuthor Commented:
Correct Marten.

So to clarify, the backup app is on my SBS2003 server, same place as SQL and the DB itself.  I am launching the backup app using a batch file and a scheduled task, however even when I launch manually it still generates the error.  The app itself still connects to the DB and works perfectly fine.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Is the SBS server 10.0.0.3?

//Marten
0
 
fuzzyfreakAuthor Commented:
Correct
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Does the backup software use Jobs in SQL Server?

If so I have seen similar errors, open the jobs, if its owned by SQL Server change owner to yourself, now close and open again and change to SA as owner. This might resolve you problem (given these jobs I assume exists really exists). Sorry for the late respons.

Please provide feedback for this!

//Marten
0
 
fuzzyfreakAuthor Commented:
Sorry, I do not understand the question - how do I know if it uses Jobs? - how do I open the Jobs?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hitting back at the older spot..
As I mentioned earlier, you seem to have some of your jobs configured using sa credentials and you can list the jobs configured in your server using the query below:

select name, description, enabled
FROM msdb.dbo.sysjobs

Either one of those jobs are configured using older password of sa credential or sa credential might have expired / changed after the job is scheduled..
0
 
fuzzyfreakAuthor Commented:
ran that query but no result - (0 row(s) affected)

Are you sure I was supposed to run it on msdb.dbo?
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
This means you don't have any jobs scheduled. That's okay, just a longshot.
I'll get back to this thread if I think of anything that matches the error description.
Right now I'm out of ideas. I would like to get my hands on this to examine myself,
but I guess it's resident is not in Stockholm :-)

Regards Marten
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> ran that query but no result - (0 row(s) affected)

Forgot to mention about this..
It will return records only if you have sysadmin privileges else it wouldn't display anything..

I think you need to check with digita for any jobs running or scheduled under old sa credentials and ask them to change it to get it work..
0
 
fuzzyfreakAuthor Commented:
Naturally I ran that query as the domain administrator.  Not quite sure what I am supposed to ask Digita, as you can see from their previous response, they no longer want responsibility.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Got it..
Can you kindly confirm whether your domain administrator is granted sysadmin rights on SQL Server..
If not, then you can't see that and as you are not getting help from Digita, I would recommend you to get your sa password so that you can start controlling your SQL Server..
0
 
fuzzyfreakAuthor Commented:
Hi, I am the domain admin and the domain admin has sysadmin rights on the SQL server.  I also have the sa password.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>>  I also have the sa password.

Can you login using sa credentials and try running the query which I have provided earlier. Provided again for reference

select name, description, enabled
FROM msdb.dbo.sysjobs
0
 
fuzzyfreakAuthor Commented:
(0 row(s) affected)

Same again.
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Then we know for sure that it,s not at job thing.

//Marten
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Also check for any jobs scheduled in your Windows Task scheduler..
0
 
fuzzyfreakAuthor Commented:
The backup module is launching as a scheduled task, but the message is also generated when launching the module manually.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Then at least one of the step in that task is provided with wrong sa credentials and the rest of the task are working fine..
0
 
fuzzyfreakAuthor Commented:
I'm confused.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok, can you kindly open up the Scheduled task and find out how many steps are present in that..
What I meant is that at least one of the step is configured using wrong sa credential so that you are receiving Login failed for sa..
Can you also confirm whether your Scheduled task executed successfully or not..
0
 
fuzzyfreakAuthor Commented:
But, as I say, the same problem occurs when launching the backup module manually i.e. not as a scheduled task.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Oh, ok..
In that case, then the job is scheduled to run automatically as a scheduled task using a login( Windows) who have sysadmin privilege in SQL Server.
But when you start the Scheduled task manually, you don't have the rights to either impersonate sa login or have sysadmin rights and hence the job is failing on the server.
0
 
fuzzyfreakAuthor Commented:
Let me explain again as your last comment suggest I haven't been clear enough.

A program which backs up a SQL database created by a company named Digita causes the "Login failed for user 'sa'. [CLIENT: 10.0.0.3] " error in the Application Log of the server it is running on.  (regardless of whether the program is launched manually or as a scheduled task)

Everything is on the one server and all is using Domain Admin rights.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok, then try this one..

Delete the existing job and reconfigure the job again which might help out..
0
 
fuzzyfreakAuthor Commented:
To which 'job' are you referring?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I meant job as the one present in Scheduled tasks from Digita..
0
 
fuzzyfreakAuthor Commented:
Forget the scheduled task - it is irrelevant.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If it is the one generating errors in your Application log, I think we need to worry about that..
Kindly let me know why you feel it as irrelevant..
0
 
fuzzyfreakAuthor Commented:
Because when I run the backup program manually the error is still generated  - when running manually, I am launching the backup program from it's shortcut under the start menu - I am going nowhere near scheduled tasks.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
So, you obtain the error from Digita into your application log under 2 cases:

1. When run through Scheduled tasks
2. When run manually

Does this conclude that Digita is the one causing issues.
Without having knowledge and access to Digita, we can't really suggest on the measures to resolve it..
Can you kindly check it with Digita Support ( I don't think we have any more choices)
0
 
fuzzyfreakAuthor Commented:
Hi, I really appreciate your input but I feel I am going round in circles and as mentioned previously, Digita will help no further.

Thanks
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Then I feel, you are stuck..
If you have any documentations available from Digita, then you can try reconfiguring the Backup job once again.
Else you can post those documents here so that we can try helping it out..
0
 
fuzzyfreakAuthor Commented:
This is all I can give you, a screen shot of the module - it has no customisable options - I am assuming that it does something to interrogate the SQL DB on launching, because the message is generated in the event log when I launch it.

 Digita-Backup.doc
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Wondering there should be some settings related screen for your Digita where you will save your connection credentials and option to configure the required databases alone.
0
 
fuzzyfreakAuthor Commented:
No, because the app is specifically written by Digita for Digita, therefore the "Use Windows Authentication" will be written into the app.

Do you now see the difficulty I am having here?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> No, because the app is specifically written by Digita for Digita, therefore the "Use Windows Authentication" will be written into the app

If Digita uses Windows Authentication, then Digita should not use sa credentials..
However it uses sa login somewhere which you wouldn't be able to change..

Yes, your condition is really pathetic..
And the only thing which you can do is ignore the error message in Application logs..
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Can you test things on this server?
If so what happends if you switch authentication mode to windows only. Does anything in the application fail? When you try backups?

What about installing on a test system to figure out if the error exists after a normal next-next-next installation?

If so, you have some leverage when communicating with Digita.

//Marten
0
 
fuzzyfreakAuthor Commented:
I have requested abandoning this issue because I have done it to death.  I think reinstalling SQL would fix it but I will not do that on a system that runs two essential SQL DBs.  I am getting no help from Digita so there isn't much I can do.
0
 
Larry Struckmeyer MVPCommented:
Skim read the thread so this may have already been suggested or ruled out.  If your issue is a specific application that is not working, and you actually do know the SA password, can you remove tha application and reinstall it without breaking anything else.  That should reset the password it uses to the known good SA password, or, if it asks for a password you can make it match or leave it blank?
0
 
fuzzyfreakAuthor Commented:
Not sure why this has not been adandoned yet but forcing this by accepting my own comment.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 28
  • 23
  • 8
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now