I ran into an issue in my current projet when I set Integrated Security = SSPI in connection string. Let me explain what I wanted to do and what I was doing. Actually I wanted to connect the database using windows authentication. To do this I specified connection string “Data Source=EIISSQL1;initial catalog=PageManagementSystem;uid=domain@username;pwd=password;Integrated Security=SSPI”. When I ran the project I was getting Login Failed for machinenameASPNET. I checked rights for my ASPNET account. It was a member of debugger users. If we change it to be the group of administrator account, It would work. But thats a bad idea.
Actually If we specified a username & pwd in connection string and we are using Integrated Security=SSPI (means windows authentication for SQL Server), its not gonna be used anywhere. So there is no meaning to specified username & pwd in connection string if we are using windows authentication for sql server. So by default It will run under the ASPNET Account. So to resolve this issue we need to create a user machinenameASPNET in database if not exists and need to give a proper rights to that user.
If we specified in web.config file, by default It will run under IUSER_MACHINENAME if we allow anonymouse user. It will not run under ASPNET Account. And If we sepcified It will run under the user name specified. So we need to give a proper rights to that user not ASPNET user.
Hope this would help you !!!