SQL 2016 Linked Server Problems

rr-4098 1,276

I am trying to setup a linked server connection to another DB running a non-MSSQL DB. On the local server the account I am using has Sysadmin right on the remote server the local DB account has what they call Admin rights as well. We have tried using impersonation and various other options on this screen all cause the console to hang on executing when you click ok. Please note we did test the ODBC connection outside of SSMS and it worked,

Any ideas?????

Erland Sommarskog 102.4K Reputation points

2024-05-15T21:38:37.4466667+00:00

I don't have much idea of what could be the problem, since I don't have much idea of what you are actually doing. Sounds like it could be a blocking situation going on. Or just a slow query.

You talk about "this screen"? What screen? What console are you talking about?

Please share with us how you set up the linked server and what query you are running. Then we can at least get an idea of further troubleshooting.
rr-4098 1,276 Reputation points

2024-05-15T23:20:22.0033333+00:00

Sorry for the confusion. I am referring to the initial setup of the linked server from the ssms console.
Omar José Tercero Moreira 20 Reputation points

2024-05-16T00:20:30.43+00:00

What exactly is the warning or error message do you have, what are you trying?, a select from a remote view? o what are you trying to do with linked?
ZoeHui-MSFT 33,781 Reputation points

2024-05-16T02:20:23.46+00:00

Hi @rr-4098,

Is there any error message? If possible, you may provide the screenshot for us to narrow down the issue.
rr-4098 1,276 Reputation points

2024-05-16T11:29:13.7+00:00

That is the problem there is no error. When setting up the linked connection in SSMS, and I click OK to created the linked server connection. It hangs on executing and I have to close out of SSMS to get out of it.
rr-4098 1,276 Reputation points

2024-05-16T11:30:21.8533333+00:00

There is no error message . When I click ok to setup the linked connection in SSMS, it hangs on executing and never completes.
Omar José Tercero Moreira 20 Reputation points

2024-05-16T15:34:01.08+00:00

Have you try update SSMS?
Omar José Tercero Moreira 20 Reputation points

2024-05-16T15:38:53.1933333+00:00

Have you try to update SSMS and try again? it seems a trouble from the SSMS.
rr-4098 1,276 Reputation points

2024-05-16T18:26:18.7566667+00:00

Updating SSMS is a good point. I was also think about using TSQL. In the meantime, I have asked the user who manages the remote DB to make sure the account they gave us has the proper permissions. If it didn't, you would think SSMS would at least throw an error during the linked server setup...
Omar José Tercero Moreira 20 Reputation points

2024-05-16T18:47:55.26+00:00

Well, if the account which your are using in order to config the linked feature doesn't have any permission, at least would have thrown a message about it, but you told us that you couldn't ever saw a notification and nothing happened, that's why i told you to update the ssms.

Accepted answer

Erland Sommarskog 102.4K Reputation points

2024-05-16T21:29:17.98+00:00
So if your description is accurate, it sound like a blocking issue. When you create a linked server, you are only updating the local system catalog. You are not connecting to the remote server. That will not happen until you use the linked server in a query or try the Test Conncection button.

Why it would be blocked? Well, maybe someone did

BEGIN TRANSACTION EXEC sp_addlinkedserver YOURSERVERNAME

And left that transaction open.

You could use sp_who2 while SSMS is open to check for blocking.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

SQL 2016 Linked Server Problems

0 additional answers