18

I have developer edition from SQL Server 2008, but now I have a piece of software with a hardcoded connection string:

Data Source=.\SQLEXPRESS;Initial Catalog=db;User Id=sa;Password=1234;

Now I tried adding an Alias in the configuration tool to SQLEXPRESS, but it just doesn't want to use this new alias.

I enabled TCP/IP and restarted the service.

schellack
  • 1,143
  • 10
  • 9
Roger Far
  • 341
  • 5
  • 17
  • Do you know what type of application it is? Is it a .NET application in which case you have an app.config or web.config file? – squillman Feb 17 '11 at 13:17
  • Hardcoding the connection string (including username and password)? **WHY** do people keep doing such awful things?!? – Massimo Mar 19 '14 at 01:21

1 Answers1

29

Follow these steps to add an alias:

  1. Open SQL Server Configuration Manager
  2. Expand SQL Native Client Configuration
  3. Right-click Aliases and select New Alias...
  4. On the New Alias window:
    • Enter .\SQLEXPRESS for the Alias Name.
    • Select the appropriate protocol (e.g., Named Pipes)
    • Enter . for the server name (that's equivalent to localhost).
  5. Click on OK to make the change.

Changing the HOSTS file to include a setting for SQLEXPRESS will only redirect DNS lookups for a machine called SQLEXPRESS to the specified IP address. That will not cause .\SQLEXPRESS to resolve to the localhost SQL instance.

schellack
  • 1,143
  • 10
  • 9
  • 1
    Great answer! I was just about to start using explicit port numbers and so on when I happened upon this answer. This solved my problems perfectly. Thank you! :) – wasatz Oct 29 '13 at 13:50
  • 8
    If you are using 64-bit Windows you might need to create the alias in both the 32-bit and 64-bit SQL Native Client Configuration. – Tod Thomson Jun 04 '14 at 01:49
  • I found my issue was with trying to use TCP, changing the alias to use named pipes made it work. – BlackICE Sep 11 '14 at 15:15
  • 2
    If it helps anybody else, I had to use the following Pipe Name for the `.\SQLEXPRESS` alias to work with my named dev instance: `\\.\pipe\MSSQL$STOLLEDEV\sql\query` – JustinStolle Apr 07 '15 at 18:11
  • 1
    In addition to the above advice I also added localhost\SQLEXPRESS as an alias gets treated explicitly. I also added both aliases to both my 32bit and 64bit configuration just for belt and braces. – The Senator Dec 05 '17 at 12:28