• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Problems with VB/ADO/DSN

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
BeerGod
Asked:
BeerGod
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Anthony PerkinsCommented:
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
 
BeerGodAuthor Commented:
i would just create a dsn in the control panel and it would save the username and hide the password.
0
 
mironCommented:
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
 
Anthony PerkinsCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now