Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Linked servers over the internet Expand / Collapse
Author
Message
Posted Sunday, August 22, 2010 8:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Ok here's the big projet that I'm going to undertake in the next few weeks and needs to be 100% complete by sept 20th :

We have approximatly 3500 clients.

We have a membership only site for them hosted on our web server(s). The site is used to recommend the best fit for products to sell to our clients' clients.

On OUR site, we need to show their inventory (the client's AND ours). We need to read this in REAL TIME, not even 1 minutes of backdata is acceptable.

The clients' database servers are completely outside our network (some of them are not even sql server as we've found at least 5 different ERPS so far).

I've considered building an asp .net application to expose web services but only a few clients already have web servers so I'd preffer not to undertake that route as that would require a whole hell of a setup and coordination and time.

From my extremely limited knowledge of networks, I'm really not sure what to do to make those connections safely.

My first instinct was to build a vpn tunnel between the servers but then I remembered that we can only have 1 vpn connection on 1 machine at any time, so how the heck can I go to 3500 (or more to the point 4-5 at the same time) and also very important, how can the connection be made in real time (1 sec delay max)?

The "safest" idea I had was to setup a new machine in a dmz. Install sql express and change the default ports. Create an account on the real server than can only read the bare minimum of data. Add that account in the linked server between Express and Real Prod server on each clients. Then connect our prod server to the new express server using linked servers. That way I could dynamically change the linked server name in the query and that would give me real time and fast access to the data.

My biggest issue with this setup is again that we need to add a new physical (or virtual) server in the mix. Is there a safe way to do it direct on the real prod server?
Post #973049
Posted Sunday, August 22, 2010 8:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:10 AM
Points: 2,694, Visits: 6,895
I don't know the answer to this, but I know what I'm planning on looking into for a similar challenge.
Look into messaging.


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #973052
Posted Sunday, August 22, 2010 8:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Cool, it would be awesome if you could post your findings and articles here. I'm sure it'd help a few people along the way.
Post #973054
Posted Sunday, August 22, 2010 8:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:10 AM
Points: 2,694, Visits: 6,895
I likely will, but that part of our project likely isn't happening for a few months and I might not have time to look into it in time to help you on this project, which is why I suggest you look into it as well.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #973057
Posted Sunday, August 22, 2010 8:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
I'm already looking hard into it.

Making a link server to and ip adress over internet is not the issue. I want to make sure I have the max security for the min amount of work and $ which is a big issue with our clients.

The risk here is huge... a single server having access to 3500 companies. That sounds like heaven for a would be hacker.
Post #973058
Posted Sunday, August 22, 2010 10:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:36 PM
Points: 1,832, Visits: 3,400
Just a thought, but for the clients that are using SQL Server (2005 or higher) you could perhaps use Service Broker?

If you need to query one of your client's SQL Servers you simply begin a new conversation and send a message to the target (client SQL Server) where you specify what kind of inventory information you request. The target replies to your message with a new message that contains the information you requested.
In that way you don't need to set up linked servers and risk granting to many privileges to the account used to authenticate to the linked server. The only thing your user (through the certificate) is allowed to do is send messages to the target server.

For the non-SQL Server DBMS you need to find another solution, perhaps web services with a set of methods that corresponds to the contracts and message types you set up in the Service Broker solution.

http://rusanu.com/articles/ has a lot of very good articles about Service Broker.
Post #973082
Posted Sunday, August 22, 2010 11:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
I'll keep service broker in mind.

However we do have sql 7, 2000 in place so I'd prefer to develop a 1 size fits all system as much as possible due to very short timeline. I'm still opened to both direct access or web services as they are basically the same request with very little modifications. However I barely know how to speel broker so I'm not too sure I'd want to include that learning curve in the project... The idea sounds simple enough but I'm pretty sure this could snowball quite a bit with all I don't know that I don't know.

What I did like about direct linked server without using express as in between is that I could shoot a mail to the system's admin with a single script to execute and bam, the whole thing was done (after exposing sql to the net of course). From a time consuming point of view and budget that was the single best solution, but not from a security aspect. It's like saying I can do that pretty, good, and cheap... pick 2 out of 3. I'm pretty sure I won't get away with that for all clients .


Asking everybody to upgrade to 2k5 is just out of the question... it's an even bigger demand than adding another server with express on it. In our case, we are actually requiring direct access to the companie's ERP system, which they would need to upgrade (assuming they can).

