[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Linked Server Connection String to utilize as pass through to remote database

Posted on 2012-09-16
Medium Priority
Last Modified: 2012-09-17
I have been asked to replace an existing SQL Server 2005 with a new SQL Server 2012 in a plant that has recently been purchased.  The existing and running SQL Server 2005 resides on the previous owners network and can be accessed by clients.  The new SQL Server is not allowed to be connected to the previous Owners network until a later date.  I have been asked to migrate 30 + databases to the new SQL Server 2012 and then add the New Server as a linked Server on the Old Server which is still connected to the previous owners network and try to set up the existing server as a pass through server so clients can access.  There is a second network both servers are connected to that is internal and unregulated.  I have used linked servers quite a bit in the past by using OpenQuery and the 4 part identifier for individual code.  There is no way I will be able to change all code to reference a linked server versus a local server.  All of 56 apps have 1 main connection string that is reused through all code in the application.  I was wondering if there was a way I could modify the connection string to connect directly to the linked server and actually use the existing server as a true pass through server to allow client access.  Is there a way to accomplish this with linked servers or another method?  My initial research looks bleak.  Thanks
Question by:rockman44
  • 2
  • 2
LVL 71

Expert Comment

ID: 38403559
The only way I can think of is to create a new schema consisting of synonyms using the linked server. Provided the applications do not use a 2- or 3-part name for DB objects ...

It is not possible to make a linked server fully transparent, so there will still be a difference thru the added Distributed Query feature. And having to create synonyms for each and every object to use is a PITA, of course, even if doing it per script.

IMHO it would be much better to allow for full routing, and provide the real server name/IP in the apps' connection strings.

Author Comment

ID: 38405225
I agree with you.  Can you direct me to any links that would provide information how to implement full routing so I can provide the New Servers name in the apps connection string?  Thanks
LVL 71

Accepted Solution

Qlemo earned 2000 total points
ID: 38405948
No links ready, sorry.

The simplest way is to install/activate RRAS (Routing and Remote Access Services) on the "bridging" server, and setting its "Routing" option (fairly straight-forward in the setup). This will allow bidirectional routing for the two NICs, and hence both networks.

Next, all clients needing access need to get the route to the secondary network. That can be done by creating a static route on each client - but I wouldn't do that. Better IMHO is to set the route on the existing default gateway, which will either propagate the route temporarily and on demand to the clients, or just do the routing.
The route in either situation needs to define the secondary network with the SQL Server 2005 as gateway.

Last, the new SQL Server needs to know the route back - again, setting a static route to the primary network on that machien (using the 2005 as gateway) will do that.

For example, if you have as the "old" network and as the "new" one:
MSSQL 2005, NIC 1:
MSSQL 2005, NIC 2:
MSSQL 2012, NIC 1:

A client needs to know the route: mask gateway
MSSQL 2012 needs to know the route: mask gateway

This is most restrictive, of course you can set up the complete network in the first route instead of a single IP.

If you then set up a connection string using the IP of, it should work. If you want to use the name, you will have to set up DNS or WINS appropriately, since it will not work automatically.

Author Closing Comment

ID: 38406344
Thanks for your help

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question