Post reply

Connecting to another server using a specific sqllogin

  • March 1, 2005 at 11:48 am

    #67070

    Hi,

    I'm writing an sp on server A, which during processing needs to join with a view on server B.

    I only have access to the view on server B through use of a specific sql login, which I obviously also have the password for.

    The sql login and password on server B is not the login that will be using the sproc on server A, since that login does not have access to server A.

     

    How do I create the join in this case? i.e. run a select statement on Server A which joins with a view on server B with a specific sql login?

    Hopefully I have explained that OK

    Any ideas would be appreciated. Many thanks in advance,

    Andy

    P.S. Things I have tried...(server name & password etc have been changed to protect the innocent)

    A) The user doesn't have access to create a linked server

    B) I when I try openquery...

    SELECT *

    FROM OPENROWSET('MSDASQL',

      'DRIVER={SQL Server};SERVER=theServer;UID=theUser;PWD=thepassword',

      theDB.dbo.vw_theView) AS fred

    GO

    this returns:

    Server: Msg 7415, Level 16, State 1, Line 1

    Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

    (looking up on MSDN there seems no way round this

    C)

    SELECT *

    FROM OPENROWSET('SQLOLEDB',

       'DRIVER={SQL Server};SERVER=theServer;UID=theUser;PWD=thePassword',

       'select * from theDB.dbo.vw_theView')

    returns:

    Server: Msg 17, Level 16, State 1, Line 1

    SQL Server does not exist or access denied.

    [OLE/DB provider returned message: Invalid connection string attribute]

  • March 2, 2005 at 4:42 am

    #544820

    You must configure a Linked Server.

    In the Properties of the Link (to access other SQL Instances) you can choose an account with connect to the instance.

    You can find it under Security "Linked Server". Be carryfully when you have created replication between to SQl Servers, the Replication automaticly gernerates a link between SQl Servers who are in replication mode.

     

    I hopr this helps you.

     

    Kind Regards

    Michael

     

  • March 2, 2005 at 7:02 am

    #544858

    Sorry. I'm really not trying to post a response to this question.  I'm trying to figure out how to post a question of my own. 

    Jacqueline

  • March 2, 2005 at 8:29 am

    #544898

    No no Jacqueline, you have to answer it now..

    Go on, have a go, you know you want to!

  • March 2, 2005 at 8:32 am

    #544901

    Is this a flirt Box *ROFL* Please Guys be smart.

     

    I sit down on the floor to save myself by falling from my chair.

     

     

  • March 2, 2005 at 8:37 am

    #544903

    a) I'm not flirting, i'd have replied the same to anyone, and

    b) whats rofl?

  • March 2, 2005 at 8:41 am

    #544905

    Michael,

    I meant to add

    c) I've now added my linked server and am just trying to use the specific login & password to get what I want, so thanks for your earlier post - i'll let you know how I get on

  • March 2, 2005 at 11:40 am

    #544960

    Done now - now that I've added the linked server login as well.

    My original attempts at adding the linked server in the first place (prior to the original question) wouldn't work - I should have added them in two seperate steps which seems to work now

    Cheers!

  • March 3, 2005 at 1:44 am

    #545124

    FYI (For your Information)

     

    In chat rooms rofl mean (rolling on the floor laughing)

     

     

     

  • March 3, 2005 at 1:49 am

    #545126

    No problem, I googled it in the end.

    Cheers

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply