How to retrieve ALIAS definitions used by SQL Server from command line

Question

Hi

I need to obtain the ALIAS definitions from a SQL Server installation for further processing. So anything that has to do with interface doesn't help.

In some installations I can fetch the ALIAS definitions from registry by executing

reg query HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
reg query HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
reg query HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo
reg query HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo

Unfortunately this doesn't work on some SQLServer installation (i.e. 14.00.3162).

Is there any other way to get the ALIAS definitions ? Maybe by running a query against a system table ?

Andreas

Answer 1

ALIAS definitions from a SQL Server installation

I think you are on a wrong track.
A SQL Server alias is a client setting, not a (global) server setting. SQL Server isn't aware, which client uses which of his local alias to connect to.
You can create an alias on a client using cliconfg.exe (SQL Client Configuration Utility).
Yes, you can use SQL Server Configuration Manager to also add an alias, but that's only for that server machine, e.g. to use for a Linked Server.

Answer 2

I'm talking about Linked Server installations. I need the ALIAS information stored in a SQL server installation to access linked servers.

And I need something with which I can extract the information without entering something in an interface. SQL Server Configuration Manager doesn't help as long as it doesn't have a command line argument which allows to extract the ALIAS settings to a file.

Thanx for your answer - but that doesn't help me

Answer 3

You can use xp_regread to read the registry, but it is not exactly a documented procedure.

I'm not sure why you like to use the aliases to define your linked servers. If the aliases are changed or removed, your linked server will stop working.

Answer 4

Hi @Andreas Cichy ,

If you can try to use exec sp_linkedservers
It can generate a summary report of the linked servers in SSMS by querying the system tables

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

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 5

I already know that sys.servers as well as exec sp_linkedservers returns information about the linked servers. That's not the problem.

In these definitions you might use ALIAS names. If ALIAS names are used you additionally need the information where those ALIASes point to. In my SQLServer2014 I can get that information from registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
AndreasSeiner REG_SZ DBMSSOCN,gechcae-col1.asg.com,50667

HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo
AndreasSeiner32 REG_SZ DBMSSOCN,gechcae-col1.asg.com,50667

But - for what reason ever - this doesn't work in other installations. It looks like newer SQL Server versions store the ALIAS definitions somewhere else.

The question still is: Where can I get that ALIAS definition from without using an interface but a command line tool or SQL SELECT statement.