WCF an Entity Framework connection

Question

I have a 100+ users on a Silverlight application that uses WCF and entity framework.

Everyone has their own database and here comes my problem.

How do I make sure that only the user gets access to his database via WCF. Do I have to send the connectionstring every time I make a call to the service, or?

• NoChance: Is this some kind of SaaS? Did you actually create 100 separate databases?

Answer 1

you should have some form of authentication mechanism, so you know who the user is that's connected to your back-end web service. Then you can just look up the user id against a 'global' DB configuration to get the connection string - so user "Dave" connects, you look up in the list of DBs to see that Dave's DB is "DB_001gh443a" (or whatever it is).

• user1741807: What could be that authentication mechanism? To authenticate the user, could i send a unique username with every WCF request? So that every operation contract expect at least a username?
• epalm: You could use the WCF UserNamePasswordValidator
• epalm: Or you could use the BeforeSendRequest method in an IClientMessageInspector in an IEndpointBehavior to attach some headers (like a username) to each client request.

Answer 2

Sending the connection string to the service would not be a good idea. The caller should really have no idea if there is one database or one million (unless you are building something truly odd, like a database front end, along the lines of phpMyAdmin).

On the Entity Framework side of things, you can change the connection when your context is created. So, for example:

 public class EFContext : DbContext
 {
 public EntityFrameworkSession()
 : base(DetermineConnection(), true)
 {
 }
 private static DbConnection DetermineConnection()
 {
 // create an ADO.NET connection here based on your criteria
 }
 }

Which would leave open the connection of how to store/retrieve the connection. There are a few ways to handle this, beginning with the simplest (storing multiple connection strings in your web.config and pulling the one that applies to the current user) to more sophisticated methods (like putting all of your connection strings in an encrypted database).

Being the paranoid type, I would be more likely to do the latter.

The method that makes sense for authenticating a user (and, thereby having the information to determine which database connection to use) is mostly dependent on your setup. Some good links on setting this up with WCF would include:

1. http://msdn.microsoft.com/en-us/library/ff647503.aspx
2. http://msdn.microsoft.com/en-us/library/bb386582(v=vs.100).aspx

Since you are using WCF, I would use Active Directory if it is at all feasible. Windows authentication tends to play very nicely with .NET security.

• Jim G.: -1: Sending the call to the service would not be a good idea. The caller should really have no idea if there is one database or one million: So tell me again - Why doesn't it make sense to let the service decide which database to route the request to?
• Michael: @JimG. The wording was a little awkward. I'll edit it to reflect this, but the point was that it didn't make sense to send the connection string to the service. e.g., that it makes sense to let the service figure out which database to route to.
• Jim G.: OK. Then I most definitely agree with you. I'll reverse my vote to a +1.