Sql-server – how to Allow remote SQL connections from specific IP

sql serverssms

i use ms sql 2005 server . guide me: how to Allow remote SQL connections from specific IP.

Thank all

Best Answer

Follow these steps to configure SQL Server 2005 to allow remote connections:

Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply. (Note : Click OK when you receive the following message: Changes to Connection Settings will not take effect until you restart the Database Engine service.)
On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Then to allow only specific IPs to access your MS SQL server, create a firewall rule that will allow this specific IP through TCP 1433 and TCP 1434, and block those ports from any other IPs.

Related Solutions

Sql-server – Limit Concurrent Database Connections by SQL Server User Account

You could try Login Triggers: http://technet.microsoft.com/en-us/library/bb326598.aspx

TechNet suggests that:

You can use logon triggers to audit and control server sessions, such as by tracking login activity, restricting logins to SQL Server, or limiting the number of sessions for a specific login.

Their first code example limits a user to 3 connections (Quoting TechNet):

USE master;
GO

CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' 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;

Sql-server – Remote MS SQL 2008 connections

A daft question but is the SQL Server actually listening on 1433?

If it's a named instance of SQL Express for example it may be listening on a different port entirely which you either need to specify in the connection string, configure in the client config or have the SQL Browser service running and available to redirect connections.

The SQL Browser service listens on port 1434.

Best Answer

Related Solutions

Sql-server – Limit Concurrent Database Connections by SQL Server User Account

Sql-server – Remote MS SQL 2008 connections

Related Topic