locked
Linked server MS Access - security rights RRS feed

  • Question

  • I've configured a linked server to MS Access.
    Testing works well.

    From within the database I call the linked server through a view. Works fine.

    When calling the view, using a datasource to the linked server in reporting services I can't use my Service account which has the privileges on the databases with the view.

    I've made the sysaccount i'm using in my datasource sysadmin server role for testing purposes but even this generates the following error when running the report:
    - Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "BudgetMDB"

    The list in the linked server properties hasn't been filled, and I chose impersonate for any user not in the list user: admin password : admin.
    When using my own account in the data-source, everything works fine



    Business Intelligence professional
    Tuesday, September 15, 2009 11:52 AM

Answers

  • It is not a bug workaround.  There are two apparent problems here:

    1.) The database has a number of tables and other important shared objects locked down in DBO with no rights for other users.  When you try with a "low privilege" database user, you can't get those.  When you try with your user account (DBO) everything works fine.  The way to address this is to go object by object and verify that they have appropriate permissions for your tasks and your user distribution (security review).

    2.) The report or some stored procedures in the database rely on a linked server through JET to perform some tasks.  This runs under one of two accounts:  1.) the SQL Server service account (would be denied in almost any directory), 2.) The account of the login (set up to impersonate).  In case (1), you must have "Full Control (ALL)" on that directory or the SQL Server Service will not be able to access it (unless you run SQL Server under a custom service account with permissions to the directory).  In case (2), you will have to add permissions to that directory that represent every user who could possibly log into SQL Server.  If you have a domain, you may be able to group them and call it a day while still retaining some level of security.

    So, the fixes are really 2 things:
    1.) Do a security review of the schemas you currently have and try to loosen where appropriate so that non-DBO can perform appropriate queries to the data.
    2.) Change the permissions on the folder containing the MDB files so that non-Windows administrators can access the MDB files.

    Thanks,

    John




    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, October 2, 2009 5:18 PM

All replies

  • Could you paste the error message?
    Thursday, September 17, 2009 3:06 AM
  • Hi,

    From your description, it seems that it would generate "Cannot initialize the data source ojbect of OLEDB provider ... " error if you've made the sysaccount sysadmin server role, right?

    What I want to say is the directory in which the .mdb resides MUST HAVE Everyone with FullControl at the share level, have you checked that?

    And the following is the general linked server creating&security configuring procedure for you to refer.

    To create a Linked Server:

    sp_addLinkedServer 'DatabaseName', 'Access', 'Microsoft.Jet.OLEDB.4.0', '\\PathToDB\vol2\Directory\someDB.mdb'

    NOTE:*** The directory in which the .mdb resides MUST HAVE Everyone with FullControl at the share level.

    Right-click the linked Server, Properties. In Security, click 'Be made using this security Context', and type in 'Admin' for the remote login, with no password. (This is a default to access the DB).

    In MS SQL Server Management Studio, Right-click the webserver where the server is, Properties. Click 'Security'. It should be the SQL Server and Windows Authentication Mode.

    Under 'Security', 'Login', make a new Login. Remember this password. 'Server Roles' should be sysadmin. 'User Mappings', select the databases required. 'Status', make sure it is enabled. This User Id and Password will be used in the connection string in .NET's Web.Config:

    <add key="ConnectionString"
     value="Data Source = YourWEBSERVER; User Id=Admin; Password=yourPassword; Database=master; Timeout=60" />
    </appSettings>

    A reference must be added and imports: Imports System.Collections.Specialized.NameValueCollection
    Dim sConnectionString As String
    In the code, add in: sConnectionString = ConfigurationSettings.AppSettings("ConnectionString").

    When launching MS SQL Server Management Studio, select the SQL Server and Windows Authentication Mode, a user that was created (as above), and their password. Everything should be good in accessing the data from the linked Server across the network.

    Thanks.


    Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, September 22, 2009 3:42 AM
  • Thank you nai-dong, i haven't executed it the way above yet, what effectively is the reference code hack?
    Where should such reference be placed?

    A reference must be added and imports: Imports System.Collections.Specialized.NameValueCollection
    Dim sConnectionString As String
    In the code, add in: sConnectionString = ConfigurationSettings.AppSettings("ConnectionString").
    .
    Business Intelligence professional
    Wednesday, September 23, 2009 8:07 PM
  • Hi,

    it represents a collection of associated String keys and String values that can be accessed either with the key or with the index.  For more information, see:

    http://msdn.microsoft.com/en-us/library/system.collections.specialized.namevaluecollection.aspx

    And the other code is used to add data connection string in your app.

    Thanks.
    Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Thursday, September 24, 2009 6:32 AM
  • Hi Nai Dong, is it actually a bug workaround?
    I need to know that because we don't support workarounds


    Business Intelligence professional
    Thursday, October 1, 2009 11:29 AM
  • It is not a bug workaround.  There are two apparent problems here:

    1.) The database has a number of tables and other important shared objects locked down in DBO with no rights for other users.  When you try with a "low privilege" database user, you can't get those.  When you try with your user account (DBO) everything works fine.  The way to address this is to go object by object and verify that they have appropriate permissions for your tasks and your user distribution (security review).

    2.) The report or some stored procedures in the database rely on a linked server through JET to perform some tasks.  This runs under one of two accounts:  1.) the SQL Server service account (would be denied in almost any directory), 2.) The account of the login (set up to impersonate).  In case (1), you must have "Full Control (ALL)" on that directory or the SQL Server Service will not be able to access it (unless you run SQL Server under a custom service account with permissions to the directory).  In case (2), you will have to add permissions to that directory that represent every user who could possibly log into SQL Server.  If you have a domain, you may be able to group them and call it a day while still retaining some level of security.

    So, the fixes are really 2 things:
    1.) Do a security review of the schemas you currently have and try to loosen where appropriate so that non-DBO can perform appropriate queries to the data.
    2.) Change the permissions on the folder containing the MDB files so that non-Windows administrators can access the MDB files.

    Thanks,

    John




    This post is provided 'as is' and confers no express or implied warranties or rights.
    Friday, October 2, 2009 5:18 PM
  • Thanks John and I met your suggestions as far as I can see.

    I gave all rights at the folder to the domain users so I met fix 2.
    All objects in the schema used, have been given query rights to the end-users.

    Next the linked server runs and does its job, it's a stored procedure which is called in reporting services.

    People can use it but at some day, over time, it suddenly stops working, and you need to restart the sql server service and everything works fine again.

    Wat is very typical, when I lock my personal active directory account, which I used to define the linkedserver (and there's no property which tells me the account which keeps the linked server  running) people can't use it. In some way, I have the feeling, the linked server is a service on its own which runs at the account which defined it instead of running under the hood of the service account.
    Business Intelligence professional
    Friday, October 9, 2009 7:37 PM