January 10, 2022 at 5:29 pm
Hi all,
We hope to (as part of a wider script) insert passwords into a table that looks encrypted. Of course we don't want to see the passwords in Mgt Studio, but we do want the application to read what we've inserted. However we need to replicate whatever encryption the app is using. The PK column holds hashed data of 172 characters each row, the Password columns 204.
Unfortunately no rows are returned by the following:
SELECT name KeyName,
symmetric_key_id KeyID,
key_length KeyLength,
algorithm_desc KeyAlgorithm
FROM sys.symmetric_keys;
TIA.
January 10, 2022 at 6:00 pm
Is it an in house written app or 3rd party?
Can you ask the developers how the app encrypts the data?
Does it use always encrypted by any chance?
Anything returned from sys.column_encryption_keys or sys.column_encryption_key_values?
Or is it doing something silly like encryptbypassphrase in a stored procedure definition?
And given than these are passwords I hope they are salted too and not reversible otherwise what’s the point in encrypting them.
Passwords should be hashed and salted and the salted hash checked against a stored salted hash to authenticate.
January 10, 2022 at 9:03 pm
Hi,
Is it an in house written app or 3rd party?
Can you ask the developers how the app encrypts the data?
3rd party
No
Does it use always encrypted by any chance?
Only for the password table, afaik.
Anything returned from sys.column_encryption_keys or sys.column_encryption_key_values?
Nope
Or is it doing something silly like encryptbypassphrase in a stored procedure definition?
No idea
January 10, 2022 at 9:20 pm
Ok so the encryption piece is written in the app then, so unless you can reverse engineer the application then your out of luck and will need to write your own encryption routines.
As you don’t know how it’s encrypted now trying to replicate it is going to be near impossible
January 10, 2022 at 10:22 pm
No problem with that, I've been duly diligent in asking the question here, and similarly I thank you for you input, good sir!!
January 11, 2022 at 6:43 am
The only other thing would be to see if the app has any web services/api’s you could programmatically hit and pass in the data that way
January 11, 2022 at 10:21 am
I have to say didn't I think of that, a bot that fills in the form at the front end - and it's a tempting idea but would likely raise security concerns as a bot would be a 3rd party (ourselves and MSSQL being the other 2) that adds another layer of vulnerability.
I'll raise the idea however, and let management shoot it down which I think they will, but nothing ventured...
Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply