Linked Table - ODBC connection failed

  • Thread starter Thread starter Igor
  • Start date Start date
I

Igor

An error "ODBC connection to [name of SQL Server] failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a linked
table to another table in SQL Server 7 . The exception is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking the
issue somehow is related to security context. Anyway I do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the next
list of "Linked Tables" select table "dbo.authors". Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com
 
Instead of trying to use Access as a go-between, just connect to the
SQL Server table directly. You will need to create a login for the
ASPNET process model account in SQLS, and grant it access (and
permissions) to the database in question. ASP.NET and Windows have
entirely different security considerations, where Windows apps run
pretty much with administrative permissions if everything is on the
same machine, and ASP.NET apps run with a very restricted security
context. You might want to take a look at the ASP.NET security best
practices whitepaper before proceeding much further --
http://www.microsoft.com/downloads/release.asp?ReleaseID=44047

-- Mary
MCW Technologies
http://www.mcwtech.com

An error "ODBC connection to [name of SQL Server] failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a linked
table to another table in SQL Server 7 . The exception is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking the
issue somehow is related to security context. Anyway I do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the next
list of "Linked Tables" select table "dbo.authors". Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com
Click to expand...
 
Well, I'm not 100% positive, but I think your approach is doomed to
failure. You can't (or shouldn't be able) to use an Access ODBC linked
table to circumvent the restricted ASPNET worker process. If you
could, then somebody really screwed up security since it looks to me
like it would be a giant security hole if you could get it to work.
You could try a google groups search if you don't get a definitive
answer here.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thank you, Mary.

Please believe me, I have a good reason to go to SQLServer
indirectly. We are trying to adjust existing functionality
to requirements of a new customer. The original problem is
too complicated to describe it here. It involves multi-
tiered distributed components. I had reduced it to the
sample presented with pubs database in order to be able to
ask for assistance.

Thanks,
Igor
-----Original Message-----
Instead of trying to use Access as a go-between, just connect to the
SQL Server table directly. You will need to create a login for the
ASPNET process model account in SQLS, and grant it access (and
permissions) to the database in question. ASP.NET and Windows have
entirely different security considerations, where Windows apps run
pretty much with administrative permissions if everything is on the
same machine, and ASP.NET apps run with a very restricted security
context. You might want to take a look at the ASP.NET security best
practices whitepaper before proceeding much further --
http://www.microsoft.com/downloads/release.asp? ReleaseID=44047

-- Mary
MCW Technologies
http://www.mcwtech.com

An error "ODBC connection to [name of SQL Server] failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a linked
table to another table in SQL Server 7 . The exception is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking the
issue somehow is related to security context. Anyway I do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the next
list of "Linked Tables" select table "dbo.authors". Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
 
I just thought of something that *might* work -- Create a DSN in the
Access database for the ASPNET account and use it to link the tables.
You'll also need to grant ASPNET permissions in the SQLS database, so
you'll need to enable ASPNET as a login. This account has to be
identical on both the web server machine, the Access mdb machine, and
the SQL Server machine, if these are all different. You might want to
take a gander at the ASP.NET security best practices whitepaper on how
to set up the accounts (involves mucking around in machine.config).
You'll also need to grant ASPNET permissions on the file share that
the mdb and mdw reside on. See
http://www.microsoft.com/downloads/release.asp?ReleaseID=44047 for the
best practices wp.

-- Mary
MCW Technologies
http://www.mcwtech.com


Well, I'm not 100% positive, but I think your approach is doomed to
failure. You can't (or shouldn't be able) to use an Access ODBC linked
table to circumvent the restricted ASPNET worker process. If you
could, then somebody really screwed up security since it looks to me
like it would be a giant security hole if you could get it to work.
You could try a google groups search if you don't get a definitive
answer here.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thank you, Mary.

