SQLTeam.com | Weblogs | Forums

Creating a linked server with alias name


#1

I have been struggling to create a linked server with an alias.
Alias: myMadeUpAliasName
SQL target: myRealServerName
M target is an SQL 2008 server, using AD credentials. I have tried using System/NT Authority redentials.
Setting up the linked server with the real serverName (FQDN) works.

I have gotten too many different SQL errors when testing this to list. The recent one I am stuck on is that my provider string is incorrect. "server=192.168.1.55", user id = forresto.user.company.com.

I have tried creating this through the GUI and through a query. Am I trying to do something that is not allowed? Do I need to set up a local ODBC connection and use that?

forresto


#2

Can you post the EXACT steps you used? What is the version of SQL Server you are using?

These are the steps you need to do. I did this a while ago and is just copying and pasting my notes from then. It was on a SQL 2008R2 or SQL 2012.

  1. Setup Linked Server
    • In SQL Server Management Studio open Linked Servers and then 'New Linked Server'.
    • Inside of appeared wizard – Select the General tab.
    • Specify alias name in "Linked server" field.
    • Select SQL Native Client as provider.
    • Add SQL Server in "Product Name" field.
    • In "Data Source" – specify name of the host to be used as linked server.

2.Specify Security
• In Security tab – specify proper security options (e.g. security context).

3.Set Server Options
• In Server Options tab – put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.


#3

I can safely say I followed those exact steps that you just posted with this exception:
• Add SQL Server in "Product Name" field. < ---became sql_server, the no underline did not work

with this I get NT authority / anonymous login not allowed error.

I also tried a slew of other things.


#4

In case helpful here is the script I use when creating a Linked Server.

Do a Find & Replace of all the elements defined at the top, and then highlight-and-execute each individual statement as appropriate

-- Create a Linked Server
-- Use this script to create a linked server by "Find&Replace" of key tokens

-- Globally change the following:

-- EITHER IP Address:		111.222.333.444
-- OR Remote server name:	MyRemoteServer
--  Server name can be name resolved by DNS, name like SERVERNAME.HOSTS.MYDomain.COM
--  or even IP address in the format 123.456.789.123
--  Do **NOT** include [ ] - so A.B.C is fine as a name

-- SQL Port: 1433	-- Change if using a non-standard port and use the SPECIAL sp_addlinkedserver command below

-- User Login:		MyUserID
-- User Password:	MyPassword
-- (This should be a database login available on both machines with the same password)


-- *** Having done Find&Replace of parameters (above), highlight each section (below) and execute

-- Test using OPENQUERY.  The Target Server needs to be set up as an "Alias" using SQL Client Configuration tools
SELECT	*
FROM	OPENQUERY([MyRemoteServer], 'SELECT TOP 10 * FROM master.dbo.sysobjects') 
GO

USE master
GO

-- Show existing linked servers
EXEC sp_linkedservers


-- EXEC sp_helpserver			-- (Also shows services etc.

-- Delete any existing linked-server - e.f. previous failed attempts (optional)
EXEC sp_dropserver 
	@server = 'MyRemoteServer', 
	@droplogins = 'droplogins'		-- 'droplogins' = Drop associated logins, NULL=Do not drop logins


-- Create Linked Server (if you are using a NON-Standard port use the command below instead)
EXEC sp_addlinkedserver 
	@server = 'MyRemoteServer'	-- local name of the linked server to create.
					-- If data_source is not specified, server is the actual name of the instance
,	@srvproduct = 'SQL Server'	-- product name of the OLE DB data source to add as a linked server
					-- If "SQL Server", provider_name, data_source, location, provider_string, and catalog do not need to be specified.
					-- If MySQL use "MySQL"
-- Execute ONLY to here IF you are connecting two SQL servers
	,@provider = 'SQLOLEDB'		-- unique programmatic identifier of the OLE DB provider (PROGID) 
					-- If MySQL use "MSDASQL"
	,@datasrc = 'MyRemoteServer'	-- name of the data source as interpreted by the OLE DB provider (DBPROP_INIT_DATASOURCE property)

-- For NON-Standard SQL Port use:
-- EXEC sp_addlinkedserver @server = 'MyRemoteServer', @srvproduct = 'SQL Server', @provider = 'SQLOLEDB', @datasrc = 'MyRemoteServer,1433'	-- Set the port appropriately
-- For Oracle try:
-- EXEC sp_addlinkedserver @server = 'MyOracleServer', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'OracleServerName'	-- , @location=NULL, @provstr=NULL, @catalog=NULL



-- Remove existing Linked Server Login (optional)
EXEC sp_droplinkedsrvlogin @rmtsrvname = 'MyRemoteServer'
,	@locallogin = 'MyUserID'



-- Create Linked Server Login
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MyRemoteServer' 
,	@useself = 'false'	-- true=Connect using current UserID/Password, false=use rmtuser/rmtpassword below
,	@locallogin = 'MyUserID'	-- NULL=All local logins use this remote login account, otherwise local login UserName being set up (repeat for each one required)
-- Execute ONLY to here IF @UseSelf='TRUE' (above)
,	@rmtuser = 'MyUserID'	-- UserName on Remote corresponding to this @LocalLogin.   
,	@rmtpassword = 'MyPassword'	-- Ditto password



-- Test connection - should list databases on remote machine
select top 10 name from [MyRemoteServer].master.dbo.sysdatabases

EXEC sp_catalogs 'MyRemoteServer'	-- Display equivalent of SQL's Databases (on remote server)



-- If you get this error message:
-- "Server '111.222.333.444' is not configured for DATA ACCESS"
-- then execute this statement
exec sp_serveroption 'MyRemoteServer', 'data access', 'true'

-- exec sp_serveroption @server='MyRemoteServer', @optname='rpc out', @optvalue=true
-- exec sp_serveroption 'MyRemoteServer', 'rpc out', false

-- Test again!
select top 10 name from [MyRemoteServer].master.dbo.sysdatabases



-- Failing that try to PING the remote server (by Extended Procedure Command line call)
-- if that fails then your SQL box cannot see the remote server

exec master.dbo.xp_cmdshell 'PING 111.222.333.444'