Linked servers over the internet

  • 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?

  • 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

  • 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.

  • 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

  • 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.

  • 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.

  • 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 :w00t:.

    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.

  • 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 :w00t:.

    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?

  • 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 :w00t:.

    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.

  • 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 :w00t:.

    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 :hehe:.

    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...

  • Ninja's_RGR'us (8/22/2010)


    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)?

    I don't know what kind of servers you are using but I find a 1 vpn connection per machine limit astounding (and I wouldn't give such a server OS house-room!). Where I worked from 2002 to 2009 we used Windows 2000 Server at first and later Windows 2003 Server and had many simultaneous VPN connections from our main in-house server in London to customer servers all around the world and to our other offices. Not 3500 connections at one time (nowhere near, in fact) but a good deal more than 4 or 5 on Win 2000 Server and when we went to Win 2003 we grew to more connections than we had had on Win 2000.

    Connection setup time depends very much on two things: firewall performance (at both ends) and network performance (both latency and bandwidth are relevant); if customers have good internet connection, decent and sensibly configured firewalls, and competent network management you can get 1 sec setup time provided the loop delay is not too great (you're obviously not going to get 1 second connection setup if the loop delay is 1.5 seconds).

    Even if your connection time is good you can't guarantee that it won't suddenly go bad: our connection setup times for customers in the Middle East increased by a large factor when some sailor accidentally cut through an underwater cable there. DO you need to maintain the 1 sec connection setup time limit when something like that happens?

    Tom

  • Ninja's_RGR'us (8/22/2010)


    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...

    Depends on the firewall - it's certainly possible to allow some particular ports only to certain remote IPs and allow those remote IPs only to see those ports for certain local (ie on the inside side of the firewall) IPs on any firewall I've ever used for business (as opposed to home use). That still allows someone to get in if they can achieve the required IP spoofing, of course, which is why you should also require the thing trying to connect to know a key (and then they have to either both discover the key and spoof the IP or break into your server) - but now you've effectively got an unencrypted (except that connection set-up dialogue is encrypted) VPN; every firewall I've used for business has provide that capability.

    Tom

  • Tom.Thomson (8/22/2010)


    Ninja's_RGR'us (8/22/2010)


    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)?

    I don't know what kind of servers you are using but I find a 1 vpn connection per machine limit astounding (and I wouldn't give such a server OS house-room!). Where I worked from 2002 to 2009 we used Windows 2000 Server at first and later Windows 2003 Server and had many simultaneous VPN connections from our main in-house server in London to customer servers all around the world and to our other offices. Not 3500 connections at one time (nowhere near, in fact) but a good deal more than 4 or 5 on Win 2000 Server and when we went to Win 2003 we grew to more connections than we had had on Win 2000.

    Connection setup time depends very much on two things: firewall performance (at both ends) and network performance (both latency and bandwidth are relevant); if customers have good internet connection, decent and sensibly configured firewalls, and competent network management you can get 1 sec setup time provided the loop delay is not too great (you're obviously not going to get 1 second connection setup if the loop delay is 1.5 seconds).

    Even if your connection time is good you can't guarantee that it won't suddenly go bad: our connection setup times for customers in the Middle East increased by a large factor when some sailor accidentally cut through an underwater cable there. DO you need to maintain the 1 sec connection setup time limit when something like that happens?

    Thanks for the correction on VPN, my only experience was from desktop to server with Cisqo where everthing gets cutoff besides that connection.

    The site I'm working on basically takes in a few parameters, wades through 20 000 items and suggests all available matches (like all possible cpu cores to fit the sockets of mother board X) and shows about 25 columns of relevant data to make a decision.

    The site is already slow as hell (to my taste) and adding another 2-5 seconds to get that single extra column of data is just out of the question in my mind. So I want to have instant access to the data like if it were in the sams db in the same server. Now I know I have no control over the internet and the clients' network but I'll do everything I can to limit the steps and required time it takes for the connection.

    We have no hard data yet as this is the first go-live for this project, but on a similar seach form we do get over 5000 searches per hour in peak rush. Certainly no google volumes, but nope a mom and pop shop either. And since this is a new flagship system, it is being used to bring in larger whale customers so I imagine those number will greatly increase in the near futur.

    Now I can't imagine myself being happy having to wait 5-8 seconds to load a page everytime I do a search in rush hours (where you have 5 other customers waiting in line to be served).

    Hence my requriement for as 0 ms connection time as possible.

  • Tom.Thomson (8/22/2010)


    Ninja's_RGR'us (8/22/2010)


    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...

    Depends on the firewall - it's certainly possible to allow some particular ports only to certain remote IPs and allow those remote IPs only to see those ports for certain local (ie on the inside side of the firewall) IPs on any firewall I've ever used for business (as opposed to home use). That still allows someone to get in if they can achieve the required IP spoofing, of course, which is why you should also require the thing trying to connect to know a key (and then they have to either both discover the key and spoof the IP or break into your server) - but now you've effectively got an unencrypted (except that connection set-up dialogue is encrypted) VPN; every firewall I've used for business has provide that capability.

    Thanks again Tom.

    Since you seem like the local expert on the subject I'll ask you this :

    If a nut job like me were to send you a request to expose your precious sql server... and Main ERP DB to the internet, what would be your preffered setup to ensure maximum security and super fast connection (assuming you can't kill me and make this go away) :-D?

  • Ninja's_RGR'us (8/22/2010)


    If a nut job like me were to send you a request to expose your precious sql server... and Main ERP DB to the internet, what would be your preffered setup to ensure maximum security and super fast connection (assuming you can't kill me and make this go away) :-D?

    Security in a number of ways: first I'd insist that you use a particular username with capabilities that I ca see are the minimum for what you are trying to do (I think that's read only access to a view which contains only the columns you need). Second I would put a firewall between me and the internet that allowed your machine (identified by a set of IP addresses) to connect only to a specific IP address behind the firewall, and only on the appropriate port (whatever port my DB provides for remote access), using a key-protected VPN. Third I would insist that you undertake not to disclose the username, the password, or the VPN key (all specified by me) to any third party (like another one of your customers).

    For performance, I'd want plenty of bandwidth between firewalls and servers and plenty of bandwith from firewall to internet, and good high performance firewalls.

    The trouble is that I'm not sure how scalable this is. I've done it with a few dozen remote machines, not more. You talk about 3500 customers who should (if they care about security) be people like me, so you would end up not only configuring 3500 sets of access data in the firewall at your end (for a PIX, if it could handle that much, that would be something around 21000 or 28000 - can't remember the number needed - lines of config data) but also 3500 remote security mappings in your server each mapping your local user to a different username and password and I'm not sure what the performance of SQLServer would be in handling all that security (trusted connection) data (or how you would do it all in about 4 weeks, if you don't change the deadline); I don't know what sort of firewall can cope with that many VPNs in its configuration, but of course the load can be shared between multiple firewalls by doing some subnetting of the external addresses, so in principle there's no problem there, but (since all the VPN target addresses and keys are different) there may be a problem entering all that data in your timescale. One nasty little issue that may or may not crop up is address clashes - will there be address space clashes between your network and some of the customer networks (this is quite probable if all are using local address space behind firewalls and the machines involved don't have global addresses); if so you need to give your server one or more extra IP addresses and use the subnetting (maybe with multiple firewalls) at your end to eliminate the address clashes.

    And of course, sitting where you are, I'd make sure that the central firewall configuration didn't allow inward connections from the remote sites (unless you need that too for something else).

    Tom

Viewing 15 posts - 1 through 15 (of 41 total)

You must be logged in to reply to this topic. Login to reply