linked server - MS Access - database pwd

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, TadHi Tad
On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
> 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
BOL gives and example for using sp_addlinkedserver to do this:
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'SEATTLE Mktg',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO
Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
if you are using SQL Server logins they will need to be mapped with
sp_addlinkedsrvlogin
which is also described in the link
e.g.
EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
John|||Thanks, John but my read of BOL is that you need user-level security to
implement their examples. My mdb has a database password but no user-level
security.
Tad
"John Bell" wrote:
> Hi Tad
> On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
> > 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
> BOL gives and example for using sp_addlinkedserver to do this:
> USE master
> GO
> -- To use named parameters:
> EXEC sp_addlinkedserver
> @.server = 'SEATTLE Mktg',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
> GO
> Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
> if you are using SQL Server logins they will need to be mapped with
> sp_addlinkedsrvlogin
> which is also described in the link
> e.g.
> EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
> John
>
>|||Hi
Database passwords will not work with linked servers and they appear to be
nothing but obfuscation see
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/813a1960b96a26a8?hl=en&
John
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:5274D022-03CD-4D8F-9F73-B3EC4D4F18EA@.microsoft.com...
> Thanks, John but my read of BOL is that you need user-level security to
> implement their examples. My mdb has a database password but no
> user-level
> security.
> Tad
> "John Bell" wrote:
>> Hi Tad
>> On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
>> > 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
>> BOL gives and example for using sp_addlinkedserver to do this:
>> USE master
>> GO
>> -- To use named parameters:
>> EXEC sp_addlinkedserver
>> @.server = 'SEATTLE Mktg',
>> @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> @.srvproduct = 'OLE DB Provider for Jet',
>> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
>> GO
>> Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
>> if you are using SQL Server logins they will need to be mapped with
>> sp_addlinkedsrvlogin
>> which is also described in the link
>> e.g.
>> EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
>> John
>>|||Thanks, John - I suspected as much but that's good ammunition to take to client
"John Bell" wrote:
> Hi
> Database passwords will not work with linked servers and they appear to be
> nothing but obfuscation see
> http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/813a1960b96a26a8?hl=en&
> John
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:5274D022-03CD-4D8F-9F73-B3EC4D4F18EA@.microsoft.com...
> > Thanks, John but my read of BOL is that you need user-level security to
> > implement their examples. My mdb has a database password but no
> > user-level
> > security.
> >
> > Tad
> >
> > "John Bell" wrote:
> >
> >> Hi Tad
> >>
> >> On Jun 14, 7:00 am, Tadwick <Tadw...@.discussions.microsoft.com> wrote:
> >> > 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
> >> BOL gives and example for using sp_addlinkedserver to do this:
> >>
> >> USE master
> >> GO
> >> -- To use named parameters:
> >> EXEC sp_addlinkedserver
> >> @.server = 'SEATTLE Mktg',
> >> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> >> @.srvproduct = 'OLE DB Provider for Jet',
> >> @.datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
> >> GO
> >>
> >> Also see http://msdn2.microsoft.com/en-us/library/aa172697(sql.80).aspx
> >> if you are using SQL Server logins they will need to be mapped with
> >> sp_addlinkedsrvlogin
> >> which is also described in the link
> >> e.g.
> >> EXEC sp_addlinkedsrvlogin 'SEATTLE Mktg', 'false', 'sa', 'Admin', NULL
> >>
> >> John
> >>
> >>
> >>
>
>