locked
SQL Server 2017 password complexity rule RRS feed

  • Question

  • Hi All,

    SQL and Windows version: Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64)   Jan 23 2020 21:00:04   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor) 

    Servers are in Workgroup.

    Our Password Complexity Parameters for SQL login:
    has atleast one digit
    has atleast one uppercase character
    has atleast one special character
    has atleast one lowercase character

    We are able set initial password for new SQL login as a TestingDB@001 and testingdb@001. But new initial password should be TestingDB@001 NOT testingdb@001 as per above Complexity Parameters. Is this expected behaviour of SQL 2017 for SQL login password?

    We have tried to create OS user (non admin windows account), OS level also its taking both passwords (TestingDB@001 and testingdb@001). As per above Complexity Parameters, it should accept only TestingDB@001 password. Is the above SQL login password issue is due to OS?

    Please suggest how to achieve above Complexity Parameters in password creation.

    We have set below policy in local computer policy on DB server. 



    Thursday, April 9, 2020 1:12 PM

Answers

  • SQL Server is taking its password validation from Windows. As far as I know the Windows rule is three out of the four groups you mention. I don't know if that is configurable. You are better off asking about that in a Windows forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 9, 2020 9:38 PM
  • Hi PraveenKumar,

    Starting with SQL Server 2005, SQL Server can apply the same complexity and expiration policies used in Windows to passwords used internally in SQL Server. By Check  "Enforce password policy" and "Enforce password expiration" when creating login.

    However, one of the requirements for password complexity in the Windows password policy mechanism is that the password must contain characters from three of the following four categories, rather than all four categories:

    • Latin uppercase letters
    • Latin lowercase letters
    • Base 10 digits
    • Non-alphanumeric characters 

    >>>Please suggest how to achieve above Complexity Parameters in password creation.
    Please refer to this article.
    It describes the best practices, location, values, and security considerations for the Password must meet complexity requirements security policy setting.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 10, 2020 6:32 AM

All replies

  • SQL Server is taking its password validation from Windows. As far as I know the Windows rule is three out of the four groups you mention. I don't know if that is configurable. You are better off asking about that in a Windows forum.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 9, 2020 9:38 PM
  • Thanks. I have posted my query on Windows-Security forums. If you found any any workaround/solution, plesae update me.

    https://social.technet.microsoft.com/Forums/ie/en-US/ddd633fb-ba3b-4c26-ac1e-8ee45a39baa7/windows-server-2016-security?forum=winserversecurity

    Friday, April 10, 2020 4:06 AM
  • Hi PraveenKumar,

    Starting with SQL Server 2005, SQL Server can apply the same complexity and expiration policies used in Windows to passwords used internally in SQL Server. By Check  "Enforce password policy" and "Enforce password expiration" when creating login.

    However, one of the requirements for password complexity in the Windows password policy mechanism is that the password must contain characters from three of the following four categories, rather than all four categories:

    • Latin uppercase letters
    • Latin lowercase letters
    • Base 10 digits
    • Non-alphanumeric characters 

    >>>Please suggest how to achieve above Complexity Parameters in password creation.
    Please refer to this article.
    It describes the best practices, location, values, and security considerations for the Password must meet complexity requirements security policy setting.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 10, 2020 6:32 AM