Linked Servers for Dummies; Troubleshooting access over VPN...

  • I've got a ticket regarding Linked Server access not working over VPN. I have very limited exposure to how Linked Servers work or are configured, but I believe the idea is that the Linked Server both a) authenticates your access to the Linked Server object, and b) passes your credentials to the final destination (whatever the Linked Server is "linking" to). Is that basically how it works (over-simplified)?

    Secondly, how might I go about troubleshooting this. I dont have access to the Linked Servers myself but was thinking of creating one in SQL Server using a two different servers I DO have access to. Basically created a Linked Server on SQL Server "Alpha" to a database hosted on SQL Server "Omega". And then try to access the Linked Server both internally and over the VPN and see what's what.

    If anyone has any input or experiences with this, i would gratefully appreciate it. Thanks in advance!

  • There are two ways you can set up a linked server:

    The default is self-mapping. That is, user A logs in on server S and runs a query against linked server T and S passes the credentials to T. This requires that Kerberos has been set up correctly. It more or less require that you are in a domain. In a workgroup, the user must have the same username and password on both machines.

    The alternative is mapping to an SQL login that exists on the remote server. In this case A not have to have a login of its own on T. This mapping can be setup per individual user or for everyone (bad for security), but not per group or role.

    As for how to troubleshoot the situation. Well, as a starting point get hold of the error message....

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • So, someone has a local instance of SQLServer on his/her/its client and declared a Linked Server pointing to another SQLServer instance which can be available with an active VPN connection.

    1. a linked server is bad. ( as you have no control over where  ( on which sql engine local/remote )  which part of your querie(s) will get processed, hence you cannot guestimate the impact this has on any of both systems )
    2. Did your network team / firewall team grant access over VPN for that target sql server instance ?
    3. I think I should still emphasize Linked Servers are bad by default

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 3 posts - 1 through 3 (of 3 total)

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