Its actually typical to have a database behind an internal firewall that only your semi-trusted applications and specific internal networks can access.
It doesn't prevent a breach, but I think I understand what you would like to achieve, and that is access restriction by what is accessing the database.
If you stored your passwords in their own schema (pass.passwords instead of dbo.passwords), you could restrict access to the pass schema to whatever you would like.
Technically applications don't necessarily need to access the tables where passwords are stored, but they do need the salts, which aren't a secret anyways. So that you could store the salts in your dbo.user table, but the actual password in pass.passwords table.
You could give your applications access to stored procs, one of which could be
dbo.Authenticate(userId, hash), and restrict access to the pass schema to an offline internal network and restricted to specific users.
And I don't think access restriction is considered obfuscation.