linked table from ACCESS

[abcd]

I have ACESS DB in that I have SQL Server table linked thru ODBC DSN of type
SQL Server and its name is 'testSQLServer'

I am accessing Access DB from my ASP page and firing a query on a linked
table. I get the below error when I open a recordset with the query on the
linked table

error number -2147467259

[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'testSQLServer' failed.

Where is the permission issue. I checked my IIS permissions. Non linked
tables just work fine.

I think SQL Server is not recognizing the permissions but where do I see
what permissions in the whole route of ASP-MS Access-SQL Server journey

thanks

 

[Norman Yuan]

First of all, why you go SQL Server->DSN->Access linked table->ASP page? You
should either directly go SQL Server->ASP page (using ADO) or SQL
Server->DNS->ASP page. There is really no need to add the extra, redundent
layer of Jet linked table for the ASP page, which does nothing except
slowing down/complicating your data access.

Secondly, if you have to reach the SQL Server via DNS, check what SQL Server
authentication mode is used (most likely, Windows integrated authentication
is used, but you have to check that). Then you have to know what user
account on the ASP web server is used to run your ASP application. At this
point, you then examine if that account has permission to the SQL Server
database or not.

 

[abcd]

Norman thanks.

my customer is using our product and they have some access tables and they
have linked to SQL Server. I have no idea what they are upto by doing so.
But our product is failing thats what I have to solve.

I am running ASP Pages as annonymous user and SQL Server as windows
Authentication.

WHere do I and what permissions I need to set.

thanks

First of all, why you go SQL Server->DSN->Access linked table->ASP page?
You should either directly go SQL Server->ASP page (using ADO) or SQL
Server->DNS->ASP page. There is really no need to add the extra, redundent
layer of Jet linked table for the ASP page, which does nothing except
slowing down/complicating your data access.

Secondly, if you have to reach the SQL Server via DNS, check what SQL
Server authentication mode is used (most likely, Windows integrated
authentication is used, but you have to check that). Then you have to know
what user account on the ASP web server is used to run your ASP
application. At this point, you then examine if that account has
permission to the SQL Server database or not.

I have ACESS DB in that I have SQL Server table linked thru ODBC DSN of
type SQL Server and its name is 'testSQLServer'

I am accessing Access DB from my ASP page and firing a query on a linked
table. I get the below error when I open a recordset with the query on
the linked table

error number -2147467259

[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'testSQLServer' failed.

Where is the permission issue. I checked my IIS permissions. Non linked
tables just work fine.

I think SQL Server is not recognizing the permissions but where do I see
what permissions in the whole route of ASP-MS Access-SQL Server journey

thanks

 

[Norman Yuan]

If the SQL Server (on the same computer as the IIS web server, or on
different computer?) only uses Windows Authentication, then you have to
configure the IIS, so that the user account used to run the ASP must be
granted access to the SQL Server. If the SQL Server is on another computer,
you need to configure IIS to use a domain account. In the case of IIS and
SQL Server on different computer, use SQL Server authentication may make
things a bit easier (by specify username/password pair in ConnectionString).

Anyway, since it is web application (ASP), you need:

1. Know how to configure IIS to run your ASP app with desired user account
(local account or domain account). You, as ASP app developer, must know
which user account is used to run your app;

2. Know how SQL Server security is configured: authentication mode (Windows,
or mixed), SQL Server login, database user and database role and how to map
them to certain Windows user account if Windows authentication is used;

3. Try to connect to SQL Server from your ASP app direcctly, using ADO,
there is not need to use ODBC DSN, especially, do not go with ASP->ADO->Jet
linked table->ODBC DSN->SQL Server. There is no place for a Jet linked table
in this situation.

Norman thanks.

my customer is using our product and they have some access tables and they
have linked to SQL Server. I have no idea what they are upto by doing so.
But our product is failing thats what I have to solve.

I am running ASP Pages as annonymous user and SQL Server as windows
Authentication.

WHere do I and what permissions I need to set.

thanks

First of all, why you go SQL Server->DSN->Access linked table->ASP page?
You should either directly go SQL Server->ASP page (using ADO) or SQL
Server->DNS->ASP page. There is really no need to add the extra,
redundent layer of Jet linked table for the ASP page, which does nothing
except slowing down/complicating your data access.

Secondly, if you have to reach the SQL Server via DNS, check what SQL
Server authentication mode is used (most likely, Windows integrated
authentication is used, but you have to check that). Then you have to
know what user account on the ASP web server is used to run your ASP
application. At this point, you then examine if that account has
permission to the SQL Server database or not.

I have ACESS DB in that I have SQL Server table linked thru ODBC DSN of
type SQL Server and its name is 'testSQLServer'

I am accessing Access DB from my ASP page and firing a query on a linked
table. I get the below error when I open a recordset with the query on
the linked table

error number -2147467259

[Microsoft][ODBC Microsoft Access Driver] ODBC--connection to
'testSQLServer' failed.

Where is the permission issue. I checked my IIS permissions. Non linked
tables just work fine.

I think SQL Server is not recognizing the permissions but where do I see
what permissions in the whole route of ASP-MS Access-SQL Server journey

thanks