Please believe me, I have a good reason to go to SQLServer
indirectly. We are trying to adjust existing functionality
to requirements of a new customer. The original problem is
too complicated to describe it here. It involves multi-
tiered distributed components. I had reduced it to the
sample presented with pubs database in order to be able to
ask for assistance.

Thanks,
Igor
-----Original Message-----
Instead of trying to use Access as a go-between, just connect to the
SQL Server table directly. You will need to create a login for the
ASPNET process model account in SQLS, and grant it access (and
permissions) to the database in question. ASP.NET and Windows have
entirely different security considerations, where Windows apps run
pretty much with administrative permissions if everything is on the
same machine, and ASP.NET apps run with a very restricted security
context. You might want to take a look at the ASP.NET security best
practices whitepaper before proceeding much further --
http://www.microsoft.com/downloads/release.asp? ReleaseID=44047

-- Mary
MCW Technologies
http://www.mcwtech.com

An error "ODBC connection to [name of SQL Server] failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a linked
table to another table in SQL Server 7 . The exception is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking the
issue somehow is related to security context. Anyway I do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the next
list of "Linked Tables" select table "dbo.authors". Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com

.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Mary.

Actually I did many things which resembled your
suggestions: I created login "ASPNET" on the SQL Server,
gave it permissions; I played with different choices for
authentication of DSN ("sa", "ASPNET"); I even changed the
entry "userName" in machine.config (<processModel>)
to "SYSTEM" from "machine". Nothing works.

I've got this white paper "Building Secure ASP.NET
Applications". It is about 600 pages long! I will try to
look through it. Many thanks to you for bringing it to my
attention.

Let me describe briefly the reason why we have chosen
this "doomed" way to do things:

We have two .Net applications (a Windows one, and a Web
one) working with an Access database which contains a lot
of queries. A new customer considers to use our
applications. This customer stores all his data in a SQL
Server which data are constantly changing. Most of the
tables in his SQL Server have the same fields as our
Access database because we belong to the same industry
(the field names are certainly different). So we could
easily rewrite our queries (using aliases) to work from
the linked (attached) tables. And we did it. And our
Windows app works fine while Web app fails. That's the
story.

Igor
www.plandata.com


-----Original Message-----
I just thought of something that *might* work -- Create a DSN in the
Access database for the ASPNET account and use it to link the tables.
You'll also need to grant ASPNET permissions in the SQLS database, so
you'll need to enable ASPNET as a login. This account has to be
identical on both the web server machine, the Access mdb machine, and
the SQL Server machine, if these are all different. You might want to
take a gander at the ASP.NET security best practices whitepaper on how
to set up the accounts (involves mucking around in machine.config).
You'll also need to grant ASPNET permissions on the file share that
the mdb and mdw reside on. See
http://www.microsoft.com/downloads/release.asp? ReleaseID=44047 for the
best practices wp.

-- Mary
MCW Technologies
http://www.mcwtech.com


Well, I'm not 100% positive, but I think your approach is doomed to
failure. You can't (or shouldn't be able) to use an Access ODBC linked
table to circumvent the restricted ASPNET worker process. If you
could, then somebody really screwed up security since it looks to me
like it would be a giant security hole if you could get it to work.
You could try a google groups search if you don't get a definitive
answer here.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thank you, Mary.

Please believe me, I have a good reason to go to SQLServer
indirectly. We are trying to adjust existing functionality
to requirements of a new customer. The original problem is
too complicated to describe it here. It involves multi-
tiered distributed components. I had reduced it to the
sample presented with pubs database in order to be able to
ask for assistance.

Thanks,
Igor

-----Original Message-----
Instead of trying to use Access as a go-between, just
connect to the
SQL Server table directly. You will need to create a
login for the
ASPNET process model account in SQLS, and grant it access
(and
permissions) to the database in question. ASP.NET and
Windows have
entirely different security considerations, where Windows
apps run
pretty much with administrative permissions if everything
is on the
same machine, and ASP.NET apps run with a very restricted
security
context. You might want to take a look at the ASP.NET
security best
practices whitepaper before proceeding much further --
http://www.microsoft.com/downloads/release.asp?
ReleaseID=44047

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 21 Oct 2003 14:54:42 -0700, "Igor"

An error "ODBC connection to [name of SQL Server]
failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a
linked
table to another table in SQL Server 7 . The exception
is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking
the
issue somehow is related to security context. Anyway I
do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data
source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the
next
list of "Linked Tables" select table "dbo.authors".
Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As
Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com

.
Click to expand...
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
 
One other thought -- as an experiment, write some code from asp.net
that connects to the Jet database (use the DAO PIA) and create a
linked table (TableDef) object that connects to one of your SQLS
tables, supplying the connection string argument using integrated
security (go to able-consulting.com or connectionstrings.com for odbc
connection string samples). Once you're created the TableDef object in
code, try executing a query on the table in the same procedure and see
what happens. Access caches linked table info, and that may be part of
the problem with the aspnet account not being able to flow through the
odbc layer.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thanks, Mary.

Actually I did many things which resembled your
suggestions: I created login "ASPNET" on the SQL Server,
gave it permissions; I played with different choices for
authentication of DSN ("sa", "ASPNET"); I even changed the
entry "userName" in machine.config (<processModel>)
to "SYSTEM" from "machine". Nothing works.

I've got this white paper "Building Secure ASP.NET
Applications". It is about 600 pages long! I will try to
look through it. Many thanks to you for bringing it to my
attention.

Let me describe briefly the reason why we have chosen
this "doomed" way to do things:

We have two .Net applications (a Windows one, and a Web
one) working with an Access database which contains a lot
of queries. A new customer considers to use our
applications. This customer stores all his data in a SQL
Server which data are constantly changing. Most of the
tables in his SQL Server have the same fields as our
Access database because we belong to the same industry
(the field names are certainly different). So we could
easily rewrite our queries (using aliases) to work from
the linked (attached) tables. And we did it. And our
Windows app works fine while Web app fails. That's the
story.

Igor
www.plandata.com


-----Original Message-----
I just thought of something that *might* work -- Create a DSN in the
Access database for the ASPNET account and use it to link the tables.
You'll also need to grant ASPNET permissions in the SQLS database, so
you'll need to enable ASPNET as a login. This account has to be
identical on both the web server machine, the Access mdb machine, and
the SQL Server machine, if these are all different. You might want to
take a gander at the ASP.NET security best practices whitepaper on how
to set up the accounts (involves mucking around in machine.config).
You'll also need to grant ASPNET permissions on the file share that
the mdb and mdw reside on. See
http://www.microsoft.com/downloads/release.asp? ReleaseID=44047 for the
best practices wp.

-- Mary
MCW Technologies
http://www.mcwtech.com


Well, I'm not 100% positive, but I think your approach is doomed to
failure. You can't (or shouldn't be able) to use an Access ODBC linked
table to circumvent the restricted ASPNET worker process. If you
could, then somebody really screwed up security since it looks to me
like it would be a giant security hole if you could get it to work.
You could try a google groups search if you don't get a definitive
answer here.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thank you, Mary.

Please believe me, I have a good reason to go to SQLServer
indirectly. We are trying to adjust existing functionality
to requirements of a new customer. The original problem is
too complicated to describe it here. It involves multi-
tiered distributed components. I had reduced it to the
sample presented with pubs database in order to be able to
ask for assistance.

Thanks,
Igor

-----Original Message-----
Instead of trying to use Access as a go-between, just
connect to the
SQL Server table directly. You will need to create a
login for the
ASPNET process model account in SQLS, and grant it access
(and
permissions) to the database in question. ASP.NET and
Windows have
entirely different security considerations, where Windows
apps run
pretty much with administrative permissions if everything
is on the
same machine, and ASP.NET apps run with a very restricted
security
context. You might want to take a look at the ASP.NET
security best
practices whitepaper before proceeding much further --
http://www.microsoft.com/downloads/release.asp?
ReleaseID=44047

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 21 Oct 2003 14:54:42 -0700, "Igor"

