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

Andreas Cichy 1 Reputation point


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 ?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,652 questions
0 comments No comments
{count} votes

7 answers

Sort by: Most helpful
  1. Olaf Helper 36,376 Reputation points

    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.

    0 comments No comments

  2. Andreas Cichy 1 Reputation point

    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

    0 comments No comments

  3. Erland Sommarskog 93,376 Reputation points

    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.

  4. YufeiShao-msft 7,031 Reputation points

    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.

  5. Andreas Cichy 1 Reputation point

    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:

    AndreasSeiner REG_SZ DBMSSOCN,,50667

    AndreasSeiner32 REG_SZ DBMSSOCN,,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.