locked
Cannot access/modify/view login infomration in SQL 2005 (Win 2003). RRS feed

  • Question

  • I have a non-trusted SQL Server login that has been active in the database for some time now.  It is used as a generic login for an application to access the database. 

     

    The application started reporting that it was experiencing connection timeouts around 4:30AM this morning.  The event logs on the server only show that the connection was a non-trusted successful connection. 

     

    I am the sys admin for the server, but I cannot veiw the properties of the login through the interface, or through transact sql.  It just sits there for a while and eventually forgets.  There is no error message raised, no event log addition. 

     

    I cannot drop the login through the interface or sp_droplogin to try to recreate it.  I can use sp_helplogins and see the values, but it says the account isn't locked and gives me no indication what is wrong. 

     

    The only thing of value I can find is that if I run Activity Monitor I can see that there is a LCK_M_SCH_M (Schema Hold)  with resource principal_id=267 on the transaction when I try to drop the login using the stored procedure. 

     

    If I check

    select * from sys.dm_tran_locks where resource_description = 'principal_id = 267'

    I get resource subtypes of SERVER_PRINCIPAL & PASSWORD_POLICY. 

     

    This account was originally set up to never expire and with CHECK_POLICY=OFF.

     

    Anyone have any ideas where I can look further? 

     

    This is a new one to me.

     

    Wednesday, September 10, 2008 3:43 PM

Answers

  • I think I have resolved this issue on my own but I am putting details here for anyone else who might run into the issue. 

     

    There is a patch for this Error: 18456, Severity: 14, State:10 (you can get this from the trace logs or the Event Logs).  The only thing is I have SQL SP2 installed and it is supposed to be integrated to SP2.  So I didn't run the patch. 

     

    After I rebooted the server last night, just on the off chance that a reboot would fix it, I could access my SQL Login account that I was having the issue with. 

     

    The interesting thing here is that the setting for Enforce Password Policy was NOT selected for this account(equates to CHECK_POLICY = OFF).  However, it was selected for the SA account. 

     

    I am not positive that was the issue, but I suppose I will find out in 3 months or so.  I have other SQL 2005 instances in production that I have never had this issue with, but the only difference I can think of between them is that this particular instance I installed from the SQL 2005 with SP1 integrated. 

     

    Rebooting the server did remove the lock and I made sure that Enforce Password Policy was not selected for the SA account as well.  I will update this thread if I encounter the issue again. 

     

    Thursday, September 11, 2008 2:28 PM