SQLTeam.com | Weblogs | Forums

Allow remote access on one db, local only on another


sql server web edition 2014

I have a production and qa database on a server with an iis app running on the same box. Both db's can be accessed from my remote development machine but now I would like to allow remote access only to the qa db, and since iis is local to the production environment I would like only local access (windows integrated security?) to the production db.

I believe I already have a firewall rule in place that only allows my remote ip address to have remote access, which since in place has stopped the brute force attacks on the sa account (which I have disabled), but I'd like to add this additional layer of security, while still allowing remote access to my qa db.

Everything I've found on the subject seems to work on the entire sql server, not individual db's, is this possible to do?


When you talk about allowing remote access to a SQL server, are you referring to using SQL Server Configuration Manager to configure Protocols? For example enabling TCP/IP protocol is commonly done to allow access across the network. But that is on an instance level not a database level.
It sounds like if you were to install a 2nd instance of SQL on the same server (and name it something unique) you could then move either the prod or qual databases there to separate them.
Then you could control access more granularly, that is allowing remote access to one instance while not enabling remote access to the other.
Hope that helps.

I don't believe you can restrict it at the db level, because I don't think the db "knows" whether the connection to it is local or remote.

You can specify some limitations on linked servers, if you're using linked servers to connect to the other server. But that's not valid for security for malicious intent, of course.

You could use a login trigger and check some special value or settings that you could use within your valid apps but that others wouldn't know about. Then log the attempt and exit if the expected security value(s) were not present.

To clarify, a single instance of sql server with a production and a testing copy of the database. testing copy needs to be accessible across the internet, the production version only from the local machine, which I do not believe requires even a local network, just a windows account with sufficient rights.

If your apps is hosted on iis and if you disabled TCP/IP=disabled then also the webapp will work, when you disabled TCP/IP then you cannot access sql remotely.