Allow access to Sql Server from generic account originating from specific machines

Question

In our production environment the Sql server has some generic accounts(sql server accounts) used by the applications to access sql server. Users themselves have windows login which are readonly or write depending on user. We want to add a restriction which would allow only those generic account(sql server accounts) connections which originate from production application servers. Users themselves can connect from non prod server so we cannot block the sql ports in prod for connections from non prod servers.

Do we have some industry wide solutions for this?

We can have some sort of filtering in firewall which would filter the connections. Database solution might be too slow if it queries an api for each connection.

Is there a cool way to prevent applications in uat environment with wrong config settings(prod settings) to connect to our production database

Answer 1

As has been suggested, you can use dynamic management views to identify login and IP addresses during the logon coupled with a logon trigger to restrict non-prod apps from accessing prod databases. Before I did this (again, as others have suggested), I would consider alternatives using:

• Firewall rules to prevent non-prod boxes from reaching prod boxes
• Ensuring the prod password does not match a non-prod account (especially good for keeping prod passwords out of things like source control or developers who may not necessarily need prod access)

With that said, here's a database-driven solution that let's you control the restrictions through a table that can live anywhere you want on the server.

CREATE TABLE SampleDB.dbo.LoginRestrictions (
    RestrictionId INT NOT NULL PRIMARY KEY,
    LoginName VARCHAR(500) NOT NULL,
    IpAddress VARCHAR(50) NOT NULL,
    Notes VARCHAR(500) NULL
)

INSERT SampleDB.dbo.LoginRestrictions VALUES
    ('app1', '10.1.1.125', 'Deny app1 from QA'),
    ('app1', '10.1.1.%', 'Deny app1 from DEV') -- Notice '%' so you can match general subnets
    -- ... Any other rules

Then, you can create a logon trigger that if a user login (can be a SQL account s in these cases, but you can also use domain logins to restrict any domain accounts if you wish). Also notice that the trigger uses LIKE so you can leverage wildcards in the IP addresses in case you can match by subnets.

CREATE TRIGGER tg_Logon_Blocker
ON ALL SERVER FOR LOGON AS
BEGIN
    SET NOCOUNT ON

    -- Rollback if restricted
    IF EXISTS (
        SELECT *
        FROM SampleDB.dbo.LoginRestrictions R
            INNER JOIN sys.server_principals P
                ON P.name = R.LoginName
            INNER JOIN sys.dm_exec_connections c
                ON c.session_id = @@SPID
                    AND c.client_net_address LIKE R.IpAddress
        WHERE p.name = ORIGINAL_LOGIN()
        )
        ROLLBACK

END
GO

Answer 2

you may apply one of the different solutions proposed in this so answer but imho you have to better define your requirements and the context first.

if you want to prevent these users access from 'other' hosts but allow other users to access freely from anywhere then you have to act at the database level because only the database knows who is trying to log on: a logon trigger is the choice.

if the database is devoted to supply information to these hosts only then you may act on the firewall (maybe a more performing solution, to be evaluated depending on load, number of connections, number of hosts, lots of other variables) and drop all connection started from unknown machines.

if your environment have no 'defined boundaries' (i mean that the db server is used by other hosts also, is not used by the application servers only) maybe the creation of a new, dedicated db server can be evaluated; this would allow to apply the firewall solution (the one I prefer).