Re: data access strategy

Hi Russ,

First of all, there must be a Domain in your LAN so that you and your users can connect to your SQL Server instance using Windows Authentication. But when you are out of your LAN, you can't do that because you'll be in a non-trusted environment. So you should use SQL Authentication instead.

Also, you did not mention if 8082 is a static or dynamic port but that's for sure it's not the default port so you must be using a Named Instance instead of a Default Instance and it means that your Named Instance will be using dynamic ports by default (if you have not changed it to use a static port) and if it uses a dynamic port, it means that this dynamic port will be changed when your SQL Server service restarts.

I don't know your network, but if you have only one IP to internet and you need to use this IP to reach to your SQL Server server inside your network, you'll need to redirect connections to your SQL Server server when connections come from outside of the network from a static IP or port etc. This is the work of network guys to redirect specific connections to your SQL Server server.

--
Ekrem Önsoy



"Russ Green" <russ.green@xxxxxxxxxx> wrote in message news:B8AB1383-3DD6-43D8-BE92-3E11C842A3F8@xxxxxxxxxxxxxxxx
I've developed an app for our company using VB.NET (winforms) and SQL server express running on our SBS server 2003 Standard

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 advice 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 (trying and failing), plus not sure thats a good idea. I also have external windows hosting and the ability to host the database on a remote site. Although I'm nervous of doing that I would be happy if it was possible to mirror my database onto the ISP's server so my remote clients can connect to that instance. Assuming of course that mirroring works both ways.

I've tried all manner of connection strings to connect remotely. I believe I have opened port 8082 on my firewall (it's managed so I can't see it with my own eyes) and pointing it to my SQL server machine.

I was using the SQL instance with windows authentication only but just changed it to test in mixed mode as well.

My SQL instance is set to listen on port 8082 (using IPALL in the TCP/IP Properties dialog)

Some of the connection strings I've tried:
Data Source=<<ext IP>>,8082;Network Library=DBMSSOCN;Initial Catalog=eproject;User ID=sa;Password=<<password>>"
Data Source=<<ext IP>>,8082\sqlexpress;Network Library=DBMSSOCN;Initial Catalog=eproject;User ID=sa;Password=<<password>>"
Data Source=<<ext IP>>,8082;Initial Catalog=eproject;Integrated Security=True"

and various other combinations. All of these connection strings work from clients within my LAN so it must be to do with my firewall or the SQL server instance itself.

I don't really know anything about database mirroring, or even if it's possible with SQL Express to a remote DB on the web.

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

TIA

Russ

.

Relevant Pages

  • Re: ADP problems after SQL 2005 Upgrade
    ... Use the SQL Server Configuration Manager to create aliases; ... each port can be associated only with a single instance. ... Sylvain Lafontaine, ing. ...
    (microsoft.public.access.adp.sqlserver)
  • [NT] Microsoft SQL Server 2000 Unauthenticated System Compromise
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Microsoft's database server SQL Server 2000 exhibits two buffer-overrun ... clients connecting to TCP port 1433 or both. ... This message is a single byte packet, ...
    (Securiteam)
  • Re: Do SqlServer 2000 & SqlServer 2005 co-exist
    ... Either SQL Server 2000 or SQL Server 2005. ... So you have to move one of them to another port. ... You could allow an program exception in your firewall instead of a port number exception. ... I understand that you can connect to SQL 2000 named instance on the local ...
    (microsoft.public.sqlserver.setup)
  • Re: IIS, SQL 2000 & XPs Firewall
    ... Only one instance of SQL Server can use TCP port 1434. ... You may find it easier to use SQL Server Configuration Manager ... 2008 will be a named instance ... you will have to open those as exceptions in the firewall. ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL 200 Dev edition on workgroup will not connect
    ... I have been using sql server for about 8 years but mainly working with sql 2000. ... know your experience level with SQL Server and yes, sometimes things being messy but letting a SQL Server instance to accept remote connections and uninstalling it are generally straightforward tasks to perform. ... I suggest you to check for the dynamic port number that your SQL Server graps. ... Write down the port number in the "TCP Dynamic Ports" property and then run a Command Prompt and see if this port is bening listened. ...
    (microsoft.public.sqlserver.connect)
Loading