May 20, 2009 at 3:47 am
My sa login continuously stopped working and work only when i change the password of sa.
Can any one help me to figure this out problem:
following the sql log :
2009-05-19 20:09:46.730,Server,Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2009-05-19 20:09:46.740,Server,(c) 2005 Microsoft Corporation.
2009-05-19 20:09:46.740,Server,All rights reserved.
2009-05-19 20:09:46.740,Server,Server process ID is 1500.
2009-05-19 20:09:46.740,Server,Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2009-05-19 20:09:46.740,Server,This instance of SQL Server last reported using a process ID of 1508 at 5/19/2009 8:07:56 PM (local) 5/19/2009 2:37:56 PM (UTC). This is an informational message only; no user action is required.
2009-05-19 20:09:46.740,Server,Registry startup parameters:
2009-05-19 20:09:46.740,Server, -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2009-05-19 20:09:46.740,Server, -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2009-05-19 20:09:46.740,Server, -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2009-05-19 20:09:46.840,Server,SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-05-19 20:09:46.840,Server,Detected 2 CPUs. This is an informational message; no user action is required.
2009-05-19 20:09:47.700,Server,Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2009-05-19 20:09:50.070,Server,Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2009-05-19 20:09:52.500,Server,Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-05-19 20:09:54.700,Server,Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-05-19 20:09:55.310,Server,Database Mirroring Transport is disabled in the endpoint configuration.
2009-05-19 20:09:56.480,spid5s,Starting up database 'master'.
2009-05-19 20:09:58.180,spid5s,Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2009-05-19 20:09:59.780,spid5s,SQL Trace ID 1 was started by login "sa".
2009-05-19 20:09:59.930,spid5s,Starting up database 'mssqlsystemresource'.
2009-05-19 20:10:01.010,spid5s,Server name is '221350-WEB1'. This is an informational message only. No user action is required.
2009-05-19 20:10:01.030,spid9s,Starting up database 'model'.
2009-05-19 20:10:01.640,spid9s,Clearing tempdb database.
2009-05-19 20:10:02.820,Server,A self-generated certificate was successfully loaded for encryption.
2009-05-19 20:10:02.920,Server,Server is listening on [ 'any'
2009-05-19 20:10:02.920,Server,Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2009-05-19 20:10:02.920,Server,Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2009-05-19 20:10:02.920,Server,Server is listening on [ 124.0.10.1
2009-05-19 20:10:02.920,Server,Dedicated admin connection support was established for listening locally on port 1434.
2009-05-19 20:10:03.010,Server,The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2009-05-19 20:10:03.010,Server,SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-05-19 20:10:03.060,spid9s,Starting up database 'tempdb'.
2009-05-19 20:10:04.230,spid12s,The Service Broker protocol transport is disabled or not configured.
2009-05-19 20:10:04.230,spid12s,The Database Mirroring protocol transport is disabled or not configured.
2009-05-19 20:10:04.610,spid12s,Service Broker manager has started.
2009-05-19 20:10:05.000,spid17s,Starting up database 'ReportServerTempDB'.
2009-05-19 20:10:05.000,spid16s,Starting up database 'msdb'.
2009-05-19 20:10:05.000,spid18s,Starting up database 'Sitedb'.
2009-05-19 20:10:05.000,spid15s,Starting up database 'ReportServer'.
2009-05-19 20:10:06.510,spid18s,Analysis of database 'Sitedb' (7) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2009-05-19 20:10:06.710,spid18s,CHECKDB for database 'Sitedb' finished without errors on 2009-03-19 04:36:52.917 (local time). This is an informational message only; no user action is required.
2009-05-19 20:10:06.710,spid5s,Recovery is writing a checkpoint in database 'Sitedb' (7). This is an informational message only. No user action is required.
2009-05-19 20:10:09.120,spid5s,Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2009-05-19 20:10:09.120,spid5s,Recovery is complete. This is an informational message only. No user action is required.
2009-05-19 21:48:14.360,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:14.360,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:14.380,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:14.380,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:14.380,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:14.380,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:14.390,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:14.390,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:17.390,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:17.390,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:17.390,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:17.390,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:17.430,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:17.430,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:17.430,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:48:17.430,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-19 21:48:17.440,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-19 21:49:54.580,Logon,Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. [CLIENT:
2009-05-20 00:00:05.300,spid25s,This instance of SQL Server has been using a process ID of 1500 since 5/19/2009 8:10:09 PM (local) 5/19/2009 2:40:09 PM (UTC). This is an informational message only; no user action is required.
2009-05-20 01:21:10.230,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-20 01:21:10.230,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-20 01:21:10.250,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-20 01:21:10.250,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-20 01:21:10.250,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-20 01:21:10.250,Logon,Login failed for user 'sa'. [CLIENT: 63.15.13.94]
2009-05-20 01:21:10.260,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-20 01:22:20.300,Logon,Error: 18486, Severity: 14, State: 1.
2009-05-20 01:22:20.300,Logon,Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. [CLIENT:
2009-05-20 02:27:25.730,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-20 02:27:25.730,Logon,Login failed for user 'sa'. [CLIENT: 84.16.7.127]
2009-05-20 02:27:26.080,Logon,Error: 18456, Severity: 14, State: 8.
2009-05-20 02:27:26.080,Logon,Login failed for user 'sa'. [CLIENT: 84.16.7.127]
May 20, 2009 at 6:39 am
Are you connecting from an application or via SSMS.
Is your system ste to mixed mode? If so its not advisable to be using SA anyway. Better to register your windows NT account as SysAdmin and use that.
Adam Zacks-------------------------------------------Be Nice, Or Leave
May 20, 2009 at 6:59 am
If it works each time that you modify the SA password, then there is a good chance that someone else is also modifying the SA password after you modify it. I also agree with Adam that recommended that you won't use the SA login and that you'll start using your windows login. In your case it also seems that an application is using the SA logins. Needless to say that there is no reason at all to use the SA login as an applicative user.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 20, 2009 at 7:05 am
Is "password expiration" configured for your SA login?
May 20, 2009 at 7:12 am
If this is coming via an application, never use sa. Unless your code is somehow encrypted, your sa password is then floating around embedded in code and easily obtained by anyone with access to code. Create a login with only the neccessary rights. Who else knows the sa password? Lock it down, have it in the proverbial "football" and only share it with those that need to know. Time for an overall security audit, methinks! And, no, you are not alone. I'm locking the doors as we move to SQL 2005. All the developers know the sa password on SQL 2000 (it's the same on ALL server, prod, dev, staging - I inherited that mess) but going forward, there will be a lot of upset, angry people because they no longer have free reign over any of the new servers. Times are a'changin here....and with management's blessing as well. 😉
-- You can't be late until you show up.
May 20, 2009 at 7:13 am
these are some web applications, which r connecting through ADO.Net
We usually use a different user sql server user to connect for different user so it might not be good to use same user NTService for allthe applications.
Can anyone let me why might be cause of this issue.
Thanks in advance,
Achlesh
May 20, 2009 at 7:23 am
guys it looks no one is changing the password as the log says "
2009-05-20 01:22:20.300,Logon,Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. [CLIENT: ] "
And how can we come to know tht some body is changing the password for sa
I am sure the nobody is changing the password otherwise it must say "Login failed" while it is saying tht "account locked"
May 20, 2009 at 7:44 am
achleshsoni (5/20/2009)
guys it looks no one is changing the password as the log says "2009-05-20 01:22:20.300,Logon,Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. [CLIENT: ] "
And how can we come to know tht some body is changing the password for sa
I am sure the nobody is changing the password otherwise it must say "Login failed" while it is saying tht "account locked"
Probably locked due to failed login attempts. What else is in the SQL logs or the server event logs? You need to find out where the invalid attempts are coming from and get a handle on who is trying to get into your database server. Perhaps time to fire up profiler? Is the sa login set to "enforce password policy"?
-- You can't be late until you show up.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply