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?
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.
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.
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.
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'