none
Odd behavior with NT Authority\IUSR RRS feed

  • Question

  • CREATE LOGIN ['NT AUTHORITY\IUSR'] FROM WINDOWS

    SELECT * FROM sys.server_principals WHERE name = 'NT AUTHORITY\IUSR'

    Why is this type G and not type U?

    Wednesday, January 13, 2010 11:00 PM

Answers

  •   It seems like the root cause of the problem is the way the Windows API we use characterizes these well known SIDs, they are reported as secondary identities, not primary identities.

      Since there was a similar bug reported for the local system, network service & local service SIDs, we explicitly hardcoded these accounts to be displayed as users instead of groups (hence the discrepancy observed by Lekss).

      Besides the differences between Windows users and groups (i.e. ability to set default schema, etc.) in SQL Server, the affected account should still work as expected in terms of authentication and authorization; if you detect any abnormal behavior, please feel free to report it as a bug using SQL Connect. Also feel free to open a new case for explicitly marking IUSR as a Windows user instead of a Windows group in metadata.

      Thanks a lot,
     -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 18, 2010 9:51 PM
    Moderator

All replies

  • Is this account nt authority\iusr the inbuilt iis account ??
    Thanks, Leks
    Wednesday, January 13, 2010 11:24 PM
  • Hello

    Principal type:

    S = SQL login

    U = Windows login

    G = Windows group

    R = Server role

    C = Login mapped to a certificate

    K = Login mapped to an asymmetric key

     

    For you its showing right only.

    Wats up the confusion in it

    Regards

    RM Thirunavukkarasu MCP, MCITP

    Thursday, January 14, 2010 6:42 AM
  • In IIS7 (Windows7/Server2008) HOSTNAME\IUSR_HOSTNAME is replaced with NT AUTHORITY\IUSR
    Everything I can find indicated that, though it's a hidden account, it's an account , not a group.

    So why is this user account showing up as a group?
    Thursday, January 14, 2010 6:07 PM
  • Hi Brian,

    I too had the same behaviour when i tried to add the IIS account to the SQL server as a login.
    Running the below query

    select name,type from sys.server_principals where name like '%iusr%'

    i too got that ntauthority\iusr is a group.

    After i tried adding all nt authority accounts like

    nt authority\network
    nt authority\service
    nt authority\iusr

    all these 3 are listed as groups and

    nt authority\network service
    nt authority\system are listed as users in SQL server.

    But i am not able find any documentations for all these accounts.


    Thanks, Leks
    Friday, January 15, 2010 1:06 AM
  • And yet;
    NT AUTHORITY\System does not show up as a group.
    So it's not a consistent behaviour with all NT Authority...
    Monday, January 18, 2010 7:13 PM
  •   It seems like the root cause of the problem is the way the Windows API we use characterizes these well known SIDs, they are reported as secondary identities, not primary identities.

      Since there was a similar bug reported for the local system, network service & local service SIDs, we explicitly hardcoded these accounts to be displayed as users instead of groups (hence the discrepancy observed by Lekss).

      Besides the differences between Windows users and groups (i.e. ability to set default schema, etc.) in SQL Server, the affected account should still work as expected in terms of authentication and authorization; if you detect any abnormal behavior, please feel free to report it as a bug using SQL Connect. Also feel free to open a new case for explicitly marking IUSR as a Windows user instead of a Windows group in metadata.

      Thanks a lot,
     -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, January 18, 2010 9:51 PM
    Moderator
  • Exactly what I was trying to find out.
    Thanks!
    Tuesday, January 19, 2010 5:05 PM
  • This easy answer:

    http://codingresource.blogspot.com/2010/01/login-failed-for-user-nt-authorityiusr.html

    Good day

    Bogotá - Colombia


    Oscar Ortiz Pinzón Microsoft Student Partners - Colombia Asp net|C#|VB .net|Sharepoint Desarrollador Intergrupo S.A http://oscardo.net Bogotá - Colombia
    Sunday, January 9, 2011 8:42 PM