SQL Server Performance

Linked server issue SQL server 2000 and MS Access 97

Discussion in 'General DBA Questions' started by AJITH123, Dec 16, 2009.

  1. AJITH123 Member

    I am having an issue while creating MS Access as a linked server in SQL server 2000. When i tried the .mdb file without password, it is working perfectly , i can see all the table in the database. But my concern is, when i try to link the password protected .mdb file, it is not working. Below are the statement i exected for creating the linked server. The error i am getting is Authenticattion faild while quering the password protected access database. Can anybody give solution for this?

    sp_addlinkedserver 'Acces', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:Test.mdb'
    sp_addlinkedsrvlogin 'Acces', false, 'sa', 'Admin', NULL
    sp_addlinkedserver 'Acces_1', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:Test1.mdb'
    sp_addlinkedsrvlogin 'Acces_1', false, 'sa', 'Admin', 'mypassword'
  2. FrankKalis Moderator

    Have you already tried something like this?

    EXEC sp_addlinkedserver
    @server = 'MyAccessDatabase',
    @provider = 'MSDASQL',
    @srvproduct = 'Microsoft Access',
    @provstr = 'DRIVER=Microsoft Access Driver
    (*.mdb);DBQ=<full path to your Access database>;PWD=YourPwd'
  3. AJITH123 Member

    I am getting the below message.
    "Data source name not found and no default driver specified"
  4. Adriaan New Member

    Why not use the wizard to set up the linked server?
  5. AJITH123 Member

    Thanks Adriaan.
    I just created a DSN and use the wizard to set up the linked server.
  6. Adriaan New Member

    Also, there are two possible password setups with MDB files. There's the optional password for the MDB file itself, like Frank showed. In general it is not recommended to set a password on the MDB file, because this way it becomes very easy to lock everyone out, and render the data completely inaccessible. (I believe there are MDB-password-removal-tools floating around the internet.)
    The other option is that the user ID and passwords are stored in an MDW (workgroup) file. In this setup, specific permissions within the MDB can be granted to specific users or user groups. You would need a copy of the correct MDW file, because there is an underlying UserID for the users and groups that is used as a double-check. In this setup, the MDW needs to be added to the provider string as the SYSTEMDB, and you also need to add the user name and password (for the user in the MDW).
  7. satya Moderator

    I;m guessing this is not a 64 bti platform?

Share This Page