2

The versions affected are SQL Server 2005, 2008, 2008 R2.

If we grant CONNECT SQL to public server role, any AD account can log in to server. So we'll have all the AD members on the server without any corresponding login.

This is not documented anywhere, and it's not reproducible on SQL Server 2000 or starting with SQL Server 2012, i.e. if we do the same on 2012, any AD account trying to log in will receive the 18456 error

Login failed for user ...

with the reason

Could not find a login matching the name provided

That's fine and that is expected, but for what reason 2005 - 2008 R2 behave differently?

UPDATE I want also to ask the community about the behavior that is still presented at least on SQL Server 2012: Let's have an AD account that is not mapped to the server, so it cannot access the server because there is no login for it, niether for any Windows Group he is member of. Now I execute the code:

    exceute as login = 'myDom\myAcc';

    select *
    from sys.fn_my_permissions(null, 'server')

The first interesting thing is that I CAN impersonate the login that does not exists. The second thing is this non existing login HAS a server-level permission VIEW ANY DATABASE.

When I was on SQL Server 2008 R2 I thought it's normal, only undocumented, because ALL THE AD PRINCIPALS ARE MEMBERS OF PUBLIC (so it was sufficient to grant CONNECT SQL to public and all the AD members could access the server)

On SQL Server 2012 granting CONNECT SQL to public does not allow to the whole AD to log in, so I thought it was a bug fixed.

But how then can I impersonate smth that does not exist and from what this non existing login inherits a permission?

Or maybe I can ask this way:

It's clear that non existing login inherits his VIEW ANY DATABASE permission from public server role, but why then he does not inherit CONNECT SQL if I grant it to public? Is it consistent behavior? Was it more consistent prior to 2012? Or maybe it was just a bug introduced with SQL Server 2005 and fixed only in the half?

Improve this question
4
  • Code behind an undocumented behavior changed.
    – Remus Rusanu
    Commented Jul 11, 2017 at 9:13
  • @Remus Rusanu So I can consider it as a bug? That appeared starting with 2005 and then was fixed because it's a security hole?
    – sepupic
    Commented Jul 11, 2017 at 9:19
  • No, it is not a bug. If the feature is not documented, there is no guarantee that it will work - or even work in consistent a manner.
    – vonPryz
    Commented Jul 11, 2017 at 9:24
  • 1
    But it can be used to give a "hidden" access to the server and even to databases that is not tracked because according to documentation no login = no access, so it's a security hole
    – sepupic
    Commented Jul 11, 2017 at 9:35

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.