I have a local SQL 2005, x32, SP3 server. On this SQL2005 server, I am trying to created a linked server to a SQL2008 x64 database I have at another site. SQL2005 is on a local domain, and SQL2008 server is in a local workgroup (no domain).
From the SQL2005 I can use the following cmd to query the remote SQL2008 server: sqlcmd -S tcp:12.345.12.345,1433 -U sa -P xxxxxx
This works fine.
Also, from the SSMS on SQL2005, I can register the SQL2008 server. Server name = 12.345.12.345,1433 login= sa password = xxxxxx
So...when I'm trying to create a linked server on SQL2005 box, linking to the SQL2008 box....
exec sp_addlinkedServer @server = 'HOME', @provider = 'SQLOLEDB', @provstr='SERVER=12.345.12.345,1433;UID=sa;PWD=xxxxxx;' ,@srvproduct=''
I've tried every combination of Login Mappings I can imagine, but consistenly receive the following error:"There is no remote user 'sa' mapped to local user 'sa' from the remote server 'HOME'.
On both servers the SA account is setup with the same exact password. On the SQL2008 server, SQL Browsing Services is enabled.
Thanks ! Randy
Is there a reason why you are using @provider='SQLOLEDB'? If you are going SQL Server:SQL Server I would recommend you use @provider='SQL Server'
answered Aug 24, 2011 at 11:01 AM
Try using the following to setup your linked server (I caveat this by saying that you should not setup the linked server to use SA, you should map local logins to remote logins on the linked server that have just the permissions that you require, what you are doing here creates a huge security whole whereby anyone with access on the local machine is a sysadmin on the remote machine)
EXEC master.dbo.sp_addlinkedserver @server = N'HOME', @srvproduct=N'SQLServer', @provider=N'SQLNCLI', @datasrc=N'12.345.12.345';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'HOME', @locallogin = NULL , @useself = N'False', @rmtuser = N'SA', @rmtpassword = N'xxxxx';
EXEC master.dbo.sp_serveroption @server=N'HOME', @optname=N'data access', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'HOME', @optname=N'rpc', @optvalue=N'true';EXEC master.dbo.sp_serveroption @server=N'HOME', @optname=N'rpc out', @optvalue=N'true';