CODEWITHSUNDEEP
sqlsql-serverlinked-server
user3446325
user3446325

Reputation: 45

SQL Linked server - Remote access error when executing SP

I've set up a linked server between an instance on SQL 2012 and SQL 2008R2 but I keep getting the below error when trying to execute a stored procedure on a remote server: 

 Msg 7201, Level 17, State 4, Line 1
Could not execute procedure on remote server 'TEST' because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

I've checked on both servers and they're configured to allow remote connections. The login that I've used has sysadmin access on the remote server and I can see all the databases when I expand 'Catalogs' within the linked server. When I test the connection this also says it's connected successfully. The SQL Service account also has SA on both instances for what it's worth

The odd thing is when I run a simple select query on one of the tables within the database it shows a result but it doesn't seem to like the SP. 

Select * from Linkserver.database.dbo.table

The above works fine but this SP doesn't: 

EXECUTE  Linkedserver.database.[dbo].[SP] ....

Any recommendation would be appreciated.

Upvotes: 2

Views: 13700

Answers (4)

Griffin
Griffin

Reputation: 31

If you want to run SPs from Server B, then make Server B as the linked server in server A.

Run the script below in Server A, not B:

EXEC sp_configure 'remote access', 1; RECONFIGURE;

Then restart SQL Service in A. Done.

Upvotes: 3

amir lakhi
amir lakhi

Reputation: 11

First Run:

EXEC sp_configure 'remote access', '1';

RECONFIGURE;

You now need to restart MSSQLSERVER in order for the sp_configure command to take effect.

Once you restart it, run sp_configure again and notice that the run_value is 1 now. That's what you want.

Upvotes: 1

Rafa Sanchez
Rafa Sanchez

Reputation: 51

Despite being an old thread, forget about enabling remote access (and restart service) on remote server and try:

EXECUTE (N'database.[dbo].[SP]') AT Linkedserver

Upvotes: 4

Eray Balkanli
Eray Balkanli

Reputation: 7960

Please first check your server properties if remote connections is allowed (related picture below). IF yes, use: exec nameofyourSP 'remote access', 1 reconfigure and try again.

enter image description here

Upvotes: 0

Related Questions