iis sql db access via global.asa

[Stuart Coney]

Hi,

I have created a website on IIS that access a backend sql 2000 db which is
located on a seperate server to the iis instance.

IIS wise have have turned of anonymous access and set up the ntfs
permissions that are required. I have tested the access to content and this
is working correctly.

The IIS and SQL Servers are located in domain A. The access required to
the SQL is for a subset of users from domain B. During dev. I used a
connection string in global.asa that used a sql user account for accessing
the db. This works fine, the original provstr was

ProvStr =
"Server=mucabc01;Database=DSGMGMNT;UID=xxxx;PWD=yy yy;Network=DBMSSOCN;"

Now I want to restrict certain users to read only and other users to
read/write on the database an changed the provstr as follows.

ProvStr =
"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"

and also tried

ProvStr = "Server=mucabc01;Database=DSGMGMNT;Integrated
Security=SSPI;Network=DBMSSOCN;"

my user account from domain B is configured as a .dbo for the database and
my access to the DB works fine using other methods of access, ie from a cmd
line vbscript using Integrated Security

my global.asa currently looks as follows.......any help or pointers as to
what I need to do to get this up and running much appreciated, thanks Stuart
Coney

<SCRIPT LANGUAGE="VBScript" RUNAT="Server">

Sub Session_OnStart ' Runs the first time a user runs any page in your
application
Dim objConn

' Create an ADO Connection
Set objConn = Server.CreateObject("ADODB.Connection")

' Specify the OLE DB provider.
objConn.Provider = "sqloledb"

ProvStr =
"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"

objConn.Open ProvStr
' Set Session("cnn") = objConn


END Sub

Sub Session_OnEnd ' Runs when a user's session times out or quits your
application

'Close the data connection
objConn.Close
set objConn = nothing

END Sub

</SCRIPT>

[Ken Schaefer]

a) DO NOT store connections in session scope. Create, open, close and
destroy the connection on each page

b) I don't think your authentication system is going to work. The Kerberos
token that the webserver has won't have permission to logon to network
resources. You'll need to enable delegation to get that working. Instead,
you can use Basic authentication + SSL (to secure the transmission of the
username/password)

Cheers
Ken

"Stuart Coney" <NOSPAM_StueyC_News@msn.com> wrote in message
news:uYZdtZakDHA.1488@TK2MSFTNGP12.phx.gbl...
: Hi,
:
: I have created a website on IIS that access a backend sql 2000 db which is
: located on a seperate server to the iis instance.
:
: IIS wise have have turned of anonymous access and set up the ntfs
: permissions that are required. I have tested the access to content and
this
: is working correctly.
:
: The IIS and SQL Servers are located in domain A. The access required to
: the SQL is for a subset of users from domain B. During dev. I used a
: connection string in global.asa that used a sql user account for accessing
: the db. This works fine, the original provstr was
:
: ProvStr =
: "Server=mucabc01;Database=DSGMGMNT;UID=xxxx;PWD=yy yy;Network=DBMSSOCN;"
:
: Now I want to restrict certain users to read only and other users to
: read/write on the database an changed the provstr as follows.
:
: ProvStr =
:
"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"
:
: and also tried
:
: ProvStr = "Server=mucabc01;Database=DSGMGMNT;Integrated
: Security=SSPI;Network=DBMSSOCN;"
:
: my user account from domain B is configured as a .dbo for the database and
: my access to the DB works fine using other methods of access, ie from a
cmd
: line vbscript using Integrated Security
:
: my global.asa currently looks as follows.......any help or pointers as to
: what I need to do to get this up and running much appreciated, thanks
Stuart
: Coney
:
: <SCRIPT LANGUAGE="VBScript" RUNAT="Server">
:
: Sub Session_OnStart ' Runs the first time a user runs any page in your
: application
: Dim objConn
:
: ' Create an ADO Connection
: Set objConn = Server.CreateObject("ADODB.Connection")
:
: ' Specify the OLE DB provider.
: objConn.Provider = "sqloledb"
:
: ProvStr =
:
"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"
:
: objConn.Open ProvStr
: ' Set Session("cnn") = objConn
:
:
: END Sub
:
: Sub Session_OnEnd ' Runs when a user's session times out or quits
your
: application
:
: 'Close the data connection
: objConn.Close
: set objConn = nothing
:
: END Sub
:
: </SCRIPT>
:
:

[Stuart Coney]

thanks Ken,

switching on Basic Authentication has opened up the desired access levels.
I will now look into implementing SSL security on top of this.

