Decoding of saved passwords in MS SQL Server
Long ago, in a remote galaxy, the previous administrator of your SQL Server set the linked server in it, using a specially created account with a generated password for this purpose. Now you need to do something with this link, for example, transfer it to another SQL Server; but just do not do it, because nobody knows the password from that account. Common situation?
Although MSSQL does not store passwords for its accounts, and only stores their hashes, it will not work with linked servers, because you need to have an open password before you can successfully authenticate to an external server. Passwords for linked servers are stored in encrypted form in table
But it's not so simple. Dedicated Administrative Connection . Significant restrictions are imposed on the DAC: only a user with the sysadmin privilege can open the DAC, and only one DAC can be opened to the same server at the same time. If you have local administrator rights on the server, but you can not log into MSSQL with sysadmin rights, that is, workaround - do not log in from your account, but from under the MSSQL service account or even from under LocalSystem.
Secondly, despite the fact that the field with the encrypted password is called
pwdhash - this is not any hash, but encrypted data. The decryption key is stored in the system table
This key is stored in two copies: the first one (
Thumbprint = 0x01 ) Allows use only from under the MSSQL service account, the second one (
Thumbprint = 0x0300000001 ) - from under any account on the server. Note that none of the stored keys are suitable for "offline" decryption of passwords outside the server, so if an attacker steals data from both these system tables, it will not do anything.
Thirdly, the decryption key itself is encrypted, and the "key for the key" is stored in the system registry in
HKLM: SOFTWAREMicrosoftMicrosoft SQL Server $ InstanceNameSecurityEntropy :
To read this value from the registry, you also need the local administrator rights on the server.
To get all three components and decrypt the saved passwords, the author created a convenient PowerShell script: gt;
If you run it from under the local administrator account on the server, it will please you about this window:
If you do not want to run scripts on the production server, then you can execute the decryption itself without administrator rights, if you first pull out the three components with SQL Studio and regedit, and paste them into the script explicitly. The first decryption step (
$ ServiceKey =[System.Security.Cryptography.ProtectedData]:: Unprotect ($ SmkBytes, $ Entropy, 'LocalMachine') ) Is required to run on the server, but the second step (
$ Decrypt = $ Decryptor.DecryDecryptor ($ ServiceKey, $ Logins.iv) and the subsequent work with CryptoStream) can also be performed offline.
Similarly, and the credentials stored in the database for execution of commands (
? etc.) are deciphered on behalf of less privileged accounts than the MSSQL service.
On the one hand, all this seems to be a blatant example of security through obscurity: if the decryption of passwords for connecting to linked servers is already implemented in MSSQL, then why is it not possible to show these passwords to the forgetful administrator? On the other hand, in terms of security, everything is very good: to decrypt passwords you need access to the server with local administrator rights, and if an attacker has received such access, then he already can do anything with the server. Unwanted elevation of privileges is possible only if the password from some linked server is used in addition to something important, for example, as the administrator password of the same server: ^
It may be interesting
visit this site
visit this site
visit this site
Pleasant data, significant and magnificent plan, as offer great stuff with smart thoughts and ideas, bunches of incredible data and motivation, the two of which I need, on account of offer such an accommodating data here.