The Solution

So, let's go through it step by step.

PWDENCRYPT

Let's try generating a password hash in SQL Server and look at the result:

SELECT PWDENCRYPT('very-secret-passwørd')
GO

0x02008D6818FFB31BAAA6F849583A0C1319E1E73A260525CFEC22D4BC9FE2FBCC535C682342634D5B87B2ED1A1DA2C63B7FED3CCDFC82DE31DD5670CCAC9BA7BA5866EB893843

The result consists of 4 parts:
0x an indication that this is a hexadecimal value.
0200 tells us the version of the hashing algorithm used. In this case, it tells us that it's hashed using SHA-512 (or SHA2_512 in the vocabulary of SQL Server). If the header is 0100 , it is instead hashed using SHA1 .
8D6818FF is the salt for the hash.
B31BAAA6F849583A0C1319E1E73A260525CFEC22D4BC9FE2FBCC535C682342634D5B87B2ED1A1DA2C63B7FED3CCDFC82DE31DD5670CCAC9BA7BA5866EB893843 is the actual salted and hashed password.

PWDCOMPARE

This function is used to compare a password against a salted hash. Let's try it out:

SELECT PWDCOMPARE('very-secret-passwørd', 0x02008D6818FFB31BAAA6F849583A0C1319E1E73A260525CFEC22D4BC9FE2FBCC535C682342634D5B87B2ED1A1DA2C63B7FED3CCDFC82DE31DD5670CCAC9BA7BA5866EB893843)
GO

-----------
          1

1 is a boolean value, indicating that the password matched the hashed password. Using another hash, the result will be 0 :

SELECT PWDCOMPARE('very-secret-passwørd', 0x02008D6818FFB31BAAA6F849583A0C1319E1E73A260525CFEC22D4BC9FE2FBCC535C682342634D5B87B2ED1A1DA2C63B7FED3CCDFC82DE31DD5670CCAC9BA7BA5866EB893800)
GO

-----------
          0

I changed the last 2 digits of the hash to be 00 and got a 0 back: The password did not match the hashed password. So far so good.

Reimplementing in SQL Using HASHBYTES

Now, in order to understand what's going on, I sort of reimplemented this with HASHBYTES based on the information found in the previously mentioned Stack Overflow questions:

SELECT HASHBYTES('SHA2_512', 'very-secret-passwørd' + CONVERT(NVARCHAR(MAX), 0x8D6818FF))

0xB31BAAA6F849583A0C1319E1E73A260525CFEC22D4BC9FE2FBCC535C682342634D5B87B2ED1A1DA2C63B7FED3CCDFC82DE31DD5670CCAC9BA7BA5866EB893843

Yay! I get the same hash. All good.

Reimplementing in Python

And then the grand finale: Doing the same in Python. This is where I ended up spending days in agony, but I will save you from it and show you what to do instead.

>>> import hashlib
>>> hashlib.sha512("very-secret-passwørd".encode("utf-16le") + bytes.fromhex("8D6818FF")).hexdigest()
'b31baaa6f849583a0c1319e1e73a260525cfec22d4bc9fe2fbcc535c682342634d5b87b2ed1a1da2c63b7fed3ccdfc82de31dd5670ccac9ba7ba5866eb893843'

There you go! Adding .upper() to the result allows us to compare the hashes from SQL Server and Python:

>>> hashlib.sha512("very-secret-passwørd".encode("utf-16le") + bytes.fromhex("8D6818FF")).hexdigest().upper() == "B31BAAA6F849583A0C1319E1E73A260525CFEC22D4BC9FE2FBCC535C682342634D5B87B2ED1A1DA2C63B7FED3CCDFC82DE31DD5670CCAC9BA7BA5866EB893843"
True