Hi,
We have SQL Server 7.0 running on NT 4.0 ( I know we should have moved on
but that's another story).
We also have VB6 apps accessing the SQL Server using Windows authentication
.
Until now only users within our domain needed to use the apps.
There are a number of NT security groups to which we add our users depending
on their required permission levels.
However we now have a situation where users from an external domain need
access to the VB6 apps. They will also need to synchronise data using merge
replication.
Is there a way of mapping such users to our NT Security groups even though
they are members of an external domain. There will be co-operation from the
administrators of the other domain.
We would rather stick with Windows Authentication rather than Mixed Mode.
Any help greatly appreciated.
DDHi
You would need to setup trusts between the 2 domains. This is done at OS
level. Get your admins to look if it is something they really want to do. An
external company, unless it is in the same legal entity group, should not be
trusted as you have no control over their admins.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dave52" wrote:
> Hi,
> We have SQL Server 7.0 running on NT 4.0 ( I know we should have moved on
> but that's another story).
> We also have VB6 apps accessing the SQL Server using Windows authenticati
on.
> Until now only users within our domain needed to use the apps.
> There are a number of NT security groups to which we add our users dependi
ng
> on their required permission levels.
> However we now have a situation where users from an external domain need
> access to the VB6 apps. They will also need to synchronise data using mer
ge
> replication.
> Is there a way of mapping such users to our NT Security groups even though
> they are members of an external domain. There will be co-operation from th
e
> administrators of the other domain.
> We would rather stick with Windows Authentication rather than Mixed Mode.
> Any help greatly appreciated.
> DD
>|||The way I'd handle this is to create a local group on the SQL Server box tha
t
contains a global group from the remote domain. Then I'd simply give permiss
ions
inside SQL Server to this local group. For this to happen of course, there m
ust
be a trust between the domain that contains the SQL Server and the "external
"
domain that wants access to it.
HTH
Thomas
"Dave52" <Dave52@.discussions.microsoft.com> wrote in message
news:D40BC61F-A792-4442-BF6B-FF29BFAB9258@.microsoft.com...
> Hi,
> We have SQL Server 7.0 running on NT 4.0 ( I know we should have moved on
> but that's another story).
> We also have VB6 apps accessing the SQL Server using Windows authenticati
on.
> Until now only users within our domain needed to use the apps.
> There are a number of NT security groups to which we add our users dependi
ng
> on their required permission levels.
> However we now have a situation where users from an external domain need
> access to the VB6 apps. They will also need to synchronise data using mer
ge
> replication.
> Is there a way of mapping such users to our NT Security groups even though
> they are members of an external domain. There will be co-operation from th
e
> administrators of the other domain.
> We would rather stick with Windows Authentication rather than Mixed Mode.
> Any help greatly appreciated.
> DD
>
Showing posts with label domain. Show all posts
Showing posts with label domain. Show all posts
Thursday, March 8, 2012
Wednesday, March 7, 2012
Anonymous connection to a remote server
I have a SQL server running on a Win2k Domain Controller set for Windows Aut
hentication only.
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 f
rom 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;Ini
tial 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 Se
rver 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 mak
e 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 d
atabase.
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 tha
t 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 an
onymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sor
t 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 Chen
g[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.)
hentication only.
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 f
rom 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;Ini
tial 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 Se
rver 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 mak
e 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 d
atabase.
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 tha
t 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 an
onymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sor
t 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 Chen
g[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.)
Anonymous connection to a remote server
I have a SQL server running on a Win2k Domain Controller set for Windows Authentication only.
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.)
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.)
Sunday, February 12, 2012
Analysis Services Dimension Question
I have a database with millions of users with email addresses. I want to create an email domain dimension that groups domains into all the big email domains (hotmail, aol, yahoo) and an "other" consisting of all the other domains. I can create a table with entries for the big domains, and get the grouping working but anything that is not a big domain will get thrown out rather than put into an "other" category.
Any idea how to get such an "other" category?I just love SPAM. May I have another serving please ?
-PatP
Any idea how to get such an "other" category?I just love SPAM. May I have another serving please ?
-PatP
Analysis Services Deployment/Management Question
I have a local server for development that is on my local domain that I can work on very easily. However, my production server is offsight and on a different domain. Since Management Studio will only allow Windows Authentication when connecting to an Analysis Services instance, how can I manage the remote cube without having to remote desktop into the production server?
TIA
Right, you need to either TS to the machine, or establish a VPN connection to the domain. One possible, solution would be to use SQL Managment Studio against your local AS database, and instead of committing the changes, script out the XMLA. Then, if you had an http pump setup on a middle-tier, you could send the XMLA to via http using an admin user basic authentication that you create and you'd have a way to effectively do the administrative actions that you want. This isn't straightforward, but would get the job done.
Hope that helps.
Subscribe to:
Posts (Atom)