SQL Server Connection String: Can the password be encrypted in line?

2018-10-22 21:03:52

I have an app, a commercial app, that stores its connection string in clear in the registry.

When I used a trusted connection to SQL Server, this is fine (the user id and password are in the IIS app, and are stored encrypted within IIS).

I now must move to SQL auth, and away from a trusted connection (long story).

I dream of a conn string like this:

Server=myServerAddress;Database=myDataBase;User Id=myUsername;

Password=@#$@#@%#$%#$@#$@#$;encryptedpassword=yes;

And a way that I can encrypt the pw (using the machine key or such).

Is there a path in this direction?

This is more for SO than DBA, but if you are using the identity/password for the app pool in IIS it will encrypt the password for you when it is stored. However, be aware that even with this process passwords can still be decrypted with not much work.

The connection string itself does not allow you to enter an encrypted password, the mechanism that is saving the connection string

  • This is more for SO than DBA, but if you are using the identity/password for the app pool in IIS it will encrypt the password for you when it is stored. However, be aware that even with this process passwords can still be decrypted with not much work.

    The connection string itself does not allow you to enter an encrypted password, the mechanism that is saving the connection string handles encrypting sensitive data (or should).

    2018-10-22 22:00:13
  • The answer is: NO

    There is no way to do inline encryption in a way that the machine or app will automatically encrypt or decrypt.

    The solutions are:

    Use .NET .config encryption (for example as documented here)

    Roll your own encryption

    Use a trusted connection

    There appear to be no other solutions to this.

    2018-10-22 22:54:12