This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(44.800000000000004, 65%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAF%3C/text%3E%3C/svg%3E)
There is a request from user to find how many external connection comes in as linked server to a database. Highly appreciated for answers
I think that question could benefit from some clarification. First of all, you are asking about incoming connections? And those are connections to a specific database? (So incoming connections to other databases does not count?) And how many? Is that within some time interval? Or is the question how many other servers in the organisation that may connect to this database?
Yes an incoming connection to specific database. Simple we need to upgrade an instance from sql2012 to sql2019. It's side by side so if I move from one server to another the incoming connection will break. That's the reason we need to find out the incoming connection change the linked server pointing to new server and database. Outgoing connections I can find in server objects but incoming we don't have a clue
This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
Hi Allen Francis,
We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Best regards,
Seeya
Hi Allen Francis,
You can run this procedure sp_linkedservers to see the list of linked servers defined in the local server.
Please feel free to let me know if I have misunderstood your meaning.
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Sp_linkedservers will give what all present from a particular sql instance / database to other instances. But I want to know how many linked server are connected externally to a database.
An access via Linked Server from an other SQL Server is as any other client access, there is no difference.
If that are dedicated SQL Server without any other app running on it, then you can get them by the server IP address.
Yes an incoming connection to specific database. Simple we need to upgrade an instance from sql2012 to sql2019.
And you are only moving one of the databases on that instance?
I think the only option is to scan sys.servers in your entire estate for linked server that points to this instance. Now, if there are umpteen databases on this instance, you may find many references that are unaffected, since they relate to other databases on this instance. A linked server can be setup with a certain target database, but that is not always the case. But at least, you can start there.
As for running a query on all your servers, check out what the friendly people at dbatools.io can offer.
Hi Allen Francis,
You can run this query for more detailed information:
SELECT *
FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. Your contribution is highly appreciated.
Hi Seeya, The result is the same that I can find out in server objects - - - linked servers. The script that you gives details about example Server 1 All database that are having linked servers to server B to z. But if I want to know how many are connected as linked server to Server 1 and fetching data.
It appears that Seeya has not understood your question. As I said, there is not really any better way here than to scan your estate. You could set up logon triggers to capture logins and it is possible to identify which are incoming linked servers. But then you need to know which database they are connecting to. And, what is worse, there could be an instance that only connects once a month.
If this is a general requirement in your organization you need to set up routines when you create linked servers, so that it is not performed through SSMS, but a PowerShell script that also registers the linked server in a central repository.