Solved

Problems with VB/ADO/DSN

Posted on 2001-09-10
5
316 Views
Last Modified: 2008-02-01
Hello-

When I switched my VB/ADO code over to use a DSN rather than OLEDB, I ran into an interesting problem. I was returning XML from the db and then using an ADO Stream to access it. When I went to the set the "Output Stream" in the ADO Command properties I learned it was no longer there when I used a DSN, only OLEDB.

That's when I found an article explaining that different providers have different dynamic properties in addition to the standard ones. So, my question is, is there still some way to use a DSN and get this to work? I don't want to have to hardcode my OLEDB string in my code or store it in plain text in a file.

Thanks - BG
0
Comment
Question by:BeerGod
5 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6472750
It can't be done because you ODBC when you use a DSN and ODBC can't let it pass.

I use an INI file for storing different parameters of my connection string. But I encrypt these parameters.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6472838
It looks like your only workaround (read hack) using ODBC, would be to use a recordset in order to save the XML to a stream and even this may not be exactly what you need.

I would suggest you explore your options using OLEDB. Using SQL Server logins to authenticate users, may not be the best way to go in any case.

How are you saving your username/password with ODBC?

Anthony
0
 

Author Comment

by:BeerGod
ID: 6472853
i would just create a dsn in the control panel and it would save the username and hide the password.
0
 
LVL 9

Expert Comment

by:miron
ID: 6473007
Well,
consider this, the DSN parameters are stored in the Windows regitries, the same can be done to  the connection string as a whole, or as a collection of Property = Value pairs. The connection string can be saved in the registries and read from the registries during run - time. Those can be encrypted and kept as securely, as DSN is kept. But if it all does not work, all you need is stored the Recordset ( DSN returns recordset, correct?) and than pass the recordset as a variable to the ADO object and use DSN recordset as a middleware between ADO and SQL Server.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 6473041
One approach you may want to consider, as I alluded to earlier on, is to separate the login to your app from the SQL Server authentication.  This can be achieved, doing the following:
1. Create a table of users, passwords and security levels if neccessary.
2. Create a generic SQL Server "user" for your app (we can call AppUser).
3. Only give access to AppUser to Stored Procedures, no direct access to tables or even Views.
4. In your app connect to the SQL Server database using AppUser hardcoded if neccessary.
5. Only access data using Stored Procedures.

Just my 2 cents worth.
Anthony
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL: Episode III - Revenge of The Dude 24 53
Webservices in T-SQL 3 31
SQL view 2 27
Present Absent from working date rage 11 21
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

803 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