Many network admins are going to see this as a very big deal / security risk hence my need for a solid solution to propose.
Post #973084
Posted Sunday, August 22, 2010 11:18 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Nils Gustav Stråbø (8/22/2010)
Just a thought, but for the clients that are using SQL Server (2005 or higher) you could perhaps use Service Broker?

...

http://rusanu.com/articles/ has a lot of very good articles about Service Broker.


Thanks for the link, but just a few quick questions before I spend 2 days reading all those articles .

1 - How fast is the response time? I see there's talk about queues, but I can't afford to just sit there and wait for 5 seconds to have my query processed. I've already written the query and it runs in only a few ms and I need to keep it that way (including returning the data).

2 - Can we get the data back as some sort of derived table or does it need to be something like say xml where you need to reparse it before joining to your data?

3 - What's the big safety gain between "sending message" permission VS read only permission on the 1 table where the inventory is saved?
Post #973085
Posted Sunday, August 22, 2010 12:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 1:36 PM
Points: 1,832, Visits: 3,400
Ninja's_RGR'us (8/22/2010)

Thanks for the link, but just a few quick questions before I spend 2 days reading all those articles .

1 - How fast is the response time? I see there's talk about queues, but I can't afford to just sit there and wait for 5 seconds to have my query processed. I've already written the query and it runs in only a few ms and I need to keep it that way (including returning the data).

2 - Can we get the data back as some sort of derived table or does it need to be something like say xml where you need to reparse it before joining to your data?

3 - What's the big safety gain between "sending message" permission VS read only permission on the 1 table where the inventory is saved?

1. The message is queued in the transmission queue should be sent immediately; only restricted by the size of existing pending messages in the transmission queue. Of course there could be a delay if you have 100's of messages put in the message queue at the same time. There will also be a little delay during authentication between the target and destination servers (just saw that I mixed target and destination in my previous post), and depending on the latency between you and your clients this could take a few seconds. But I guess you will have the same issue with linked servers as well. Only way to find out is by testing.

2. No, there is no way to return data that SQL Server interprets as a table. You can return XML, but that has to be parsed, or you can return any text or binary, but that has to be parsed too.

3. None really.

My own experience is that it takes time to get comfortable with Service Broker, especially when dealing with communication between servers. There are a lot of new concepts to get familiar with; transport and dialog security, certificates, keys, endpoints, service bindings, routes, contracts etc. Given that your deadline is 20. September and you have never played with Service Broker before, I would personally not go down that path this time.
Post #973089
Posted Sunday, August 22, 2010 2:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Nils Gustav Stråbø (8/22/2010)
Ninja's_RGR'us (8/22/2010)

Thanks for the link, but just a few quick questions before I spend 2 days reading all those articles .

1 - How fast is the response time? I see there's talk about queues, but I can't afford to just sit there and wait for 5 seconds to have my query processed. I've already written the query and it runs in only a few ms and I need to keep it that way (including returning the data).

2 - Can we get the data back as some sort of derived table or does it need to be something like say xml where you need to reparse it before joining to your data?

3 - What's the big safety gain between "sending message" permission VS read only permission on the 1 table where the inventory is saved?

1. The message is queued in the transmission queue should be sent immediately; only restricted by the size of existing pending messages in the transmission queue. Of course there could be a delay if you have 100's of messages put in the message queue at the same time. There will also be a little delay during authentication between the target and destination servers (just saw that I mixed target and destination in my previous post), and depending on the latency between you and your clients this could take a few seconds. But I guess you will have the same issue with linked servers as well. Only way to find out is by testing.



2. No, there is no way to return data that SQL Server interprets as a table. You can return XML, but that has to be parsed, or you can return any text or binary, but that has to be parsed too.

3. None really.

My own experience is that it takes time to get comfortable with Service Broker, especially when dealing with communication between servers. There are a lot of new concepts to get familiar with; transport and dialog security, certificates, keys, endpoints, service bindings, routes, contracts etc. Given that your deadline is 20. September and you have never played with Service Broker before, I would personally not go down that path this time.


Thanks for all the info. I'll follow our own advice and put off Service Broker untill I have time to extend the deadline a little bit .


Is there a way on a firewall to accept traffic from only a single IP and redirect to another secured machine on a secured port far beyond the firewall even if it's not in DMZ? Im not network expert but that seems like a pretty safe way to go.

So that way only someone who's successfully hacked our server would be able to get in...
Post #973100
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse