SQL Server - How to prevent public connections?

Question

Today we found that two of our SQL Servers (both 2017 CU21) are accepting connections from any random domain account, even those that most definitely do not have a direct login or access via AD group membership.

On each server there is a user database that allows these random domain accounts to connect.

Based on the configuration we see, we would expect any random domain account (i.e. any that does not have a login on the instance) to get a login failure, and aside from that to also be blocked from connecting to the user database.

For these databases, the guest account is disabled, but for good measure we have executed:

REVOKE CONNECT FROM guest

This made no difference.

So two questions:

1. How can we turn off / prevent these public connections from succeeding?
2. What TSQL statement could show us the problematic configuration?

Things we have looked at so far:

• The 'public' server role seems to have the default configuration, same as all our other servers where this is not an issue
• The guest user definitely shows as disabled on all databases & servers
• The 'public' database role has just the default permissions we would expect to see
• It is only 1 user database (plus master & msdb & tempdb) on each server that is accessible to these random accounts -- all the other databases behave as normal, i.e. the account cannot connect to them.
• The database Containment Type setting is 'None' is all cases.
• There is no login present on either server that covers all domain users

Some context: the two servers are related -- they are the DEV & PROD servers for the same application. It is very likely this configuration was done deliberately for some historical reason -- but no-one working here now knows why, so we would like to disable this public access.

Answer 1

As per your description, it seems to me that CONNECT SQL (or perhaps CONNECT) permissions has been assigned to the public server role.

Answer 2

What TSQL statement could show us the problematic configuration?

Any login connects to server thanks to CONNECT SQL granted to it directly or to it role/windows group at the server level. You should examine these roles/groups, and to sO them you should impersonate your suspected login by running this code:

execute as login = 'your_suspected_login';
select *
from sys.login_token; -- shows all groups/server roles
select *
from sys.login_token t
     join sys.server_permissions p
        on t.principal_id = p.grantee_principal_id 
           and p.permission_name = 'CONNECT SQL' -- shows what grants him CONNECT SQL
revert;

The first part of the code shows you all the roles/groups that login is member of, the second shows you which role/group has CONNECT SQL. So until your login is member of this last group/role he will connect to your server.

Answer 3

Windows accounts inherit permissions via Windows group membership. Even though a login does not exist for the user account, the account can still connect if a member of group allowed to do so.

Execute xp_logininfo, specifying the problem Windows account along with `all' to list all the permission paths (i.e. account and/or group(s)) that provide access.

EXEC xp_logininfo 'YourDomain\YourUser', 'all';

Answer 4

There is a T-SQL script on another post here that will pull back all of your database_role permissions.

It's also worth noting that CONNECT SQL and VIEW DATABASE are granted to the public server role by default.