0

I'm currently developing an application based on ASP.NET MVC3, SQL Server 2008 and EF with database first.

My application requires every user to have it's own SQL Server database. These databases all have an identical structure.

My customers are identified with a customercode, e.g. fgt. This code is provided in the url. I need to know how can I retrieve the customercode from the url and set the connection string accordingly.

Thanks for the help

My idea is to connect to the database once the customercode is retrieved from the URL and then prompt to user to enter his username and password for access data. But yes, is a good idea to create a database to store the connection string of each customer. Can anyone write the code that I need for do this please?. I am new to asp. I come from php.

(I'm just learning English. Sorry about the bad grammar)

Improve this question
3
  • 2
    What's the URL that you'll be using? Commented Sep 14, 2011 at 20:48
  • 1
    As noted on the answer below, you might want to create a central database where you pull the customer code off the URL and query a table that contains your connection strings. That way you can have full control of the content of such strings (such as server IP, initial catalog, login information, etc). Commented Sep 14, 2011 at 21:00
  • Are these databases on the same instance? Are the user credentials for each database the same? Commented Sep 14, 2011 at 21:45

4 Answers 4

Reset to default
1

Try something like this to get started:

string customerCode = Request.QueryString["cust"].ToString();

string myNewConnString = ConfigurationManager
                           .ConnectionStrings["MyDatabase"]
                           .ConnectionString
                           .Replace("[placeholder]", customerCode);

Where your connection string in your .config is something like this. Note that I've assumed you'll place a token to be replaced ([placeholder]). 

 <add name="MyDatabase" 
      connectionString="Data Source=192.168.0.1;Initial Catalog=[placeholder];User ID=foo;Password=bar"     
      providerName="System.Data.SqlClient" />
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Perhaps I'm paranoid, but I would prefer to have an independent database that contained the customerCode to connectionstring mappings. Perhaps irrational, but allowing an end user to adjust my connection string in any way feels insecure.
@Godeke That is a very good point - this is an extremely dangerous approach; all the customer would have to do to access another customer's data is replace the customer code. At the minimum, you need to do some checking to make sure the customer who is logged in is the customer whose code you're accessing.
@WCRC : did I miss something where all the databases live side-by-side on the same DB instance?
1

Suggest that you whitelist your customers and their connection strings.

  • setup a web service on your side.
  • your deployed application calls your web service using the customer code.
  • web service validates the customer code, and returns a valid conn string.
  • customer's app keeps the conn string in memory (session, cache, whathaveyou).

This would allow you to ensure the conn string is always valid for a given customer code. You'd have fine grain control on access to the database for any reason (non-payment, etc). Your service would have a few hits thanks to any caching that you build in.

Improve this answer

Comments

0

maybe sqlshard could help you in using multiple databases?

http://enzosqlshard.codeplex.com/

Improve this answer

2 Comments

An interesting idea, insomuch as each user could be a horizontal partition... but I suspect that may be a lot of overhead and query complexity. My guess (only a guess) is that this is some hosted solution where keeping user data fully apart in different database files would make sense.
i agree with your observation, aswell as with Ladislav Mrnka.. without the intent it is kindof hard to really give an answer. your guess would almost be the only situation where you want to be in that situation.
0

Sounds like pretty insecure solution. What if customer put another customer code in URL? Where is validated if customer can access that code if you don't have any central database with customer's permissions?

You need authentication and some central store (additional database) where you will validate that user accessing the application has access permissions to provided URL. It will also validate if such database even exists. Next you need just SqlConnectionStringBuilder and use the customer code as a name of database (or part of the name). For security reason each database should have a separate SQL account with permissions to read only from that database. This db account can also be stored with that central storage with encrypted passwords.

There can be additional complexities if you also expect dynamical adding or removing customer codes = databases. That would require high privileged account to manage logins, accounts, databases, etc.

Because you are asking how to get part of Uri it looks like you have almost no experience with ASP.NET MVC and perhaps with everything related. You should definitely ask any more skilled colleague or boss to review your architecture because at this point it looks like you are going to have serious problems and you can develop very insecure application which can significantly harm reputation of your company and your customer.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.