Post reply

Property IsLocked is not available for Login

  • February 14, 2014 at 3:36 am

    #289538

    Hi,

    Yesterday I ran into a strange situation. Users started telling that thay were not able to connect to their application using the sql account 'abcacc'. I checked logs and found several login failures from this account. When checking login property, it threw me error "Property IsLocked is not available for Login '[abcacc]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)". I googled about it and executed below t-sql.

    alter login abcacc with check_policy = off,check_expiration = off

    Consequently, it started making successfull connections from this account. Gradually same issue was faced for other sql accounts on the instance and I executed the same script. Please note account was not locked.

    Later 'Stack Dump' error was observed in the log just before the login failures started. Can someone please help me understand that above error came due to memory pressure or there could be other reasons as well? Wanted to know the root cause to avoid this issue in future.

    Thanks in advance.

    Regards

    sqldba4u

  • February 15, 2014 at 8:46 am

    #1689295

    Just a possibility, you might want to check to see if your SQL user accounts have a password expiration policy set.

    select name, is_policy_checked

    from sys.sql_logins

    (shamelessly stolen from this thread: http://www.sqlservercentral.com/Forums/Topic397068-338-1.aspx)

    edit: just wanted to mention that I think this check is for SQL server user accounts that are NOT windows logins)

  • February 15, 2014 at 10:05 am

    #1689302

    Sounds like the accounts were expiring.

    You can checked for disabled, locked, or expired accounts using this script. You should probably monitor for this kind of thing.

    SELECT sl.name,sl.is_disabled,sl.is_expiration_checked,sl.is_policy_checked,sl.modify_date

    ,LOGINPROPERTY(sl.name,'IsExpired') AS IsExpired,LOGINPROPERTY(sl.name,'DaysUntilExpiration') AS DaysToExpiry

    ,LOGINPROPERTY(sl.name,'IsMustChange') AS IsMustChange

    ,LOGINPROPERTY(sl.name,'BadPasswordCount') AS BadPasswordCount

    FROM sys.sql_logins sl

    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

  • February 17, 2014 at 1:24 am

    #1689416

    Thanks for the reply but how come all accounts on that instance were expiring at the same time. I want to know could this happen due to memory pressure or there could be other reasons as well.

    Thanks.

  • February 17, 2014 at 7:28 am

    #1689510

    They were probably expiring at the same time because they were created at the same time.

    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

  • February 17, 2014 at 8:51 am

    #1689566

    No, all SQL accounts were created on different dates.

  • February 17, 2014 at 8:57 am

    #1689567

    Did somebody alter their Expired property?

    Has somebody altered the local account policy on SQL Server to have the accounts expired?

    In short, the most probable answer is that something was changed to cause them to expire.

    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

  • February 18, 2014 at 7:24 am

    #1689895

    It doesn't seem so. Is it anyway possible that it could be due to memory issue? I know its something unusual to ask but need to go thru all possibilities.

    Thanks.

  • February 18, 2014 at 7:42 am

    #1689908

    Do you have any other reason to believe you have/had a memory issue?

    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

  • February 21, 2014 at 6:30 am

    #1691073

    There were memory physical dump details logged in the error log just before login failures started.

  • February 21, 2014 at 8:04 am

    #1691132

    I can see memory issues causing (memory dumps) blocking logins. I haven't seen it actually change a login to be expired. I guess it's not much of a reach.

    The next time it happens (hopefully it doesn't happen again), you might want to also check the sys.dm_os_ring_buffers for the logon failures. You might be able to procure a bit more information.

    And since you saw the memory dumps, what is your max memory setting?

    Is this a VM or a physical box?

    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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply