SQL Server – Can Connection String Password Be Encrypted Inline?

sql server

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=@#$@#@%#$%#$<encrypted 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?

Best Answer

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).

Related Solutions

Sql-server – Secure way to connect: Windows Authentication or SQL Server Authentication and why

Windows Authentication is going to be the most secure means, as opposed to SQL Server Authentication. The reason behind this is because with Windows Authentication, the OS handles the auth as opposed to SQL Server handling it with its respective method.

When you use Windows Auth, SQL Server just uses the authentication token that is stored when you log into Windows (the OS). This token-based security is more secure than SQL Server's native username/password security.

In other words if you can, choose Windows Authentication. There are times though when you cannot get around SQL Server authentication (legacy application, third-party vendor, etc.).

Sql-server – Integrated Security Success on Master, Fails on Other Database

In SQL Server, as long as a LOGIN is valid, it will have access to the master database in a public role. This is both for historical and practical reasons. For example, stored procedures beginning with "sp_" are searched first in the master database and will execute from there. The implication is that users need to first have access to the master database to begin with. tempdb is another database that allows user access via the public role.

User databases do not have this as a default setup, so unless the Login is specifically given permission, or has server-wide roles such as "sysadmin", it will not be able to switch to the database context, hence your error.

The fact that you manage to log in at all using the machine account means that it is either explicitly a created LOGIN, or is a member of a group that appears under "Logins" when you browse the Object Explorer in SQL Server Management Studio.

[SERVER\Instance]
    Security
        Logins
             .... here ....

You can specifically assign roles or grant execute permissions to the machine account:

use other;
exec sp_addrolemember db_datareader, [DOMAIN\IIS_SERVERNAME$]
GRANT EXECUTE ON sp_myproc TO [DOMAIN\IIS_SERVERNAME$]

Best Answer

Related Solutions

Sql-server – Secure way to connect: Windows Authentication or SQL Server Authentication and why

Sql-server – Integrated Security Success on Master, Fails on Other Database

Related Question