Board index MS SQL Server

IIS/SQL NT integrated security set-up for Intranet

IIS/SQL NT integrated security set-up for Intranet

Post by Scott Rimme » Thu, 07 Dec 2000 04:00:00



Set-up is as follows ...

1.    Webserver allows only NT challenge response security
2.    Physically separate SQL server only allows NT security
3.    SQL server services are set-up with domain accounts
4.    ASP pages on webserver check LOGON_USER server variable and build a
connection string
5.    Single domain 'mydomain'
6.    default website set to NT challenge response only
7.    testsite set to NT challenge response only

Problem

Although the connection string looks great e.g.
    "driver =sql server; server = myserver;database = testdatabase;
trusted_connection =yes;uid = \\mydomain\testuser"

it is only possible to logon to the webserver and get authenticated by the
SQL server ?

If you logon on as the same admin user or any other user on another pc in
the domain  although the connection string looks good the SQl server seems
to not be able to see the domain\username credentials - message on browser
is ...

strconn is...

driver =sql server; server = myserver;database = testdatabase;
trusted_connection =yes;uid = \\mydomain\testuser

error is

MICROSOFT OLEDB PROVIDER FOR ODBC DRIVERS Error ODBC Drivers error
'80040e4d'

[MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER] Logon failed for user '\'.

/testsite/default.asp,  line 22

If you place SQL onto  the same server as the webserver  - everything works
fine  - it only when you want a separate SQL server running NT security the
issue arises

PLEASE HELP !!PLEASE HELP !!PLEASE HELP !!PLEASE HELP !!PLEASE HELP !!PLEASE
HELP !!PLEASE HELP !!

 
 
 
Top

IIS/SQL NT integrated security set-up for Intranet

Post by Ian Posne » Sat, 20 Jan 2001 23:25:07


Firstly, with trusted security you don't need UID at all, nor the PWD, so
drop that UID nonsense from the connection string.

The next problem is that the users must log on to the domain. This means
that EITHER you use NT Challenge Response OR Clear Text -- when I last set
this up, one or the other worked but not both. For C/R, they must already be
authenticated by the domain before they hit the webserver.

Next, those accounts must be members of NT Groups which have been mapped to
SQL Server Server Logins.



Quote:> Set-up is as follows ...

> 1.    Webserver allows only NT challenge response security
> 2.    Physically separate SQL server only allows NT security
> 3.    SQL server services are set-up with domain accounts
> 4.    ASP pages on webserver check LOGON_USER server variable and build a
> connection string
> 5.    Single domain 'mydomain'
> 6.    default website set to NT challenge response only
> 7.    testsite set to NT challenge response only

> Problem

> Although the connection string looks great e.g.
>     "driver =sql server; server = myserver;database = testdatabase;
> trusted_connection =yes;uid = \\mydomain\testuser"

> it is only possible to logon to the webserver and get authenticated by the
> SQL server ?

> If you logon on as the same admin user or any other user on another pc in
> the domain  although the connection string looks good the SQl server seems
> to not be able to see the domain\username credentials - message on browser
> is ...

> strconn is...

> driver =sql server; server = myserver;database = testdatabase;
> trusted_connection =yes;uid = \\mydomain\testuser

> error is

> MICROSOFT OLEDB PROVIDER FOR ODBC DRIVERS Error ODBC Drivers error
> '80040e4d'

> [MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER] Logon failed for user '\'.

> /testsite/default.asp,  line 22

> If you place SQL onto  the same server as the webserver  - everything
works
> fine  - it only when you want a separate SQL server running NT security
the
> issue arises

> PLEASE HELP !!PLEASE HELP !!PLEASE HELP !!PLEASE HELP !!PLEASE HELP
!!PLEASE
> HELP !!PLEASE HELP !!


 
 
 
Top

1. intranet clients and SQL/NT integrated security

Hi. Hope one of you gurus out there can help me.

If possible, I want to keep the generic web user (IUSR_machine) out of
our SQL Server. I also want to leverage our SQL Server integrated
permissions structure for intranet users accessing SQL Server via the
web, since those users already have usernames and passwords in the NT
domain. Otherwise I have to reinvent the wheel and build and maintain a
whole separate permission structure for intranet users, using SQL Server
standard security.

I have an ASP app running on IIS 4. It connects with SQL Server running
on a different machine in the same domain. The web server is the PDC
and the db server is a BDC. Anonymous and NT Challenge/Response
authentication are enabled on IIS. The folders on the web server where
the asp app lives are intentionally off-limits to IUSR_machinename,
because I want IE to either use the user's active NT logon (if any), or
else prompt the iser for his/her NT logon info to proceed. That much
works fine: If the user is not logged onto the NT domain when he/she
surfs to the directory, the browser displays a prompt, the web server
accepts the logon, and then allows access.

Unfortunately, the next piece doesn't work. Somehow the NT logon info
for
the user is not being passed successfully over to SQL Server, since it
rejects the logins every time. (*Direct* trusted connections by the same
users to SQL Server work fine.)  If I set the DSN on the web server to
use SQL standard security, and have the web page query the user for a
standard username and password - or else allow IUSR_machine into SQL as
a guest under standard security - the connection works fine, so there is
probably no fundamental problem with connectivity in the asp. I have
also tried using "DSN-less" connections and that doesn't seem to help.

I'm pretty sure the problem lies between the web server and SQL Server,
but I don't know how to fix it. Any ideas?

Thanks in advance!!

- Tony

Tony Scilipoti
****************
Jamaica Plain, Massachusetts


2. Object Oriented Databases

3. Using Windows NT Integrated Security with IIS

4. Where is registry entry for system.mdw

5. NT Integrated Security Without SQL Security Manager ?

6. St. Louis Area Informix Users Group Technical Training Day on Sep

7. Setting up NT Integrated Security

8. They don't come any greener.HELP!! Informix/Unixware 2

9. IIS and SQL SERVER connection-integrated security

10. IIS and Integrated Security on SQL SRV

11. Q: Best practise IIS (DMZ) using integrated security to MS SQL (prod)

12. Integrated security?? (SQLserver, intranet, graduation project)

13. SQL / IIS / Intranet set-up


All times are UTC
Board index