SQL Server – How to Prevent Public Connections

sql server

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.

Best Answer

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';
Related Question