SQL Server Performance

OpenRowSet query on a local server gets Access to the remote server denied error

Discussion in 'SQL Server 2008 General Developer Questions' started by WingSzeto, Jul 6, 2011.

  1. WingSzeto Member

    I am trying to run a OpenRowSet query below and gettting the error 'Access to the remote server is denied because no login-mapping exists.' Here is the query.

    SELECT g.* FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES','set nocount on set fmtonly off exec dbname.dbo.proc_someStoreProcedure ''765'', 0, 0, 1, 0 ') as g
    I am executing this query on a local server and not calling anything to another server so I am not sure why the message gave me the remote server and the no login-mapping. The login account I use to execute this statement in query analyzer is a SQL account with execution permission on that stored procedure. In fact, I can use that login to run that sp without OpenRowSet successfully. There are two not-perferable ways to make the above query work. One is to give the SQL account the sa right. Two is to change the Trusted connection to a sql login name and password connection.

    I do like to make the trusted connection work. I have tried to create a Windows login account in SQL security logins, that the local server is using for the login. But it is not working. What am I missing? Any help on this is very much appreicated.

    W
  2. Matjaz Justin New Member

    Did you find any solution?
  3. satya Moderator

    Did you try adding "User ID=Username" into the provider string on your linked server
  4. johnson_ef Member

    Just my thought:

    Can we use Integrated Security=SSPI here?

    -Johnson

Share This Page