How can I restrict which computers can connect to an instance of SQL Server?

I'm not sure if this is a more a database or sysadmin question, so let me know if it doesn't fit.

Because of some licensing agreements, I've been asked to find a way to restrict which computers on our domain can connect to an instance of SQL Server.

My original idea was to try and create a login trigger to check against a list of computers to see if they matched but I've managed to lock myself out of the server 3 times already. Further to this, apparently using HOST_NAME() to identify the connecting client is not not secure.

How else could I do this from within the SQL Server environment or do I need to look at setting up Firewall rules elsewhere?

Just to be clear. We want it so any user can connect from a specific collection of computers.

Answers 1

  • If you want to totally restrict certain servers, then the Windows Firewall should be configured to the restriction.

    See: Configure the Windows Firewall to Allow SQL Server Access

    If you need to prevent a specific login from connecting, then of course you would need to manage that login's rights to the server.

    Depending on the granularity of what you need to do, you might need to use both approaches.


Related Questions