SQL Server aliases

(a.k.a. Honestly…My top Layer-8 issue of 2010)

SQL Server aliases: they need to be created on all clients that use them, not just the SQL Server!

How do we create them?  Two primary ways:

  1. CLICONFG.EXE (%systemroot%\system32\cliconfg).  Native to Windows, the SQL Server Client Network Utility is the tool you can depend on when you need to create a local alias.
  2. SQL Server Configuration Manager.  This is the tool that ships with the Basic management tools installation of SQL.  This is the one you’ll be familiar with if you configure aliases, protocols, etc. on your SQL server.

When configuring aliases there’s a little caveat.  You will, in many cases, require both a 32-bit and 64-bit alias –assuming a 64-bit OS here.  The path above, which isn’t strictly necessary as it’s part of the %PATH% environment variable, is the 64-bit version of the tool.  If you need a 32-bit alias, and you will if you want SQL Server Management Studio (SSMS) to talk to your database using the alias, then you’ll need to use %systemroot%\SysWOW64\cliconfg.exe.  In SQL Server Configuration Utility there’s a separate branch for 32-bit and 64-bit client configuration.

In addition to the above there’s also scripts (the third way).  The following *example* scripts illustrate how to create a SQL Server alias using two techniques.  The first uses SMO: the supported and recommended approach.  The second directly modifies the registry.  Supportability is grey for this one but the script is required when you’re deploying aliases onto application servers that don’t have the basic SQL client tools installed, e.g. I use a version of this script to deploy aliases to my FIM servers during deployment.

Creating an alias in PowerShell using SMO

########################################################################################
## CreateSqlServerAlias.ps1 v1.0
## 
## This script uses SQL Server SMO (Server Management Objects) to create an alias.
##  
## This script is used to create an alias.  This script will need to be run using both:
##  - %systemroot%\system32\powershell.exe; and
##  - %systemroot%\syswow64\powershell.ex
## 
## The documentation for SMO can be found here:
## http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.wmi.ser
## veralias.aspx
## 
########################################################################################

########################################################################################
[String]$hostname = (
    [String]::Format("{0}.{1}", 
    (gc Env:\ComputerName), 
    (gc Env:\USERDNSDOMAIN)).ToLower());
[String]$aliasName = "IDMSYS";
[String]$serverName = "sqlsvr.infra.contoso.com\idmsys";
[String]$connectionString = [String]::Empty #this is the port; leave as empty for dynamic
[String]$protocolName = "TCP";
########################################################################################

Write-Host "`nCreateSqlServerAlias v1.0ps1 Paul Williams (pawill@microsoft.com) Aug. 2011`n";

function is32BitProcess()
{ # note that this function assumes the script is being run by powershell.exe
  # thus we can rely on the path of that executable.
    if(([Diagnostics.Process]::GetCurrentProcess()).Path.Contains("syswow64"))
    {
        return $true;
    }

    return $false;
}

if(is32BitProcess)
{
    Write-Host "Attempting to create a 32-bit SQL Server alias on <$hostname>.";
}
else
{
    Write-Host "Attempting to create a 64-bit SQL Server alias on <$hostname>.";
}

# Load the SQL SMO assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null;

# Create an object that represents the WMI installation on an instance of a Microsoft
# SQL Server client because an alias must reside on a SQL Server client instance
[Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer]$computer = `
    New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer;

# Next create an Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias alias object
[Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias]$newalias = `
    New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ServerAlias");

# Parent: Gets or sets the ManagedComputer object that is the parent of the 
# ServerAlias object.
$newalias.Parent = $computer;

# Name: Gets or sets the name of the referenced Windows Management 
# Instrumentation (WMI) object.
$newalias.Name = $aliasName;

# ServerName: Gets or sets the name of the instance of SQL Server to which the 
# server alias connects.  Computer\Instance is the SQL Server instance the alias 
# points to.  If this is a SQL Cluster remember to use the SQL Cluster Name.  You 
# should use a qualified name to ensure Kerberos authN too, e.g.
# sqlsvr3.corp.contoso.com (default) or sqlsvr4.corp.contoso.com\idmsys (instance).
$newalias.ServerName = $serverName;

