I have a Windows 2003 Web Edition server that is a member of the domain.
When permissions are correct I can open and update databases on the server from the Web Edition server.
I want to allow anyone that hits a web site on the 2003 server to be able to update a specific database on the SQL server. The
users on that site are running under the local IIS anonymous user id.
I tried the obvious and simple connection
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WebRedirectLog;Data Source=ZEUS;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ROY;Use Encryption for Data=False;
Tag with column collation when possible=False");
(which I expected to fail) and it did telling me
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
(I did NOT supply a userid in the call to ADODB.Connection.Open
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not a domain account) as authenticated.
I do NOT want to allowed mixed mode authentication on this server.
Please bear in mind, I am still a novice at administering SQL, so please make your response a little detailed.
Thanks
Roy Chastain
KMSystems, Inc.
Hi Roy,
Welcome to MSDN newsgroup.
Regarding on the problem you mentioned, I think the it'll be a bit
difficult to meet all your requirement. Here are some of my understandings:
First, ASP will always impersonate the anonymous account( IIS's default
IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
Then, when our asp page try accessing any protected resource, the
IUSR_machine account will be the authenticated and executing account of our
asp page's thread. Then, return back to the quesitons you mentioned:
==============
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated
==============
1) I think the most standard means for allow all users to access db is to
use SQLServer Autehntcaiton(provide the username account in
connectionstring. This will require the SQLServer db to allow SQL
authentication. In fact, this is limited by the ASP , in asp.net we can
impersonate a certain fixed account so as to use t hat account to access
the database through integrated windows authentication.
2) If the webserver and SqlServer's database server is the same box, we
can simply grant the IIS's IUSR_MACHINE account the permisson to access
sqlserver db. However, as you said the DB server is a remote server to the
webserver, the IUSR_machine(local account ) on webserver is not valid on DB
server. For such scenario, there are two options:
a. use a Domain Account as your IIS virtual dir's anonymous account.
(Seems you didn't want to use DomainAcount
)b. create a duplicate local account on the SQLServer 's machine which has
the same username and password with the IIS virtual dir's anonymous account
(on the webserver box). However, the IIS's default anonymous account(
IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
we need to either explicitly set IUSR_MACHINE's password or create a
custom local account and replace the IUSR_machine as the virtual dir's
anonymous account.
Anyway, since there hasn't any means which will satisfy all the
requirement, we may need to make our decision according to the actual
situation. Please have a look of all the above things and feel free to let
us know if you have any ideas.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Here is what I did and it is working and I think secure for what I want.
I created a new domain account and gave it insert and select access to the database.
I set anonymous access for the web site to that account.
I created a Application Pool on the web server and have it running under that account.
I set the web site to use the newly created Application Pool.
From my point of view that gives all anonymous users of this one web site anonymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sort of anonymous access setting that said don't validate
this user, just let them do this, but...
Please let me know if you see any flaws in my reasoning.
Thanks
On Thu, 14 Apr 2005 05:18:35 GMT, v-schang@.online.microsoft.com (Steven Cheng[MSFT]) wrote:
>Hi Roy,
>Welcome to MSDN newsgroup.
>Regarding on the problem you mentioned, I think the it'll be a bit
>difficult to meet all your requirement. Here are some of my understandings:
>First, ASP will always impersonate the anonymous account( IIS's default
>IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
>Then, when our asp page try accessing any protected resource, the
>IUSR_machine account will be the authenticated and executing account of our
>asp page's thread. Then, return back to the quesitons you mentioned:
>==============
>What is the correct way to do one of the following.
>1) - Allow all users (authenticated or not) to update a database?
>2) - Set up the SQL server so that it can treat the 2003 local account (not
>a domain account) as authenticated
>==============
>1) I think the most standard means for allow all users to access db is to
>use SQLServer Autehntcaiton(provide the username account in
>connectionstring. This will require the SQLServer db to allow SQL
>authentication. In fact, this is limited by the ASP , in asp.net we can
>impersonate a certain fixed account so as to use t hat account to access
>the database through integrated windows authentication.
>2) If the webserver and SqlServer's database server is the same box, we
>can simply grant the IIS's IUSR_MACHINE account the permisson to access
>sqlserver db. However, as you said the DB server is a remote server to the
>webserver, the IUSR_machine(local account ) on webserver is not valid on DB
>server. For such scenario, there are two options:
>a. use a Domain Account as your IIS virtual dir's anonymous account.
>(Seems you didn't want to use DomainAcount
)>b. create a duplicate local account on the SQLServer 's machine which has
>the same username and password with the IIS virtual dir's anonymous account
>(on the webserver box). However, the IIS's default anonymous account(
>IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
>we need to either explicitly set IUSR_MACHINE's password or create a
>custom local account and replace the IUSR_machine as the virtual dir's
>anonymous account.
>Anyway, since there hasn't any means which will satisfy all the
>requirement, we may need to make our decision according to the actual
>situation. Please have a look of all the above things and feel free to let
>us know if you have any ideas.
>Thanks,
>Steven Cheng
>Microsoft Online Support
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
Roy Chastain
KMSystems, Inc.
|||Glad to hear from you Roy,
I think it's OK since ASP will impersonate the authenticated user by
default( if allow anonymous then impersonate the anonymous user). And
using Integrated windows authentication at back end db is also what we
recommend.
BTW, is there any future action plan that you'll migrate your web
application from classic ASP to ASP.NET. The asp.net web app framework will
have more strong support for stable and high performance web application.
Also, as for security, the asp.net can let the asp.net running under the
process idenity (the application pool identity in IIS6) together with allow
anonymous in IIS. Thus, we can still keep the IIS's anonymous account as a
very restricted account.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
No comments:
Post a Comment