Solved

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

Posted on 2004-08-16
18
375 Views
Last Modified: 2006-11-17
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()
  rs.close
  set rs=nothing
end function

set rs=openrs("SELECT * FROM tblTableName WHERE tblTableName.LastName Like '" & LastName & "'")
0
Comment
Question by:brian_appliedcpu
  • 6
  • 4
  • 2
  • +5
18 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 11815973
>>Security is a must and is there no way not to have the UID and Password show?<<
Use a Trusted Connection:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

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

Expert Comment

by:EugeneZ
ID: 11816364
see examples of connection strings @:
http://www.connectionstrings.com
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11816927
OK what about your opinions,

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

0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 125 total points
ID: 11817529
"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....
0
 
LVL 19

Assisted Solution

by:Melih SARICA
Melih SARICA earned 125 total points
ID: 11817940
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


Melih SARICA
0
 
LVL 1

Assisted Solution

by:amulya_333
amulya_333 earned 125 total points
ID: 11818672
Hi,
   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.
Amulya.
   
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11821041
arbert,

>>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 :)

0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11823071
0
 

Expert Comment

by:liquiddatainc
ID: 11826265
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11857372
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11857383
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
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11857394
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11857420
>>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


0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11857518
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?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 11857749
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:


Melih SARICA
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11858869
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.

0
 
LVL 34

Expert Comment

by:arbert
ID: 11872969
"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...
0
 
LVL 2

Author Comment

by:brian_appliedcpu
ID: 11875675
Tough split 125 all around
for nz, am, al, and ac

Thanks for all your experience

bkl
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now