 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / April 2006asp.net sql connection problem
Hi all, I have a production environment where I have a 2-node SQL 2005 Cluster running on boxes which are part of a domain "MyCompany". I have setup a domain user account called "netasp" and then given it permissions to the database. I then setup a new App Pool in IIS on my Web Server (not part of domain, can access SQL server via TCP) with identity setup to a local account called "netasp" with the same password as the domain user account. I then setup my website under this App Pool and tried my app. For some reason it will not connect. This works in a development environment where my web & sql is on the same box (non cluster environment). How do I go about getting this to work? For security reasons, my web servers cannot be part of the domain. TIA! ¤ Hi all, ¤ ¤ I have a production environment where I have a 2-node SQL 2005 Cluster ¤ running on boxes which are part of a domain "MyCompany". I have setup a ¤ domain user account called "netasp" and then given it permissions to the ¤ database. I then setup a new App Pool in IIS on my Web Server (not part of ¤ domain, can access SQL server via TCP) with identity setup to a local ¤ account called "netasp" with the same password as the domain user account. I ¤ then setup my website under this App Pool and tried my app. For some reason ¤ it will not connect. This works in a development environment where my web & ¤ sql is on the same box (non cluster environment). How do I go about getting ¤ this to work? For security reasons, my web servers cannot be part of the ¤ domain. ¤ Is your web app and database connection configured for Windows integrated security? If so you probably have a delegation issue. Paul ~~~~ Microsoft MVP (Visual Basic) My web app actually uses forms authentication by authenticating against the database. I have heard of cases where people got this to work. I just need some guidance/help in getting it to work. TIA! > ¤ Hi all, > ¤ [quoted text clipped - 23 lines] > ~~~~ > Microsoft MVP (Visual Basic) The problem is that you gave permissions to the domain account, but you running it as a local account. The remote SQL Server doesn't know about your local 'netasp', it only knows about the domain one. I'm not sure you can 'fool' the SQL server in thinking that the local machine account of the remote web server is the same thing as the domain account it knows about. I think you'd have to make your web server part of the domain, and run it under the domain account. That, or just use username/password for the the sql server authentication instead of integrated. > Hi all, > [quoted text clipped - 11 lines] > > TIA! I would love to use SQL Auth, but the beauty of windows auth is I dont have to specify the username/pwd in the config file. Is there no way at all to get this to work? Does MS recommend putting your Web Servers in the same domain as the SQL? Our security consultant told us never to do that. Leave the Web in the DMZ.. TIA! > The problem is that you gave permissions to the domain account, but you > running it as a local account. The remote SQL Server doesn't know about [quoted text clipped - 20 lines] >> >> TIA! I can't say I have complete knowledge of windows security, but I don't know how to get this to work, although there could be a way. First off, if someone gets access to your web server, you could argue that they will get access to the sql server through windows authentication if they really try anyway - so it will be the same as if they saw the username/password in the config file. As far as storing the username/password in the config file (or elsewhere in a different file, even outside your virtual directory), you could encrypt the connection information to provide an extra level of security. >I would love to use SQL Auth, but the beauty of windows auth is I dont have >to specify the username/pwd in the config file. Is there no way at all to [quoted text clipped - 28 lines] >>> >>> TIA! ¤ I would love to use SQL Auth, but the beauty of windows auth is I dont have ¤ to specify the username/pwd in the config file. Is there no way at all to ¤ get this to work? Does MS recommend putting your Web Servers in the same ¤ domain as the SQL? Our security consultant told us never to do that. Leave ¤ the Web in the DMZ.. ¤ See if the following helps: How To: Use Forms Authentication with Active Directory in Multiple Domains in ASP.NET 2.0 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/pag ht000021.asp Paul ~~~~ Microsoft MVP (Visual Basic) I guess, I need to clarify. I do not wish to use Active Directory in anyway to authenticate my web application users. They will be authenticated off tables in SQL Server. The problem I am having is making the Asp.Net Web Application connect to and authenticate against the SQL Server using a standard specified Windows account. Is there a way to do it when the SQL server is in a domain environment and the Web Server are in a Workgroup environment. I have tried SQL Auth and it works fine. TIA! > ¤ I would love to use SQL Auth, but the beauty of windows auth is I dont > have [quoted text clipped - 15 lines] > ~~~~ > Microsoft MVP (Visual Basic) >I guess, I need to clarify. I do not wish to use Active Directory in anyway >to authenticate my web application users. They will be authenticated off [quoted text clipped - 3 lines] >server is in a domain environment and the Web Server are in a Workgroup >environment. I have tried SQL Auth and it works fine. Right - how do you expect to use Windows Auth if both machines are not in a domain?  SignatureTom Kaminski IIS MVP http://www.microsoft.com/windowsserver2003/community/centers/iis/ http://mvp.support.microsoft.com/ http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS ¤ I guess, I need to clarify. I do not wish to use Active Directory in anyway ¤ to authenticate my web application users. They will be authenticated off ¤ tables in SQL Server. The problem I am having is making the Asp.Net Web ¤ Application connect to and authenticate against the SQL Server using a ¤ standard specified Windows account. Is there a way to do it when the SQL ¤ server is in a domain environment and the Web Server are in a Workgroup ¤ environment. I have tried SQL Auth and it works fine. ¤ The bottom line is that there must be a delegation of credentials between the two systems if you plan on using Windows Integrated Security with SQL Server. I'm fairly certain that you understand that. What I would do is create a local account on the web server and specify that as the anonymous account in IIS under which your application executes. I would then create a local account with the same exact credentials (sam account ID and password) on the SQL Server box in the other domain and provide the necessary permissions for this account under SQL Server. This is essentially what Dan described in his post. Since I wouldn't be completely familiar with your configuration, I can't be certain what other tweaks might be involved in order to get this to work. Paul ~~~~ Microsoft MVP (Visual Basic) Paul wrote on Wed, 19 Apr 2006 09:47:36 -0500: > ¤ I guess, I need to clarify. I do not wish to use Active Directory in > anyway ¤ to authenticate my web application users. They will be [quoted text clipped - 23 lines] > ~~~~ > Microsoft MVP (Visual Basic) Yep, it's as simple as doing that, at least as far as IIS is concerned. Whether this delegates to ASP.NET correctly I can't say. Dan ¤ Paul wrote on Wed, 19 Apr 2006 09:47:36 -0500: ¤ ¤ > On Tue, 18 Apr 2006 14:02:13 -0500, <param@community.nospam> wrote: ¤ > ¤ > ¤ I guess, I need to clarify. I do not wish to use Active Directory in ¤ > anyway ¤ to authenticate my web application users. They will be ¤ > authenticated off ¤ tables in SQL Server. The problem I am having is ¤ > making the Asp.Net Web ¤ Application connect to and authenticate against ¤ > the SQL Server using a ¤ standard specified Windows account. Is there a ¤ > way to do it when the SQL ¤ server is in a domain environment and the Web ¤ > Server are in a Workgroup ¤ environment. I have tried SQL Auth and it ¤ > works fine. ¤ ¤ > ¤ > The bottom line is that there must be a delegation of credentials between ¤ > the two systems if you plan on using Windows Integrated Security with SQL ¤ > Server. I'm fairly certain that you understand that. ¤ > ¤ > What I would do is create a local account on the web server and specify ¤ > that as the anonymous account in IIS under which your application ¤ > executes. I would then create a local account with the same exact ¤ > credentials (sam account ID and password) on the SQL Server box in the ¤ > other domain and provide the necessary permissions for this account under ¤ > SQL Server. This is essentially what Dan described in his post. ¤ > ¤ > Since I wouldn't be completely familiar with your configuration, I can't ¤ > be certain what other tweaks might be involved in order to get this to ¤ > work. ¤ > ¤ > Paul ¤ > ~~~~ ¤ > Microsoft MVP (Visual Basic) ¤ ¤ Yep, it's as simple as doing that, at least as far as IIS is concerned. ¤ Whether this delegates to ASP.NET correctly I can't say. ¤ ¤ Dan ¤ It does, however I've never attempted this across domains or in a clustered SQL Server environment and I'm not sure what other security barriers may exist in his environment. Paul ~~~~ Microsoft MVP (Visual Basic) Paul wrote on Wed, 19 Apr 2006 14:03:26 -0500: > On Wed, 19 Apr 2006 16:29:53 +0100, "Daniel Crichton" > <msnews@worldofspack.co.uk> [quoted text clipped - 45 lines] > ~~~~ > Microsoft MVP (Visual Basic) That's a good point. Re-reading the original post the account configuration appears correct for this to work, but obviously it's not. Surface Area Configuration needs to be run in SQL Server 2005 to allow connections from "remote" machines, that could be the reason for it not working if that hasn't been done - it will allow connections from the local machine, but nowhere else. Dan When you say Surface Area Config has to be run? It appears that is configured correctly, because SQL Authentication is working right? TIA! > Paul wrote on Wed, 19 Apr 2006 14:03:26 -0500: > [quoted text clipped - 61 lines] > > Dan I missed your reply saying SQL Auth is working. I'm guessing then that the clustering is the issue, as I haven't had a problem running SQL in Windows Auth mode on a different server to IIS while not using a domain. Dan param@community.nospam wrote on Thu, 20 Apr 2006 23:52:17 -0500: > When you say Surface Area Config has to be run? It appears that is > configured correctly, because SQL Authentication is working right? [quoted text clipped - 64 lines] >> >> Dan Well here is the trick. The SQL Server is a cluster. If I setup an account local to SQL1, then how would it work when SQL2 takes over? Or do I just duplicate that local account on SQL2 as well? TIA! > ¤ I guess, I need to clarify. I do not wish to use Active Directory in > anyway [quoted text clipped - 29 lines] > ~~~~ > Microsoft MVP (Visual Basic) Marina wrote on Tue, 18 Apr 2006 10:42:07 -0400: > The problem is that you gave permissions to the domain account, but you > running it as a local account. The remote SQL Server doesn't know about [quoted text clipped - 4 lines] > run it under the domain account. That, or just use username/password for > the the sql server authentication instead of integrated. By using the same login name and password on both machines it should work - certainly does here where I have an IIS server running sites under local accounts that match credentials on a SQL server machine and they login fine, both servers are configured to run in the same workgroup (no domains running in my DMZ). I am however doing this using ASP and via ODBC and OLE DB connections, so it could be that something is different in the way ASP.NET passes credentials around. Dan Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.