Standard approach connecting to a remote SQL Server 2

[seaport]

My workplace hosts its own web server and SQL Server. Connection between those two severs is an easy job because they are in the same domain. Any data received from the web site is available at the SQL server (and other internal applications) immediately.
Now I have to host one web application at a web hosting company. I wonder what is the standard way to achieve the connectivity between the hosted web application and the internal SQL Server. By "standard" I mean the most popular one, which I can find lots of internet resource about.
It seems like I have following options:
1. Connecting the web application to the internal SQL server using the usual connection string. I need to configure office firewall to accept outside requests.
2. Connecting the web application to the internal SQL Server using EndPoint web services. This seems to be more secure than the first option.
3. Using the SQL Server provided by the hosting company, and transferring data from the hosting company SQL server to my internal SQL Server. This option gives fast response time to web application users. It is OK for me if the data at the internal server is not up-to-date because the transfer occurs only a couple times a day. But I do not know how to do that.

Any comments?

Thanks in advance.

 

[DrSql]

It’s all depends on how critical your data is and how much Swiss cheese holes let your security team allows in your firewall. The safe bet is your third option and SQL Server has a new version to accommodate that is SQL Server Web edition. Web tier directly connects to SQL Server web edition and from your web edition then connects to the internal database. You security team will be also happy with design compared to direct connections. This will help you to limit the amount of outage and data lose.

Dr.Sql
Good Luck.

 

[1DMF]

The way we do it is having a permanent VPN tunnel between the webserver and our main server firewalls.

That way both SQL DB's are visible to us on the LAN and it means all data transfers are DES encrypted down the tunnel.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[seaport]

Thanks for your replies.

I talked to the hosting company. They do not allow calls to outside database. So the first option is out. I think the second option is possible, but the third one certainly has the best performance. I prefer the third one - using the SQL server of the hosting company.

Now the question is how to do it. The hosting company did not provide any method. The tech support suggested getting some open source tools from

http://www.asp.net/community/projects/.

Whatever those tools are, they are asp.net codes and I need to upload to the web server.

So for instant access of data in the hosting company SQL server, I need to have some asp.net codes as a web service to transfer data, probably in XML format. If the security is a concern, then the XML has to be encrypted first.

Am I on the right track?

Seaport

 

[1DMF]

I talked to the hosting company. They do not allow calls to outside database.

They actively block your code from being able to make connections via connection strings to other outside resourses?

Find a new host!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[ramam1]

How would they know it's a DB you are connecting to? Do they allow connections to outside webservers?

I think your best paths are either:
1 a private connection (ssh tunnel, IPSEC VPN, ...) that you pull your data through - be it a SQL connection or update files of some sort [like transaction logs for example]. Alternatively the same setup but pushing from the hoster to your enterprise.
2 encrypted data packages (update files) sent via any method you can [like a web POST to your enterprise or a web GET from the hoster].

In either scenario you are better off initiating the connections and driving the process from your enterprise - no reason to put the keys to the office on the hoster if you don't have to.

Doing this correctly requires knowledge of the tools and technologies you're using; if any of this is sensitive data be sure you know what you're doing - security is tricky stuff.

 

[seaport]

Thanks again. Your replies really help clearing my thoughts.

1. Blocking calls to outside resources via connection string is NOT a common practice in hosting company. So I should start looking for another hosting company.

2. For right now I am using a shared web server. So a private connection is not possible since the only thing I can do on the server is to upload files to my web directory.

3.

encrypted data packages (update files) sent via any method you can (like a web POST to your enterprise or a web GET from the hoster).

I guess this is the web service I talked about. It is what i am going to do.

4.

security is tricky stuff.

I totally agree. That's why I am using Web Service to go around this. As I understand, the user of a web service cannot do anything beyond the functionality that the service provides.

 

[1DMF]

What about MS Access, if all you can do is local?

I used to use MS Access before I moved to a MySQL host.

But the host will need to have the JET ODBC driver installed.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[seaport]

IDMF,

I got to admit that I totally forget Access as an option. Actually, I started my career as an Access programmer. I have some doubt about Access's ability of handling multiple online users at the same time. Do you have any input on this?

Anyway, the traffic on my site is low, like less than 200 per day. Maybe Access is OK.

Long time ago when I used classic asp I did use Access as data source. I use one Access mdb file for reading data online and another file for accepting data online. Regularly I updated and uploaded the "reading data" file, and downloaded the "accepting data" file and merged the data into the SQL server.

Am I doing it right?

 

[RRinTetons]

We run our web sites at Rackspace. They have connections to our primary db server here at the resort to do bookings and reservations. We set up a point-to-point VPN from a Cisco ASA box at Rackspace to our Juniper Networks firewall here and run the connection to the db over that. It works fine.

If the hosting company won't work to get that set up then I think it might be time to find a new hosting company.


-
Richard Ray
Jackson Hole Mountain Resort

 

[1DMF]

seaport, I used MS Access for an online system and it worked fine.

OK, not too many concurrent users, but it did work with mulitple online users at the same time.

The only way to know for sure is to give it a whirl.

But as Richard says, your web hosting company needs to work with you to find a solution that is acceptable for both parties.

If all they give you is a CP login then expect you do DIY, then you need to consider alternative hosting.

I know it's a nightmare, and I can't even recommend a hosting company, ours was down all weekend, wasn't even up this morning, they did nothing to fix it, till I complained at 10am this morning and they are paid £400.00 per month for this crappy service.

I kept telling my boss we need to move to a company that gives us value for money, but there is legacy friendship and other factors which means even we are stuck with unreliable, expensive hosting.

I've yet to find a hosting company that delivers efficient, well support, realiable services, so good luck if you do move!





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[seaport]

Hi,

I really should have changed the thread title to "Small Business standard approach connecting to a remote SQL Server."

I worked in a small company and the cost of that hosting service is less than $200 per month. So Richard's option is out of my reach. What I got is a shared web server and there isn't much I can do about it except for uploading files.

Therefore, from the small business point of view, I have to do everything at the application level. For instant communication between systems in different domains, xml web service seems to be the only choice. If security is the concern, xml data should be encrypted before transmitting.

Seaport