# ConnectionString: Gets or sets the connection string that the alias uses to connect
# to the server.  This property cannot be null.  To assign a dynamic port provide an 
# empty string.
# e.g.  $newalias.ConnectionString = 57890; for a specific port
$newalias.ConnectionString = $connectionString;

# ProtocolName: Get/set the name of the protocol used by the server connection alias.
$newalias.ProtocolName = $protocolName;

# Finally we create
try
{
    $newalias.Create();
    Write-Host " - Successfully created SQL Alias of <$aliasName>.`n`n";
}
catch
{
    Write-Host "Failed to create SQL server alias!`n`n";
}

Creating an alias in PowerShell without SMO

###################################################################################################
## CreateSqlServerAlias.ps1 v1.2
## 
## This script is used to create an alias.  The script supports one of three flags provided:
## -32Bit, -64Bit or -32And64Bit.  As the flag implies this defines whether or not you create
## a 32-bit alias, a 64-bit alias or both a 32-bit and 64-bit alias.
## 
## This script has no dependency on SQL components -the SMO approach requires the
## SQL client tools (basic) to function; this script doesn't.
## 
###################################################################################################

[CmdletBinding(DefaultParametersetName="default")] 
PARAM
(
    [Parameter(Mandatory = $false)] # alias name -update default value
    [String]$SQLAliasName = "IDMSYS", 
    
    [Parameter(Mandatory = $false)] # server [and \instance]
                                    # consider modifying this default value
    [String]$SQLInstance =  "ptw-hp8540w-w7e\Development", 
    
    [Parameter(Mandatory = $false)] # empty string for dynamic
    [String]$TCPPort =      "", 
    
    [Parameter(Mandatory = $false)] # protocol is always tcp for us
    [String]$ProtocolName = "TCP",

    [Parameter(Mandatory = $false, ParameterSetName = "default")]
    [Switch]$32And64Bit  = [Switch]::Present,
    
    [Parameter(Mandatory = $false, ParameterSetName = "x86")]
    [Switch]$32Bit,

    [Parameter(Mandatory = $false, ParameterSetName = "x64")]
    [Switch]$64Bit
);


###################################################################################################
## TestRegistryValue(String Path, String Value)
## 
## Returns true if the path and value exist; false otherwise
## 
###################################################################################################
function TestRegistryValue()
{
    PARAM
    (
        [Parameter(Mandatory = $true)]
        [String]$Path,
        
        [Parameter(Mandatory = $true)]
        [String]$Value
    );
    
    Get-ItemProperty `
        -Path $Path `
        -Name $Value `
        -ErrorAction SilentlyContinue | Out-Null;
    
    return $?;
}

Write-Host "`nCreateSqlServerAlias v1.1ps1 Paul Williams (pawill@microsoft.com) Jan. 2012`n";

# build hostname
[String]$hostname = ([String]::Format("{0}.{1}", 
    (gc Env:\ComputerName), 
    (gc Env:\USERDNSDOMAIN)).ToLower()
);

if($32And64Bit.IsPresent)
{
    Write-Host "Attempting to create a 32-bit and 64-bit SQL Server alias on <$hostname>.";
    [String[]]$regPaths = @( "HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo",
        "HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo" );
}
else
{
    if($32Bit.IsPresent)
    {
        Write-Host "Attempting to create a 32-bit SQL Server alias on <$hostname>.";
        [String[]]$regPaths = @( "HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo" );
    }
        
    if($64Bit.IsPresent)
    {
        Write-Host "Attempting to create a 64-bit SQL Server alias on <$hostname>.";
        [String[]]$regPaths = @( "HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo" );
    }
}

[String]$sQLAliasValue = "DBMSSOCN,$sQLInstance";
if(![String]::IsNullOrEmpty($tCPPort))
{
    $sQLAliasValue += ",$tCPPort";
}

foreach($regPath in $regPaths)
{
    if(!(Test-Path -Path $regPath))
    { # ConnectTo has not been created yet
        if(Test-Path -Path $regPath.Replace("\ConnectTo", [String]::Empty))
        {
            [void](New-Item -Path $regPath);
        }
        else
        {
            Write-Host "Unable to configure SQL Server aliases on this computer!`n`n";
            Exit;
        }
    }
    
    if($?)
    {
        try
        {
            if($regPath.Contains("Wow64"))
            {
                [String]$platform = "32-bit";
            }
            else
            {
                [String]$platform = "64-bit";
            }

            if(testRegistryValue -Path $regPath -Value $sQLAliasName)
            {
                [String]$cur = (Get-ItemProperty -Path $regPath -Name $sQLAliasName).($sQLAliasName)
                if($cur.ToLower() -ne $sQLAliasValue.ToLower())
                {
                    Set-ItemProperty -Path $regPath -Name $sQLAliasName -Value $sQLAliasValue;
                    Write-Host " - Successfully updated $platform SQL Alias of <$sQLAliasName>.";
                    $cur = $cur.Replace("DBMSSOCN,", [String]::Empty);
                    $sQLAliasValue = $sQLAliasValue.Replace("DBMSSOCN,", [String]::Empty);
                    Write-Host "Previous value: <$cur>`nCurrent value:  <$sQLAliasValue>";
                }
                else
                {
                    Write-Host " - $platform alias <$sQLAliasName> already exists (and is the same).  No changes made.";
                }
            }
            else
            {
                New-ItemProperty `
                    -Path $regPath `
                    -Name $sQLAliasName `
                    -PropertyType String `
                    -Value $sQLAliasValue | Out-Null;
            
                Write-Host " - Successfully created $platform SQL Alias of <$sQLAliasName>.";
                    
            }
        }
        catch
        {
            Write-Host "Failed to create $platform SQL server alias!"
            Write-Host $_.Exception "`n`n";
        }
    }
}

