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

more ▼

asked Aug 24, 2011 at 10:02 AM in Default

SirRandall gravatar image

11 1 1 1

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?
Aug 24, 2011 at 11:02 AM SirRandall

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?
Aug 24, 2011 at 11:13 AM SirRandall
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?
Aug 24, 2011 at 11:43 AM SirSQL
(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest
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'
more ▼

answered Aug 24, 2011 at 11:01 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108


Thank you. Linked servers are new to me. That is what SirSQL suggested as well. I used that in his example.
Aug 24, 2011 at 11:04 AM SirRandall
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! :)
Aug 24, 2011 at 11:45 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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';
more ▼

answered Aug 24, 2011 at 10:15 AM

SirSQL gravatar image

4.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 24, 2011 at 10:02 AM

Seen: 3134 times

Last Updated: Aug 24, 2011 at 10:02 AM