Opening a secured ODBC connection on SQL server

[justme92506 via AccessMonster.com]

I have a database in which I would like the users to be able to run their own
reports off of. In order to do so, they will need to login to the SQL server
due to the fact that the tables are linked in Access. Unfortunately, they do
not have accounts nor are they authorized to the SQL server. Is there a way
that I can use VBA in order to connect them to the SQL server when they open
the Access database. It would have to be opened using my user name and
password. Please advise.

Thanks....

 

[ErezM via AccessMonster.com]

hi there
if you are linking to sql server with odbc (you are creating a dsn file), you
can set the username and password inside the dsn file, and save it with the
connection string.
this is could be dangerous, if your users are dangerous, but otherwise very
easy

you do need to allow user and password logins (mixed mode) for this to work

i have many sql server back-ends to access front-ends, and i always use one
user's login for all users and manage their rights from code

Erez

 

[justme92506 via AccessMonster.com]

Thanks for the info Erez....
Do you have sample code on how to do this. I am fairly new to using a sql
server back end to an access front end. Also, how do manage user rights from
code??

hi there
if you are linking to sql server with odbc (you are creating a dsn file), you
can set the username and password inside the dsn file, and save it with the
connection string.
this is could be dangerous, if your users are dangerous, but otherwise very
easy

you do need to allow user and password logins (mixed mode) for this to work

i have many sql server back-ends to access front-ends, and i always use one
user's login for all users and manage their rights from code

Erez

I have a database in which I would like the users to be able to run their own
reports off of. In order to do so, they will need to login to the SQL server

[quoted text clipped - 5 lines]

Thanks....

 

[ErezM via AccessMonster.com]

all you have to do is select
External data > More > ODBC database (on access 2007)
or
File > Link > ODBC (on older access)

and a wizard will take you through creating the dsn file, setting the user
and password

there is no code i can write for you, cause i need the sql server and user
present in order to make this file

for the other question, about rights, there are many ways, and it depends on

what is your way to identify the user (there is a logon form?, you use the
windows username?)

which access version are you using? (in access2007 i use the new TempVars to
hold user rights, in older versions, i used CurrentDB.Properties)

are your users computer savvys and in active interest to get to information
they are not supposed to? if so, you might need to use the sql server's
security afterall

if' for example i have a TempVars("IsAdmin") variable in my application, i
use it on
Buttons Click event ("If TempVars("IsAdmin")=True Then do 1..2..3, otherwise
ignore the click)
Forms Open event ("If TempVars("IsAdmin")=False Then cancel=1... and the form
will only open for admins)

and so on...

there is no one thing i can send or write, sorry

Erez

Thanks for the info Erez....
Do you have sample code on how to do this. I am fairly new to using a sql
server back end to an access front end. Also, how do manage user rights from
code??

hi there
if you are linking to sql server with odbc (you are creating a dsn file), you

[quoted text clipped - 15 lines]

 

[Rick Brandt]

all you have to do is select
External data > More > ODBC database (on access 2007) or
File > Link > ODBC (on older access)

and a wizard will take you through creating the dsn file, setting the
user and password

The part of that wizard that "looks like" you get to set a user name and
password is not actually making that a saved part of the DSN. That
option is actually letting you supply a user name and password to pull
some default settings from the server. Those entries are not used when
you actually connect with the DSN later on. The user is prompted.

 

[justme92506 via AccessMonster.com]

Thanks ErezM... Its a starting point.

all you have to do is select
External data > More > ODBC database (on access 2007)
or
File > Link > ODBC (on older access)

and a wizard will take you through creating the dsn file, setting the user
and password

there is no code i can write for you, cause i need the sql server and user
present in order to make this file

for the other question, about rights, there are many ways, and it depends on

what is your way to identify the user (there is a logon form?, you use the
windows username?)

which access version are you using? (in access2007 i use the new TempVars to
hold user rights, in older versions, i used CurrentDB.Properties)

are your users computer savvys and in active interest to get to information
they are not supposed to? if so, you might need to use the sql server's
security afterall

if' for example i have a TempVars("IsAdmin") variable in my application, i
use it on
Buttons Click event ("If TempVars("IsAdmin")=True Then do 1..2..3, otherwise
ignore the click)
Forms Open event ("If TempVars("IsAdmin")=False Then cancel=1... and the form
will only open for admins)

and so on...

there is no one thing i can send or write, sorry

Erez

Thanks for the info Erez....
Do you have sample code on how to do this. I am fairly new to using a sql

[quoted text clipped - 6 lines]

 

[justme92506 via AccessMonster.com]

Thanks Rick....
How do I prevent the prompt from the server for user id and password from
displaying???

 

[Rick Brandt]

Thanks Rick....
How do I prevent the prompt from the server for user id and password
from displaying???

With a DSN I'm not sure you can. Perhaps with a file DSN it is possible.

If you use ODBC to create links to server tables you do have the option
to save the user name and password with the link definition. Also if you
create the links with a DSNLess connection string then that can include
the credentials.

The only "secure" way to not have the user prompted is to use trusted
connections so that the user's Windows/Networking credentials are
automatically utilized.

 

[ErezM via AccessMonster.com]

i always use file dsn because it runs faster in access (machine dsn checks
for connection data every connection, file dsn checks for it only once when
access loads) and it's tranfarable among user machines, along with the front
end file/s.
and yes, it saves the user and pwd information (you're saying it's the link
in access that saves it and not the dsn file? i never even bothered to check..
.)