Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: Linked Remote Server

    I need to be able to query remote server tables and join them to local tables. Thought that linked server would be best so that I can use local stored procs for parameters if needed. I have no administrative control over remote server. I have been given select permissions only to one db on remote server. Both servers are sql2000, local machine has spk3.

    I have established the remote server in network client utility. I can register the remote server in EM, look at tables. I can run select statements against the remote server in QA when I log in and specify that server. I can reference an odbc data connection with web pages and display rowsets.

    Next, I created a linked server under security, using sql (since it's an sql 2000 db) with the same name used above in the client utility and the em. I created a local login with the same name and password as the login used successfully above (Paragraph 2). On the security tab, I've used sa as the local login and entered the remote user and password, using the default 'be made without using a security context'. When I try to view the tables (which I can do through registered) or run this through QA:
    SELECT * FROM OPENQUERY(CASCE, 'SELECT person_pk, lastname FROM dbName.dbo.tblName (NOLOCK)'),

    I get: sql error 17: sql server does not exist or access denied

    If I edit security, wipe out the top settings and specify the security context below using the login information, same thing. Tried messing around with different security contexts, no help or ole db error.

    Any help? In addition, any tutorials out there a little more user friendly than the bol? I think I want to use the linked server option and stored procs.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Have you tried using the four part name like

    server.database.owner.table
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2003
    Posts
    43
    I can reference the linked server alias name I created, but I don't know the server name - just the ip address. Am I understanding you? When in sql client network utility, I created an alias with an ip address. I don't know the internal name of the remote linked server. It's in another city, at an outside organization.

    Also, within enterprise manager, if I click on linked servers, the alias name I created under linked servers, the tables - I get the error. When I attempt to execute the query, doesn't it execute the syntax at the remote server, so my alias would be of no value???

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I got you now. I have never done this without VPN but I remember reading about connecting over the internet nin Books Online. There seems to be some nasty security configuration stuff regarding firewalls pointing to PORT 1433 and it just seems a like a bad idea. Yep here it is. The BOL article is called "Connections to SQL Server Over the Internet".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    The value of the alias is to get you to the remote server.

    Your problem appears to be related to permissions.

    Can you connect to the remote server using (from a Command Prompt) isqlw -U LoginName -P Password -S ipaddress?

    Probably not.

    Get your network people to add a firewall rule that allows you to hit that server via Port x, where x != 1433. Then, set up SQL Server to listen on that port and create an alias that points to it from your trusted machine (ip).
    Last edited by MaxA; 02-11-05 at 16:05. Reason: Thyrasymuch's Reply
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  6. #6
    Join Date
    Oct 2003
    Posts
    43
    Don't ask me what changed, but the baby's purrin' now.

    You've been so gracious, can I impose once more? Since I only have select permissions on remote server tables and views and cannot create/execute stored procs on remote server, do you know of the pros and cons of using locally stored procs to take advantage of joins, parameters, etc.? I know it's not ideal, but my alternatives? Concatenate sql strings and exeucte?

    Final results will be display on web page for some of the joined queries (linked/local tables), selection through web page, and only updates for local tables and input from web page .

    Make any sense?

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Try using [IPAddress].[DBName].[User].[TableName]
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Enigma
    Try using [IPAddress].[DBName].[User].[TableName]

    DUDE!

    Where you bean?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    hanging around on the ledge somewhere ....
    saw ur post today reminding of Mr B. Lindman , Rudy and Pat ... and i thought that definitely deserves a comeback
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    seemed like you had forgotten me
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Oct 2003
    Posts
    43
    Okay you two, get a room, or include me in the conversation by answering my question...

    ;-)

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    ok ... do you have the linked server registered by the IP address or the name ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not much to go on.

    Did you try the 4 part name?


    How come you can't get the dba of the other box to create some sprocs for you..that would be best...otherwise it'll be slower
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are doing straight SELECT operations, using a four-part name is fine. If you are doing many JOIN operations, the performance can get "interesting" due to the way the operations are sometimes resolved. Just for the jolly factor, could you try to execute the following snippet to see if you can remotely execute code:
    Code:
    EXECUTE [remote server name goes here].master.dbo.sp_sqlexec 'SELECT @@servername'
    This is minimally intrusive, all it does is try to execute a simple select on the remote server. If you can't do that, then doing the SELECT/JOIN locally against the remote tables is your only resort. If you can, it opens up a few windows that might perform better.

    Don't get too excited about Brett and Enigma. They're both fine, upstanding SQL geeks. We haven't seen much of Enigma in ages, he's been hiding for a while, so I'm sure that everybody will be glad to see him again. Long lost geeks returning home always seem to generate quite a stir when they return.

    -PatP

  15. #15
    Join Date
    Oct 2003
    Posts
    43
    Pat,
    Thanks for the reply - yes I got a response: guess the server's name is VIPER.

    No worries - just kiddin' the geeks really. Glad a long lost soul has returned to the village.

    jb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •