realtimer
asked on
Granting a computer access to SQL database in IIS web application
We are developing a simple web application which connects to a SQL server and allows users to edit SQL data. The IIS server and the SQL server are running on the same Windows server in an Active Directory environment. The application is designed to be only available within the local network. It appears that the local server (which runs IIS and SQL) needs to have permission to access the database for the application to work properly with Active Directory authentication. I am familiar with SQL logins for users but I don't think you can add a computer account in SQL. Can someone tell me how I may accomplish this?
ASKER
Thanks for a quick response. I apologize for my lack of knowledge in IIS. If the following is what you are referring to (masked in black in screenshot), then it is currently set to the domain user account for the developer. And yes, SQL server is using mixed authentication mode.
OK this means the domain user account must be allowed in the SQL Server.
Add it to Logins in SSMS under the Security menu tree at the Server level and set access to the app database
Add it to Logins in SSMS under the Security menu tree at the Server level and set access to the app database
We are developing a simple web application which connects to a SQL server and allows users to edit SQL data.Even when it is a "simple" application, you need to define your security targets first. Especially: Are users required to authenticate against the application?
Should every IIS hosted site access your SQL Server database?
It appears that the local server (which runs IIS and SQL)This is a bad idea. Even for a small application.
ASKER
Thank you Pavel. The domain user account that is masked in black in the screenshot is a member of an Active Directory security group which has a SQL login and has dbowner role for the database in question.
Thank you ste5an. This IIS server has a root web page we call the launch pad from which users will click links to subpages. Each subpage will access a different database on the same SQL server. For the time being, all access will be limited to databases on the same SQL server (local).
Thank you ste5an. This IIS server has a root web page we call the launch pad from which users will click links to subpages. Each subpage will access a different database on the same SQL server. For the time being, all access will be limited to databases on the same SQL server (local).
OK, that means you have computer account defined in SQL. Does it work when the given user is logged in the computer? Is the user able to login from e.g. SSMS?
IIS should leave connection attempt result in some application log. Did you check these logs?
IIS should leave connection attempt result in some application log. Did you check these logs?
ASKER
That user (who is a developer) has local admin rights on the SQL/IIS server. He can log into the server and use SSMS and access the data in the database to which he has dbowner database role.
I have looked at IIS logs but not much information is available.
I have looked at IIS logs but not much information is available.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the app will have a connection string defined. the app will use this connection string to connect to sql server
ASKER
If we were to create an account specifically for this IIS application purposes (say we call it IIS_AppPool_User), should it have datawriter role in the database or can it only be datareader?
Please read my post again.
You should create a separate database role for this application access. Grant all permissions to this role. Only use this role to assign to the user, thus the login.
You should create a separate database role for this application access. Grant all permissions to this role. Only use this role to assign to the user, thus the login.
ASKER
Thank you all. I have created a security group and granted datareader and datawriter roles. I have created a user account, configured it as the default account for the IIS application pool and also added it to the group. I was able to log in as a generic user and modify SQL data and the developer confirmed this will work.
Does the SQL Server have the mixed authentication mode allowed?