Avoid storing connection string in session for different sql schema

Question

I am planning to have a multi tenant application in Azure where data isolation is achieved via schema separation.

I am planning to use subdomain to identify the tenant. Idea is to get the tenant name from the sub-domain, get the user-id and password from the login page and validate the uid, pwd & tenant id for the authentication. If authenticated, all the calls to SPROCS from the application needs to be directed to the schema (same name as the tenant).

However, i dont want to store tenant wise connection string in the web.config file. 1 option i can think of is, store the schema name and password in a table that is acceptable from the login page, and create the tenant specific connection string (with its UID and Password) and store it in session. Use this connection string while initializing any Stored Procedure.

However, i am not keen on storing uid and password of the schema in session. Is there any other way to manage this scenario?

Answer 1

There is no security harm to store connection string in session, as session values are not stored in Cookie. Only Session ID will be stored in cookie, and each values of session stored in server along with the Session ID.

But the connection string is common for group of users who access the application, in your case tenant. So storing in session will use more memory. Always try to use Context Approach, for example your Data Layer code will refer connection string as follows

String con=AppContext.Current.ConnectionString;

Above AppContext class will have actual logic to retrieve the connection string based on type of the host, such as worker role, web role, unit test etc..

•You can store the connection string in web.config, and key can be prefixed with sub domain value Example: Subdomain1_connection =connection string

•You can store them in a data base if you have central DB where all the tenants information stored

•If you don’t have such DB, you can create Azure table to store tenant information

I always have two context variables in any multi-tenant application, AppContext and UserContext, this two contexts provides appropriate data. So my unit test don’t bother about sessions, the context will deliver the values from static dictionary OR session OR database OR Azure table based on where my app is running.