locked
SQL Server administrator login gets blocked automatically.. RRS feed

  • Question

  • Hi All,

    here's my question on today's forum. Can someone help on this issue... 

    I don't understand why frequently SQL Server 2008 administrator user [sa] gets blocked by itself.  resulting my application running on default user [sa] ends up with sql access error.

    to resolve this I have created 2 similar admin accounts through which I reset the password of sa.. and then continue again..

    does anyone has come-across such issue? Help Needed

    Thanks in advance.

    Wednesday, May 7, 2014 6:21 AM

Answers

All replies

  • Check SQL logs from when the locking starts and from which machine, there could be any application which is trying to use SQL server from sa account using old password or you can set the profiler trace to check this.

    Also, go through the below thread seems similar issue

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d47bb849-6b0f-4598-9949-074114531f68/the-user-id-sa-is-getting-locked-frequently-in-sql-server-2008-r2?forum=sqlsecurity


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by athiq.bari Wednesday, May 7, 2014 2:52 PM
    Wednesday, May 7, 2014 7:21 AM
  • Hello,

    What do you mean by getting blocked .Is there a logon trigger configured can you check that


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    My TechNet Wiki Articles

    Wednesday, May 7, 2014 7:58 AM
  • blocked - I mean i cannot enter into sqlserver management studio. all i do is enter through difference admin user change the password and try to login again.
    Wednesday, May 7, 2014 2:54 PM
  • Check the SQL Server errorlog for the failed login attempts. There you see the state for the error. The check this blog post what that state means:
    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    resulting my application running on default user [sa] ends up with sql access error.

    Your application logs in as "sa"? That's very bad! An application should only run with regular permissions. That is, db_datareader/writer and/or EXEC rights on stored procedures. That minimizes the impact of any SQL injection holes you may have.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, May 7, 2014 9:34 PM