Database access strategy

[Guest]

I've developed an app for our company using VB.NET (winforms) and SQL server
express running on our SBS server.

The app works really well within our LAN but I often work remotely and quite
often would benefit from using my custom app. I've tried the VPN but for
some reason my app just won't connect to the database.

What I'd like is some advise about how I can allow remote clients to access
a SQL database that lives inside our LAN. I've never done any work with web
services but I thought I might be able to do something there. Or maybe it's
a simple matter of a firewall port and a clever connection string.

Any advice on the best strategies to adopt for this problem would be hugely
appreciated.

TIA

Russ

 

[sloan]

If you want to talk to Sql Server directly, then you need two things:

Be able to ping the Sql Server Box.
Open up (usually) port 1433.

check www.connectionstrings.com for a connection string that has the IP and
Port Number in it. (Which is kinda like the most anal way to define a
connection string).

OR

You can also expose services to the outside world, instead of your database.
WCF is one of the latest for that.

See
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!158.entry
for a basic WCF example.

My example is based on a DotNet to DotNet world, as opposed to a Java to
DotNet world for example.

Juval Lowy's book on Programming WCF services would help you out alot "in
general" about the "Software as Service(s)" approach.



You can also google:
Microsoft REST
Microsoft Astoria

for another flavor.

 

[Russ Green]

Thanks for this. I have setup my SQL express instance with a port and
opened that port on my firewall....cannot connect

I'm trying this connection string externally
Data Source=<<ext IP static>>,8082;Initial Catalog=eproject;User
ID=sa;Password=<<password>>"

it's a bit odd as this works from another LAN client.
Data Source=<<internal server IP>>,8082;Initial Catalog=eproject;User
ID=sa;Password=<<password>>"

My SQL instance is set to listen on port 8082 (using IPALL in the TCP/IP
Properties dialog). Should that work or am I missing something? If it
should then I'm guessing a firewall issue.

I'll look into WCF as well. Is this likely to be an alternative to my data
access layer or sit between the DAL and database? By DAL currently opens
and closes the connection, initializes commands and fills datasets and
datatables.

Russ

 

[sloan]

WCF would replace your DAL

Clients would talk to your "Services" instead of your DAL.

Your services can call/use your DAL.

............

I don't know why you can't connect. If it works on one machine and not
another, then I would guess firewall as the first hinderance.

IP and Port, that's been the magic combination for some many years now.

Good luck.

I would get Juval Lowy's book in Programming WCF. It's the the investment
because he gives "overview" stuff in teh appendixes which helps you
understand some trends.

 

[harborsparrow]

I've developed an app for our company using VB.NET (winforms) and SQL server
express running on our SBS server.

The app works really well within our LAN but I often work remotely and quite
often would benefit from using my custom app.  I've tried the VPN but for
some reason my app just won't connect to the database.

What I'd like is some advise about how I can allow remote clients to access
a SQL database that lives inside our LAN. I've never done any work with web
services but I thought I might be able to do something there.  Or maybeit's
a simple matter of a firewall port and a clever connection string.

Any advice on the best strategies to adopt for this problem would be hugely
appreciated.

TIA

Russ

Web services in .NET are the way to go, assuming the web service will
scale (that is, is not having hundreds of simultaneous hits or
something). It's really quite trivial to do them using .NET 2 or
later.

I've got a small tutorial online at http://www.harbormist.com/cis573_06B/code/WebServices/index.htm
and there are many other sources. It's really a small learning curve,
and it just works.

 

[Russ Green]

Thanks....Nice tutorials....I'll give them a go first.....I was just this
minute about to dive into WCF for the first time.

 

[JDS]

By default SQL Server Express does not allow remote connections. Have
you changed this (using the surface area configuration)?

Also, I'd advise against using (or even having) the 'sa' account. Just
set up a specific SQL account with the privileges required. You may be
OK using a non-standard port but once you have opened it up then you
are liable to an attack by someone attempting to log on to the sa
account (with a dictionary attack for the password).

HTH
Jeremy

 

[Russ Green]

ah good point about the sa account.

 

[William Vaughn \(MVP\)]

We've discussed this many times. I have a summary of the connection issues
on my blog (search for "connecting"). Remember, SQL Server 2005 no longer
broadcasts its presence on the network so you also need to run the SQL
Browser service on the host server to make is visible. I think for your
situation a VPN is (by far) the most reasonable approach. See Chapter 9 of
my book for more details. I focus quite a bit on Windows Forms applications
(in VB.NET).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com/blog/billva
http://betav.com
____________________________________________________________________________________________