I have a remote server with an SQL Server instance running on it. I can only access it over a VPN, but my users want to be able to connect to it without using a VPN.
I also have SQL Server running on my computer, and would like the users to connect to the remote server through my computer. I have read about linked servers, but it seems like I have to use the server name in each query. For example:
select * from [192.168.0.45].[tblUser]
I would like other users that use the linked server to only have to use the table name. For example:
select * from tblUser
Additionally, when I use a linked server, I have to use "master" in the connection string, even though that is not my database.
It is unavoidable that you must reference the remote object using a four-part-name, but you can use synonyms to make a remote table look like a local table.
The four-part-name is composed of [The server name].[The remote database name].[The remote schema (usually dbo)].[The remote table name] and all parts must be specified for queries made against linked servers.
You can, however, create a synonym in a local database to avoid having to specify the four-part-name each time you reference the table.
CREATE SYNONYM [synonymName] FOR [four].[part].[name].[here]
For example, you could proxy an entire database by creating synonyms for each table. Something like the following should work:
SELECT DISTINCT 'CREATE SCHEMA ['+TABLE_SCHEMA+'];'
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_SCHEMA <> 'dbo';
SELECT 'CREATE SYNONYM ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] FOR [192.168.0.45].['+TABLE_CATALOG+'].['+TABLE_SCHEMA+'].['+TABLE_NAME+'];'
FROM [INFORMATION_SCHEMA].[TABLES];
Running such a script will produce a script which you can run on the server you are using as a proxy to create all of the synonyms.