Amar Kulo

Me and my unorganized thoughts

Integrating database of pwned password hashes with Microsoft AD

Few weeks ago, Troy Hunt has released password hash dumps from haveibeenpwned.com site. Dumps are large, splitted to 3 parts and contains 324+ millions of hashes. In this blog post I will show you how to integrate that large hash dump with Microsoft Active Directory and enable DC servers to check against that list before allowing user to change their password.

Microsoft has one feature that has been present since Windows server 2003 and it’s called password filters. It’s not often used as it’s meant to be used as an additional method for adding more complexity to password requirements in larger organisations. The smaller organisations and companies are sticking with the rules that are already present in Windows (both server and workstations), which are:

  • enforce password history
  • minimum password age
  • maximum password age
  • minimum password length
  • password must meet complexity requirements
  • store passwords using reverse encryption

There are some commercial solutions that can add more complex requirements to this list, but price tag is quite high. As soon as you see “contact us for price” you can count with that.

When Troy released hashes I got idea to implement them in some way with AD (Active Directory) to enable DCs (Domain Controllers) to verify passwords against it. In past few months Nist and Microsoft have came out with the new password guidelines as well, but I won’t write about that here, if you are interested to read about it you can read it on following links:

In short or tl;dr new guidelines are recommending removing password complexity, history and aging requirements as they are not adding to password security at all and are recommending comparing passwords or hashes to dictionary lists so that easy passwords can be eliminated on time as well as keeping length of passwords at minimum of 8 characters.

So today I will write about comparing passwords against hashes. While looking how to do this in proper way I stumbled upon OpenPasswordFilter by Josh Stone on Github. Code was easy to understand and it was really easy to start extending, in my case to support database validation.

OpenPasswordFilter in its current form is doing validation against 2 password lists and it’s doing partial and full-word validation of passwords. It is based on 2 components, one is OpenPasswordFilter.dll file which is integrating into AD, the other one is OPFService (Windows service) which is listening on loopback address for client connections. Client (in this case .dll) is connecting to service, sending first “hello” sequence and then password after that. If service has found password as a partial or full match it’s returning boolean which .dll needs as answer. Password filters are working in a way that all of them (you can have as many as you want) have to return true, if any of them fails password change is denied by DC.

I have extended OpenPasswordFilter and added following to it:

  • SQL server hash validation
  • Logging of exceptions
  • Custom “hello” keyword so you can change it to whatever you like from config file

My fork of it is available here on Github, you will find more details of it there, feel free to comment or create an issue if needed.

So to get OpenPasswordFilter or OPF working (my way) we need to do following:

  • install SQL server & SQL Management Studio
  • create database, in this example called PwnedPwdDB
  • create table BadHashes
  • create unique index for sorting and elimination of duplicate hashes
  • create Hashes view for easier data loading with bcp
  • download data from haveibeenpwned.com and unzip it to some directory
  • import to SQL server with bcp
  • test for hashes
  • install OPF

I won’t explain installation of SQL server, you can download SQL Server Express if you don’t have one running somewhere. Install is quite straightforward. After installation, create PwnedPwdDB database and run following code to create table, view and index on it.

Index is needed for faster searching of data, after all we are searching trough 324+ million of records. View is there to make it easier to load data with bcp as loading data directly to BadHashes table requires us to have an hash_id as well, that’s why we are loading data to the table trough view we created.

For loading of data to database I’m using bcp (bulk copy), Microsoft’s util for this kind of situations, loading of data to/from SQL server when data is formatted in special way, in our case with new line.

Now we are ready to import data. As data is quite big and bcp is sending 1000 rows per insert, here is the command I have used to load 10 000 rows per insert which was quite OK value for my server without too much deadlocking on the it.

bcp dbo.Hashes IN pwned-passwords-1.0.txt -T -S Server_ip\instance_name -d PwnedPwdDB -c -b 10000

Repeat command for other 2 files, pwned-passwords-update-1.txt and pwned-passwords-update-2.txt and any other you might have with SHA1 hashes.

In case that import fails or you get some client error, you can just repeat the commands. Thanks to that index above, if record has been found in database it won’t be imported again, it will be reported as a warning from bcp and import will continue on.

After import is done, and it will take some time based on performance of your SQL server, we are ready to test it. To simple test hash against database run this command, replace password with password you want to test. If you get hash back from database it means it has been compromised on some site breach and should not be used.

So after you have loaded data, tested it it’s time to download and install OPFService.

Head to OpenPasswordFilter fork that I did and download and compile source code or download precompiled version. I recommend that you compile your own version just because these are very sensitive things we are working on. If you decide to download precompiled versions do check hash values on .zip files to be sure they are from me and those I committed in a latest commit to repository.

Installation is quite simple:

  • When you have release (compiled or downloaded) folder just move it to a DC server, start elevated command prompt and change to that directory and write following command to install service
    • for 64bit Windows Server: \windows\Microsoft.NET\Framework64\v4.0.30319\InstallUtil.exe OPFService.exe
    • for 32bit Windows Server: \windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe OPFService.exe
  • change settings in OPFService.config to match your database settings
  • start service from services.msc or by writing sc start OPF in the same command prompt
  • if service has been started you can test it by typing OPFTest password and you should get following output

This means that password has been found and service is returning failure which will later on tell to DC not to accept password change. If you try any other more secure password that is not compromised you should get success as response which means that password is valid and not compromised.

Now the last thing remaining to do is to copy/move OpenPasswordFilter.dll to c:\Windows\System32 directory and validate registry key HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Notification Packages with regedit that it contains OpenPasswordFilter key as well. If all of that is set you just need to restart DC and test password change from normal Windows UI, ctrl+alt+del -> change password is the simplest way.

Note: if you have more than one DC you will have to install OPFService on all of them, easiest way is to just copy release directory from the first server to another one and run service installation command.

Happy hashing!

Useful links:

In case of any questions or problems leave me a comment and I will get back to you.

Update #1:

As Mitch pointed out in comments, you will have to install .Net 3.5 (or higher) to be able to start service properly as well as add key HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Notification Packages  if it doesn’t exists.

