Home » How to configure SQL Server client aliases in a SharePoint farm

How to configure SQL Server client aliases in a SharePoint farm

Summary:

This article shows how to configure SQL Server client aliases to listen on a non-default port and how to create aliases on application servers. It closely follows Microsoft Technet’s “Harden SQL Server for SharePoint environments.”

Step-by-step

Configure the SQL server to listen on a non-default port.

Note: this step is optional and is not required to create an SQL alias. But it is good policy to use a non-standard port for SQL.

  1. Log into your SQL database server
  2. Go to Start > All Programs > Microsfot SQL Server 2008 R2 (in our case) > Configuration Tools > SQL Server Configuration Manager:
    SQL Server Configuration Manager
  3. In “Sql Server Configuration Manager ,” click the plus sign next to “SQL Server Network Configuration” and then click on “Protocols for SHAREPOINT”.
  4. In the center area, Double-click TCP/IP, click on the “IP Addresses” tab, and specify the default port in the default port box as shown here:SQL Server Configuration Manager
  5. Edit your firewall to block ports 1433 and 1434
  6. Edit your firewall to allow traffic on your chosen non-default port
  7. Log off the SQL Server

Create the alias on the application servers

Note: if you haven’t installed SQL Server 2005/2008 client tools, using SQL Server Configuration Manager (Option 1) may not really be an option for you. If that is the case, skip to option 2 below.

Option 1: Create the alias with SQL Server Configuration Manager

  1. Log into the Application and/or front-end Web servers
  2. Go to Start > SQL Server Configuration Manager
    Start SQL Server Configuration manager
  3. Click all the plus signs so that you can see everything. In particular, highlight “Aliases” under the “SQL Native Client 10.0 Configuration (32bit)”
  4. Create a new alias by right-clicking and choosing “New Alias…”

    Create a new alias
    Right-click and choose
  5. Fill in:
    • Alias Name (a name for your alias),
    • The SQL Server port number you chose (or possibly ‘found’ using netstat),
    • the server name (we did not use a FQDN… only the computer name, but it might be different in your case).

    …But watch out! There’s an extra space by default!

    Default extra space needs to be deleted
    Default extra space needs to be deleted!!!
  6. You should now see a line that looks like this:alias for sql server
  7. Rinse and repeat with the “Aliases” that appear under “SQL Native Client 10.0 Configuration.” This is the one actually used by the SharePoint installer and, probably, by the application itself. Note: to test the alias, I opened up SQL Server Management Studio and tried to make a new connection. It works only if you have an alias in the “SQL Native Client 10.0 Configuration (32bit)” section. When SharePoint tries to make a connection, it looks in the other “SQL Native Client 10.0 Configuration” section.
  8. You’re now ready to test it… Fire up Microsoft SQL Server Management Studio (If you have it installed… it should be if you have SQL Server Configuration Manager and followed the steps in the “Harden SQL Server for SharePoint environment” article). Note: SQL Server Management Studio will probably use the 32-bit alias, so if it’s not working tweak that one to troubleshoot.

    Connect to SQL
    Connect to SQL Server
  9. For “Server Name” use the name of your alias… ours is called “SharePoint_Alias” (without an extra space at the end)

    Connect to Server
    Connect to SQL Server
  10. If the previous step fails for whatever reason, try using “computerName,portNumber” instead of the alias name… For example, next to “Server name,” you would have “Server1,1433”. If that works, it means your server is, obviously, available on the network but there’s something wrong with the alias you’ve created (double-check that extra space).

Option 2: Create the alias with cliconfg.exe

  1. Log into the Application and/or front-end Web servers
  2. Got to Start > cliconfg.exe (Note: it’s c-l-i-c-o-n-f-g-dot-e-x-e)
    CliConfig
  3. Then click on “Alias”:
    Alias
    If you already have an alias set up, it will show up there. Otherwise…
  4. Click “Add”
    Add
  5. Specify the port number and give it a name. That’s it!

Test your SQL Alias:

To test your SQL alias, create a Microsoft Data Link (udl) file on your desktop:

  1. From your desktop, right click on the desktop and choose New > Text file
  2. A new file named New Text Document.txt appears in the directory.
  3. Rename this file to “TestDBConnection.udl”, removing all spaces. Be sure to change its file extension to .udl.
  4. Open the file and, under Connection, type either the SQL server name or, if you are testing an SQL alias, the alias name.
  5. Under 2, chose “Windows Authenticated security”
  6. Click “refresh”
  7. If the connection was successful, list of databases should appear in the dropdown menu under #3.

I have a separate blog post that covers the same steps for creating a udl file.

 

6 comments

  1. rob says:

    Followed these steps exactly. I try to connect using SQL Management studio, and it works with the named instance (serverinstance) but fails with the alias. Says “server was not found or is not accessible”. Any suggestions?

Leave a Reply

Your email address will not be published. Required fields are marked *


*