ASP.NET/Linked Server connection problem

[Guest]

I am trying to create/use a SQL Server Linked Server definition from
ASP.NET in order to transfer data from a SQL Server db to a 'local'
MSDE database. The linked server definition is created OK, but I have
to do a 'refresh' from the Enterprise Manager in order to get my
ASP.NET calls to succeed (otherwise, they return a 'SQL Server does not
exist or access denied' message). Does anyone have any idea what is going
on? Thanks!

 

[Guest]

Any idea? Permissions chain issue. You are circumventing it with your refresh.

Options?
1) Destroy security and open the database for hackers by reducing security
so ASP.NET has full rights. This is the option so many in this group choose,
as it is, by far, the easiest (fully declarative).

2) Wrap your work in a stored procedure that your connecting user account
has access to. Have this do the move.

3) Change to DTS for the move. It can be fired in a variety of ways,
including a stored procedure, Process object, etc.

4) Create a custom ETL application to move the data.

5) Set up replication.

I am completely unenamored with Option #1, although it is quite popular.
Stored procedure is a good option, as you can easily customize and still use
the link (downside, you have to know T-SQL). DTS is rather easy to set up,
but a bit harder to customize using the drag and drop designer. I would only
choose Custom ETL if you have a situation that will not fit the stored proc
or DTS model. Finally, Replication is a good option if you can have data move
at scheduled times, preferably automated and not fired from an ASP.NET page.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************