DB access denied with ASP.Net MVC application after switching to windows authentication mode

Question

I have a MVC application that I am now trying to add authentication and authorization to.

I want to allow users to get to the site and be automatically authenticated. So I set authentication mode="Windows" in the web.config, and enabled NTLM in the project options. The site now shows my domain name in the top right when I run it, but when I hit a action than needs DB access, it tells me access is denied for my user-name?

What step am I missing?

Answer 1

This is the subtle difference between authentication and authorization.

Authentication is the act of identifying who the user is (And you've done this bit) Authorisation is the act of determining who is allowed to do what (You need to apply the appropriate access permissions to the database, for each of your users/roles)

The subject of database access permissions is a little to complicated for sensible coverage on this forum, so i suggest that you do a bit of research via Google, etc

Answer 2

This is not necessarily an IIS or Windows Authentication issue. I would assume that your connection string looks something like this

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Now that you are using Windows authentication, the Domain\username is being passed to SQL to authenticate to the database. If you do not have the entire domain (or at least the subset logging into your application) as valid users in SQL, then you will get an unauthorized exception. You will need to a) pass a username/password to SQL in the conneciton string as below or b) add the users of your application to the security users of the database or c) use the impersonate attribute in the web.config file to impersonate a user that has access to both the application files on the web server and the database

SQL connection string with username/password

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;