|
Home > Archive > SQL Anywhere database > July 2005 > ODBC - need help with DSN-LESS connection
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ODBC - need help with DSN-LESS connection
|
|
|
| Hello everyone:
I deployed SQL Anywhere 9 and have set up DSN's to access the db properly.
I have a need to restrict access to the database to "read-only" if accessing
outside the main application.
I am concerned about savvy users being able to link to the DB tables via MS
access provided they know the DSN name.
What I want to be able to do is:
1) keep the current DSN configuration but give the current user on the DSN
"Read-Only" access
2) From within the authorized application, switch the links on all the
tables to log in as another user and password (one with ALL permissions).
So far I've found how to refresh the links for each table (see code below),
but the refresh doesn't seem to register the NEW userID and password,
because I tested it for one table (which I'm expecting that I should have
ALL access to that one table), and that one table still has the read only
access:
PseudoCODE below:
For Each table in db.TableDefs
db.tabledefs(i).connect = _
"ODBC;DSN=<DSN name with orig ID and PW on it>;uid=<new user
ID>;pwd=<password>;TABLE=<OWNER>.<tblName>"
db.tabledefs(i).RefreshLink
Next
I've also tried to remove the "DSN=" clause, but when I do that I am
prompted by a dialog box to specify the DSN
I've also tried a different connection string to see if that worked as well:
--> "ODBC;eng=<server name>;dbn=<database Name>;uid=<new user
ID>;pwd=<password>;dsn=<DSN name with orig ID and PW>"
As always I am very grateful for all your valuable help during these past 2
weeks, thank you in advance for your help with this important matter.
Sincerely,
Paul Grossman
PAULGRO Consulting LLC
| |
|
| Hi Paul,
This may not suit your needs, but here's a different approach:
If your users read-access isn't otherwise restricted, I'd setup the DSN with
a common read-only access user (like a guest account).
In your app you can re-connect to the 'real' user with write-access
and password. Should you worry about users trying to login with their own
uid/pwd outside the app, you can always modify their password in your app.
regards/Peter Simonsen
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42ca036d$1@foru
ms-1-dub...
> Hello everyone:
>
> I deployed SQL Anywhere 9 and have set up DSN's to access the db properly.
>
> I have a need to restrict access to the database to "read-only" if
accessing
> outside the main application.
> I am concerned about savvy users being able to link to the DB tables via
MS
> access provided they know the DSN name.
>
> What I want to be able to do is:
> 1) keep the current DSN configuration but give the current user on the
DSN
> "Read-Only" access
> 2) From within the authorized application, switch the links on all the
> tables to log in as another user and password (one with ALL permissions).
>
> So far I've found how to refresh the links for each table (see code
below),
> but the refresh doesn't seem to register the NEW userID and password,
> because I tested it for one table (which I'm expecting that I should have
> ALL access to that one table), and that one table still has the read only
> access:
>
> PseudoCODE below:
>
> For Each table in db.TableDefs
> db.tabledefs(i).connect = _
> "ODBC;DSN=<DSN name with orig ID and PW on it>;uid=<new
user
> ID>;pwd=<password>;TABLE=<OWNER>.<tblName>"
> db.tabledefs(i).RefreshLink
> Next
>
> I've also tried to remove the "DSN=" clause, but when I do that I am
> prompted by a dialog box to specify the DSN
> I've also tried a different connection string to see if that worked as
well:
> --> "ODBC;eng=<server name>;dbn=<database Name>;uid=<new user
> ID>;pwd=<password>;dsn=<DSN name with orig ID and PW>"
>
> As always I am very grateful for all your valuable help during these past
2
> weeks, thank you in advance for your help with this important matter.
>
> Sincerely,
> Paul Grossman
> PAULGRO Consulting LLC
>
>
>
| |
| Breck Carter [TeamSybase] 2005-07-05, 7:23 am |
| Here's a WAG... try leaving the password out of the DSN, or (better
yet) put an invalid but real-looking password in the DSN (just to
drive the hackers nuts :)
Then, if you can successfully connect from the application (never a
safe assumption, not in *my* world :), you will know you did it with
the password you specified in the application.
But you asked about DSN-less connections; here is a PowerBuilder
sample:
SQLCA.DBMS = 'ODB'
SQLCA.DBParm &
= " ConnectString='Drive
r=Adaptive Server Anywhere 8.0;" &
+ "UID=DBA;PWD=SQL;" &
+ "START=C:\Program Files\Sybase\SQL Anywhere
8\win32\dbeng8.exe;" &
+ "DBF=" &
+ aaa &
+ "'"
CONNECT USING SQLCA;
I think you can substitute ENG= and DBN= for the START and DBF, if the
database is already running.
Breck
On 4 Jul 2005 20:50:05 -0700, "Paul" <paul.grossman@paulgro.com>
wrote:
>Hello everyone:
>
>I deployed SQL Anywhere 9 and have set up DSN's to access the db properly.
>
>I have a need to restrict access to the database to "read-only" if accessing
>outside the main application.
>I am concerned about savvy users being able to link to the DB tables via MS
>access provided they know the DSN name.
>
>What I want to be able to do is:
>1) keep the current DSN configuration but give the current user on the DSN
>"Read-Only" access
>2) From within the authorized application, switch the links on all the
>tables to log in as another user and password (one with ALL permissions).
>
>So far I've found how to refresh the links for each table (see code below),
>but the refresh doesn't seem to register the NEW userID and password,
>because I tested it for one table (which I'm expecting that I should have
>ALL access to that one table), and that one table still has the read only
>access:
>
>PseudoCODE below:
>
>For Each table in db.TableDefs
> db.tabledefs(i).connect = _
> "ODBC;DSN=<DSN name with orig ID and PW on it>;uid=<new user
>ID>;pwd=<password>;TABLE=<OWNER>.<tblName>"
> db.tabledefs(i).RefreshLink
>Next
>
>I've also tried to remove the "DSN=" clause, but when I do that I am
>prompted by a dialog box to specify the DSN
>I've also tried a different connection string to see if that worked as well:
> --> "ODBC;eng=<server name>;dbn=<database Name>;uid=<new user
>ID>;pwd=<password>;dsn=<DSN name with orig ID and PW>"
>
>As always I am very grateful for all your valuable help during these past 2
>weeks, thank you in advance for your help with this important matter.
>
>Sincerely,
>Paul Grossman
>PAULGRO Consulting LLC
>
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
|
| Hi Peter,
That's what I'm looking to do. However I tried to have the app log on as a
more powerful user and I get th -7778 error.
<Peter Simonsen> wrote in message news:42ca3970@forums
-1-dub...
> Hi Paul,
>
> This may not suit your needs, but here's a different approach:
>
> If your users read-access isn't otherwise restricted, I'd setup the DSN
> with
> a common read-only access user (like a guest account).
>
> In your app you can re-connect to the 'real' user with write-access
> and password. Should you worry about users trying to login with their own
> uid/pwd outside the app, you can always modify their password in your app.
>
> regards/Peter Simonsen
>
> "Paul" <paul.grossman@paulgro.com> wrote in message
> news:42ca036d$1@foru
ms-1-dub...
> accessing
> MS
> DSN
> below),
> user
> well:
> 2
>
>
| |
|
| Hi Breck,
Thank you very much for the info.
I tried doing what you suggested last night and got a -7778 error
(unidentified error).
Here's the connection string I used at first:
ODBC;eng=<servername>;DBN=<dbname>;uid=<new user>ID>;pwd=<password>"
When I used this, I got a dialog box asking for the DSN name, otherwise it
wouldn't let me thru.
How can I invoke the procedure you show below without getting prompted to
provide the DSN name?
Thank you again for your help,
Paul.
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:uetkc190cp639bv
sskitanq3unu4h0n7eq@
4ax.com...
> Here's a WAG... try leaving the password out of the DSN, or (better
> yet) put an invalid but real-looking password in the DSN (just to
> drive the hackers nuts :)
>
> Then, if you can successfully connect from the application (never a
> safe assumption, not in *my* world :), you will know you did it with
> the password you specified in the application.
>
> But you asked about DSN-less connections; here is a PowerBuilder
> sample:
>
> SQLCA.DBMS = 'ODB'
> SQLCA.DBParm &
> = " ConnectString='Drive
r=Adaptive Server Anywhere 8.0;" &
> + "UID=DBA;PWD=SQL;" &
> + "START=C:\Program Files\Sybase\SQL Anywhere
> 8\win32\dbeng8.exe;" &
> + "DBF=" &
> + aaa &
> + "'"
> CONNECT USING SQLCA;
>
> I think you can substitute ENG= and DBN= for the START and DBF, if the
> database is already running.
>
> Breck
>
> On 4 Jul 2005 20:50:05 -0700, "Paul" <paul.grossman@paulgro.com>
> wrote:
>
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
> http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Breck Carter [TeamSybase] 2005-07-05, 8:23 pm |
| Add this
Driver=Adaptive Server Anywhere 8.0;
or 9.0.
Breck
On 5 Jul 2005 07:16:18 -0700, "Paul" <paul.grossman@paulgro.com>
wrote:
>Hi Breck,
>
>Thank you very much for the info.
>
>I tried doing what you suggested last night and got a -7778 error
>(unidentified error).
>Here's the connection string I used at first:
>ODBC;eng=<servername>;DBN=<dbname>;uid=<new user>ID>;pwd=<password>"
>When I used this, I got a dialog box asking for the DSN name, otherwise it
>wouldn't let me thru.
>
>How can I invoke the procedure you show below without getting prompted to
>provide the DSN name?
>
>Thank you again for your help,
>Paul.
>
>
>
>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
>message news:uetkc190cp639bv
sskitanq3unu4h0n7eq@
4ax.com...
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
|
| Hi Paul,
I have no problems doing this on the ASA9 demo database.
I created a user with only select access and set this user in the dsn.
In the app, if I connect like this:
SQLCA.DBParm = " ConnectString='DSN=A
SA 9.0 Sample;UID=dba;PWD=s
ql'"
then I connect as dba with full access.
On the other hand if I connect like this:
SQLCA.DBParm = " ConnectString='DSN=A
SA 9.0 Sample'"
then I connect with the user specified in the dsn, i.e. with select access
only.
BTW I'm using 9.0.2, but I don't see how this shouldn't work in previous
versions.
regards/Peter Simonsen
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42ca955f@forums
-1-dub...
> Hi Peter,
>
> That's what I'm looking to do. However I tried to have the app log on as
a
> more powerful user and I get th -7778 error.
>
>
>
>
> <Peter Simonsen> wrote in message news:42ca3970@forums
-1-dub...
own[color=darkred]
app.[color=darkred]
via[color=darkred]
permissions).[color=darkred]
have[color=darkred]
only[color=darkred]
past[color=darkred]
>
>
| |
|
| Hi Peter,
Thanks for the info (again). I really appreciate your help.
I used the DSN and just overrode the UID and PWD and that worked. I read in
a white paper that Access has to have a DSN (it didn't say that you couldn't
do DSN-Less connections, but after three 12+ hour days of researching I came
up with nothing that shows or says Access can do a DSN-less connection.
Anyhow, the method I used above works so that's what I'm going to go with.
Now I have to figure out how to set the Connection name to be the computer
name (so far I'm trying to use the CON parm of the connection string but so
far that's not working - the search continues...)
Thanks so much again,
Paul.
<Peter Simonsen> wrote in message news:42cb8679$1@foru
ms-2-dub...
> Hi Paul,
>
> I have no problems doing this on the ASA9 demo database.
> I created a user with only select access and set this user in the dsn.
>
> In the app, if I connect like this:
> SQLCA.DBParm = " ConnectString='DSN=A
SA 9.0 Sample;UID=dba;PWD=s
ql'"
> then I connect as dba with full access.
>
> On the other hand if I connect like this:
> SQLCA.DBParm = " ConnectString='DSN=A
SA 9.0 Sample'"
> then I connect with the user specified in the dsn, i.e. with select access
> only.
>
> BTW I'm using 9.0.2, but I don't see how this shouldn't work in previous
> versions.
>
> regards/Peter Simonsen
>
>
> "Paul" <paul.grossman@paulgro.com> wrote in message
> news:42ca955f@forums
-1-dub...
> a
> own
> app.
> via
> permissions).
> have
> only
> past
>
>
| |
| Chris Keating \(iAnywhere Solutions\) 2005-07-07, 7:23 am |
| None of your sample connection strings include "DRIVER=" which is a core
requirement for a DSNless connection.
"Paul" <paul.grossman@paulgro.com> wrote in message
news:42ca9632@forums
-1-dub...
> Hi Breck,
>
> Thank you very much for the info.
>
> I tried doing what you suggested last night and got a -7778 error
> (unidentified error).
> Here's the connection string I used at first:
> ODBC;eng=<servername>;DBN=<dbname>;uid=<new user>ID>;pwd=<password>"
> When I used this, I got a dialog box asking for the DSN name, otherwise it
> wouldn't let me thru.
>
> How can I invoke the procedure you show below without getting prompted to
> provide the DSN name?
>
> Thank you again for your help,
> Paul.
>
>
>
> "Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> message news:uetkc190cp639bv
sskitanq3unu4h0n7eq@
4ax.com...
>
>
| |
|
| I originally had a string that included DRIVER=Adaptive Server Anywhere 9.0
That also didn't work.
What I did was keep the DSN in the connection string, and just overrode the
UID and PWD parameters. From what I found from this experience it seems as
though I can not connect / link tables from code without including the DSN.
Keeping the DSN and overriding the UID and PWD parms is what I found that
worked.
Thanks again for all your help
"Chris Keating (iAnywhere Solutions)" < Spam_NoThanks_keatin
g@iAnywhere.com>
wrote in message news:42cd21a6$1@foru
ms-2-dub...
> None of your sample connection strings include "DRIVER=" which is a core
> requirement for a DSNless connection.
>
>
> "Paul" <paul.grossman@paulgro.com> wrote in message
> news:42ca9632@forums
-1-dub...
>
>
| |
| Steve M 2005-07-09, 8:23 pm |
| There is no need for a DSN. We use ASA all the time and never create a DSN.
If local / standalone try adding:
-x NONE
If connecting to server try adding:
CommLinks=ALL
combined with
EngineName=whatever
DatabaseName=whateve
r
(There are more efficient ways of choosing the correct communications protocol,
but that should work for all types.)
--
Steve
[color=darkred]
> I originally had a string that included DRIVER=Adaptive Server
> Anywhere 9.0
>
> That also didn't work.
>
> What I did was keep the DSN in the connection string, and just
> overrode the UID and PWD parameters. From what I found from this
> experience it seems as though I can not connect / link tables from
> code without including the DSN.
>
> Keeping the DSN and overriding the UID and PWD parms is what I found
> that worked.
>
> Thanks again for all your help
>
> "Chris Keating (iAnywhere Solutions)"
> < Spam_NoThanks_keatin
g@iAnywhere.com> wrote in message
> news:42cd21a6$1@foru
ms-2-dub...
>
|
|
|
|
|