SQL Server 2008 Security - Policy Example

  • Article

Hi, Gaurav Sharma here, I’m a developer with the Information Security Tools (IST) team. A few months ago I posted a blog, SQL Policy Based Management (PBM) and posted a follow up introductory “How Do I” video on the same topic. Since then I’ve received a lot of feedback and questions regarding how to create more security policies for SQL Server.

Today, I’ll show how to create a security policy specifically checking for the "SQL Server login passwords should not be older than 70 days" policy. For this blog, I won't be covering the basics of SQL PBM here, however if you're new to this topic I suggest you check out my blog post on PBM and “How Do I“ video.

SQL Server Login Passwords Should Not be Older than 70 Days

  • Policy Description
    Commonly organizations define this kind of policy which force users to change their SQL Server login password periodically.
  • Policy Creation

    • Step 1 – Create a condition to check last modified Login date. This will tell us the last time the login password was modified. The following below are the condition details.

      Name: ServerLoginPasswordOlderThan70Days
      Facet: Login
      Expression: @DateLastModified > DateAdd('day', -70, GetDate())

      image

    • Step 2 – Create a condition that will check SQL Server user type. There is no need to run policy for users who belong to one of the following categories: Windows Users, Windows Groups or SQL Server special service accounts (login names that start with ##). This is because windows user accounts and groups are managed by the windows policy mechanism and verifying SQL Policy for those users will be of no value. Below are the condition details.

      Name: LoginIsNotWindowsUserOrGroupOrSpecialAccount
      Facet: Login
      Expression: (@LoginType not equal to Windows User) AND (@LoginType not equal to Windows Group) AND
      (@Name not like ‘##%’)         

      image 

    • Step 3 – Create a policy that checks for “SQL Server login passwords should not be older than 70 days.” This will use the two conditions that we created above. Below is the policy details.

      General Tab
      Name: ServerLoginPasswordShouldNotBeOlderThan70Days
      Check Condition: ServerLoginPasswordOlderThan70Days
      Against Target: LoginIsNotWindowsUserOrGroupOrSpecialAccount – Login
      Evaluation Mode: On Demand
      Server Restriction: None
      Description Tab
      Category: Server Security (you can create a new category and give it any name)
      Description:
      Passwords on SQL accounts found to be older than 70 days are in violation of corporate policy / standards. Passwords MUST be configured to be changed at least once every seventy (70) days. (this message will display if the policy is violated)
       image

    • Step 4 – Now we need to execute this policy against the SQL Server instance. Please check out my “How Do I” video for more information on how to execute policies on a database.

Please feel free to email me with any questions you might have related to this topic. I’ve managed to create a few security policies which I’ll be glad to share. My email is gauravsh@microsoft.com.

Now for today’s tip....

While generating database object scripts, SQL management studio provides us with an option to choose which database version to target. This is very helpful in scenarios where you are using SSMS 2008 client and connecting to SQL Server 2005 (or any other version) from your client. Now if you generate a script of 2005 server object, by default scripts are created as per client version i.e. 2008 in our case. To change the database version, go to Tools > Options window.

image 

Happy Coding!

-Gaurav Sharma
Microsoft Information Security Tools (IST) Team
Software Developer Engineer