asked
Creating a Linked Server, linking to SQL 2008
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. Any suggestions? Thanks ! Randy
Thanks for the tips. Created an account on the SQL2008 server called sa_link. Tried what you suggested and received the following: Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91 An error occurred during decryption. Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98 There is no remote user 'sa_link' mapped to local user '(null)' from the remote server 'HOME'. Perhaps it's more to do with the first error regarding decryption. Any other thoughts?
I should also add that I have an additional SQL2008 server on the same domain as the SQL2005 server, and from THERE I have no problems creating a linked server to the offsite SQL2008 box. So, it must be something in the SQL2005 configuration. I found a posting elsewhere online that suggested ALTER SERVICE MASTER KEY FORCE REGENERATE but I'm afraid of running that on our production SQL2005 server. Any ideas?
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';
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'
Fatherjack, Thank you. Linked servers are new to me. That is what SirSQL suggested as well. I used that in his example.
That's great. If your question has been answered please tick the relevant answer to show anyone else who might read this forum that you were helped ny it. You and the person who contributes the answer will get a few karma points too! :)
