SQL Server - How to manage SQL server Authentication in Access2007

Asked By Amos Sobel

17-Nov-09 10:29 AM

I have an Access 2007 application using an MSSQL 2005 Database and Views. The
SQL 2005 is using SQL Server authentication with a password. The ODBC
connector is using the same password. On each activation of the Access
aplication, I am requested to enter the sa user password (authentication) one
or more times and this have become a hassle.
How do I enter the SQL authentication password into the Access aplication
such that it will enter the sa password whenever nessasary.

your help is very wellcome

Amos Sobel

18-Nov-09 05:41 AM

Hi Amos,

Are you using User DSN or System DSN. If you are using User DSN try with
system DSN.

Regards, Balaji

18-Nov-09 12:10 PM

What you are doing -- using the sa login with a password -- is
extremely dangerous. Access caches login information in clear text,
and any attacker who has the sa login and password can take over not
only your SQL Server, but can potentially compromise your entire
network. DSNs are also saved on the local machine in clear text, so
they are a security vulnerability all by themselves. I would urge you
to run the following search and follow the recommendations:
http://www.bing.com/search?q=sql+server+security+best+practices

Here is a paper that will help you understanding how Access and SQL
Server work together:

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

--Mary

19-Nov-09 03:24 PM

Dear Balaji

I did try your advice and canceled my User DSN ODBC entries and made System
DSN ODBC entries instead. The result: Access 2007 does report: ODBC
connection to 'XXX' failed. How do I use System DSN properly?

Amos Sobel