SQL Server Client Aliases Through GPO/ADM

This week I had an interesting question proposed by a customer on how to implement a group policy that would generate the proper SQL Server alias records on all machines in the domain. The desired result was to have users be able to connect to using a simple CNAME that could easily be changed in the future, if needed.

I successfully reproduced this in my lab and confirmed the same process worked for the customer in question. I figured this would be be great walk-through to broadcast to a larger audience. However before we get started, here are some things to keep in mind:

1) We strongly suggest trying this in a test environment before pushing to production

2) Relevant system/environmental information for this repro:

• DC is running Windows Server 2016
• SQL Server editions tested are SQL 2017 and SQL 2012
• In my lab there is only one DC, so replication isn't in consideration
• I used a domain admin account for this process
• The FQDN of the node I am trying to create an Alias for is CSSSQL-PUBDIS.CSSSQL.LAB, and is running a named instance of SQL called "PUBDIS"

Now that we got that out of the way, let's get started!

• Step 1
  • Let's create an .adm file to use for the registry value. Below you can see an example I used for my machine that differentiates alias' for 32 and 64 bit processes (for your environment you’ll likely want them to be the same but I separated them to show proof of concept). Create a new text file, paste the script in there, and save it with a sensible name with an .adm extension.
  • For this example, I named the file "SqlAliasGPO.adm" 
    • CLASS MACHINE
      CATEGORY "SQL Server Client aliases"
                      KEYNAME "SOFTWARE\Policies"
                      POLICY "List of SQL Server aliases"
                                     EXPLAIN "This list of SQL Server aliases will be deployed to users which are affected by this GPO !"
                                     PART "ALIASLIST64"                           LISTBOX
                                                     KEYNAME "SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo"
                                                     EXPLICITVALUE
                                     END PART
                                     PART "ALIASLIST32"                         LISTBOX
                                                     KEYNAME "SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
                                                     EXPLICITVALUE
                                     END PART
                      END POLICY
      END CATEGORY
• Step 2
  • Open Group Policy Management and right-click in the desired location and select "New GPO" to create a new object. Make sure you select the proper location when creating the object (whether it be at the top of the domain or inside the OU containing the computers where you wish to create the alias).
  • In this example, I am creating the object "SQLAliasGPO" and put it at the top of the domain.
    • 
• Step 3
  • Right-click the new GPO and select "Edit…"
    • 
    •  
• Step 4
  • Create a new Template through Policies --> Administrative Templates --> Right-click and select "Add/Remove Templates…"
    • 
• Step 5
  • Select "Add"
    • 
• Step 6
  • This will prompt you to select a file. Navigate to the .adm file created in Step 1. Select "Open" then "Close"
    • 
• Step 7
  • Then go to Classic Administrative Templates and right-click and select "Edit"
    • 
• Step 8
  • Then you can mark it as Enabled and can select "Show…" for both values and enter in the appropriate string. Then select Apply and Ok. Validate the settings saved.
    • 
    • 
• Step 9 
  • Go to your client machine and force the GP update (making sure that it meets the requirements of where you configured the GPO).
    
    • C:\Users\Administrator>gpupdate /force
      Updating policy...
       
      Computer Policy update has completed successfully.
• Step 10
  • Test your connection from your remote machine. Here I am using SSMS which is a 32 bit process, so I need to pass the 32 bit alias. If the values were the same for both the 32 and 64 bit alias as briefly mentioned in step 1, it wouldn't matter. You can see the connections below resolve properly. When I tried my 64 bit alias GPOSQLAlias64 it failed (expected behavior).
    • 
  • Furthermore you can confirm that you see the appropriate records in the following registry locations:
    • 32 bit:
      • HKLM\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client\ConnectTo
    • 64 bit:
      • HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo 
         

Hope this helps!

James Ferebee

SQL Server Support Engineer

Microsoft CSS