March 25, 2011 at 5:48 am
Hi All,
I have installed the sql server 2005 SE and using the user 'sa' for login and working on DB's. 2-3 Days it is working fine and logging from all the option(SSMS, Application) for 'sa' user working fine. but after 2-3 days automatically I am getting error "Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)". After this I am not able to connect to the server either from SSMS or from any application using 'sa' user. Even I am able to login to Sql Server using Windows Authentications mode.
I have installed sql server with Mixed mode.
So Please tell me why I am getting this error/problem and why this problem got solve after restarting Sql server. is there any workaround to solve this problem.
I can't start my server so freequently.
Please help in this issue.
Thanks is Advance
Pradeep Sharma
March 25, 2011 at 7:14 am
Sounds like the sa password got changed. Change it back, or reset it and rework your application or whatever is using it.
You shouldn't have an application using sa anyway, for a lot of really good reasons.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 12:01 pm
GSquared (3/25/2011)
Sounds like the sa password got changed.
Maybe, but I don't think restarting the server would have resolved a changed password issue.
I do agree that SA should not be used for applications. That's just asking for trouble.
EDIT: Did anyone disable Remote Connections? What default database is the SA account set to use?
March 25, 2011 at 12:05 pm
What is your service pack? Sounds like maybe a connection pooling issue that was fixed by service packs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 25, 2011 at 12:11 pm
Here's what Microsoft has to say about the error number:
Error 18456
Topic last updated -- January 2004
Severity Level 14
Message Text
Login failed for user '%ls'.
Login failed for user distributor_admin.
Explanation
•If the error message includes the account with which you are trying to access the server, you do not have permission to log in to the server.
•If the error message includes the account distributor_admin, the issue is with an account used by replication. Replication creates a remote server, repl_distributor, which allows communication between the Distributor and Publisher. The login distributor_admin is associated with this remote server. The distributor_admin account can be designated as trusted or non-trusted when you configure replication. If it is designated as non-trusted, a password is required, and login will fail without one.
Action
•If the error message contains the login with which you are trying to access the server, contact a member of the sysadmin fixed server role to request login permission.
•If the error message includes the user distributor_admin, ensure that there is a password associated with the account. For more information, see the topic Connecting to the Distributor.
Note It is recommended to use a non-trusted connection for the distributor_admin account.
This data is specific to SQL 2000, and I couldn't find an update.
This means that either sa doesn't have login permissions and restarting the server somehow gives sa login permissions (which doesn't make sense), or the password is changed and restarting the server resets the password (which doesn't make sense), or there's something else going on here and the post is incomplete/incorrect (which would be totally normal).
I still suggest checking the sa login. Verify the password and permissions. Then change your application to use a different login and make sure that one's working. Then check for changes to the sa login, possibly due to an on-login trigger or something of that sort.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 25, 2011 at 12:20 pm
Checking for triggers or logon scripts is a good idea. Also, check the application code to see if it has a way of altering the SA password or the SA account permissions.
A way, way, way outside chance is that somehow the app asks the user for a password which it then tries to stick in the SA connection string and the user got it wrong.
March 25, 2011 at 12:46 pm
Brandie Tarvin (3/25/2011)
Checking for triggers or logon scripts is a good idea. Also, check the application code to see if it has a way of altering the SA password or the SA account permissions.A way, way, way outside chance is that somehow the app asks the user for a password which it then tries to stick in the SA connection string and the user got it wrong.
That isn't too terribly far-fetched. SQL Server does have the password complexity and lockout for failed attempts (especially when combined with the local policy [secpol] of the server). Too many bad attempts locked the account for x-number minutes and the reboot just happened to cover that amount of time. That is definitely plausible.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2011 at 12:24 am
Hi All,
As per all the above replies,
1. sa Password is not changed. After restarting the server with same password I am able to connect to sql server.
2. Remote Connections are enable and default database for sa is "master"
3. Windows Server 2003 S.Edition With SP2
4. There is not any code changing SA user password.
5. There is not any wrong attempt for user SA from application end.
6. There is not any logon trigger for user SA.
I have already checked all the things.
Kindly help me further in this issue.
Regards
Pradeep Sharma
March 28, 2011 at 5:12 am
I hate to say it, but this might be a case of "Call Microsoft." I certainly can't think of why your connection would fail out of the blue if nothing got changed, you haven't lost the SA's default database, and everything works after a service restart.
The only other possibility is a network issue. But I think Microsoft needs to get involved in this one.
March 28, 2011 at 9:18 am
You say there are no bad password attempts from the application, but are there bad attempts coming from anywhere else? Maybe somebody else is trying to guess that password.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 28, 2011 at 9:19 am
Are you checking the log for failed login attempts, or are you just assuming that there are none?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply