adding linked server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

adding linked server

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-10-18 : 08:11:02

i'm trying

EXEC sp_addlinkedserver
@server = 'accessdb',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\\xyz\Databases\db.mdb'
GO

I have this working for 1 database but now i'm trying with an access database that has a password in access and i'm obviously getting authentication errors. where do i provide the password

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-18 : 08:49:34

try substituting the value of userid and password in below statements.
1 st try adding the below statement to your existing string.

@provstr='Password=test;User ID=myuserid;'

If above method don't work, then try the below statement.

EXEC sp_addlinkedserver
@server = 'accessdb',
@provstr='Provider=Microsoft.Jet.OLEDB.4.0;Password=test;User ID=myuserid;Data Source=\\xyz\Databases\db.mdb';Persist Security Info=True'

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-10-18 : 08:53:45

quote:

EXEC sp_addlinkedserver
@server = 'accessdb',
@provstr='Provider=Microsoft.Jet.OLEDB.4.0;Password=test;User ID=myuserid;Data Source=\\xyz\Databases\db.mdb';Persist Security Info=True'

there is no username only a password -- what would I put for the username?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-18 : 09:04:15

I never connected access using linked server.