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

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

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

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.

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!

Hey HB!

It has been a while since I worked on a web application, but I think the use of an ID/password in the connection string has something to do with connection pooling.  Others will have to chime in here to flesh this out a bit.  Since the web server is client agnostic, I think the integrated security option versus security declarations is six of one, half dozen of another.  The web server should always be connecting to the data server under the same security context, so it may be better to specify that in the connection string.  The web server's security context, then, does not need to be directly involved.  The application itself maintains control.

Just rambling, here.  I doubt that my explanation is very clear. 

I need more coffee. 

Bill

Bill and Trent,

Thanks - I've changed it to look like this:

DataLayer.DataSources.Add(new SqlDataSourceItem("", "Data Source=SQLLR01\\SQLLR01;Initial Catalog=PTPlusData;Integrated Security=SSPI"));

Seems to work now.

But...a question on the user name...help me understand how this works.  I was thinking the webserver would be the one logging into the SQL Server.  As such, I was thinking I could use the Windows security instead of a user name/password scenario.  Why is it that I need a user name instead?  (I'm not arguing with you here - just wanting to understand - I really appreciate the help!!!)

HB

Oops...that is what fast typing get you...speed kills

New acronym?

I'll knock those off, then.  Thanks!

Bill

Just FWI...SF automatically adds the Asynchronous processing tag for you as needed.  Since the DataLayer can save on multiple threads, this is a core level functionality of the framework.