DSN connection w/o password

hi all

i want to connect to SQL Server using DSN.

I create a System DSN, say 'DSN_PWD', provide SQL server authentication in that step and test the connection which works fine.

Now, currently i am using a code by my seniors which is as below

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN_PWD","username","password"
objConn.Close
Set objConn =Nothing

what i am concerned is that this will be saved as ASP file, i don't want any login informatoin to be saved in a physical file openly.

i have tried this but it gives error

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")

objConn.ConnectionString="DSN=DSN_PWD;"
objConn.Open

objConn.Close
Set objConn =Nothing

Error
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user

is there any effective way?

Thanks all

Yogesh JangamYou can hardcode the username and the password within the DSN itself using the ODBC Connection Manager. Is that what you want?

-PatP|||my primary concern being security, i don't want any SQL login information to be saved in any ASP files.

I am creating DSN with SQL Authentication with Login ID/PWD entered by user

i want a method to use this DSN to create an ADODB.Connection w/o manually again typing username and password in objConn.open method

i hope i am clear ;-).......

i have looked for connection code, but most of them have login info in the Open method.

Thanks
Yogesh Jangam|||The easiest way to do this is if your IIS service runs as an NT account. Then just use Windows Authentication for your ODBC connection and you are "good to go" without any fuss at all.

If you must run your IIS Service as LocalSystem, then you need to butcher a DSN to force static SQL authentication. That would be a last resort.

-PatPsql

Drop Table Gets skipped in a query batch

I have a situation in SQL Svr 2k that I can't explain. I have a query batch that runs in a SQL step in a DTS package. All steps execute except the Drop Table statements. Here is a section of the code;

************************************************** ****
INSERT INTO Oral_VitDhistory ( MeNumber, DrugType, Territory, Quantity, SalesAmt, DataType, RXDate )
SELECT tbl_Oral_VitDHistory.MENumber, tbl_Oral_VitDHistory.DrugType, tbl_Oral_VitDHistory.Territory, tbl_Oral_VitDHistory.Quantity, tbl_Oral_VitDHistory.SalesAmt, tbl_Oral_VitDHistory.DataType, tbl_Oral_VitDHistory.RXDate
FROM Data_Warehouse..tbl_Oral_VitDHistory WHERE tbl_Oral_VitDHistory.Territory Is Not Null

drop table Data_Warehouse..NDC24month_Cost
drop table Data_Warehouse..NDC24month_Count

/* Process New Rx */

select MeNumber, zip, RXTypecode, DrugTYpe, NRX1 into NDC24month_Cost from Data_Warehouse..NDC24month where RxTypecode = '$ B'

Insert into tbl_Oral_VitDHistory(MeNumber, DrugType, Quantity, SalesAmt) select a.MeNumber,
a.DrugType, a.NRX1 as Quantity, b.NRX1 as SalesAmt from Data_Warehouse..NDC24month_Count as a, NDC24month_Cost
as b where a.Menumber = b.MeNumber and a.DrugType = b.DrugType
************************************************** *******

The drop table statements appear to get skipped and the code fails because the column names should have been changed as the dropped tables should have been recreated with a different column name.

This used to work fine. Then suddenly it stopped working. No changes to the SQL installation from when it worked to when it stopped. The batch also fails in the QA if cut and pasted and ran from there.

I ran accross this once before in a Stored Proc with the Drop table statement and had to put the statement in a different procedure.

Anybody ever run accross this? I have searched the MS KB to no avail.

Thanks in advance to the genius who can help a guy out!Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.|||Thanks for your help!

Thats about where I thought I would have to go with it.
You confirmed my suspicions!

Have a great one!

Originally posted by sqlguy7777
Howdy,

I have come across this before - usually the best way to get around it is to break the code into separate sprocs & then call each in turn from a main sproc. Even using BEGIN TRAN...COMMIT TRAN wont help....it seems to get a bee in its bonnet and thats it......

Also, if its in DTS package etc , break up the code into logical chunks with end of batch "GO" commands. This will mean separate sprocs if you run it in one sproc currently.

Cheers,

SG.