Conn as session var?

[Des Perado]

I have just tried this, and it works:

Global.asa contains:

sub Session_OnStart
set Session("conn")=Server.CreateObject("ADODB.Connection")
dsntemp="driver={sql Server}; server=master1; database=db1; uid=sa; pwd=;
dsn=;"
session("conn").Open dsntemp
end sub

and in any asp:

sql="select * from table1"
set rs=session("conn").execute(sql)

As I say, it works.

But is there any reason we shouldn't do this? Is it bad?

TIA

 

[Bob Barrows [MVP]]

I have just tried this, and it works:

Global.asa contains:

sub Session_OnStart
set Session("conn")=Server.CreateObject("ADODB.Connection")
dsntemp="driver={sql Server}; server=master1; database=db1; uid=sa;
pwd=; dsn=;"
session("conn").Open dsntemp
end sub

and in any asp:

sql="select * from table1"
set rs=session("conn").execute(sql)

As I say, it works.

But is there any reason we shouldn't do this? Is it bad?

Extremely.

http://www.aspfaq.com/2053

Bob Barrows

 

[Des Perado]

Extremely.

http://www.aspfaq.com/2053

Bob Barrows

Whoops! I see.
Many thanks Bob.

 

[Bob Barrows [MVP]]

I have just tried this, and it works:

Global.asa contains:

sub Session_OnStart
set Session("conn")=Server.CreateObject("ADODB.Connection")
dsntemp="driver={sql Server}; server=master1; database=db1; uid=sa;

This is also bad for two reasons:
1. Never use the sa account for you applications. The sa account is an
administrative account with many abilities that should not be granted to
application users. Protect the sa account as if your job depended on it (it
probably does). Create a login account with limited permissions for your
application to use. Also, it your sa account really has no password, go
right now and assign a password to it. Several internet worms target sql
servers whose sa account has no password.

2. The OLEDB Provider for ODBC has been deprecated. You are advised to use
the native OLEDB Provider for SQL Server instead. Your connection string
should look like this:
dsntemp="Provider=SQLOLEDB; Data Source=master1;" & _
"Initial Catalog=db1;User ID=xxxx;Password=xxxx"

Bob Barrows

 

[Ray Costanzo [MVP]]

I applaud you for this. Most people would just stop at "it works."
Instead, you thought, "Hmm, this works, but just because something works
doesn't mean it's right. Let me see if I can get some opinions about this."
That's very cool. I wish that the people I worked with thought that way!
:]

Ray at work

 

[Des Perado]

This is also bad for two reasons:
1. Never use the sa account for you applications. The sa account is an
administrative account with many abilities that should not be granted to
application users. Protect the sa account as if your job depended on it (it
probably does). Create a login account with limited permissions for your
application to use. Also, it your sa account really has no password, go
right now and assign a password to it. Several internet worms target sql
servers whose sa account has no password.

I don't normally Bob, that was just a quick cut 'n' paste from the test
script I wrote on my local machine! But thanks for the advice of course.

2. The OLEDB Provider for ODBC has been deprecated. You are advised to use
the native OLEDB Provider for SQL Server instead. Your connection string
should look like this:
dsntemp="Provider=SQLOLEDB; Data Source=master1;" & _
"Initial Catalog=db1;User ID=xxxx;Password=xxxx"

Thank you. I will have a play with that.

 

[Des Perado]

I applaud you for this. Most people would just stop at "it works."
Instead, you thought, "Hmm, this works, but just because something works
doesn't mean it's right. Let me see if I can get some opinions about this."
That's very cool. I wish that the people I worked with thought that way!
:]

Ray at work

It's kind of you to say that Ray. I should add - and intended to in my
first message - that I did Google for the answer but, as with many similar
concepts, selection of the exact search term was tricky, and I didn't
actually find anything! I felt I needed some expert advice because I
thought there HAD to be a tradeoff if we were saving so much time by not
repeatedly opening a connection to the server with every page - it all
seemed to good to be feasible.