Understanding SQL Connection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have some linked SQL 2005 tables in my Access front end. I went through
the linked table wizard and created a DSN. I then went into the DSN and
added the correct username and password that 'should' authenticate Access
each time it needs to connect. Sample DSN below:

[ODBC]
DRIVER=SQL Native Client
UID=username
pwd=password
WSID=myComputer
APP=Microsoft Office 2003
SERVER=mySQLServer

Even with this code, Access keeps asking for credentials when I open a form
where these linked tables are used. How do I edit a DSN file so that Access
will use the credentials in the file to authenticate to the SQL server?

I think I'm missing some basic understanding regarding how Access goes
through the authentication steps.

Can someone point me in the right direction here?

Thanks in advance,
Bernie
 
bernadou said:
I have some linked SQL 2005 tables in my Access front end. I went through
the linked table wizard and created a DSN. I then went into the DSN and
added the correct username and password that 'should' authenticate Access
each time it needs to connect. Sample DSN below:
Click to expand...

Look at your DSN entry again where you entered a UserName and Password. Those
entries are not what you think they are. They are associated with the checkbox
labeled...

Connect to SQL Server to obtain default settings for the additional
configuration options.

In other words it means "While I am creating this DSN use these credentials to
get some default settings from the server". It does not mean that those
credentials are automatically passed when the DSN is actually used later on.

Now...when you create linked tables using that DSN you will have to supply
credentials and you should be given the option to save them as part of the table
link definition. That would allow for that link to be used without supplying
credentials later on. If the link is refreshed later on though you might lose
the credentials and the link would have to be deleted and recreated to put them
back.

With SQL Server it is best to set it up using Trusted Connections (Integrated
Security). This passes the Windows Domain credentials to the server so the user
is never prompted.
 
Rick,
Thanks again! You did exactly what I asked.... pointed me in the right
direction. After looking carefully at the linked table setup dialog boxes, I
finally came to the "Save Password" checkbox tucked neatly at the bottom
right of the "Link Table" dialog box. ( Seems like a very strange place to
put that option, but, what do I know?)

Based on my understanding, we won't be able to use NT security due to the
production SQL server being hosted by a 3rd party on an external domain.

So, this leads me to my follow-ups. Under what conditions would my
application need to 'refresh the link' to the tables, thereby requiring the
deletion and re-linking you mentioned in your post? Is there a way to ensure
the tables stay linked, with the SQL username and password I supply during
the table linking steps? Does Access keep this information somewhere that
can be programatically controlled? Is there just a better way to do this
then using the standard linked table senario?

Thanks,
Bernie
 
bernadou said:
Rick,
Thanks again! You did exactly what I asked.... pointed me in the
right direction. After looking carefully at the linked table setup
dialog boxes, I finally came to the "Save Password" checkbox tucked
neatly at the bottom right of the "Link Table" dialog box. ( Seems
like a very strange place to put that option, but, what do I know?)

Based on my understanding, we won't be able to use NT security due to
the production SQL server being hosted by a 3rd party on an external
domain.

So, this leads me to my follow-ups. Under what conditions would my
application need to 'refresh the link' to the tables, thereby
requiring the deletion and re-linking you mentioned in your post? Is
there a way to ensure the tables stay linked, with the SQL username
and password I supply during the table linking steps? Does Access
keep this information somewhere that can be programatically
controlled? Is there just a better way to do this then using the
standard linked table senario?
Click to expand...

It might not ever be necessary, particularly with a server back end, but many
developers include code to re-link tables either automatically at startup or
when initiated by the user. I just wanted to point out that doing so might not
result in a link that retains all of the information that was in the original
"manually created" one. If you have no such relinking mechanism in place then
you shouldn't have to worry about it.
 
Back
Top