SQL DBA issues and solutions

ISSUE: How to Unlock a SQL Login Without Resetting the Password
--------------------------------------------------------------------------------------------------------------------------
SOLUTION :

            SQL Server uses the same password policies as that of the operating system. Password policies include various parameters with regard to password length, when a password gets locked out, and how long until the password expires.

           To see these parameters by checking the local security policy on your server. To do this, go to Start >> Control Panel >> Administrative Tools >> Local Security Policy if you're using the Classic View for Control Panel. Otherwise, go to Start >> Control Panel>> Performance and Maintenance >> Administrative Tools >> Local Security Policy.

          Once inside the Local Security Policy console, expand Account Policies and you should see two subfolders, Password Policy and Account Lockout Policy.

Click on Account Lockout policy>

                  

The Account Lockout Policy folder has the following settings:

• Account Lockout Duration - how many minutes before a locked account is unlocked again
• Account Lockout Threshold - how many failed login attempts can occur before the account is locked out
• Reset Account Lockout Counter After - how long before the failed login attempts are reset to zero (assuming no successful logins have occurred since, since that automatically resets the failed logic count to zero).

If a SQL Server login is configured to use password policy enforcement and the organization uses account lockout after a certain number of failed logins, you can end up locking out a SQL Server login.

                   If the account gets locked out, unlocking the account is easy to do if you want to reset the password. If you don't however, SQL Server won't let you directly. For instance, simply unchecking the box beside Login is locked out and clicking OK won't work.

Instead, you'll get the following error:

Temporarily take the login out of password policy enforcement by unchecking it, as shown.

This will disable the unlock checkbox.

                     Click OK to confirm the change and the SQL Server login will unlock. This will permit connections via that login again. If there is a need for the password enforcement to be turned back on, you can do so and it won't re-lock the login. However, in either case be sure you know what caused the lockout in the first place. The reason for having account lockout is to prevent a security breach due to someone brute forcing the password for an account. Disabling password policy enforcement basically means an attacker can try to guess the password.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Activity : How to Changing SQL Server Service Account or Password

--------------------------------------------------------------------------------------------------------------------------

Workaround :

                    As per Microsoft documentation, we should always use SQL Server configuration manager to do any modification for SQL Service. Account change and password change, both should be done only via configuration manager.
                 
                    For service account change we need to restart SQL server service. But if we are only changing the password then there is no need to restart the SQL Service.
                  
                   Keep in mind a bug in SQL Server where if we change the password in clusters on the passive node, SQL services would stop.https://support.microsoft.com/en-us/kb/972387 (SQL Server Failover Cluster Instance is taken offline when you change the password for the service account on the passive node).

we should not use services.msc? Why?

                   There are valid reasons for that. As per books online – In addition to changing the account name, SQL Server Configuration Manager performs additional configuration, such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.

The right way…

1. Go to Start –> All programs –> Microsoft SQL Server <version>
2. Go to Configuration Tools
3. Click on “SQL Server Configuration Manager”

 

     4.Within the SQL Server Configuration Manager, go to the “SQL Server Services” node

1. In the right-hand side pane, you will see all the SQL Server services listed
2. Simply right-click on the required SQL Server Service and go to “Properties”

           

    3. Next, change the domain service password and click OK

                     

       4.Once the password has been changed – there is no need for the SQL Server service to be restarted

If ever you need a service restart:

Let’s say you need a service restart because of a configuration change. Instead of heading over to the Services.msc console, one should be using one of the following options:

Using SQL Server Configuration Manager

1. Right click on the SQL Server Service
2. Click on “Restart”
3. 

Using SQL Server Management Studio

1. In the Object Explorer pane, connect to the server in question
2. Right-click the server name and click “Restart”

3 .Click on “Yes” on the following Message Box that comes up (beware, this message box might appear in the background)

For more reference on how to setup Windows Service accounts for Microsoft SQL Server, please refer: http://msdn.microsoft.com/en-us/library/ms143504.aspx



Until we meet next time.....,

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------