March 21, 2016 at 2:45 am
Guys,
Have you ever heard of the the SQL password changing on it's own - for our software the SA password?
I have this site that the password keeps changing,the customer swears up and down that is not them but it keeps happening. any ideas?
Also, the SA login account duplicates itself even after deleting the account from 'SQL Server Management Studio -->Security-->Logins.'
**The log message as follows**
i) TITLE: Connect to Server
------------------------------
Cannot connect to "Server X"
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476
------------------------------
No process is on the other end of the pipe
------------------------------
BUTTONS:
OK
------------------------
ii) SourceLogon
Message
Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT:]
Message
Error: 18456, Severity: 14, State: 8.
**Version and details as follows.**
Microsoft SQL Server Management Studio12.0.4213.0
Microsoft Analysis Services Client Tools12.0.2000.8
Microsoft Data Access Components (MDAC)6.1.7601.17514
Microsoft MSXML3.0 5.0 6.0
Microsoft Internet Explorer9.11.9600.18204
Microsoft .NET Framework4.0.30319.42000
Operating System6.1.7601
'Enforce password policy' is turned off.
'Enforce password Expiration' is turned off.
Server Authentication is in 'SQL Server and in Windows Authentication Mode.'
Network Configuration Protocols: Shared Memory(1), Named Pipes(2),TCP/IP(3), - All Enabled.
Thanks in Advance.
Pradeep.
March 21, 2016 at 5:19 am
This happens only for 'SA'?
March 21, 2016 at 5:32 am
Yes this issue happens only for SA account, and i can able to see two sa accounts every time, even after deleting one of the SA account from SSMS--> Security --> Logins.
March 21, 2016 at 6:18 am
I've never heard of such a thing, no. Unless you've found some kind of very obscure bug, I can almost 100% guarantee that something is being done on that system. Either the people there are doing it, some automated process is doing it, or some third party software is doing it. Something is making that happen. The 'sa' password doesn't spontaneously change, ever. Accounts don't appear on the system with zero outside agency, ever. I would strongly suggest putting extended events sessions on the server to see what you can capture in terms of queries, connections, etc., to understand where this is coming from.
"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
March 21, 2016 at 2:19 pm
You could also track it down / prevent the change via Login Triggers:
CREATE TRIGGER [alter_login]
ON ALL SERVER
FOR ALTER_LOGIN
AS...
[Send yourself an email including who tried to change it]
March 21, 2016 at 2:43 pm
I'm a fan of disabling the SA account and using a SQL account with a different name if I need a SQL account with elevated privileges.
In addition to what everyone said, this is concerning:
'Enforce password policy' is turned off.
'Enforce password Expiration' is turned off.
If you're going to have an account named SA (presumably with elevated privileges) then please change these settings to "On".
-- Itzik Ben-Gan 2001
March 21, 2016 at 2:57 pm
Does the application normally login using the 'SA' account or using another account with SYSADMIN privillage? A user can change the password on any account for which they can login, even SA.
Also, perhaps the SA account password is expiring.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 21, 2016 at 2:59 pm
Also, consider setting up an Audit trace on account password changes.
http://www.toadworld.com/platforms/sql-server/w/wiki/10429.tracking-password-changes
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 21, 2016 at 11:09 pm
Check the following things
>> Monitor this password reset activity using PROFILER for SA activities.
>> May be some policy is running on the database server to change the password try to find if any and stop it.
>> Try to see if you or someone has set the SA account up to change password after a set amount of time.
March 22, 2016 at 7:44 am
pradeep 69787 (3/21/2016)
MessageLogin failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT:]
Message
Error: 18456, Severity: 14, State: 8.
It sounds to me that the application is using the "sa" login. If so, that needs to be fixed immediately and then the "sa" account needs to be given a two GUID password (each half stored on paper in seal envelopes in two different safes) and then it must be disabled.
Also, any application that is using ANY login the has "sysadmin" or "control server" privs should be reworked immediately to not have such a high level of privs. My personal feeling is that all apps should have only PUBLIC privs and the privs to execute stored procedures. That, notwithstanding (it makes Developers whine a lot), no public facing app should have more than Read, Write, and Execute privs. You're just asking for a hack attack otherwise.
Speaking of that, it sounds like that might be the actual problem... you may be under attack either externally or internally. I'd recommend treating the situation that way and with great cause for concern until you can prove otherwise.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2016 at 8:51 am
Jeff Moden (3/22/2016)
pradeep 69787 (3/21/2016)
MessageLogin failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT:]
Message
Error: 18456, Severity: 14, State: 8.
It sounds to me that the application is using the "sa" login. If so, that needs to be fixed immediately and then the "sa" account needs to be given a two GUID password (each half stored on paper in seal envelopes in two different safes) and then it must be disabled.
Also, any application that is using ANY login the has "sysadmin" or "control server" privs should be reworked immediately to not have such a high level of privs. My personal feeling is that all apps should have only PUBLIC privs and the privs to execute stored procedures. That, notwithstanding (it makes Developers whine a lot), no public facing app should have more than Read, Write, and Execute privs. You're just asking for a hack attack otherwise.
Speaking of that, it sounds like that might be the actual problem... you may be under attack either externally or internally. I'd recommend treating the situation that way and with great cause for concern until you can prove otherwise.
It sounds like #3.
I can't recall ever having an application use SA account if setup properly.
It is like putting all windows users as a Domain Admin in many respects.
March 22, 2016 at 10:31 am
Are you sure the password is changing and it's not that the account is being locked out?
March 23, 2016 at 3:46 am
In a previous job, ACT! by Sage did some tricks like this, removing all logins it hadn't created from the front application and especially admin accounts. It was a total pain!
March 23, 2016 at 6:26 am
Are you sure the password is changing and it's not that the account is being locked out?
Yes the account will get locked after that, i used to execute the following query to reset the password.
--LOGIN sa ENABLE
GO
ALTER LOGIN sa WITH PASSWORD = 'password'
GO
Thank You
March 23, 2016 at 6:27 am
Yes the account will get locked after that, i used to execute the following query to reset the password.
--LOGIN sa ENABLE
GO
ALTER LOGIN sa WITH PASSWORD = 'password'
GO
Thank You
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply