SQL connection strings

Question

SQL connection strings

CycleDude 61

I have a few SQL Servers, and I need to figure out a way to query all of them and extract the connection strings to determine which other servers are making connections to the SQL servers. Is there a way to do this task? Thanks all.

Ronen Ariely 15,206 Reputation points

2022-01-06T18:58:42.227+00:00

Hi Razzi

Welcome to the QnA forums

Can you please try to elaborate what you have and what you need and please remember that we know nothing about your system so you need to start from the beginning and explain it in a way that we could understand it. Your description is vague.

Do you mean to execute the same query on multiple SQL Server instances? Are these query on the database level or server level or both? Are all your databases have the same structure? Is this a task which you need to do from SSMS (if yes then SSSM have this feature built-in and can manage multiple connection and execute the same query on these)
YufeiShao-msft 7,146 Reputation points

2022-01-11T01:01:44.817+00:00

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

3 answers

Your answer

Ronen Ariely 15,206 Reputation points

2022-01-06T18:58:42.227+00:00

Hi Razzi

Welcome to the QnA forums

Can you please try to elaborate what you have and what you need and please remember that we know nothing about your system so you need to start from the beginning and explain it in a way that we could understand it. Your description is vague.

Do you mean to execute the same query on multiple SQL Server instances? Are these query on the database level or server level or both? Are all your databases have the same structure? Is this a task which you need to do from SSMS (if yes then SSSM have this feature built-in and can manage multiple connection and execute the same query on these)
YufeiShao-msft 7,146 Reputation points

2022-01-11T01:01:44.817+00:00

Please do not forget to mark as accepted answer wherever information provided to you were helpful.

Regards

Answer 1

If I am reading your question correctly, you want to query each server to get the connection string for how to connect to that server.

That is not information you find in the server itself, but it is up to the caller how to refer to the server in the connection string. And there are some options:

By server name only.
By FQDN.
By IP-address.
By a DNS-alias. That is, while the name of the server is Charles, there is an alias somewhere mapping James to Charles, so that James works as well.
The same above, except that the alias is set up in /etc/hosts locally on the server, or in the registry (through SQL Server Configuration Manager, for instance.)

And if server in question is a named instance, the instance may be referred to by name or port number. Or by an alias.

A better approach may be to set up logon triggers that logs the information from the eventdata() function in a table. Of particular interest is the client_net_address attribute, which informs you from which machine the connection was made.

But be careful! Incorrectly implemented logon trigger can cause an outage when no one can login.

Answer 2

Hi @CycleDude ，

If you want to determine what apps are accessing SQL Server, the first place to start is SQL server profiler(Trace). This tool will allow you to track all the app and users that login to the server and access SQL for as long as the trace is running. Choose the correct events to capture, such as Audit Login. Use activity monitor for the same.

If you want to know connection string, the easiest way to get is using the "Server Explorer" in Visual Studio if you have,
Get the details from this thread: How to get the connection String from a database

-------------

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".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Olaf Helper 47,436

extract the connection strings

Extract from where? There is no repository storing such informations.
A connction string is a client setting and highly depends on the used data provider like ODBC, OleDB, ADO.NET etc.

See https://www.connectionstrings.com/sql-server/

Share via

SQL connection strings

3 answers

Your answer