Wednesday, March 21, 2012

Fix: "Property IsLocked is not available for Login" error

Issue: SQL Server accounts (Non-AD) appear to no longer have database access. When attempting to open the account in the SSMS GUI, you receive the message:

"Property IsLocked is not available for Login '[Name_Of_Login]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)"



This message appears due to having the "Enforce Password Policy" box checked on a SQL Server (non-AD) account.


Here is T-SQL to fix the problem:


ALTER LOGIN [login]
WITH PASSWORD = 'password' UNLOCK
,CHECK_POLICY = OFF
,CHECK_EXPIRATION = OFF


When I saw this issue recently, more than one SQL Server instance (SQL Server 2008 R2) was affected simultaneously. They were in the same AD domain, but on different hosts. This leads me to believe it is AD related, though I've found no clear acknowledgment from Microsoft on the issue. The issue has been present since SQL Server 2005.

If you want to enforce AD password policies for SQL Server access, I recommend using an AD account for accessing SQL Server. You gain all the benefits of security / account management with AD, not the least of which is Kerberos authentication. Applications which are still passing plain text passwords to SQL Server in a connection string need to be updated to support AD Authentication. I encourage you to raise this concern with your application development team and support them as they update the apps.

No comments:

Post a Comment