62 Comments

  1. I noticed that the OPFS service involves communication of passwords on the loopback network interface. Would this not be a security concern especially with the ability of tools like wireshark granting users permission to sniff localhost?

    • amar

      2017-10-04 at 17:17

      Technically yes, but if attacker has any kind of access (user permitted or not) to domain controller where OPF service is installed you have bigger problems than traffic capture 🙂

  2. Mitch Miller

    2017-10-03 at 22:02

    I see “create PwnedPwdDB database and run following code to create table, view and index on it.” Where is the code to do this?

  3. Mitch Miller

    2017-10-05 at 03:28

    The service doesn’t seem to want to start on 2016 domain controllers.

    • Mitch Miller

      2017-10-05 at 03:33

      Error 1053: The service did not respond to the start or control request in a timely fashion.

      • Mitch Miller

        2017-10-05 at 03:46

        For 2016 domain controllers, install the .NET 3.5 features. Service will start up.

    • Mitch Miller

      2017-10-05 at 10:30

      Also note, this does not exist on 2016 DCs. HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Notification Packages

      • amar

        2017-10-05 at 12:38

        Thanks for update Mitch!

        I had that key as well as .Net 3.5 from before so everything worked out of the box.
        I’ll add small update with your finds.

  4. Any chance you could give a brief summary on how to recompile the project from scratch? I am trying to get this running on Windows Server 2008 R2. Been having a lot of trouble figuring this out… Thank you so much!

    • amar

      2017-10-09 at 13:53

      Well it should be quite straightforward, download source, open it with Visual Studio and press on compile, nothing more than that. What kind of problems are you having while trying to compile it?

      • Logger.cs is missing from OPFService so it will not compile and any reference to logger gives an error in VS.

        • Yes, I have the same issue.

        • amar

          2017-10-12 at 12:45

          Ah, nice find. I have committed it now to repo. The issue was that there was a line in .gitignore file that would ignore all/any log files but it did also ignored Logger.cs because it had Log in the name. So I have explicitly excluded any .cs file from ignoring and pushed new commit to Github. Just do a git pull and build should work now. Sorry for this.

          • Thanks, this works great now! Do you have any tips for how to setting and stepping through breakpoints for debugging the service itself? I am having issues attaching to the running process. Is this possible? Or is going through logs the best way?

          • amar

            2017-10-13 at 13:59

            Well actually I have implemented something for that 🙂

            Just change configuration to DEBUG and run service from VS, you will get command prompt with service started and all breakpoints will be hit in Visual Studio.

  5. Seems like I couldn’t reply directly to your last comment. Thanks – I got the debugging working properly for the service itself. Very useful! What about debugging the DLL itself? It seems as though multiple machines and kernel debuggers would be needed for this, so I’m assuming logging is best for this?

  6. Hi,

    What is the best way to use OPF without the database but only a txt as dictionary ?

    In fact I tried to install the service etc.. But the Windows client can use passwords include in the blacklist. It will be greatfull if you can give me any help about that.

    • amar

      2018-03-18 at 12:05

      Hi.

      Sorry for late answer, you comment was sent to spam by Akismet ¯\_(ツ)_/¯

      There is new service available that someone wrote which is using new Pwned database online directly from HIBP, so maybe you can try that one.

      As a txt dict I don’t think it would be good idea, as it is huge, and searches will take long time. You could always port DB part of service to SQLite, that way you don’t need DB server, just one file.

  7. Mitch Miller

    2018-02-27 at 17:50

    pwned-passwords-2.0.txt has been released and the import function doesn’t seem to be working. I am getting the following.
    Starting copy…
    SQLState = 22001, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 11.0]String data, right truncation

    BCP copy in failed
    Anything need to be done to support this new list of password hashes?

    • Mitch Miller

      2018-02-27 at 18:03

      I see why. The new file includes the count of how many times the password was seen. Guess I will write up a powershell script to remove the :#

      Get-Content c:\temp\pwned-passwords-2.0.txt -Wait -Tail 30
      FFFFFCD1D16A8E9CA02C237E1B00916CB7B72AE8:1
      FFFFFCDAB41491954FBC0228B522567510C50926:1

      Get-Content c:\temp\pwned-passwords-update-2.txt -Wait -Tail 30
      FFFB41360380B1EE87EF87E0CCEB31B17B2FCE14
      FFFB95C829C2B58E07EBA26290F6D8193D2F2F11

      • Mitch Miller

        2018-02-27 at 18:10

        In case anyone needs it:

        $hashes = Get-Content c:\temp\pwned-passwords-2.0.txt

        foreach ($hash in $hashes){
        $hash.Substring(0, $hash.IndexOf(‘:’)) | out-file -append c:\temp\NEW-pwned-passwords-2.0.txt
        }

        • Mitch Miller

          2018-02-27 at 18:27

          Better way to not load the whole damn thing into ram:

          Get-Content c:\temp\pwned-passwords-2.0.txt |

          foreach-object{
          $_.Substring(0, $_.IndexOf(‘:’)) | out-file -append c:\temp\NEW-pwned-passwords-2.0.txt
          }

      • Mitch Miller

        2018-02-27 at 22:27

        The other scripts I posted used way too much ram. This one seems to do the trick.
        $path = ‘c:\temp\pwned-passwords-2.0.txt’
        $r = [IO.File]::OpenText($path)
        while ($r.Peek() -ge 0) {
        $line = $r.ReadLine()
        $line.Substring(0, $line.IndexOf(‘:’)) >> c:\temp\NEW-pwned-passwords-2.0.txt
        }
        $r.Dispose()

        • amar

          2018-02-28 at 10:36

          Nice find Mitch!

          If you want you can send me pull request on github and I will merge it with code there.

        • EnviableOne

          2018-02-28 at 12:01

          Did a lot of work on not using ram, my work machine is still 32 bit, this is quicker and uses less resource

          [GC]::Collect()
          $In_File=”C:\temp\pwned-passwords-2.0.txt”;
          $Out_File=”C:\temp\pwned-passwords-2.0-NC.txt”;
          $j=0;
          $line = “”;
          $output = “”;
          $Clock = [System.Diagnostics.Stopwatch]::StartNew()
          $sr = new-object System.IO.StreamReader($In_File);
          $sw = new-object system.IO.StreamWriter($Out_file,$false,[System.Text.Encoding]::UTF8);

          $line=$sr.ReadLine();
          while ($line -ne $null){
          $line = $line.Substring(0, $line.IndexOf(‘:’));
          $sw.WriteLine($line);
          $j++;
          $line=$sr.ReadLine();
          }
          $Clock.Stop()
          $sr.Close();
          $sr.Dispose();
          $sw.flush();
          $sw.Close();
          $sw.Dispose();
          Write-Output “total lines Converted: $j”,”Time elapsed:” $Clock.elapsed;

  8. Just curious, I’m getting the following:

    C:\opf>opftest password

    ———————————————————————-
    Test program for OpenPasswordFilter.dll (Josh Stone, yakovdk@gmail.com)
    ———————————————————————-

    Initializing winsock…done.
    Loading library…success (b73d0000).
    Getting PasswordFilter() address…success (b73d1030).
    Testing password password…success.

    I’ve installed this on two DCs and the service is running fine. the DB is up and running without issue. I think the only difference is that I’m running the newest 3.0 text. Would that cause this?

    I thought “password” would fail like in the example. I’ve also tested “123” with it succeeding.

    • amar

      2018-09-26 at 09:07

      Hmm, it could be. It could be difference in hashes or something like that.

      • Same problem here, all passwords are passing the test.
        I’m using the v4 database, but the test sql query for good/bad passwords is working fine…

    • Hello, did you figure out what caused this ?

      • I’m in the same boat..
        All passwords passing with OPFTest with v4.0 list.
        Direct SQL Query working.

      • Hello, I am also experiencing this issue with v4 database, were any of you able to figure it out?

  9. Hi Amar

    Testing it right now and it looks great so far!
    Just a question, is it possible to use both the DB with the hashes and the file that contains partial matches? Or can I add an entry in the database that checks for partial matches (for example postal codes)?

    • amar

      2018-12-10 at 12:17

      Hi Roel!

      Yes, it’s possible to use both, config file for it contains partial checks as well so you could use both database and partial checks file.

      • Sorry Amar, but I cannot seem to find how to do that.

        The database check is working, but adding partial matches to opfcont.txt doesn’t seem to work. What exact changes do I have to apply in OPFService.config?

        Thank you!

      • Hi!
        Do you know what i’m suppose to add in the config file? By default it’s not checking against the “opfcont.txt” file in the root folder

      • Hi,
        where exactly gets this configured? Can´t seem to get it working.

        Thanks!

        Tom

      • Hello Amar,

        I wondered if you could elaborate on this a bit more?
        I have got the service and DB checks working as expected but I cannot seem to figure out how to tell it to also use opfcont.txt for partial checks.
        You mention that the config file contains partial checks but I can seem to figure out where to specify the location and filename in OPFService.exe.config

        P.S. You’ve done a great job with this.

      • Hi ! Could you provide guidelines on how to implement a partial check (text file) in addition to the full word (sql query) with your fork ?

    • Everton Bruno Bernardi

      2019-03-29 at 15:36

      Hi there, Roel.

      Have you ever managed to achieve this? I’m trying to set it up but cannot find the setting Amar mentioned at all..

  10. Hi,

    We have implemented the OPF service in our testing environment and it works great. What is the best approach to remove OPF service? We face the problem that we can not change passwords anymore.
    So how can we make use of the default domain password policy only?

    Kind regards,
    Quintin

    • amar

      2018-12-10 at 12:16

      I would say reverse way of implementing it, so just edit  HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Notification and remove OPFFilter from there.

  11. Rodolfo Noronha

    2018-12-20 at 07:34

    Hi guys,

    getting this when I try to start service on Domain Controller… can someone help?

    Only one usage of each socket address (protocol/network address/port) is normally permitted
    StackTrace : at System.Net.Sockets.Socket.DoBind(EndPoint endPointSnapshot, SocketAddress socketAddress)
    at System.Net.Sockets.Socket.Bind(EndPoint localEP)
    at OPFService.NetworkService.main()

    • amar

      2019-01-10 at 08:47

      Uh, I have never seen this one, but based on the message error something else is listening on the same port and that’s why you get that conflict.

  12. Hi Amar,
    Great article, it works like a charm thanks!
    Do you know how you can make this work with AD FS.
    We have enabled the password update page, but if you change there a password the check doesn’t work.
    I’ve tried to installed the OPF Service in the AD FS server, unfortunaly no result.

    • amar

      2019-02-02 at 13:49

      Hi Gerko,

      Unfortunately I haven’t worked with federation services on AD, so I don’t have a clue how the authentication process is looking over there.

  13. Hi Amar,

    What do you do when there is a new password list? Do you just run the BCP import again? Will it import all the data again? it took quite some time just to import the password list in the first place.

    • amar

      2019-06-13 at 10:15

      Yup, I would say you need to import it again, if you don’t have diffs to just import them.

  14. Hi Amar,

    How do we use SQL and also authenticate using a Windows account?

  15. Does the v4 password file fit in SQL Express?

  16. Everton Bruno Bernardi

    2019-03-28 at 20:31

    Hi there, Amar!

    First of all: THANK YOU SO MUCH for this great project!!

    I’d like to know how could I port the project to connect to an Oracle database. I have absolutely no knowledge programming on C# and we have no SQL Server licenses available (SQL Server Express will stop importing hashes once the 10 GB limit is reached – the latest HIBP database has 22 GB!).

    Please help me with that!

    Kind regards

    • amar

      2019-06-13 at 10:14

      Hi,

      One thing you could do with V4 db is to split it to few databases and then do search stored procedure that would search in all of them. It won’t be that fast, but could work.

      I might write a post about it soon when I get some time.

  17. Im having trouble with timeouts.. In the mysql cmd line, the test search works but takes about 1 minute 15seconds to complete.

    The OPF Service seems to timeout after 60 seconds.

    Is there anyway to increase the timeout? I’ve dug around in the github repo a bit, but couldn’t find anything relating to a DB Query Timeout..

    Thanks

    • amar

      2019-06-13 at 10:14

      This sound like you need index on the table as well as faster disk. If you are not dependent to local database, it’s possible to use HIBP api directly.

  18. Hi.
    First thank you for great manual.
    Also thanks to those who provided the script to do proper importing.

    I was trying to do all of this with SQL Express and apparently with new v4 file it is impossible as the amount of records in the file is more than 500 billions and DB file grows more then 10GB in size.

    If someone tried to do this with any open source sql servers please share your experience.

    Thank you

  19. Hi,

    As v4 become really big is there a way to use any other open source DB server?
    Much appreciated if any body would help with steps.

    Thank you

    • amar

      2019-06-13 at 10:12

      Hi,

      One thing you could do with V4 db is to split it to few databases and then do search stored procedure that would search in all of them. It won’t be that fast, but could work.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2019 Amar Kulo

Theme by Anders NorenUp ↑