4

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.

3
  • Every connection must have a login, usually a domain login. Try to look into domain groups or local groups that have a login in that instance to find nested groups that maybe can contain authenticated users group or domain users group or something like that – MBuschi Jan 26 at 8:05
  • 2
    Run EXEC xp_logininfo 'YourDomain\YourUser', 'all'; to identify the permission path(s) that provide access. – Dan Guzman Jan 26 at 11:34
  • @DanGuzman -- please add your comment as an answer, -- as xp_logininfo was able to prove how the access was being given. Thanks! – Mike Jan 28 at 1:35
1

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';
1
  • Thanks Dan. I am marking this as the accepted answer because it was the most useful in identifying which login was a group that contained \Domain Users. – Mike Jan 28 at 9:17
4

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

2
  • 3
    @Mike Agreed with Tibor here, verify the Server Permission CONNECT is not granted to public. Also, keep in mind Domain Users is an out of box AD Group that applies to all users too. Verify that Group is not setup under Logins with incorrect permissions. – J.D. Jan 26 at 12:37
  • Ok -- it looks like J.D. nailed it.... \Domain Users was a member of a group that had a login. We missed it on our first review on the logins -- thanks for the prompt to go back and double check. – Mike Jan 27 at 4:51
3

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.

1

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.

2
  • 1
    The mentioned above script will never show CONNECT SQL permission as this is SERVER level permission, not database level. In the script mentioned the sys.database_permissions is checked for permissions while the permission in question can be fund only in sys.server_permissions – sepupic Jan 27 at 12:04
  • Thank you @sepupic! Edited for correction. Leaving the script as it might be useful anyway. – sudocoffee Jan 27 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.