Trouble with redirect alias name in SQL 2008 R2

sql-server-2008-r2

I'm working with a team of developers who have SQL instances installed in different places. For the purposes of having one config file for our local machines (instead of having a config file for each machines) we're trying to deal with the SQL installed with instance names, no instance names, etc:

(local)\SQLEXPRESS

(local)\

(local)\personName

I looked at this guide to setting up alias redirects but when we create the same alias name, say "MyOldServer" I get errors on connecting to an untrusted domain using Windows authentication.

Short of reinstalling SQL to the same place on all these machines? How can we make it so that they're all pointing to the same name (preferably (local) but I don't care either way.

Edit: My connection string (working)

<add name="masterDb" connectionString="integrated security=true;Data Source=(local);Database=masterDb" />

Colleague's connection string (also working on their local machine)

<add name="masterDb" connectionString="integrated security=true;Data Source=(local)\local;Database=masterDb" />

We would like to use one or the other so we have one local config checked into source. I'm okay with changing both our connection strings … but I'd like us to have the same connection string without installing SQL if possible. We're onloading more developers and having them all reinstall SQL to conform will be cumbersome.

Alias configuration (my machine):

Alias Name: TestAlias
Port no: blank
Portocol: TCP/IP
Server: localhost

If I added TestAlias to my hosts file, and try to connect (using SQL configuration manager) to Server name: testalias, and windows authentication, I get "Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication"

Best Answer

First, make sure the SQL Browser service is running on your machine. It's required for aliases to work.

Second, if you're in a 64-bit environment make sure you are adding the alias to both the 32bit and 64bit SQL Native Client config. SSMS uses the 32-bit client.

You shouldn't need to make dns or host file changes so long as the target server you use in the alias configuration is resolvable by the client. If using localhost as the server for your alias is not working, try using (local) instead (although localhost should work just fine).

On a side note, if you use a build automation tool like rake, psake, nant, etc. then you can set the build file to generate the app.config / web.config for you from a template based on a local configuration settings. The template for the local settings file is checked into source control, along with the settings files for other environments (production, QS, staging, etc). Then it's up to the local dev to maintain their settings file. When they're ready to test they run the build which generates the config files. The config template is stored in source control but not the actual config file itself. You can then make use of automated build / continuous integration servers such as TeamCity and CruiseControl.NET to generate builds for different environments. If you're not doing this, it's something you might want to look into. All of the build tools I mentioned are free and open source. CruiseControl.NET is free and open source. TeamCity is a commercial product from JetBrains, but there is a free option for it for smaller build environments.

Related Topic