An error "ODBC connection to [name of SQL Server]
failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a
linked
table to another table in SQL Server 7 . The exception
is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking
the
issue somehow is related to security context. Anyway I
do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb connection to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data
source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data->Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the
next
list of "Linked Tables" select table "dbo.authors".
Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following code to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As
Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows(0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com

.
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
After several attempts, the problem is solved now.

I worked with a Microsoft Support professional.
The feature that matters is: when we create a Linked Table
in Access, the password should be saved (together with the
link) even if it is a blank password (like for the
Login "sa").

After that both of my apps (Windows .Net, and ASP.Net)
work from one and the same Access database.

You wrote "Access caches linked table info", and probably
the crucial thing here is the password.

Thank you for many useful thoughts.

Igor
www.plandata.com

-----Original Message-----
One other thought -- as an experiment, write some code from asp.net
that connects to the Jet database (use the DAO PIA) and create a
linked table (TableDef) object that connects to one of your SQLS
tables, supplying the connection string argument using integrated
security (go to able-consulting.com or connectionstrings.com for odbc
connection string samples). Once you're created the TableDef object in
code, try executing a query on the table in the same procedure and see
what happens. Access caches linked table info, and that may be part of
the problem with the aspnet account not being able to flow through the
odbc layer.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thanks, Mary.

Actually I did many things which resembled your
suggestions: I created login "ASPNET" on the SQL Server,
gave it permissions; I played with different choices for
authentication of DSN ("sa", "ASPNET"); I even changed the
entry "userName" in machine.config (<processModel>)
to "SYSTEM" from "machine". Nothing works.

I've got this white paper "Building Secure ASP.NET
Applications". It is about 600 pages long! I will try to
look through it. Many thanks to you for bringing it to my
attention.

Let me describe briefly the reason why we have chosen
this "doomed" way to do things:

We have two .Net applications (a Windows one, and a Web
one) working with an Access database which contains a lot
of queries. A new customer considers to use our
applications. This customer stores all his data in a SQL
Server which data are constantly changing. Most of the
tables in his SQL Server have the same fields as our
Access database because we belong to the same industry
(the field names are certainly different). So we could
easily rewrite our queries (using aliases) to work from
the linked (attached) tables. And we did it. And our
Windows app works fine while Web app fails. That's the
story.

Igor
www.plandata.com


-----Original Message-----
I just thought of something that *might* work -- Create
Click to expand...
a
DSN in the
Access database for the ASPNET account and use it to
Click to expand...
link
the tables.
You'll also need to grant ASPNET permissions in the
Click to expand...
SQLS
database, so
you'll need to enable ASPNET as a login. This account
Click to expand...
has
to be
identical on both the web server machine, the Access
Click to expand...
mdb
machine, and
the SQL Server machine, if these are all different. You might want to
take a gander at the ASP.NET security best practices whitepaper on how
to set up the accounts (involves mucking around in machine.config).
You'll also need to grant ASPNET permissions on the
Click to expand...
file
share that
the mdb and mdw reside on. See
http://www.microsoft.com/downloads/release.asp? ReleaseID=44047 for the
best practices wp.

-- Mary
MCW Technologies
http://www.mcwtech.com
Click to expand...
wrote:

Well, I'm not 100% positive, but I think your approach is doomed to
failure. You can't (or shouldn't be able) to use an Access ODBC linked
table to circumvent the restricted ASPNET worker process. If you
could, then somebody really screwed up security since
Click to expand...
it
looks to me
like it would be a giant security hole if you could
Click to expand...
get
it to work.
You could try a google groups search if you don't get
Click to expand...
a
definitive
answer here.

