Connecting to SQL database

misskaos · October 20th, 2006, 08:13 AM

QUESTION:
When you are connecting to a database in an ASP page, is there a way you can connect without having to put the username/password into the Connection String, if you have a password set for the sa? I would like to prevent having to write the password in the code for security reasons.

Please advise if possible.

Note: I am connecting using the database provider, oledb.
Thank you!

Toni Burgess
[email protected]

ricespn · October 20th, 2006, 10:14 AM

You can create a DLL file in VB and have the conection string there.

=======================
Strange and crazy, but everything is possible

ricespn · October 20th, 2006, 10:17 AM

I forgot!.. Check out this article http://www.asp101.com/articles/carvi...ss/default.asp

=======================
Strange and crazy, but everything is possible

ricespn · October 21st, 2006, 02:02 AM

Here is another option using an include file, this one inserts a record but you can modify it to your needs..

This part goes in your include file "dbConn.asp"
<%
Option Explicit
DIM strConnect ' Variable to hold connection string
strConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=ECPTool2;Data Source=(local)" ' Assign Connection String to variable
%>

This part goes in your "postnew.asp"

<%
DIM objComm 'Variable to Hold Command Object
Const adCmdText = &H0001 'Constatnt to Hold
SET objComm =Server.CreateObject ("ADODB.Command") ' Create Command Object
objComm.ActiveConnection = strConnect ' Set the connection active usng the string value from the include file
objComm.CommandText = "INSERT INTO ecpForms (fields) VALUES ('" & field values & "')" 'Insert Values Into Table ecpForms
objComm.CommandType = adCmdText 'Set command type to value &H0001 This value was taken from the ADO constant Library file located at : C:\Program Files\Common Files\System\Ado\adovbs.inc
objComm.Execute 'Execute transaction
SET objComm = NOTHING 'Clear object from memory
%>

=======================
Strange and crazy, but everything is possible

dparsons · October 21st, 2006, 02:35 PM

Alternatively, you can store the connection string in the global.asa and pull it from there.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

cancer10 · October 22nd, 2006, 11:37 PM

Still if we put the connection string in global.asa file then we cannot hide it from anyone to whom we are giving the source code.

Right?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 23rd, 2006, 06:15 AM

The only way to "hide" the connection string would be to compile the file into a .DLL, otherwise anyone with access to the source code will be able to find the connection string. DLL's are not full proof as you can use a disassembler on them and convert them back to source code, but you get the idea.

However, you shouldn't be using the built in SQL Administrator account (sa) any way for rudiementary database calls, because that account has full rights to execute ANY command in the database like, say, DROP TABLE!! (Not to mention access to the master database)

Personally I have seperate, "dummy" user accounts that I use to preform my database operations; each with limited rights to the database's they work with. Anyway thats my 2 cents.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

misskaos · October 23rd, 2006, 04:43 PM

I have used an include file, thank u.

The include file works as far as viewing and inserting the database records.... Now I want to test updating them. This is not working (detail: It will not save the changed information to the database).

Now, I've looked up several different ways to this,and I have tried all that I could find, that seemed logical and didn't require me to change too much of the code.
After my 40th attempt, this is where I stand:
[u]postModify.asp</u>
....[omitted code]....

Code:

%>

<%
mEcpNumber = RS("ecpNumber")
mEcpTitle = RS("ecpTitle")
rs.open "Select * from ecpForms where ID = " & mEcpID & " ", Connection
rs.open "UPDATE ecpForms set ecpMV22PilotImpact = '" & mEcpMV22PilotImpact & "'," & _ 
" set ecpMV22AircrewImpact = '" & mEcpMV22AircrewImpact & "'," & _ 
" ecpMV22MaintenanceImpact = '" & mEcpMV22MaintenanceImpact  & "'," & _ 
" ecpCV22AircrewImpact = '" & mEcpCV22AircrewImpact & "'," & _ 
" ecpCV22MaintenanceImpact = '" & mEcpCV22MaintenanceImpact & "'," & _
" ecpSubmitEmail = '" & mEcpSubmitEmail & "'," & _
" ecpCostSubmitEmail = '" & mEcpCostSubmitEmail & "' where ID = " & mEcpID & "", Connection
'RS.Update
RS.Close
Response.Redirect("viewAll.asp")
%>
<html>
<head>
<title>Message</title>
</head>
<body bgcolor="#ABCDEF">
</body>
</html>

[u]dbConn.asp </u>

Code:

<%
Dim Connection, RS
Dim recount
Connection = "Provider=SQLOLEDB.1;UID=sa;PWD=password;Initial Catalog=ECPTool2;Data Source=webserver"
Set RS = server.CreateObject("ADODB.RecordSet")
%>

Please assist whenever/however you can. Thanks in advance.

Errors: Without the <% On Error Resume Next %> it goes to an
HTTP: 500 Internal Server Error page and says the page cannot be displayed.
Note: I have my permissions set for read/write/etc
T.B.
[email protected]

dparsons · October 23rd, 2006, 05:57 PM

For your SELECT use the rs.open, for the update, only use your connection object:

Connection.Open [Your update statement]

That should work for you =]

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature