question

SirRandall avatar image
SirRandall 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
sql-server-2008sql-server-2005linked
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·
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?
0 Likes 0 ·
Have you scripted out the linked server configuration from the 2008 machine and executed that on the 2005 machine? Is that what gave you the original error?
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
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';
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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'
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack, Thank you. Linked servers are new to me. That is what SirSQL suggested as well. I used that in his example.
0 Likes 0 ·
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! :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.