How do I limit access to my SQL Server database to specific IP?

Question

Can I restrict access to a specific database on my SQL Server 2008 database based on client IP address? If so, how do I do it? There are multiple databases on this server so we can't block access to the SERVER based on the IP, just a specific DATABASE.

SQL Server doesn't have any means to do this --> you'll have to use your network infrastructure (firewalls, routers etc.) to achieve this. — marc_s, Aug 10 '12 at 14:12
see http://stackoverflow.com/questions/7127602/restrict-sql-server-connection-to-specific-ip-address — stb, Aug 10 '12 at 14:12

score 2 · Answer 1 · answered Aug 10 '12 at 14:35

You absolutely cannot do this at database granularity. You are talking about detecting things like queries using three part names ([banned_db].[dbo].[table]) and synonyms referencing the protected DB while using an unprotected DB. There just isn't any framework in place to do such. You must rely on access security (GRANT/DENY/REVOKE). At best, do what gbn suggests and separate the protected DB into it's own instance and then use Firewall rules to protect its ports.

score 1 · Answer 2 · answered Aug 10 '12 at 14:28

1

Personally, I'd use security to do this. Limit access via permissions not via IP.

If you really do need IP address security (eg for banking Secrecy Jurisdictions like Switzerland or Singapore), then use separate servers

answered Aug 10 '12 at 14:28

gbn

422,506
82
585
676

score 1 · Answer 3 · answered Nov 15 '12 at 21:33

There is currently no way to limit access to a specific database based on the network segment you are coming from. I can see the need for this to limit access to a production DB from a stage server. I guess this is why there is the need to build different SQL servers for each environment.

I believe you can limit SQL access by application http://msdn.microsoft.com/en-us/library/bb669062.aspx

How do I limit access to my SQL Server database to specific IP?

3 Answers3