asp.net sql connection problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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.
 
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!
 
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
¤ 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/paght000021.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 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.
Click to expand...

Right - how do you expect to use Windows Auth if both machines are not in a
domain?
 
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
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.
Click to expand...

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
 
¤ 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
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)
Click to expand...

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:
¤
¤ >
¤ > ¤ 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"
<[email protected]>
wrote:

¤ 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
¤ > 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)
Click to expand...

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
 
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!
 
When you say Surface Area Config has to be run? It appears that is
configured correctly, because SQL Authentication is working right?

TIA!
 
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
 
Back
Top