| Author |
Topic |
|
usernametaken
Starting Member
6 Posts |
 Posted - 2009-11-17 : 12:52:42
|
| Need to allow a partner company to access our SQL server to run queries for their front end .net app and be able to link to our server. I currently have them set up to use our Cisco VPN to connect so it's secured. We want to get away from that so they can not see our network. Can you set up a SSL connection that will allow them to link to our server? I know you can create endpoints and publish SP's to them as a method. But we need a way to have a secured connection that allows them to link to our server. What are my options? Both sides running SQL Svr 2005. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 15:14:38
|
| 1st, VPN is probably the most secure way. They can only see machines on your network that you allow them to.A better way would be to use replication to push the data you want them to have access to to them.If they must access your SQL Server and you're not going to use VPN, then you should push the data to a seperate server that is exposed to the WAN. Absolutely positively change the port # that SQL is listening on. You can install certificates, but that's not wholly necessary. Set up a proxy account for them to use and be sure to enforce password policies so that multiple failed logins will lock out the account. This account should have no network permissions other than SQL Server. I would grant SQL permissions as needed and be as specific as possible. Don't grant them any administrative access, don't add it to dbo role. Grant access at the object or SP level if possible.Also, best practice would dictate placing production boxes in DMZ.We use VPN to connect our remote data centers, but also have one where we don't. We've taken the steps listed above except for certificates. |
 |
|
|
usernametaken
Starting Member
6 Posts |
Posted - 2009-11-17 : 16:00:19
|
| I agree on the vpn and only allowing them access to the one server. Director doesn't want them in at all really. I like the second sql box with the data replicated. I have not touched replication yet but the only concern I have with that is how it replicates. Does it do a full database or only write the latest transactions each time? My vote is for the vpn option but I have to explore every avenue to keep the boss man happy. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 16:05:25
|
| with transactional replication, you specify the tables (even just columns if you want within those tables) that will be replicated. changes will be pushed to 2nd server in near real time (about 2 to 3 seconds is average on a 1Gb network). |
|
|
|
usernametaken
Starting Member
6 Posts |
Posted - 2009-11-17 : 16:20:05
|
| thanks! my only concern with just letting them access through the firewall is how to secure the data in transit. Any ideas there? Is it really necessary to secure it during transit? Guess that really depends on what it contains huh. I'll let the Director and the other company decide that one I guess. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 16:49:06
|
| unlikely anyone breaks into the tunnel, but this is where certificates and encryption come in |
|
|
|
usernametaken
Starting Member
6 Posts |
Posted - 2009-11-17 : 18:10:46
|
| russell I agree it's not likely but my Director is uberparanoid, say he didn't want to set up a permanent tunnel for some strange reason or the partner company balked at the idea, is there a way like with Exchange to create a self signed certificate and use SSL for the other company to connect to our SQL server? like i said i have to cover all my bases on this one |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-17 : 20:14:20
|
| Have your NetOps Director see this: http://www.cisco.com/en/US/products/sw/secursw/ps2308/ |
|
|
|
usernametaken
Starting Member
6 Posts |
|
|
|
linked servers over a WAN