I have seen written a few times about not create/destroy connections on a
per page basis rather than going the session global.asa route but am not
sure of the reason why, ie whether it is resourse hungry, unreliable or a
security risk. If you can provide a little background on this issue then
much appreciated.

thanks again
Stu


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:efJPIEgkDHA.1004@TK2MSFTNGP09.phx.gbl...

> a) DO NOT store connections in session scope. Create, open, close and
> destroy the connection on each page
>
> b) I don't think your authentication system is going to work. The Kerberos
> token that the webserver has won't have permission to logon to network
> resources. You'll need to enable delegation to get that working. Instead,
> you can use Basic authentication + SSL (to secure the transmission of the
> username/password)
>
> Cheers
> Ken
>
> "Stuart Coney" <NOSPAM_StueyC_News@msn.com> wrote in message
> news:uYZdtZakDHA.1488@TK2MSFTNGP12.phx.gbl...
> : Hi,
> :
> : I have created a website on IIS that access a backend sql 2000 db which

is

> : located on a seperate server to the iis instance.
> :
> : IIS wise have have turned of anonymous access and set up the ntfs
> : permissions that are required. I have tested the access to content and
> this
> : is working correctly.
> :
> : The IIS and SQL Servers are located in domain A. The access required

to

> : the SQL is for a subset of users from domain B. During dev. I used a
> : connection string in global.asa that used a sql user account for

accessing

> : the db. This works fine, the original provstr was
> :
> : ProvStr =
> : "Server=mucabc01;Database=DSGMGMNT;UID=xxxx;PWD=yy yy;Network=DBMSSOCN;"
> :
> : Now I want to restrict certain users to read only and other users to
> : read/write on the database an changed the provstr as follows.
> :
> : ProvStr =
> :
>

"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"

> :
> : and also tried
> :
> : ProvStr = "Server=mucabc01;Database=DSGMGMNT;Integrated
> : Security=SSPI;Network=DBMSSOCN;"
> :
> : my user account from domain B is configured as a .dbo for the database

and

> : my access to the DB works fine using other methods of access, ie from a
> cmd
> : line vbscript using Integrated Security
> :
> : my global.asa currently looks as follows.......any help or pointers as

to

> : what I need to do to get this up and running much appreciated, thanks
> Stuart
> : Coney
> :
> : <SCRIPT LANGUAGE="VBScript" RUNAT="Server">
> :
> : Sub Session_OnStart ' Runs the first time a user runs any page in

your

> : application
> : Dim objConn
> :
> : ' Create an ADO Connection
> : Set objConn = Server.CreateObject("ADODB.Connection")
> :
> : ' Specify the OLE DB provider.
> : objConn.Provider = "sqloledb"
> :
> : ProvStr =
> :
>

"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"

> :
> : objConn.Open ProvStr
> : ' Set Session("cnn") = objConn
> :
> :
> : END Sub
> :
> : Sub Session_OnEnd ' Runs when a user's session times out or quits
> your
> : application
> :
> : 'Close the data connection
> : objConn.Close
> : set objConn = nothing
> :
> : END Sub
> :
> : </SCRIPT>
> :
> :
>
>

[Bob Barrows]

Stuart Coney wrote:

> thanks Ken,
>
> switching on Basic Authentication has opened up the desired access
> levels. I will now look into implementing SSL security on top of this.
>
> I have seen written a few times about not create/destroy connections
> on a per page basis rather than going the session global.asa route
> but am not sure of the reason why, ie whether it is resourse hungry,
> unreliable or a security risk. If you can provide a little
> background on this issue then much appreciated.
>

[url]http://www.aspfaq.com/2053[/url]

Bob Barrows

[Ken Schaefer]

Because you defeat connection pooling.

If you give each user their own connection (via Session_OnStart), then
there's a physical connection for each user, even if they aren't using it
(eg, they are just browsing a page).

