Connecting to SQL Server 2005 on local machine via an alias in the hosts file

Don't know if it works for .NET but creating an Alias could be the solution
to your problem. See SQL Server Configuration Manager | SQL Native Client
Configuration | Alias. Using an alias is usually much better than fiddling
with the Host file but as I said, I never tried them with .NET.
Finally, from your connection string, I'm not sure if you are using the
Native Provider for SQL-Server 2005 instead of the older provider for
SQL-Server 2000. Maybe your problem with 127.0.0.1 comes from that. I also
don't understand why you are using the connection reset=false parameter.
This parameter should only be used when you are not using the connection
pooling.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sam" <samchurchill82@.gmail.com> wrote in message
news:45ea7082-6c27-4f12-85a8-0c33aec870d7@.b15g2000hsa.googlegroups.com...
> Hi,
> I work in a team of .NET developers who all have a local version of
> SQL Server 2005 on their PC for normal development projects. However,
> sometimes more than one of us has to work on the same server for
> development as we are sharing data. If we change the server name in
> the connection string in our application's config file from "(local)"
> to "SharedDevelopmentServer" and check it in to source control all of
> the other developers will get it and suddenly be using the shared
> server rather that their own local server. We got into the situation
> where the config file was being checked in and out every 15 minutes
> because one group wanted it to be "(local)" and the other group wanted
> it to be "SharedDevelopmentServer".
> I thought I had an elegant solution to this: make the server in the
> config file's connection string point to a non-existant server called
> "DevelopmentServer", then each developer could add a record into his
> HOSTS file to point this DNS alias to the IP address of the server
> that they want to use at that time. This works very well if the
> development server that you want to point to is not localhost but for
> some reason it gives the following error if you try to use your own IP
> address (or the loopback 127.0.0.1 address).
> Login failed for user ''. The user is not associated with a trusted
> SQL Server connection.
> It looks as if it's not passing through my credentials properly so
> it's not able to authenticate me. Our connection string is as
> follows:
> <add name="MyConnectionString" connectionString="data
> source=DevelopmentServer;database=MyDatabase;Integ rated
> Security=SSPI;application name=MyApplication;Connection Reset=false"
> providerName="System.Data.SqlClient" />
> Has anyone got any ideas how to resolve this or use another way of
> mapping to our selected development server which could be different
> for everyone in the team?
> Many thanks,
> Sam
"Sam" <samchurchill82@.gmail.com> wrote in message
news:595337f6-a7ae-4161-aad2-10d871991aac@.q77g2000hsh.googlegroups.com...
> On 11 Dec, 17:18, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
> Thanks, that works a treat. I guess any application trying to connect
> to an SQL server on my machine must be going via this before it hits
> DNS.
To my knowledge, aliases are only for the client side; the server never sees
them; so the answer would be yes: any application willing to use an alias
must set it up on its side. Notice that you can also use a TCP/IP address
(like 127.0.0.1) as an alias; so they are definitely looked up before the
DNS servers.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)