Solved

Problems with VB/ADO/DSN

Posted on 2001-09-10
5
311 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 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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 …
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 the fundamental information of how to create a table.

743 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

12 Experts available now in Live!

Get 1:1 Help Now