How to configure website guest access?

Question

I finished creating my C#/SQL web application in VS 2008 on an XP Pro OS. It builds and executes successfully. Using the same code, I developed a website in VS 2008. Although this website builds and executes successfully, when I deploy it from my computer, it does not display the data grid. I think this is because the internet user does not have access to my SQL database.

So I read up on configuring SQL securables, trusted accounts vs. impersonation, etc. But I still don't have a real good handle on all this. I have tried both anonymous access with customer user logins specified in IIS Web Site properties, as well as disabling anonymous access. I am using same computer to host website (IIS), storing SQL Server and databases, and storing VS 2008.

Right now my goal is to just get this website to work from the internet. It resolves to the current page and everything appears except for this gridview which allows the user to choose which table from my Adventureworks database they want to append to. I noticed that the same image appears from IIS6 Manager window as what appears on the internet. And I am using Integrated instead of Windows account. I'm using IIS 5.1 with the default aspnet_wp.exe process.

You can view it at URL:

http://sqlmagic.net/Default.aspx

What type of authentication and user access do you recommend for me to give minimal but sufficient access to internet users for appending new data to existing tables on my database? And do you have any URL's you could recommend to me to look at regarding this? I have googled and read alot of URL's but I've not found that many helpful ones for an XP OS.

Also, I have the following connection string in my web.config file:

<add name="***" connectionString="Data Source=.;AttachDbFilename=&quot;C:\Program Files\Microsoft SQL Server\***\DATA\AdventureWorks_Data.mdf&quot;;Integrated Security=True;Connect Timeout=30;User Instance=False"
  providerName="System.Data.SqlClient" />

Is this correct or do I need "User ID" and "Password"? And right now I have Authentication Mode = Windows. Should this be Forms or something else?

I got rid of the Failure Audit error from Event Manager now. However, the GridView still doesn't display on the URL above. Right now I am using IUSR_ as my anonymous user access name. Is this the correct account to use?

GridView displays now in IIS6 Manager, but not in this URL. Since I no longer receive errors in event manager, how can I diagnose cause of missing GridView? Also, why does it take so long for this website to finish loading on my website URL, but only one second when viewing it from IIS Manager?

Answer 1

Using integrated security means that connections to SQL Server are made by the user id under which the requesting process is run. In this case, it's the user that IIS runs under.

It works in VS 2008 because you're using the "debug" server and, as such, it is connecting as you. When you publish to IIS, it's running as an unprivileged system account -- or it should be.

The easiest solution is to turn off integrated security and create a user that can connect to the database using SQL Server authentication. The alternate solution is to determine the windows user id that IIS is running as and allow that user the ability to connect to the database with the least amount of privileges possible.

And the "Authentication Mode" question isn't really relevant to connecting to SQL Server unless you're doing impersonation. Impersonation allows the IIS process/worker thread to run as the user making the web request. Since you're not doing impersonation, it's moot.

Answer 2

When I was trying to get to grips with these concepts, it was useful for me to remember that it's not the user of the web application that connects with the database, it's the process of the web application, running under IIS.

When a user authenticates to the web app, it just means that the app understands who the user is.

When the user does something which requires database access, the IIS process connects to the DB, it can either connect as the ASPNET user, or you can ask it to impersonate a different account.

Personally I prefer to create a user account specifically for the task of connecting to the DB and then setting IIS to impersonate this account. I think this is preferable to giving the ASPNET user access to the database.