How to enable an AdventureWorks database in a web application?

Question

I am developing a C#/SQL ASP.NET web application in VS 2008. I want the users to be able to select an Adventureworks table and then an input file. The user is then able to map columns from the file with the selected table. This app works fine now in VS. But when I Browse in IIS, it doesn't show any of the tables. I think this is because currently Adventureworks connection string is integrated security (Windows authentication).

If I want users to be able to select a table, do I have to change connection to SQL Server authentication? And if so, is the only way to do this to reinstall SQL Server? Or can I just reinstall Adventureworks? And if I reinstall SQL Server, do I need to reinstall VS also for this to work? This could be painful...

Answer 1

When you using Visual Studio to host the web app that authenticates with Windows Integrated Authentication it uses the credentials of whatever account started Visual Studio, typically your user account.

Very often app pools for IIS will use Network Service, so you can give that account permissions to the database, the account name you would use to grant access in SQL Server is "Domain\Servername$" Domain is the domain the server is in the Servername is the name of the server and the $ tacked on the end is required.