Security is very important when it comes to keeping data, databases, and servers secure. Where I work there is a group dedicated to scanning all servers for vulnerabilities from the operating system to the applications and everywhere in between.
Recently, I stood up 7 new database servers and will be adding more in the near future. This particular project will more than double the number of servers I am responsible for maintaining. That does not include all the other servers I am the backup DBA. Automating certain processes is paramount when you have to alter something on all of the servers.
I was provided a script to create test users on the servers. I reviewed the scripts but missed on key point. As a result on a recent scan a vulnerability/issue was identified and I had to fix it on all 7 of the new servers. The issue identified was related to the user accounts and enforcing password policy. What I missed in the script was this.
Why is this a big deal you ask? Let me explain. You see, regardless of how complex your password policy might be, if you don’t enforce it a user can have a simple password. Allowing a simple password increases the potential for someone to compromise your systems.
First we need to find the logins we are not checking the password policy for. The following query can be used for this purpose.
You can run this script to update the checks and enforce your password policies.
That will work if we only have one login needing to be updated. What if you have many users on many servers as in my case recently? Well, I put together a script that will check all the users on the server and if the password policy is not being checked, it will update that specific login and then move to the next. I also had the problem on several different servers. Solving that problem is outside the scope of this post, but can be easily handled with a Central Management Server, if you have one or use a local server group.
So lets look at the script I put together so you understand what I am doing.
The first thing is to create a few variables. We will need login names so we can update the correct one, passwordchecked to filter on, and the other two are for the loop later in the code.
I use a table to store the records we need to update. Notice I am filtering and only grabbing the logins where the password policy is not being checked.
Next, set the values for our loop to count to increment one at a time. Then set another variable to hold the update code and finally select the top record in the table and update the records accordingly.
Now our code to update the records, notice how this is being done. After the code is executed that record is dropped from the table, the counter is incremented and the loop starts again grabbing the next record.
As with most everything we do in SQL Server there are a few ways to do this. You could use a cursor (I know some people think that is a bad word) but I choose a simple WHILE loop. I have not done any performance testing to see if the code I created is the most efficient. Since this is not run all the time and will only be executed if issues are identified I am confident the code is fine for the purpose it was created to address.
Hopefully this gives you some ideas on how to address a senario like this in your environment.