How can I connect to a different server in SQL Server?
<p>We have base database on one server and other database on another server and we need to connect to it.</p>

<p>How can link them with a stored procedure?</p>

<pre><code>CREATE PROCEDURE link_sv
   @UserName nvarchar(max)='',
   @Password nvarchar(max)='',
   @Server nvarchar(max)='',
   @DataBase nvarchar(max)=''
AS
BEGIN
   -- how do it
   -- select [dbo].tbl1.*,@Server.@DataBase.tblUsers.Name from [dbo].tbl1 inner join @Server.@DataBase.tblUsers on [dbo].tbl1.ID=@Server.@DataBase.tblUsers.ID
 END
 GO
</code></pre>

<p>How write this procedure?</p>

<p>I use it:</p>

<pre><code>SELECT a.*
FROM OPENROWSET('SQLOLEDB','Data Source=hodb01; UID=MyLogin; PWD=MyPassowrd', 'select top 1 * from HQPMMPRD.dbo.item_master') AS a
</code></pre>

<p>but SQL Server blocked access to statement </p>

<blockquote>
  <p>'OpenRowset/OpenDatasource'.this component is turned off as part of the security configuration for this server.</p>
</blockquote>

<p>My database in host server. I can't access and change security configuration.</p>