0

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"

Geoffrey
  • 273
  • 1
  • 4
  • 12
  • Sorry, I might be missing something obvious. What standardized config file are you trying to create? Is it an app config? With different SQL instance names the alias configuration is going to be different in each circumstance. The only way around that is to reinstall SQL using standardized instance names. Can you post the exact configuration you're using in one of the cases where you're getting the error? – squillman Apr 24 '13 at 19:38
  • What happens if you use SSMS to connect instead of config manager? – squillman Apr 24 '13 at 20:06
  • That's the error I get when I use SSMS to connect – Geoffrey Apr 24 '13 at 20:10

1 Answers1

2

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.

squillman
  • 37,883
  • 12
  • 92
  • 146
  • Worked! The 32-bit was the problem. – Geoffrey Apr 24 '13 at 20:35
  • Oh and we cannot use config transforms due to client environment restraints, we have to use whole file replacements, e.g., have a folder for each environment with complete config settings for that environment. – Geoffrey Apr 24 '13 at 20:36
  • @Geoffrey Glad it worked! Bummer about the transforms... Hacks like this and merge hell, that's what they prevent. Good luck! – squillman Apr 25 '13 at 13:14