none
query sql server table attached in Access

    Question

  • Hello,

    I have attached a Sql Server table in an Access database.

    This wasn't a problem, I can open my attached table from Access, use it in queries.

    Now I'm trying to open it from an asp page, for example :

    SQL="SELECT * FROM myAttachedTable"
    Set Rs= server.createobject("ADODB.Recordset")
    Rs.Open SQL, myAccessConnectionString , 3, 4

    I get :

     Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [Microsoft][Pilote ODBC Microsoft Access] ODBC -- failed connection to  'My SQL Server'.

    again if I enter the same sql query directly in Access it all works.

    What am I missing ?

    Thanks a lot

     

    Wednesday, January 10, 2007 10:41 AM

Answers

  • This is very likely a permissions or a delegation issue. If you're allowing anonymous access, the account that IIS is running under is attempting to access your database (NOTE: there is some possibility for variation here depending on how your server is configured...the end result, however, is the same). If you've enabled Windows Authentication for your web site, then it's likely that either the user account that is visiting the web page does not have access (which you could verify by attempting to open the file using that user account) or the database resides on a different machine and the user authentication cannot be passed along from the web server to the database server (e.g. the web server impersonates the IE user's account but that user token cannot be re-authenticated by the database server).

    Also, are you connecting to the Access database in your web app or are you connecting to the SQL Server directly? If you're using Access as an intermediary, you may want to consider removing this layer...I'm not sure that it provides any advantages.

    There is a great overview of this general problem and possible solutions available at: http://support.microsoft.com/kb/207671

    If you're interested in trying out ASP.NET, ScottGu is a very cool and very knowledgeable guy with a lot of good info on his blog. As an example of this topic, you can check out his posting at: http://weblogs.asp.net/scottgu/archive/2006/07/12/Recipe_3A00_-Enabling-Windows-Authentication-within-an-Intranet-ASP.NET-Web-application.aspx

    Hope this helps,

    Jason

    Wednesday, January 10, 2007 10:32 PM
  • If you check the security setting on the Access file, you'll likely see that Everyone has read permissions, which explains why there are no issues accessing content in the native Access tables. The linked tables have to pull in data from your SQL Server and that connection is not getting access.

    When you set up the linked tables in Access, did you specify a username and password in your connection string? Or are you using Integrated Security? Can you provide the full connection string that is being used to connect to the SQL Server?

    I suspect that your connection string is using Windows Authentication via Integrated Security or Trusted_Connection (depending on the version of the ODBC/OLEDB driver that you are using)...that would explain why you can open up the Access database when logged into the server but not when you're using it remotely via IIS. In these scenarios, you're opening the database as the local user (yourself, aka domainname\username) or as the anonymous account (IUSR_<machinename>) respectively. IUSER_<machinename> does not have access to the network and very likely does not have access to your database (if the database is on the same machine as the web server). If you were using SQL Authentication to access the linked tables, the connection should succeed in either case.

    If your web server and SQL Server database are on the same machine, simply disable Anonymous Access (right click on your web site's node in Computer Management, select the Directory Security tab, click the Edit button in the 'Authentication and access control' section, and uncheck the 'Enable Anonymous Access' check box) and then select the Integrated Windows authentication. If your web server and SQL Server are on different machines, the local authentication token used inside of IIS cannot be used to access resources on the network...in this scenario, create a new SQL account and then reconfigure the connection string in Access to use that SQL account.

    Hope that helps,

    Jason

    Thursday, January 18, 2007 4:36 AM

All replies

  • This is very likely a permissions or a delegation issue. If you're allowing anonymous access, the account that IIS is running under is attempting to access your database (NOTE: there is some possibility for variation here depending on how your server is configured...the end result, however, is the same). If you've enabled Windows Authentication for your web site, then it's likely that either the user account that is visiting the web page does not have access (which you could verify by attempting to open the file using that user account) or the database resides on a different machine and the user authentication cannot be passed along from the web server to the database server (e.g. the web server impersonates the IE user's account but that user token cannot be re-authenticated by the database server).

    Also, are you connecting to the Access database in your web app or are you connecting to the SQL Server directly? If you're using Access as an intermediary, you may want to consider removing this layer...I'm not sure that it provides any advantages.

    There is a great overview of this general problem and possible solutions available at: http://support.microsoft.com/kb/207671

    If you're interested in trying out ASP.NET, ScottGu is a very cool and very knowledgeable guy with a lot of good info on his blog. As an example of this topic, you can check out his posting at: http://weblogs.asp.net/scottgu/archive/2006/07/12/Recipe_3A00_-Enabling-Windows-Authentication-within-an-Intranet-ASP.NET-Web-application.aspx

    Hope this helps,

    Jason

    Wednesday, January 10, 2007 10:32 PM
  •  Jason Smith - MSFT wrote:

    This is very likely a permissions or a delegation issue. If you're allowing anonymous access, the account that IIS is running under is attempting to access your database (NOTE: there is some possibility for variation here depending on how your server is configured...the end result, however, is the same). If you've enabled Windows Authentication for your web site, then it's likely that either the user account that is visiting the web page does not have access (which you could verify by attempting to open the file using that user account) or the database resides on a different machine and the user authentication cannot be passed along from the web server to the database server (e.g. the web server impersonates the IE user's account but that user token cannot be re-authenticated by the database server).

    Also, are you connecting to the Access database in your web app or are you connecting to the SQL Server directly? If you're using Access as an intermediary, you may want to consider removing this layer...I'm not sure that it provides any advantages.

    There is a great overview of this general problem and possible solutions available at: http://support.microsoft.com/kb/207671

    If you're interested in trying out ASP.NET, ScottGu is a very cool and very knowledgeable guy with a lot of good info on his blog. As an example of this topic, you can check out his posting at: http://weblogs.asp.net/scottgu/archive/2006/07/12/Recipe_3A00_-Enabling-Windows-Authentication-within-an-Intranet-ASP.NET-Web-application.aspx

    Hope this helps,

    Jason

     

    Thank you Jason,

    Yes it is definately a permission issue.

    My asp page is connecting to the Access database using a connection string like this : "Driver={Microsoft Access Driver (*.mdb)};DBQ=E:\Inetpub\wwwroot\databases\myAccessDb.mdb"

    No username/password required, IIS is configured to accept annonymous connections.

    Sql server is configured to accept both Windows and it's own authentification systems.

    The asp page can query any tables no problems except the attached Sql Server table. Like I said if I query the attached table from within Access it works.

    The reason for using Access is that I'm in the process of migrating an intranet from Access to Sql server, I can do only one db at a time, but all Access dbs have attached tables.

    Hope it all make sense, english isn't my first language :)

    Have a nice day everyone.

    Thursday, January 11, 2007 11:13 AM
  • If you check the security setting on the Access file, you'll likely see that Everyone has read permissions, which explains why there are no issues accessing content in the native Access tables. The linked tables have to pull in data from your SQL Server and that connection is not getting access.

    When you set up the linked tables in Access, did you specify a username and password in your connection string? Or are you using Integrated Security? Can you provide the full connection string that is being used to connect to the SQL Server?

    I suspect that your connection string is using Windows Authentication via Integrated Security or Trusted_Connection (depending on the version of the ODBC/OLEDB driver that you are using)...that would explain why you can open up the Access database when logged into the server but not when you're using it remotely via IIS. In these scenarios, you're opening the database as the local user (yourself, aka domainname\username) or as the anonymous account (IUSR_<machinename>) respectively. IUSER_<machinename> does not have access to the network and very likely does not have access to your database (if the database is on the same machine as the web server). If you were using SQL Authentication to access the linked tables, the connection should succeed in either case.

    If your web server and SQL Server database are on the same machine, simply disable Anonymous Access (right click on your web site's node in Computer Management, select the Directory Security tab, click the Edit button in the 'Authentication and access control' section, and uncheck the 'Enable Anonymous Access' check box) and then select the Integrated Windows authentication. If your web server and SQL Server are on different machines, the local authentication token used inside of IIS cannot be used to access resources on the network...in this scenario, create a new SQL account and then reconfigure the connection string in Access to use that SQL account.

    Hope that helps,

    Jason

    Thursday, January 18, 2007 4:36 AM