-- Mary
MCW Technologies
http://www.mcwtech.com

Thank you, Mary.

Please believe me, I have a good reason to go to SQLServer
indirectly. We are trying to adjust existing functionality
to requirements of a new customer. The original
Click to expand...
problem
is
too complicated to describe it here. It involves multi-
tiered distributed components. I had reduced it to the
sample presented with pubs database in order to be
Click to expand...
able
to
ask for assistance.

Thanks,
Igor

-----Original Message-----
Instead of trying to use Access as a go-between, just
connect to the
SQL Server table directly. You will need to create a
login for the
ASPNET process model account in SQLS, and grant it access
(and
permissions) to the database in question. ASP.NET and
Windows have
entirely different security considerations, where Windows
apps run
pretty much with administrative permissions if everything
is on the
same machine, and ASP.NET apps run with a very restricted
security
context. You might want to take a look at the ASP.NET
security best
practices whitepaper before proceeding much further - -
http://www.microsoft.com/downloads/release.asp?
ReleaseID=44047

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 21 Oct 2003 14:54:42 -0700, "Igor"

An error "ODBC connection to [name of SQL Server]
failed"
is raised when I am trying to get data from a table in
Microsoft Access (97, or 2000) which is actually a
linked
table to another table in SQL Server 7 . The exception
is
raised from a Web .Net app while exactly the same code
works fine in Windows .Net app. That makes me thinking
the
issue somehow is related to security context.
Click to expand...
Anyway
I
do
not know how to solve the problem, and any help is
appreciated.

Windows 2000, XP; IIS 5.0; .Net Framework 1.1.; Visual
Studio .Net 7.1.3088; SQL Server 7; OleDb
Click to expand...
connection
to
Jet 4.0;

Below there are the steps to reproduce the problem.

Step 1.
Run AdministrativeTools->DataSources. Create a data
source
(DSN) to the database "pubs" of SQL Server.
Let's call it "LinkedPubs".

Step 2.
Create a database in MSAccess 97. Let's call
it "db1.mdb". Go to File->Get External Data- Linked
Tables.Select "ODBC databases". In the list of data
sources select newly created DSN "LinkedPubs". In the
next
list of "Linked Tables" select table "dbo.authors".
Close
MSAccess.

Step 3.
Start Visual Studio .Net. Create a new ASP.Net
application. Drag OleDBAdapter to the page, and start
configuring it. Select database "db1.mdb" as the data
connection. Create a query, say "SELECT au_fname FROM
dbo_authors". Finish the wizard.
Put a button on the page and place the following
Click to expand...
code
to
handle button click:

==================
Private Sub btnGetAuthors_Click(ByVal sender As
Object,
ByVal e As System.EventArgs) Handles btnGetAuthors.Click

Dim ds As New DataSet
Dim ID As String
Dim err As String

Try
Me.OleDbConnection1.Open()
Me.OleDbDataAdapter1.Fill(ds)
ID = ds.Tables("dbo_authors").Rows (0).Item
(0).ToString()
btnGetAuthors.Text = ID
Catch ex As System.Data.OleDb.OleDbException
err = ex.Message
btnGetAuthors.Text = err
End Try

Me.OleDbConnection1.Close()
End Sub
=======

That's it.

When I run the application, click the button, the line
Me.OleDbDataAdapter1.Fill(ds) raises an exception "ODBC
connection to [name of SQL Server] failed".

I created a Windows .Net application using the same DSN,
the same adapter, and it works fine. For Web .Net app
described above I can use "Preview Data" of the adapter,
and I can see the list of authors.

Could somebody help me to resolve this problem?

Igor
www.plandata.com

.


.
Click to expand...
Click to expand...

.
Click to expand...
Click to expand...
Click to expand...
 
Hello igor

You Solved a Huge Problem
but remmember that your DSN in ODBC must be a "System DSN" and
not a "User DSN".
Thank.
 
Back
Top