intranet clients and SQL/NT integrated security

intranet clients and SQL/NT integrated security

Post by Tony Scilipot » Wed, 05 Aug 1998 04:00:00



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


 
 
 

intranet clients and SQL/NT integrated security

Post by John » Fri, 07 Aug 1998 04:00:00


How are you making the connection to SQL Server?  

Named Pipes or TCP/IP Sockets.  

If you call MSoft, they will most likely suggest you setup the SQL
connection to utilize standard security and TCP/IP Sockets and don't
utilize trusted connections - it works with IIS 4.0 and SQL 6.x with
anonymous or NT Challenge/Response.  

You should be able to configure your global.asa file as either a system DSN
or DSN-less if you utilize Sockets.

With NT Challenge/Response, your clients will have to be IE 3.x and above.

 
 
 

intranet clients and SQL/NT integrated security

Post by Kevin Klasma » Sat, 08 Aug 1998 04:00:00


I believe Microsoft's preferred way of doing this is to host your data
access components in MTS. The component runs under a specific username; I
use MTSUser, but it doesn't matter, as long as it belongs to the
MTS$Trusted$Impersonators group, which has permissions on SQL Server (using
NT Integrated or Mixed security). Users have (or not, as the case may be)
access rights to the component, via MTS role-based security, which simply
maps standard NT user accounts and/or groups to MTS roles.

It works very well for me.

--
Kevin Klasman
The Taylor Group


>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



 
 
 

1. IIS/SQL NT integrated security set-up for Intranet

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 !!

2. Error migrating from Postgres 6.3.2

3. NT Integrated Security Without SQL Security Manager ?

4. SQL Sever 2000 Service Pack 2

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

6. SQL Server 2000 SP2

7. DTS from SQL to AS400

8. change from integrated security to nt only security

9. NT and sql 6.5 integrated security

10. NT and ms sql 6.5 integrated security

11. SQL/NT Integrated Security