How to restrict in SSMS to allow remote connections only specific IP / Host

ssms

I cannot change sql server user. How can I restrict in SSMS to allow remote connections only specific IP / HostAddress.

Best Answer

SSMS is only a tool that connects to a SQL Server instance. SQL Server listens on a specific IP address for connections. It doesn't really care which tool is connecting.

SQL Server is not like PostgreSQL that has a pg_hba.conf where you can restrict connections, nor is it MySQL where you assign permissions to a user@source combination when creating the user login.

Answer

It's not currently possible by using simple means (PostgreSQL, MySQL), but if you are willing to start creating Logon Triggers (Microsoft Docs), then you might be able to build a restriction that meets your requirements.

The SQL Syntax for triggers can be found in the article CREATE TRIGGER (Transact-SQL) (Microsoft Docs). The article lists an example for a Logon Trigger.

Because your requirements are pretty dire, I am unable to provide a solid solution. Please edit your question and provide as much details as possible.

USE master  
GO  
CREATE LOGIN login_test WITH PASSWORD = 'password' MUST_CHANGE, CHECK_EXPIRATION = ON  
GO  
GRANT VIEW SERVER STATE TO login_test
GO  
CREATE TRIGGER connection_limit_trigger  
ON ALL SERVER WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK  
END

Alternative

You could equally create a blocking firewall rule or hack the routing table on the server / network.

Related Question