none
How to add IIS_IUSRS into MS SQL database users RRS feed

  • Question

  • Hello

    Windows Server 2019, MS SQL server 2017.

    I need to allow one database for all IIS App Pools

    This should be easy by adding IIS_IUSRS group into database users, but I cannot manage it.

    USE [foo]
    GO
    CREATE USER [IIS_IUSRS] FOR LOGIN [IIS_IUSRS]
    GO

    >>> Error

    Msg 15007, Level 16, State 1, Line 3
    'IIS_IUSRS' is not a valid login or you do not have permission.

    Thanks for any help
    Miroslav

    Saturday, August 24, 2019 8:44 AM

Answers

  • the Server is not in domain. It is standalone web server.

    In that case you should use the machine name.

    Obviously, it requires that there actually is a Windows group or user with that name on the machine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Using the machine name instead of the domain like bellow:

    CREATE LOGIN [YourMachineName\IIS_IUSRS] FROM WINDOWS;
    GO

    will not work probably and you will get the error:

    Windows NT user or group 'YourMachineName\IIS_IUSRS' not found. Check the name again.

    You can use the following script instead (NOT RECOMMENDED!)

    CREATE LOGIN [BUILTIN\IIS_IUSRS] FROM WINDOWS 
    	WITH DEFAULT_DATABASE=[master]
    GO

    Using the "BUILTIN" you can use (I think) all types of built-in groups. This for example also works on the group "Administrators"


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Sunday, August 25, 2019 8:04 AM
    Moderator

All replies

  • I assume that IIS_IURSS is a Windows thing. Thus, you need include the domain (or the machine, if this is a machine account):

    CREATE USER [DOMAIN\IIS_IUSRS]

    You may also have to create a login:

    CREATE LOGIN [DOMAIN\IIS_IUSRS] FROM WINDOWS


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 24, 2019 10:17 AM
    Moderator
  • Hello

    the Server is not in domain. It is standalone web server.

    I tried NS\IIS_IUSRS (NS is name of Server) without success.

    Thank you
    Miroslav


    Saturday, August 24, 2019 10:19 AM
  • the Server is not in domain. It is standalone web server.

    In that case you should use the machine name.

    Obviously, it requires that there actually is a Windows group or user with that name on the machine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 24, 2019 6:24 PM
    Moderator
  • NS is thne name of machine.

    IIS_IUSRS is standard Windows group for IIS App Pools.

    Can anybody with Windows 2019 and MS SQL 2017 try this?

    Thanks

    Miroslav

    Sunday, August 25, 2019 7:01 AM
  • Good day ,

    IIS_IUSRS is not a windows USER but a windows GROUP, which has permissions related to what the IIS needs in order to execute the IIS web applications. All the (dynamic) app pool identity users are members of the Group IIS_IUSRS. Depending on your operating system you have a default application pool (probably named DefaultAppPool).

    First, I need to recommend NOT to use this solution!

    You can create new LOGIN based on the DefaultAppPool by using the following query:

    CREATE LOGIN [IIS APPPOOL\DefaultAppPool] FROM WINDOWS;
    GO

    Next you create USER in any database which you want the IIS ro connect directly, by using the following query:

    USE MyDataBase
    GO
    
    CREATE USER DefaultAppPool 
    	FOR LOGIN [IIS APPPOOL\DefaultAppPool];
    GO

    This should allow the IIS connect the database "MyDataBase"

    THIS IS HIGHLY NOT RECOMMENDED IN MY OPINION!

    If you already decided to use windows authentication the I would probably prefer to (1) create new windows USER -> (2) add new APPLICATION POOL (for each web application I like to use different USER) which is based on that windows USER -> (3) create new LOGIN in the SQL based on that new windows USER -> (4) create new USER in each database which you need.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, August 25, 2019 7:46 AM
    Moderator
  • Hello, thank you for your explanation.

    I know everythink you wrote.

    IIS_ISUSR is group of all APP Pools. That is exactly I want to use.
    I need allow access to all my web sites (each web site has unique APP Pool identity) for one database for statistics.

    Adding IIS APPPOOL\DefaultAppPool allows access only for default web site. It doesn't allow other sites.

    Why MS SQL doesn't allow me add this group?

    Thank you for your help.
    Miroslav

    Sunday, August 25, 2019 7:55 AM
  • the Server is not in domain. It is standalone web server.

    In that case you should use the machine name.

    Obviously, it requires that there actually is a Windows group or user with that name on the machine.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Using the machine name instead of the domain like bellow:

    CREATE LOGIN [YourMachineName\IIS_IUSRS] FROM WINDOWS;
    GO

    will not work probably and you will get the error:

    Windows NT user or group 'YourMachineName\IIS_IUSRS' not found. Check the name again.

    You can use the following script instead (NOT RECOMMENDED!)

    CREATE LOGIN [BUILTIN\IIS_IUSRS] FROM WINDOWS 
    	WITH DEFAULT_DATABASE=[master]
    GO

    Using the "BUILTIN" you can use (I think) all types of built-in groups. This for example also works on the group "Administrators"


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Sunday, August 25, 2019 8:04 AM
    Moderator
  • Login creating ends with error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Create failed for Login 'NS\IIS_IUSRS'.  (Microsoft.SqlServer.Smo)
    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18142.0+((SSMS_Rel).190722-0816)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Windows NT user or group 'NS\IIS_IUSRS' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    Sunday, August 25, 2019 8:08 AM
  • Hi,

    You can try using the script I mentioned bellow as response to Erland. I did NOT used it ever but in theory it might give you what you want (probably not what you need or what recommended but what you asked for)

    I mean that you can try use: [BUILTIN\IIS_IUSRS]


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, August 25, 2019 8:10 AM
    Moderator
  • Using BUILTIN\IIS_IUSRS seems to be OK.

    I will try to use it in Database and let you know.

    Miroslav

    Sunday, August 25, 2019 8:10 AM
  • Login creating ends with error:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Create failed for Login 'NS\IIS_IUSRS'.  (Microsoft.SqlServer.Smo)
    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18142.0+((SSMS_Rel).190722-0816)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Windows NT user or group 'NS\IIS_IUSRS' not found. Check the name again. (Microsoft SQL Server, Error: 15401)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476
    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    I told you that this will not work and you can try [BUILTIN\IIS_IUSRS] instead :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, August 25, 2019 8:12 AM
    Moderator
  • Great!

    Using BUILTIN\IIS_IUSRS instead of MachineName\IIS_IUSRS is the way.

    Thank you very much!

    Miroslav

    Sunday, August 25, 2019 8:13 AM
  • Great!

    Using BUILTIN\IIS_IUSRS instead of MachineName\IIS_IUSRS is the way.

    Thank you very much!

    Miroslav

    You are most welcome 😃

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, August 25, 2019 8:26 AM
    Moderator