MS, via the MS Data Access Components (which includes ADO, OLEDB, ODBC)
provides a pooling mechanism, whereby OLEDB keeps a pool of connections
open, and when your ASP page wants a connection, one is given to the ASP
page out of the pool, and when you dispose of the connection it's returned
to the pool transparently (even though your ASP page thinks it's gone).

Here's the details:

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp[/url]
Pooling in the Microsoft Data Access Components

Cheers
Ken


"Stuart Coney" <NOSPAM_StueyC_News@msn.com> wrote in message
news:ujZ6o$ikDHA.1808@TK2MSFTNGP09.phx.gbl...
: thanks Ken,
:
: switching on Basic Authentication has opened up the desired access levels.
: I will now look into implementing SSL security on top of this.
:
: I have seen written a few times about not create/destroy connections on a
: per page basis rather than going the session global.asa route but am not
: sure of the reason why, ie whether it is resourse hungry, unreliable or a
: security risk. If you can provide a little background on this issue
then
: much appreciated.
:
: thanks again
: Stu
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:efJPIEgkDHA.1004@TK2MSFTNGP09.phx.gbl...
: > a) DO NOT store connections in session scope. Create, open, close and
: > destroy the connection on each page
: >
: > b) I don't think your authentication system is going to work. The
Kerberos
: > token that the webserver has won't have permission to logon to network
: > resources. You'll need to enable delegation to get that working.
Instead,
: > you can use Basic authentication + SSL (to secure the transmission of
the
: > username/password)
: >
: > Cheers
: > Ken
: >
: > "Stuart Coney" <NOSPAM_StueyC_News@msn.com> wrote in message
: > news:uYZdtZakDHA.1488@TK2MSFTNGP12.phx.gbl...
: > : Hi,
: > :
: > : I have created a website on IIS that access a backend sql 2000 db
which
: is
: > : located on a seperate server to the iis instance.
: > :
: > : IIS wise have have turned of anonymous access and set up the ntfs
: > : permissions that are required. I have tested the access to content
and
: > this
: > : is working correctly.
: > :
: > : The IIS and SQL Servers are located in domain A. The access required
: to
: > : the SQL is for a subset of users from domain B. During dev. I used
a
: > : connection string in global.asa that used a sql user account for
: accessing
: > : the db. This works fine, the original provstr was
: > :
: > : ProvStr =
: > :
"Server=mucabc01;Database=DSGMGMNT;UID=xxxx;PWD=yy yy;Network=DBMSSOCN;"
: > :
: > : Now I want to restrict certain users to read only and other users to
: > : read/write on the database an changed the provstr as follows.
: > :
: > : ProvStr =
: > :
: >
:
"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"
: > :
: > : and also tried
: > :
: > : ProvStr = "Server=mucabc01;Database=DSGMGMNT;Integrated
: > : Security=SSPI;Network=DBMSSOCN;"
: > :
: > : my user account from domain B is configured as a .dbo for the database
: and
: > : my access to the DB works fine using other methods of access, ie from
a
: > cmd
: > : line vbscript using Integrated Security
: > :
: > : my global.asa currently looks as follows.......any help or pointers as
: to
: > : what I need to do to get this up and running much appreciated, thanks
: > Stuart
: > : Coney
: > :
: > : <SCRIPT LANGUAGE="VBScript" RUNAT="Server">
: > :
: > : Sub Session_OnStart ' Runs the first time a user runs any page in
: your
: > : application
: > : Dim objConn
: > :
: > : ' Create an ADO Connection
: > : Set objConn = Server.CreateObject("ADODB.Connection")
: > :
: > : ' Specify the OLE DB provider.
: > : objConn.Provider = "sqloledb"
: > :
: > : ProvStr =
: > :
: >
:
"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"
: > :
: > : objConn.Open ProvStr
: > : ' Set Session("cnn") = objConn
: > :
: > :
: > : END Sub
: > :
: > : Sub Session_OnEnd ' Runs when a user's session times out or quits
: > your
: > : application
: > :
: > : 'Close the data connection
: > : objConn.Close
: > : set objConn = nothing
: > :
: > : END Sub
: > :
: > : </SCRIPT>
: > :
: > :
: >
: >
:
:

[Stuart Coney]

the link was very informative and I have removed the global.asa from the
app. and created individual connections on a per asp page basis. I added
"OLE DB Services=-1" to the connection string.

thanks again.

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:OUU3bJlkDHA.360@TK2MSFTNGP12.phx.gbl...

