How do I deal with connection strings in a Click-Once application?

Question

I'm thinking of building a Click-Once application, and am trying to wrap my head around how to handle the whole connection string issue. The problem is the following:

I want to use EF to get data directly from each client, thus requiring each client to have a connection string configured (this is not the issue). How do I deal with clients that are not on the same domain as the SQL Server being queried? In other words, if I tried to run my app from home as opposed to from work, what should my connection string look like? Would I need to configure my SQL Server differently to allow this scenario?

Thanks in advance!

Answer 1

Use SQL authentication. That will work across domains.

Answer 2

How are you planning to authenticate the home users? Within a domain you can check the user accounts permissions, using integrated authentication so the connection string itself does not contain credentials. For users outside of a domain I don't think this is possible.

I would think you need to wrap your database behind a webservice, with the webservice supporting a non-domain based authentication scheme. Remote users access that not the database.

Answer 3

Although SQL auth would work, you'd need a different user/password combo set up for each user of your application on the SQL server. Not an ideal or easy to maintain situation.

Your best bet is to not connect to the database at all. Instead, build a secure web service that your application calls to obtain its data. That web service can then be secured via Windows or username/password auth (which can be validated against the AD domain).

Answer 4

If the SQL Server is routeable on the same hostname regardless of where the client is (i.e. your home, client's offices) - I'm guessing you've got some sort of VPN or DMZ set up so your SQL server is accessible outside of your office building - then you can leave the connection string in your application fine and use SQL authentication (which is username/password vs Windows authentication.)

If your SQL server requires a different connection string (e.g. at work it's accessed at sql.internal.domain.com, outside of work at sql.external.domain.com) then you're going to need some sort of selection mechanism where the user can choose which connection to use before logging in - have all possible connection strings in your application.

You could also do funky things like using the IP address to determine which connection string to use (e.g. if the current PCs IP address is in the range 172.40.10.1 - 172.40.10.254) then connect to sql.internal.domain.com, otherwise sql.external.domain.com. Of course this is more difficult to maintain though.

EDIT:

As others have suggested, a web service may also pay in dividends here.

Answer 5

This issue goes beyond ClickOnce, it's a general networking issue.

The first question is: Can you reach the SQL Server at all from home?

Most likely the answer is "no" if the SQL Server is on an internal company network. You would need to set up a VPN in order to reach the server. If you set up a VPN, then you should be able to use the same connection string from home as long as you fully-qualify the server names, i.e. SqlServer.mycompany.local as opposed to just SqlServer. You would likely connect to the VPN using domain credentials, so you would be able to use the same connection strings.

But an even better way would be to use a wrapper such as WCF Data Services. This will expose the data through a Web Service (WCF) and gives you a lot of functionality - you can write Linq against it and everything. In the long run, this is probably going to be easier to work with if you expect the application to be used remotely.

In our case, we actually have business services - a fairly large API that provides business logic over Web Services as opposed to just wrapping database tables (call it an SOA if you like). In the super-long term, this is the best option because it abstracts a logical API as opposed to a database, but if you're looking for a quick fix, WCF Data Services might be the way to go.