• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

500pts for Seasoned Pros Best Practice for Connecting to a SQL server for ASP Web and VB

What is the best practice for connecting to a SQL server.

I understand that there is ODBC, OLEDB, etc and I would just be interested in the best practice and also for the ODBC. The method I typically use Option A but have been suggested to use B.
Security is a must and is there no way not to have the UID and Password show?

Option A
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "DSN=DB_DatabaseName;UID=DBUser;PWD=Password;DATABASE=DB_DatabaseName"

objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM tblTableName WHERE tblTableName.LastName Like '" & LastName & "'", objConnection, adOpenStatic, adLockOptimistic

Option B
set db = Server.CreateObject("ADODB.Connection")
  db.open "DSN=DB_DatabaseName;UID=DBUser;PWD=Password;DATABASE=DB_DatabaseName"

function openrs(strsql)
  set rs=Server.createobject("ADODB.Recordset")
  rs.open strsql, db, 3, 1
  set openrs=rs
end function

function closers()
  set rs=nothing
end function

set rs=openrs("SELECT * FROM tblTableName WHERE tblTableName.LastName Like '" & LastName & "'")
  • 6
  • 4
  • 2
  • +5
4 Solutions
Anthony PerkinsCommented:
>>Security is a must and is there no way not to have the UID and Password show?<<
Use a Trusted Connection:

And don't use Stored Procedures instead of direct access to the tables with (SELECT, INSERT, UPDATE and DELETE)
Eugene ZCommented:
see examples of connection strings @:
brian_appliedcpuAuthor Commented:
OK what about your opinions,

How about the structure of the RS and Queries...option a vs b

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

"And don't use Stored Procedures instead of direct access to the tables with (SELECT, INSERT, UPDATE and DELETE)"

acperkins, I know the way you are--you totally meant to say AND USE instead of "and don't use" right?

"How about the structure of the RS and Queries...option a vs b"

What is the rest of your question?  I think most people would recommend that you use stored procs and base your recordset off of these.

If you can't use a trusted connection, use a basic account that only has rights to execute stored procs.  While you still have security gaps, this will lessen the risk a bit.  Of course, if security is a BIG must, code your data access routines into a DLL or .NET compiled application--no plain text users/passwords to be seen....
Melih SARICAOwnerCommented:
Use Windows NT Authentication for SQL Server Connection.
 or create a new login and give permissions only for Stored Procs.

Always use Stored Procs to Select , Insert and Update Queries.  Ur New user must ave execute permission to these Stored Procs but Tables.

This is the best Solution not for Coding and also data Managing. Once u want to change the data mining script of ur Application U ave to change the source and redistrubute the executable but when u use stored procs u only change ur SP and then everyting works fine without compiling or redistrubuting any executable.. and Also For Ado DB Connection Use Connection.Execute Method. To run Ur Stored Procs. It also returns Recordset and u can use it without creating an Recordset

   I personally would suggest method A. I have used similar approach both in vb as well as in ASP.
Few more tips....
If you are using advanced SQL procedures then, mention the provider also in the connect string else in future some asp statements dont work.
Inputs for connect string like db name etc can be read from a file this makes changing values easier.
Good luck.
Anthony PerkinsCommented:

>>acperkins, I know the way you are--you totally meant to say AND USE instead of "and don't use" right?<<
Sure.  I wish my typing would follow my brain :)

I believe the best pratice is usually oledb provider string. Using ODBC is kinda klunky and has its issues. Also, if using dot.net they have a special object geared towards SQL which helps to increase performace.
brian_appliedcpuAuthor Commented:
liquiddatainc ...
what kind of issues?
I am curious as I am having intermittent issues in my vb app.  and it appears it is dying at or during the odbc.
Anthony PerkinsCommented:
Any chance we can see some movement on the following old abandoned questions?
1 06/24/2004 250 Recommended Reading Win2k and 2003 Secur...  Open Windows Security
2 06/18/2004 500 Multiple PIX to PIX to PIX hub and spoke...  Open Networking
brian_appliedcpuAuthor Commented:
I was wondering if anyone looks at old questions, in fact I just ran down the list and reviewed the open ones.
1.  Was a non-answer...I still would like to get some better feedback but it looks like no one is interested.
2. This is still an open issue.  I have had 2 cisco engineers working on it for about a month, and they don't know how to make it work either.
Anthony PerkinsCommented:
>>I was wondering if anyone looks at old questions<<
You will find experts more responsive if you maintain your open questions.  Questions here are considered technically abandoned after three weeks.

Here are your options:
How do I accept a comment? http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi68
More than one Expert helped solve my problem. What do I do? http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi68
I answered my question myself. What do I do? http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
Nobody answered my question. What do I do? http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71
What happens if I forget about my question? http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi78

brian_appliedcpuAuthor Commented:
What do you think of non-zero's recommendation to
Use Windows NT Authentication for SQL Server Connection.
I thought it was always best to just use SQL authentication with limited privileges?..that way it is not a windows account and has no other function/privileges on the network.

I also understand that with oledb one is coding not only the username/password but also the SQL machine name.  Is this risk greater than the gain in speed?
Melih SARICAOwnerCommented:
As i can understand Ur not using LDAP or any other Authentication Protocols in ur Web Pages..

So ur Windows NT user ll be IUSR_MachineNAme. And it has no privilages except Web pages and WEB scripts(Should be Like this.)

So a user connecting from web server would not be able to do any other things that u dont permit.

But over all. there is a problem with the SQL Statements and lamers(newbie baby hackers :p ) use these SQL Staments to damage ur Db..

TO protect ur DB from lamers or hackers u musnt Write the SQL select or Update Statements in clean text.

Using SP's to do all data actions ll provide more security.

For Example IUSR_MachineName ll only ave Execute SP privilages. And wont ave any select,,insert or Uptate permissions to ant table of ur db .
so noone will be able to see anything that u wont let in ur SP's.

Hope it helps:

brian_appliedcpuAuthor Commented:
Excellent info...does anyone have a problem with splitting the points
NZ 100
AM 150
AL 100
AC 100
LIQ 100 if he replies

as soon as I hear from liquiddatainc  on the clunky issue I will close this string.

"What do you think of non-zero's recommendation to
Use Windows NT Authentication for SQL Server Connection. "

Guess it all depends on your network setup.  Usually, you have your webserver in a DMZ type setup that's not connected to the SQL Server on the same network, so, WIndows Authentication isn't a possibility....If you're working on an intrenet application, then Windows Authentication is a possibility...
brian_appliedcpuAuthor Commented:
Tough split 125 all around
for nz, am, al, and ac

Thanks for all your experience

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 4
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now