SharePoint 2007

How to create a SQL Alias 32 bit and 64 bit

When installing SharePoint, a good practice is to use SQL Aliases for your installation.
To manage and create a SQL Alias via a gui tool, go to start > run > cliconfg and Enter. Then go to the Alias tab.

Since SharePoint servers are all 64bit, if you create a SQL Alias via the gui you would only create a 64bit SQL Alias. What about a 32bit SQL Alias? Do we need a 32 bit SQL Alias??

Keep in mind that tools such as SSMS – SQL Server Management Studio are 32bit, so we DO need a 32bit SQL Alias created if you want to connect to SQL using the SQL Alias name.
I find it easy to script out both my 64bit SQL Alias (for SharePoint) as well as 32bit SQL Alias so that we can connect via SSMS (makes life easier to remember friendly names!).

Now there are other ways to do this but here is a simple PowerShell script that I have used over the years to create a 32 bit and a 64 bit SQL Alias.

# Set the two variables and then run in a PowerShell prompt
 $sqlserver = "SQLservername\instancename,1433" # Update as required. Change the port number if using a static custom port. If using a dynamic custom port, then remove the comma and port number - ,1433.
# This script creates a 64bit and 32bit SQL Alias on the server. Run this script on all servers in your SharePoint farm.
Write-Host “Creating x64 SQL Alias”
New-Item -path HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
 New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo -name $SQLAliasName -propertytype String -value "DBMSSOCN,$sqlserver"
Write-Host “Creating 32bit SQL Alias”
New-Item -path HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo
 Write-Host "Configured SQL Alias on the Server"
 New-ItemProperty HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo -name $SQLAliasName -propertytype String -value "DBMSSOCN,$sqlserver"

Note: Not all applications are SQL Alias aware. When configuring Reporting Services (SharePoint integrated mode), specifying a SQL Alias in the Reporting Services Configuration Manager does not work. I had to specify the database server in the SQLSERVER\InstanceName format.

Will be interested to know if you have had issues using SQL Alias names with other applications/features.

Write A Comment