I am going to explain the security process I did in the previous message. I think some concepts were crossed up.
The article at MSDN Building Secure ASP.NET Applications
explains multiple processes that can be used to secure applications.
Microsoft has enabled ASP.NET to encrypt credentials and session state connection strings. (Q329290).
What I have done is to use the utility Aspnet_setreg.exe
to encrypt a SQL DB login. This login is only granted the rights as explained in the (Q329290)
Each database environment has a different login. So, for DEV I use a database login of something like DB_DEV_ID, QA has a login of DB_QA_ID and PROD is DB_PROD_ID. Since the registry only gets a single setting, even if someone were to change the other registry settings the login would fail. The registry can not be ripped and placed on another machine as part of the decryption is a key to that specific machine.
Once I had that in place and proper rights granted to ONLY Stored Procedures for this login, I modified the web.config file to impersonate that login. ASP.NET security will not allow the impersonation id to be displayed. It is not available to the code.
This is all done per the 2 articles mentioned.
I placed an Un-Encrypted registry setting for the environment. Every application will use the same setting so it only has 3 possible settings (DEV, QA, PROD), depending on the environment.
I then put 3 DB connection strings in the web.config file and name them uniquely.
<add key="cnHRDb_DEV" value="Integrated Security=SSPI;Persist Security Info=False;data source=DB_DEV;database=HR;" />
<add key="cnHRDb_QA" value="Integrated Security=SSPI;Persist Security Info=False;data source=DB_QA;database=HR;" />
<add key="cnHRDb_PROD" value="Integrated Security=SSPI;Persist Security Info=False;data source=DB_PROD;database=HR;" />
When I go to define the database connection, I read from the registry to determine what environment I am in and then use the proper connection string.
I can not connect to the Prod database with the QA login. Because of this, we can't cross environments on accident (or thru evil intent).
This process means that the only location of the login is encrypted and can not be displayed or used for anything other than logging in. We only grant EXEC to Stored Procedures in the database so have no direct SQL happening.
I hope this explains it a bit better.