Linking to Access from SQL Server (linked server)

[Tadwick]

From SQL Server 2000 I have to link to an MS Access file that has a database
password but no user-level security (as far as I'm aware). I could not get
the OLE DB provider for Jet to authenticate and instead had to create an ODBC
System DSN
for the Access database and then use OLE DB for ODBC databases as my linked
server provider.

Is is it possible to create a linked server using the OLE DB provider for
Jet in this scenario so I don't have to manage the ODBC System DSN?

Thanks, Tad

 

['69 Camaro]

Hi, Tad.

From SQL Server 2000 I have to link to an MS Access file

The recommended solution is to migrate the data in the Jet tables to SQL
Server tables, then link to those tables with the Linked Table Manager from
within Access. Any data input, updated, or deleted from the Access
interface would actually occur in the SQL Server tables. There would be no
need for SQL Server to use a linked server, and the data in the tables would
have all of the benefits of a client/server database (i.e., transaction log,
backups, security, scalability, et cetera), while still maintaining the
Access front end database application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tadwick]

well of course it would be great to move everything to SQL Server but I don't
have that luxury unfortunately.

 

['69 Camaro]

Hi, Tad.

well of course it would be great to move everything to SQL Server but I
don't
have that luxury unfortunately.

Tell them that you can't enforce referential integrity on the data unless
all of the tables are in the same database file (or file group). The
Microsoft Access file is a separate file from the main data, so either you
can move all the data from SQL Server into the Microsoft Access database
file (and it had better fit inside 2 GB), or else you can move the data from
the Microsoft Access database file to tables in SQL Server. Their choice.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[DAVID]

You know that's a fairly uncommon scenario :~)
Especially now that SQL Server includes it's
own version of Jet replication, and Jet access
is turned off by default ...

So I don't know if you will ever find anyone else
who has tried it.

There is no obvious reason why OLEDB should fail
to authenticate. Do you have any other OLEDB
consumers on the server that you can test with?
For example, do you have a scripting language
like VBS or JS installed?

The possiblilities that spring to mind are

1) File permissions. Where is the data mdb
installed? Where is the system.mdb installed?
Does your SQL Server have permission to create
ldb files in these areas?

2) SQL Server configuration. Is your SQL Server
configured to allow OLEDB linked servers or tables?

3) Login credentials. Is your SQL Server trying
to connect using SQL Server or Windows login
credentials?

(david)

 

[Tadwick]

David,

I agree that the legacy scenario is unusual - I don't spend much time with
Access these days and I'm trying to determine what the difference in
authentication is, if any, between user-level security and a database
password.

Tad

 

[DAVID]

Access these days and I'm trying to determine what the

difference in authentication is, if any, between user-
level security and a database password.

A database password is a property of the database.

User-level security is user names, passwords, groups,
etc, stored in tables in a separate database, by default
"system.mda", sometimes called "the workgroup" or
"the workgroup database" or "the system database",
used to lookup security ID strings.

Security ID strings are properties of every item
in a database, and of the database object it's self.

To use a database, you either need the database
password (if it is a password secured database),
or you need a database permission security ID string,
(which you lookup in the workgroup database).


So one critical difference is that with a password
secured database, you normally use the default
workgroup database, and with user-level security,
you normally need to specify the correct workgroup
database.

(david)

 

[Tadwick]

Thanks,

I also got this response on the SQL newsgroups

http://www.microsoft.com/communitie...rver&mid=4c7c2b07-a7e0-4970-af39-c8ff49d548fb

Tad

 

[Guest]

You are ALWAYS using user-level security.

By default, you use the default user name, the default
password, and the default security database.

The default user name is 'admin', the default password
is blank, and the default security database always
returns a well-known security ID string for that user.

You may find that you have to provide an explicit
mapping for the login credentials required by the
Access database, even when only the default values are
used.

(david)

 

[Guest]

The comments about the security of database passwords
are a little old: I don't know if they are still relevant. Certainly
the free code referenced in that posting is no longer relevant:
it only worked for 95-98 databases, and the password
was strengthened for 2000-2003 databases. It may have
been strengthened again for 2007 databases.

Regardless, the most secure way to secure a Jet database
is to put it on a stand-alone PC in a locked room.

(david)

 

[Sylvain Lafontaine]

Did you try the parameter « Jet OLEDBatabase Password=MyDbPassword; » in
the connection string?

See:
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet