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).

Answer 3

You could potentially create a Logon Trigger to check the client_net_address field of sys.dm_exec_connections, but that would tightly couple the security to the network setup and that can easily be forgotten about. And if the IT group makes a change and doesn't know about this, the Logon Trigger might either let too much in or too little ;-).

A firewall might be slightly better in that at least all control of the network setup and access is maintained within the IT group. But this is most likely overly complicated, especially if there are other accounts that are valid to reach from QA and/or UAT to Production.

The setup that I have seen work well in this situation is to use environment-specific service accounts. So you would have DEV-App, QA-App, UAT-App, and Prod-App. This would allow you to control other aspects of network access (such as file shares, etc) more granularly between environments. And you should probably be doing this anyway because you would want to know if there was a misconfiguration and the Prod-App account was trying to connect to a DEV SQL Server or file share.

Answer 4

How about this... Network Service Account

Answer 5

Sounds like Application Roles could be a good solution for this.

Basically you would set up some application roles with appropriate permissions for the apps in question, and allow appropriate users to make the initial DB connection, with no other privileges. I recommend Windows authentication via AD group for this.

Your apps would then call sp_setapprole immediately after connection, at which point the connection assumes the permission of the application role.

The big advantage of this is that it remains within the standard SQL Server security model.

Of course you would still need to take some care that your app configs are correct.