Solved

Problems with VB/ADO/DSN

Posted on 2001-09-10
5
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

724 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