Microsoft KB Archive/246255

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Article ID: 246255

Article Last Modified on 2/12/2007



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 6.0 Enterprise Edition
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.01
  • Microsoft ActiveX Data Objects 2.1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 1
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft Access 97 Standard Edition



This article was previously published under Q246255

SUMMARY

This article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database.

MORE INFORMATION

Microsoft SQL Server version 7.0 provides you the ability to perform queries against different databases by using OLE DB providers. You query databases by using:

  • OpenQuery or OpenRowset Transact-SQL functions.


-or-

  • A query with four-part names including a linked server name.

To set up a linked server to access a secured Microsoft Access database, use these steps:

  1. Configure the registry (by using the Registry Editor) to use the correct Microsoft Access Workgroup Information file (the .mdw file). Use the Registry Editor to add the full path name of the Workgroup Information file to the following registry entry:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB

    Next, set the value to the path and name of the file, such as:

    C:\...\MySystem.mdw

    1. To open the registry editor, navigate to the Start button and click Run. In the Run dialog box, type Regedit, and then press OK.
    2. In the registry editor, navigate to this key:

      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\

    3. Right-click and select New/String Value.
    4. Type SystemDb, and then press Enter.
    5. Double-click the SystemDb key in the left window pane.
    6. In the Value Data text box, type the full path to your .mdw file.
    7. Close the registry editor.
  2. Execute the sp_addlinkedserver stored procedure to create the linked server. Specify Microsoft.Jet.OLEDB.4.0 as the provider_name, and specify the full pathname of the Microsoft Access .mdb database file as the data_source. The data_source is evaluated on the server, not the client, and the path must be valid on the server.
  3. Execute the sp_addlinkedsrvlogin stored procedure to create login mappings from local logins to Microsoft Access logins.

Steps to Query Secured Microsoft Access Database

  1. Modify the registry key shown in step 1 of the "More Information" section and add the location of your .mdw file.
  2. Start Microsoft Visual Basic 6.0 and select a Standard EXE project. Form1 is created by default.
  3. On the Project menu, select References, and set a reference to the Microsoft ActiveX Data Objects 2.1 Library or later.
  4. Place two command buttons and a DataGrid control on Form1 (named Command1, Command2 and DataGrid1 respectively).
  5. Paste the following code into the Declarations section of Form1:

    Note You must change User ID=<User ID> and password= to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.

    Dim adorst As New ADODB.Recordset
    Dim adoconn As New ADODB.Connection
    
    Private Sub Command1_Click()
        Dim strConn As String
        
        adoconn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;"
        adoconn.Execute "EXEC sp_addlinkedserver 'SecuredJetLS', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\.....\MyDatabase.mdb'"
        adoconn.Execute "EXEC sp_addlinkedsrvlogin 'SecuredJetLS', FALSE, NULL, 'UserName', 'Password'"
        adoconn.Close
        MsgBox "Successful Setup"
    
    End Sub
    
    Private Sub Command2_Click()
        Dim SQL As String
    
      ' Using OpenQuery syntax. 
        SQL = " Select a.* from OPENQUERY(SecuredJetLS, 'Select * from MyTable') a"
    
      ' Using OpenRowset syntax.
      ' SQL = "SELECT * From OpenRowset('Microsoft.Jet.OLEDB.4.0','c:\....\MyDatabase.mdb'; 'UserName';'Password', MyTable)"
    
      ' Using four-part name syntax.
      ' SQL = "Select * from SecuredJetLS...MyTable"
    
        adoconn.CursorLocation = adUseClient
        adoconn.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=master;User Id=<User ID>;Password=<Strong Password>;"
        adorst.Open SQL, adoconn, adOpenStatic, adLockReadOnly
        
        Set DataGrid1.DataSource = adorst
    
    End Sub
    
    Private Sub Form_Load()
        Command1.Caption = "Setup Linked Server"
        Command2.Caption = "Query Linked Server"
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
        adorst.Close
        Set adorst = Nothing
        adoconn.Close
        Set adoconn = Nothing
    End Sub
  6. Run the project.
  7. Click Setup Linked Server. If you modify sp_addlinkedserver and sp_addlinkedsrvlogin in the connection string with the correct parameters, the linked server is created successfully.
  8. Click Query Linked Server. If you modify the connection string and the query text to the correct parameters, the DataGrid control is populated with your data.


REFERENCES

Microsoft SQL Server 7.0 Books Online; topics: "sp_addlinkedserver"; "sp_addlinkedsrvlogin"; "OpenQuery"; "OpenRowset"; "OLE DB Provider for Jet"

Keywords: kbdatabase kbhowto kbjet kbsecurity KB246255