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.
sql
sql-server linked-server asked Apr 4 '16 at 15:49
user3446325user3446325
1115
add a comment |
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.
sql
sql-server linked-server asked Apr 4 '16 at 15:49
user3446325user3446325
1115
Try---> exec nameofyourSP 'remote access', 1 reconfigure
– Eray Balkanli
Apr 4 '16 at 16:03
add a comment |
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.
sql
sql-server linked-server asked Apr 4 '16 at 15:49
user3446325user3446325
1115
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.
sql
sql-server linked-server sql
sql-server linked-server asked Apr 4 '16 at 15:49
user3446325user3446325
1115
asked Apr 4 '16 at 15:49
user3446325user3446325
1115
asked Apr 4 '16 at 15:49
user3446325user3446325
1115
asked Apr 4 '16 at 15:49
user3446325user3446325
1115
asked Apr 4 '16 at 15:49
user3446325user3446325
1115
1115
Try---> exec nameofyourSP 'remote access', 1 reconfigure
– Eray Balkanli
Apr 4 '16 at 16:03
add a comment |
Try---> exec nameofyourSP 'remote access', 1 reconfigure
– Eray Balkanli
Apr 4 '16 at 16:03
Try---> exec nameofyourSP 'remote access', 1 reconfigure
– Eray Balkanli
Apr 4 '16 at 16:03
Try---> exec nameofyourSP 'remote access', 1 reconfigure
– Eray Balkanli
Apr 4 '16 at 16:03
add a comment |
4 Answers
4
active
oldest
votes
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
edited Sep 14 '17 at 8:56
TT.
12.5k63465
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
add a comment |
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.
answered Nov 23 '18 at 7:20
GriffinGriffin
112
add a comment |
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.
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
add a comment |
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.
edited Jan 22 '18 at 8:24
TT.
12.5k63465
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f36407261%2fsql-linked-server-remote-access-error-when-executing-sp%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
edited Sep 14 '17 at 8:56
TT.
12.5k63465
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
add a comment |
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
edited Sep 14 '17 at 8:56
TT.
12.5k63465
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
add a comment |
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
edited Sep 14 '17 at 8:56
TT.
12.5k63465
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
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
edited Sep 14 '17 at 8:56
TT.
12.5k63465
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
edited Sep 14 '17 at 8:56
TT.
12.5k63465
edited Sep 14 '17 at 8:56
TT.
12.5k63465
edited Sep 14 '17 at 8:56
TT.
12.5k63465
12.5k63465
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
answered Sep 14 '17 at 8:52
Rafa SanchezRafa Sanchez
111
111
add a comment |
add a comment |
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.
answered Nov 23 '18 at 7:20
GriffinGriffin
112
add a comment |
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.
answered Nov 23 '18 at 7:20
GriffinGriffin
112
add a comment |
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.
answered Nov 23 '18 at 7:20
GriffinGriffin
112
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.
answered Nov 23 '18 at 7:20
GriffinGriffin
112
edited Nov 26 '18 at 3:33
answered Nov 23 '18 at 7:20
GriffinGriffin
112
answered Nov 23 '18 at 7:20
GriffinGriffin
112
answered Nov 23 '18 at 7:20
GriffinGriffin
112
112
add a comment |
add a comment |
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.
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
add a comment |
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.
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
add a comment |
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.
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
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.
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
answered Apr 4 '16 at 16:05
Eray BalkanliEray Balkanli
4,47452346
4,47452346
add a comment |
add a comment |
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.
edited Jan 22 '18 at 8:24
TT.
12.5k63465
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
add a comment |
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.
edited Jan 22 '18 at 8:24
TT.
12.5k63465
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
add a comment |
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.
edited Jan 22 '18 at 8:24
TT.
12.5k63465
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
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.
edited Jan 22 '18 at 8:24
TT.
12.5k63465
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
edited Jan 22 '18 at 8:24
TT.
12.5k63465
edited Jan 22 '18 at 8:24
TT.
12.5k63465
edited Jan 22 '18 at 8:24
TT.
12.5k63465
12.5k63465
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
answered Jan 22 '18 at 7:18
amir lakhiamir lakhi
1
1
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f36407261%2fsql-linked-server-remote-access-error-when-executing-sp%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try---> exec nameofyourSP 'remote access', 1 reconfigure
– Eray Balkanli
Apr 4 '16 at 16:03