Here is the deal...it would seem that you have the SQL Server instance installed on the same machine as your web server, if I were guessing, since you can use integrated security.  This brings up a lot of other security issues.  Since you web server can directly authenticate the SQL Server, this opens up a door for potential security risks.

When setting up a web server, it is generally best NOT to have it as part of the domain that the rest of your network runs on...you can setup DMZ domains and then link to other domains if you must, but the most secure environment is one to which the web server does not lie on the same network as all of the other machines.  It should look something like this:

OK...I know that there are some UML and diagram people out there that would like to hang me for this somewhat inaccurate picture...but it gets my point accross.  The DMZ/Firewall many times are the same box (i.e. a Cisco PIX firewall).  The web server is not part of the internal network, yet, the database is.  So in this case, you would DEFINITELY not want the SQL Server to be able to use integrated security which means that it can authenticate off of the OS and network credentials...big security risk!  You would want to create a secure password on the SQL Server that has the credentials you need for your application.  You would then "punch" a hole...securely...through the firewall that allows the web server to talk to the SQL Server using port 1433 (default port) and authenticate using an explicit SQL user ID.

There are many other security risks....but this could turn into a really long discussion.  Hope this helps!

Howard,



The issue is that in order for integrated security to work, you would need to give the IIS user (IUSR_ComputerName) access to the database server. Which would mean that EVERYONE IN THE UNIVERSE would have access to the database server.



OK, that is a bit overstated. But in general it is a bad idea to allow that user access to anything other than web pages. I do believe there is another option to setup the web site such that the user has to log into a domain when accessing the web site, in which case, each of those users would then need database access (and as I recall, this can get complicated fast too). This then kills connection pooling as Bill was indicating. It also means that you have to manage all those users on the database as well as in the domain and in the application. In general, it is waaaaaaaaaaaaay easier and safer to just have a database user that your application will use.



Hope that makes sense.

...what Trent said! I really, really need to look and see if Trent is checking out a post before I post....

LOL...not to worry...all good stuff

Yep - that makes sense.  Just so you'll know, our web server is not on our domain and our SQL Server is (which means it's not on the same machine as the web server)

I do understand about the security issues and  - again - thank you for being helpful in educating me on all this.

I'm still testing and haven't published to the web server yet - so hadn't run into the problems you brought up.  I have changed the connection string to specify a user and (after changing the SQL Server to the "mixed" mode of authentication) can connect just fine.

Thanks for all your help...until next time!

HB