Only allow the specified Machine to connect to the SQLServer Server

Background only computers with an IP address can connect to the SQLServer server. Other clients are not allowed to connect to the server. The solution can be directly restricted in the firewall and only allow 1433 communication with the specified IP address. Of course, from a more secure perspective, you should change port 1433 to another port. Other solution 1 (limited from

Background only computers with an IP address can connect to the SQL Server. Other clients are not allowed to connect. The solution can be directly restricted in the firewall, and only allow 1433 communication with the specified IP address. Of course, from a more secure perspective, you should change port 1433 to another port. Other solution 1 (limited from

Background

Only computers with an IP address can connect to the SQL Server. Other clients are not allowed to connect.

Solution

You can set limits on the firewall to allow only 1433 communication with the specified IP address. Of course, from a more secure perspective, you should change port 1433 to another port.

Other solutions1(Restricted from specifiedIPAccess client)

If SQL Server 2005 is used, you can also use the endpoint restriction method. This method requires a dedicated Nic, all clients that can connect to SQL Server are connected through this NIC (assuming the IP address of this Nic is 192.168.1.1 ):

1. In "SQL Server 2005 network configuration" of "SQL Server Configuration Manager", all protocols except TCP/IP are prohibited;

2. Disable the default TCP endpoint with the following T-SQL

Alter endpoint [TSQL Default TCP]

STATE = STOPPED

3. Use the following T-SQL to create new TCP endpoints and authorizations

USEmaster

GO

-- Create a New Endpoint

Create endpoint [TSQL User TCP]

STATE = STARTED

As tcp (

LISTENER_PORT = 1433,

LISTENER_IP = (192.168.1.1) -- network address of the listener

)

For tsql ()

GO

-- Grant all logon (or specified logon) connection permissions to use this endpoint.

Grant connect on endpoint: [TSQL User TCP]

TO [public]

After completing the preceding configuration, you can only access SQL Server from the client that is connected to the network adapter with the network address 192.168.1.1. In addition, if you only grant the connection permission of the specified logon terminal, only the specified logon can access the SQL Server instance.

Other solutions2(Limited to specifiedIPClient Access)

When SQL Server 2005 is upgraded to SP2 or a later version, you can use the new trigger to implement control.

After the following T-SQL is executed, client connection except IP address 192.168.1.1 fails.

USE master

GO

Create trigger tr_LoginCheck

ON ALL SERVER

FOR LOGON

AS

If eventdata (). value ('(/EVENT_INSTANCE/ClientHost) [1]', 'varchar (15) ') <> '2017. 168.1.1'

ROLLBACK TRAN

GO