Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problems with VB/ADO/DSN

Posted on 2001-09-10
5
Medium Priority
?
362 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 200 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

604 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