1

Is it possible, through SQL Management Studio, to set a particular database to only accept remote commands from a particular white list of IP addresses? Note that there might be other database on the same server instance, and I would like this limit to only apply for one particular database.

Thanks!

Osprey
  • 1,523
  • 8
  • 27
  • 44
  • There is some excellent info on this post... http://stackoverflow.com/questions/7127602/restrict-sql-server-connection-to-specific-ip-address – Steph Locke Jun 10 '13 at 11:57
  • From what I could gather, what they are suggesting would block access to all databases in the instance. I only want to make this restriction to one of several databases. – Osprey Jun 10 '13 at 12:26
  • Then the trigger based solutions might be useful to you, here's a more in depth article... http://connectsql.blogspot.co.uk/2012/07/sql-server-restrict-login-from-valid.html – Steph Locke Jun 10 '13 at 12:33
  • Thanks Steph. I'm not an expert in triggers. How would I modify that code so that it only applies for one database? – Osprey Jun 10 '13 at 12:55
  • Looking about I'm not sure you can, however, an alternative would be to base it on the application name in combination with IP... so including a line like `APP_NAME() LIKE 'Payment App%'` and adding the name to the hardcoded connection string – Steph Locke Jun 10 '13 at 13:26

1 Answers1

0

it's going to depend on what you mean exactly by remote commands, authentication method and questions such as : is your range of IPs going to be managed and static?

it would seem simpler to set up a user with the appropriate permissions and only use that to authenticate from the desired machines, this way if the IP addresses of the client machines change the solution still works?

then you will just need a second user with whatever lower permissions are required on other machines

for example if you want to have read access for most people but some machines need to play with ddl:

log in from 127.0.0.2 with a user named REMOTE_POWERS which has the ddl admin role in that database

all other machines log in to the instance as the user RO_PEON with a role such as data reader

rs'
  • 301
  • 1
  • 4
  • I have an application with a hard coded SQL connection string. This application connects to a remote database at regular intervals and checks for new orders. If it finds them, it processes them and marks them as acknowledged. The problem is that if the exe is accidentally run from another location, for testing, for example, it could process the orders and then the proper machine would not see them. So I only want the database to be updated when the commands come from a specific IP. – Osprey Jun 10 '13 at 12:23
  • that seems risky to me it would be much better practice to have separate testing and production environments which are isolated by firewalls and a change control process! also if you are limiting the commands from executing from certain machines it seems you aren't actually running an effective test? if you don't have the option to run a second set of applications perhaps replicate your main database into a shadow instance and test against that - then you only need to make sure you have enough disk space to store the extra data and log files – rs' Jun 10 '13 at 12:31