RE: ASP.NET/Linked Server connection problem

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!
***************************


"Rick P" wrote:

> 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!
>
.

Relevant Pages

  • Re: Alternative to Dynamic SQL?
    ... We have a single user per database. ... You use SQL Server logins for security as opposed to Integrated Security? ... DBA....and Tony was the developer and Tony wanted a pagination query, ... You can use a stored procedure to paginate database-side as well, ...
    (microsoft.public.sqlserver.programming)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... from any database without having to qualify the stored procedure name fully ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: Debug stored procedures with VB6
    ... > I can't see in the sql server analyser a tool to debug a stored procedure. ... > "Val Mazur" a écrit dans le message de ... >>>>> My database is installed locally. ...
    (microsoft.public.vb.database.ado)
  • Re: Where the !@?!@ is my Stored Procedure?
    ... > Responding to my own post - I created a store procedure and deleted it ... > database under system stored procedures, so if you can't find your ... > stored procedure, this is probably a good place to look. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: Facing the same problem
    ... My stored procedure is being activated from a Service Broker ... remote server is denied because the current security context is not ... The linked server is a SQL server, but not one where I have any ... ALTER DATABASE db SET TRUSTWORTHY ...
    (microsoft.public.sqlserver.security)