Re: ASP access to SQL Database
- From: "Steve Foster [SBS MVP]" <steve.foster@xxxxxxxxxxxxx>
- Date: Tue, 21 Feb 2006 07:36:27 -0800
Ken-T wrote:
Hi,
on SBS premium....
taking some first steps with a test DB in SQL and some ASP pages .
Just need to get one or two things working so can get a better idea if a
project involving a major DB with lookups from Domain users via ASP on IIS is
worth the effort.
(Yes, I will buy a book and study it properly when I start.... anyone has
and ideas for a really good book for database access via ASP would appreciate
it).
For first step, an ASP page on IIS with contzent shown below fails with
"no trusted SQL connection.... error with login of user sa" etc.
<%
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB; Initial Catalog = Northwind; User Id = sa;
Password="
If conn.errors.count = 0 Then
Response.Write "Connected OK"
End If
%>
Am sure it is due to the users set for DB acess on SQL.
In Enterprise, I see only dbo and dbadmin.
IIS is running as default (I think) with IUSR_Sysname as user for "anonymous"
Would appreciate any help/tips for setup of a correct, read only access
method to db's
The default condition for SQL on SBS is that it is configured for Windows Authentication only. Since SA is a SQL Login, this fails.
If the plan is to make use of domain user accounts, this is fine, but you need to configure IIS to impersonate the domain users (ie not use IUSR_*) *and* to add the domain users to SQL (SQL starts out with only <domain>\Administrators having access). You need to change the Connection String to request a Trusted Connection to SQL Server as well.
To configure IIS to impersonate users, either:
a) disable anonymous access in the website/virtual directory properties, or
b) use NTFS security on the underlying files and folders so that the IUSR_* account doesn't have privileges.
To configure SQL Access for domain accounts, use Enterprise Manager to add them as Logins, and then specify the database rights for the logins. I would recommend you create domain groups that mirror the security levels you wish to create, grant the groups SQL logins, and then place user accounts in the relevant groups.
--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.
.
- Follow-Ups:
- Re: ASP access to SQL Database
- From: Ken-T
- Re: ASP access to SQL Database
- From: Ken-T
- Re: ASP access to SQL Database
- Prev by Date: Re: SBS Licencing
- Next by Date: Re: Project Abandoned
- Previous by thread: Re: Add a second DC to SBS2K3 network
- Next by thread: Re: ASP access to SQL Database
- Index(es):
Relevant Pages
|
