locked
Sql Server Create Role for IIS App Pool Error --- RRS feed

  • Question

  • User-215451226 posted

    I have a problem [which includes sql server, IIS and app pools of .NET] which I will describe here in full details. I need some good assistance/help on solving it.

    Basically I have this .NET MVC project and there's also a database in sql server 2008. I had configured my application on my IIS V 7.5 and initially chosen a default .NET 4.0 App Pool to configure my site. Finally I went ahead to create a separate appPool for it called 'RateITPool' and associated it with my website bindings. Went to the sql server database engine as well and ran the script for rule like --
    CREATE USER [RateITPool] FOR LOGIN [IIS APPPOOL\RateITPool]
    as you'd expect.

    Now I went ahead and did some db related coding [in a web API]. But when I tried to test it, it's giving me errors related to SqlConnection, SqlTransaction, SqlConnectionFactory, LoginFailed etc. Which is slightly frustrating for me.
    Take a look at this you'll understand ---  https://ibin.co/2qC71VJSwhi9.png


    SO what do I need to do? It's not as if I provided wrong connection string, credentials or the db name. None of that. Is it about permissions? Roles or anything of that sort? Is there something I need to enable/allow in this scenario? How do I get it sorted so that the error is removed. Can you point me in the correct direction? It would be such a immense help as I am stuck at this point.


    Kindly help me, it's crucial.
    Thanks!!

    Tuesday, August 2, 2016 3:50 PM

Answers

All replies

  • User-2057865890 posted

    Hi PGChoudhury,

    You could expand Logins in SSMS and select this newly created login. Right Click > Properties. Check the appropriate database, and the appropriate roles. Click on User mapping and map this login to the database you want to provide access to.

    Best Regards,

    Chris

    Wednesday, August 3, 2016 8:59 AM
  • User-215451226 posted

    Hi Chris Zhao, @ChrisZhao

    There are a few points I'd like to get cleared before I go ahead with my db.
    Suppose I created my login the way I highlighted in the original post, no probs.
    Now, after expanding my db and ->Security->Users-> MyCreatedLogin; then I rt. click and bring up properties. In the panes you showed above, I see '111' for User. Surely it's the database engine? In my case it's PGC-PC\SQLEXPRESS. What about the default schema?

    In the 'database role membership for' section I hope public and guest are the same? Some confusion there also. In the User Type what should I choose, Windows User or something else? What should I choose in the Owned Schema and Membership?
    Please tell me these points before I go ahead.

    Thanks!!

    Thursday, August 4, 2016 3:59 PM
  • User-2057865890 posted

    Hi PGChoudhury,

    You could use SQL Server Authentication to connect to the server and create a SQL Server Authentication Login ID first. See How to create a SQL server authentication login ID.

    For more information about SQL users and roles, see the following resources.

    Database-Level Roles

    Server and Database Roles in SQL Server

    Best Regards,

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 15, 2016 7:15 AM