> Because you defeat connection pooling.
>
> If you give each user their own connection (via Session_OnStart), then
> there's a physical connection for each user, even if they aren't using it
> (eg, they are just browsing a page).
>
> MS, via the MS Data Access Components (which includes ADO, OLEDB, ODBC)
> provides a pooling mechanism, whereby OLEDB keeps a pool of connections
> open, and when your ASP page wants a connection, one is given to the ASP
> page out of the pool, and when you dispose of the connection it's returned
> to the pool transparently (even though your ASP page thinks it's gone).
>
> Here's the details:
>
>

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp[/url]

> Pooling in the Microsoft Data Access Components
>
> Cheers
> Ken
>
>
> "Stuart Coney" <NOSPAM_StueyC_News@msn.com> wrote in message
> news:ujZ6o$ikDHA.1808@TK2MSFTNGP09.phx.gbl...
> : thanks Ken,
> :
> : switching on Basic Authentication has opened up the desired access

levels.

> : I will now look into implementing SSL security on top of this.
> :
> : I have seen written a few times about not create/destroy connections on

a

> : per page basis rather than going the session global.asa route but am not
> : sure of the reason why, ie whether it is resourse hungry, unreliable or

a

> : security risk. If you can provide a little background on this issue
> then
> : much appreciated.
> :
> : thanks again
> : Stu
> :
> :
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:efJPIEgkDHA.1004@TK2MSFTNGP09.phx.gbl...
> : > a) DO NOT store connections in session scope. Create, open, close and
> : > destroy the connection on each page
> : >
> : > b) I don't think your authentication system is going to work. The
> Kerberos
> : > token that the webserver has won't have permission to logon to network
> : > resources. You'll need to enable delegation to get that working.
> Instead,
> : > you can use Basic authentication + SSL (to secure the transmission of
> the
> : > username/password)
> : >
> : > Cheers
> : > Ken
> : >
> : > "Stuart Coney" <NOSPAM_StueyC_News@msn.com> wrote in message
> : > news:uYZdtZakDHA.1488@TK2MSFTNGP12.phx.gbl...
> : > : Hi,
> : > :
> : > : I have created a website on IIS that access a backend sql 2000 db
> which
> : is
> : > : located on a seperate server to the iis instance.
> : > :
> : > : IIS wise have have turned of anonymous access and set up the ntfs
> : > : permissions that are required. I have tested the access to content
> and
> : > this
> : > : is working correctly.
> : > :
> : > : The IIS and SQL Servers are located in domain A. The access

required

> : to
> : > : the SQL is for a subset of users from domain B. During dev. I

used

> a
> : > : connection string in global.asa that used a sql user account for
> : accessing
> : > : the db. This works fine, the original provstr was
> : > :
> : > : ProvStr =
> : > :
> "Server=mucabc01;Database=DSGMGMNT;UID=xxxx;PWD=yy yy;Network=DBMSSOCN;"
> : > :
> : > : Now I want to restrict certain users to read only and other users to
> : > : read/write on the database an changed the provstr as follows.
> : > :
> : > : ProvStr =
> : > :
> : >
> :
>

"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"

> : > :
> : > : and also tried
> : > :
> : > : ProvStr = "Server=mucabc01;Database=DSGMGMNT;Integrated
> : > : Security=SSPI;Network=DBMSSOCN;"
> : > :
> : > : my user account from domain B is configured as a .dbo for the

database

> : and
> : > : my access to the DB works fine using other methods of access, ie

from

> a
> : > cmd
> : > : line vbscript using Integrated Security
> : > :
> : > : my global.asa currently looks as follows.......any help or pointers

as

> : to
> : > : what I need to do to get this up and running much appreciated,

thanks

> : > Stuart
> : > : Coney
> : > :
> : > : <SCRIPT LANGUAGE="VBScript" RUNAT="Server">
> : > :
> : > : Sub Session_OnStart ' Runs the first time a user runs any page

in

> : your
> : > : application
> : > : Dim objConn
> : > :
> : > : ' Create an ADO Connection
> : > : Set objConn = Server.CreateObject("ADODB.Connection")
> : > :
> : > : ' Specify the OLE DB provider.
> : > : objConn.Provider = "sqloledb"
> : > :
> : > : ProvStr =
> : > :
> : >
> :
>

"Server=mucabc01;Database=DSGMGMNT;trusted_connect ion=yes;Network=DBMSSOCN;"

> : > :
> : > : objConn.Open ProvStr
> : > : ' Set Session("cnn") = objConn
> : > :
> : > :
> : > : END Sub
> : > :
> : > : Sub Session_OnEnd ' Runs when a user's session times out or

quits

> : > your
> : > : application
> : > :
> : > : 'Close the data connection
> : > : objConn.Close
> : > : set objConn = nothing
> : > :
> : > : END Sub
> : > :
> : > : </SCRIPT>
> : > :
> : > :
> : >
> : >
> :
> :
>
>

[Bob Barrows]

Stuart Coney wrote:

> the link was very informative and I have removed the global.asa from
> the
> app. and created individual connections on a per asp page basis. I
> added "OLE DB Services=-1" to the connection string.

That's overkill. -1 is the default setting, i.e., Pooling is on by default.
It doesn't hurt to put the setting in the connection string, but it is not
needed.

--
HTH,
Bob Barrows - ASP MVP
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.