14

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.

  • 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 1:21
23

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.

  • 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
  • 5
    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 4 '14 at 1: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 7 '15 at 18:11
  • 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 5 '17 at 12:28

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.