Write-Host "`n";

Why am I writing this blog?  Well, I noticed in two different labs that the FIM Service was authenticating to SQL using NTLM[1] when I’d specified an unqualified host for the SQL Server, e.g. SQLSVR3\IDMSYS (unqualified host plus instance).  Changing this to a qualified host, e.g. sqlsvr3.corp.tailspin-toys.com\idmsys resulted in the subsequent authentication using Kerberos (obviously this is what I want, and I had configured the ability for the service account to register it’s own SPNs).  Because of this I decided I would use aliases in my FIM deployments.  As I write this blog I’m working on two different sets of design documents where I’m proposing that a SQL Alias be configured on all FIM nodes (FIM Service and FIM Synchronization Service) and that all database configuration use the alias.


[1] To ascertain the authentication protocol of the various connections to my SQL Server instance I used the following query.

 USE [master]; SELECT s.host_name, s.program_name, s.client_interface_name, c.net_transport, c.protocol_type, c.net_packet_size, c.auth_scheme, c.client_net_address, s.login_name FROM sys.dm_exec_connections AS C INNER JOIN sys.dm_exec_sessions AS S ON c.session_id = s.session_id ORDER BY s.host_name, s.program_name ASC; 

Thanks to my colleagues James Noyce and Mark Shepard (.NET super hero), for the inspiration for the presented SMO PowerShell script.

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in Scripting, SQL Server and tagged , , , , , , , , , , . Bookmark the permalink.

4 Responses to SQL Server aliases

  1. joe mamma says:

    Interesting, but a lot more useful if you’d bothered to notice your code is cut off on the right side.

    • Hmmm, while I agree and am frustrated by how difficult it is to get PowerShell and other code to layout properly with this theme, using IE7 and IE8 the scrollbar at the bottom of the snippet allows me to scroll to the right and view all the code.

      Cutting and pasting is a tad more frustrating mind you!

      That being said I actually have an update for this post -an alternative that doesn’t require SMO, so when I apply that shortly I will attempt to move the code over to the better syntax highlighter that I’m using elsewhere.

      • Well that prompted me to prioritise the update. 🙂

        I’ve updated the post to include SMO and non-SMO approach and hopefully the layout looks better. Certainly copy works better than it used to. I used IE8 and IE8 in compat mode and it renders fine. I’m not overly happy with the spacing between the code block and the heading but that’s beyond my control.

  2. My apologies for reverting the code snippets back to the older style with the poor line scrolling (the scroll bar is at the bottom only so you have to copy and paste the lot I’m afraid). I had to change the second script because of a bug I encountered using it in production and I’ve lost the syntax highlighter that I used to use. I’ve reverted back to a trusted highlighter until I can iron out the issues with multiple quotes in the other one or find the one I used originally. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s